SQL Injection

Common and dangerous web security vulnerabilities

Introduction

SQL Injection is one of the most common and dangerous web security vulnerabilities. It occurs when an attacker manipulates a web application's SQL queries by injecting malicious SQL code through user inputs. This vulnerability allows attackers to view, modify, or delete database data, bypass authentication, and in some cases, take control of the server.

This section covers the concept of SQL Injection, types of attacks, potential impacts, and how to prevent them using various techniques.

Understanding SQL Injection

What is SQL Injection?

SQL Injection happens when untrusted data (usually user input) is included in a SQL query without proper validation or escaping, allowing the attacker to manipulate the query's structure and potentially execute arbitrary SQL commands.

Example of Vulnerable Code:

Here’s a basic example of an SQL query vulnerable to injection:

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

If the above query is built using string concatenation based on user input, it becomes vulnerable:

query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

If the attacker inputs admin' -- as the username, the query becomes:

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

The -- is a comment symbol in SQL, so everything after it is ignored. The query becomes:

SELECT * FROM users WHERE username = 'admin';

This bypasses password validation, potentially granting unauthorized access.

Types of SQL Injection

  1. In-Band SQL Injection (Classic):

    • Error-Based: The attacker uses database error messages to deduce the database structure.
    • Union-Based: The attacker uses the UNION SQL operator to combine the results of two queries, enabling the retrieval of additional data.

  2. Blind SQL Injection:

    • The web application does not show errors, but attackers can still infer details based on the application’s behavior.
    • Boolean-Based: The attacker injects queries that evaluate to true or false and observes the application’s responses.
    • Time-Based: The attacker injects a query that makes the database wait (e.g., SLEEP()) and detects delays in the response.

  3. Out-of-Band SQL Injection:

    • The attacker triggers the database to communicate with an external server, such as exfiltrating data via HTTP or DNS.

Impact of SQL Injection

  • Data Theft: Attackers can read sensitive information such as user credentials, credit card numbers, etc.
  • Data Manipulation: Attackers can modify or delete records, disrupting business operations.
  • Authentication Bypass: Attackers can log in as any user, including admins.
  • Denial of Service (DoS): Attackers can overload the database, causing application crashes.
  • Full System Takeover: In some cases, attackers can execute shell commands and take control of the database server.

SQL Injection Prevention Techniques

  1. Parameterized Queries (Prepared Statements): The best way to avoid SQL Injection is by using parameterized queries (or prepared statements) instead of directly concatenating user inputs into SQL queries.

    Example of a Secure Query (using parameterized statements):

    query = "SELECT * FROM users WHERE username = ? AND password = ?";
prepared_statement = connection.prepareStatement(query);
prepared_statement.setString(1, username);
prepared_statement.setString(2, password);

    This ensures that user input is treated as data, not executable code.

  2. Input Validation and Sanitization:

    • Validate all inputs, ensuring they match expected formats (e.g., numbers, email addresses).
    • Reject or sanitize potentially harmful characters like quotes (', ") and semicolons (;).

  3. Escaping User Inputs:

    • Escape characters in user inputs to prevent them from altering SQL queries.

  4. Use of ORM (Object-Relational Mapping) Frameworks: ORMs like Hibernate, Entity Framework, etc., provide abstraction layers that help avoid direct SQL writing, reducing the risk of injection.

  5. Database Permissions:

    • Limit database user permissions. The application should only have the minimum required access (e.g., the app user should not have admin privileges).
    • Use different database users for different roles, limiting access to sensitive tables.

  6. Stored Procedures: Using stored procedures can add an extra layer of abstraction, making SQL injection harder, although it's not a foolproof solution if inputs are not handled properly.

    Example:

    CREATE PROCEDURE sp_getUser
    @username NVARCHAR(50),
    @password NVARCHAR(50)
AS
BEGIN
    SELECT * FROM users WHERE username = @username AND password = @password;
END

  7. Error Handling:

    • Never expose detailed error messages to users. Database errors should be logged, but the output to users should be generic.
    • Example: Instead of showing a detailed SQL error, return a message like "An error occurred, please try again."

  8. Web Application Firewalls (WAFs): A WAF can detect and block common SQL injection patterns at the network level.

Example: SQL Injection Attack on a Customer Management System

Let’s consider a simple customer management system where the application allows a user to view customer details by entering a customer ID:

SELECT * FROM customers WHERE customer_id = '123';

If this query is built using unsanitized user input, the system is vulnerable. An attacker could input:

123 OR 1=1;

The resulting query becomes:

SELECT * FROM customers WHERE customer_id = '123' OR 1=1;

This would return all customers in the database, as 1=1 is always true.

Prevention Using Parameterized Queries:

SELECT * FROM customers WHERE customer_id = ?;

In this case, no matter what the user inputs, the input is treated as a literal value, not as part of the query logic.

Key Takeaways

  1. SQL Injection allows attackers to manipulate SQL queries by injecting malicious input.
  2. Prepared Statements are the most effective way to prevent SQL injection.
  3. Always validate and sanitize user input to ensure it conforms to expected formats.
  4. Never expose detailed error messages to users; use error handling to protect internal system details.
  5. Database permissions should follow the principle of least privilege to minimize risk.

Conclusion

SQL Injection is a critical security vulnerability that can have disastrous consequences for applications and databases. The key to preventing SQL injection lies in ensuring that user inputs are never directly included in SQL queries. By using parameterized queries, proper input validation, escaping user inputs, and applying the principle of least privilege, developers can significantly reduce the risk of SQL injection in their applications.

Asked in Interview

  • What is SQL Injection?
  • How does SQL Injection work, and can you give an example?
  • What are some methods to prevent SQL Injection?
  • How do Prepared Statements prevent SQL Injection?
  • How do you test if an application is vulnerable to SQL Injection?
  • Can you describe a real-world scenario where SQL Injection was exploited?
  • If a vulnerability is detected, how would you go about fixing it?