SQL Injection: Error-Based Attacks

TL;DR

Error-based SQL injection attacks happen when a website shows you database errors instead of hiding them. These errors give attackers clues about the database structure, letting them figure out how to inject malicious SQL code and steal or change data.

What is Error-Based SQL Injection?

Normally, websites are designed to show friendly error messages if something goes wrong (like a ‘page not found’ message). But sometimes, developers don’t properly handle errors. Instead of a nice message, the website displays detailed database error information. This is bad because:

• Reveals Information: Error messages can tell attackers what type of database is being used (MySQL, PostgreSQL, etc.).
• Exposes Structure: They might show table names, column names, and even parts of the SQL query itself.

Attackers use this information to craft specific SQL injection payloads that exploit vulnerabilities in the website’s code.

How Error-Based Attacks Work – Step by Step

1. Find an Input Field: The attacker looks for places on the website where they can enter data – search boxes, login forms, comment sections, etc.
2. Trigger Errors: They try entering unusual input that’s likely to cause a database error. Common techniques include:
   • Single quotes (‘) – often breaks string literals in SQL queries.
   • Double dashes (–) – comments out the rest of the query, potentially revealing earlier parts.
   • Invalid characters or data types.
3. Analyze Error Messages: The attacker carefully examines any error messages that appear. These messages are their key source of information.
4. Craft Payloads: Based on the error messages, they build SQL injection code to extract data or modify the database.

Example Scenario

Let’s say a website has a search box that uses this SQL query (simplified):

SELECT * FROM products WHERE name = '$search_term';

If the attacker enters ‘ into the search box, the query might become:

SELECT * FROM products WHERE name = '';

The database will likely throw an error because of the unmatched quote. The error message might reveal that the database is MySQL and show part of the original query.

Common Error Messages & What They Tell You

• MySQL: “You have an error in your SQL syntax…” – Indicates a MySQL database, often with details about the incorrect syntax.
• PostgreSQL: “syntax error at or near…” – Similar to MySQL, revealing PostgreSQL specifics.
• Microsoft SQL Server: “Incorrect syntax near…” – Points to MSSQL and provides information on the problematic part of the query.

Exploitation Techniques

1. Union-Based Injection: If the attacker knows the number of columns in the result set, they can use UNION SELECT to combine their malicious query with the original one and extract data.

   ' UNION SELECT version(), database(), user() -- '

2. Subqueries: Attackers might try injecting subqueries to execute arbitrary SQL commands.

   ' AND (SELECT 1 FROM (SELECT SLEEP(5)) AS a) -- '

3. Error-Based Boolean Logic: Using conditions that cause different errors based on the truth value of a statement. This allows them to extract data bit by bit.

How to Prevent Error-Based SQL Injection

• Never Show Raw Errors: Configure your website and database to log errors securely but never display them directly to users.
• Prepared Statements (Parameterized Queries): Use prepared statements with placeholders for user input. This prevents the database from interpreting user input as part of the SQL command.

  $stmt = $pdo->prepare("SELECT * FROM products WHERE name = ?");

  $stmt->execute([$search_term]);

• Input Validation: Sanitize and validate all user input. Check data types, lengths, and allowed characters.
• Least Privilege Principle: Grant database users only the minimum necessary permissions.
• Web Application Firewall (WAF): Use a WAF to detect and block common SQL injection attacks.