What is a SQL injection (SQLi) attack ?

A SQL injection (SQLi) attack is a type of cybersecurity vulnerability that allows attackers to interfere with the queries that an application makes to its database. It typically occurs when an application improperly handles user-provided input, allowing malicious SQL statements to be executed on the database. This can result in unauthorized access to or manipulation of the database, including reading sensitive data, modifying data, or even deleting entire tables.

How SQL Injection Works

The root cause of SQL injection is the failure to sanitize or validate input provided by users. For example, if a web application takes a username and password as input and constructs an SQL query like this:

SELECT * FROM users WHERE username = 'user_input' AND password = 'user_input';

An attacker could input something like:

  • Username: admin
  • Password: ' OR '1'='1

The resulting query would become:

SELECT * FROM users WHERE username = 'admin' AND password = '' OR '1'='1';

Since '1'='1' is always true, the query returns all rows in the users table, potentially granting unauthorized access.

Common Types of SQL Injection

  1. Classic SQL Injection: Exploits poorly sanitized inputs to execute malicious SQL.
  2. Blind SQL Injection: The attacker infers information from the application’s behavior or output, such as error messages or response times, without directly seeing the results of the query.
  3. Error-Based SQL Injection: Relies on database error messages to gain information about the database structure.
  4. Union-Based SQL Injection: Combines malicious and legitimate queries to extract data.
  5. Time-Based SQL Injection: Delays the database response to infer information based on the time it takes.

Potential Consequences

  • Unauthorized data access (e.g., viewing confidential user information).
  • Data manipulation or deletion.
  • Full control of the database server in severe cases.
  • Compromise of system integrity and security.

How to Prevent SQL Injection

  1. Use Prepared Statements (Parameterized Queries):
    • Use placeholders instead of directly concatenating user inputs.
    cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))
  2. Input Validation:
    • Validate and sanitize user inputs to ensure they meet expected formats.
  3. Use Stored Procedures:
    • Encapsulate queries in stored procedures to reduce exposure to SQL injection.
  4. Limit Database Privileges:
    • Follow the principle of least privilege to restrict access.
  5. Use Web Application Firewalls (WAFs):
    • Deploy WAFs to detect and block malicious queries.
  6. Avoid Displaying Detailed Error Messages:
    • Prevent error messages from leaking database details.

Proper awareness and implementation of these countermeasures can significantly reduce the risk of SQL injection.