SQL Injection for Scummies

SQL injection remains one of the most critical web application security vulnerabilities, consistently ranking in the OWASP Top 10. Despite being well-known for decades, SQL injection attacks continue to plague web applications worldwide, causing data breaches, financial losses, and compromised user privacy.

In this comprehensive guide, we’ll explore everything you need to know about SQL injection - from understanding the basic concepts to advanced exploitation techniques and, most importantly, how to prevent these attacks.

What is SQL Injection?

SQL injection is a code injection technique that exploits vulnerabilities in an application’s database layer. It occurs when user input is not properly validated, sanitized, or parameterized, allowing attackers to inject malicious SQL code into database queries.

When successful, SQL injection attacks can allow attackers to:

• View sensitive data that they shouldn’t have access to
• Modify or delete database records
• Execute administrative operations on the database
• In some cases, execute commands on the underlying operating system

Types of SQL Injection Attacks

Understanding the different types of SQL injection attacks is crucial for both offensive and defensive security. Let’s explore the main categories:

1. In-Band SQL Injection (Classic)

In-band SQL injection is the most common and straightforward type of SQL injection attack. In this method:

• The attacker uses the same communication channel to both launch the attack and gather results
• Retrieved data is presented directly in the application’s web page
• This type is comparatively easier to exploit than other methods

There are two main sub-types of in-band SQL injection:

Error-Based SQL Injection

Error-based SQL injection relies on error messages thrown by the database server to obtain information about the structure of the database. Here’s how it works:

• The attacker forces the application to generate database errors
• These error messages reveal valuable information about the database structure
• Attacks are then refined based on the information gathered from these errors

Example:

URL: suhesh.com.np/app.php?id='
Error Response: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version..."

This error message confirms that the application is vulnerable to SQL injection and reveals that it’s using a MySQL database.

Union-Based SQL Injection

Union-based SQL injection leverages the UNION SQL operator to combine the results of two or more SELECT statements into a single result set. This technique allows attackers to extract data from other tables within the database.

Example:

URL: suhesh.com.np/app.php?id=' UNION SELECT username, password FROM users--

Potential Response:

carlos
pufpowjoasnxcano
administrator
opgq9u3fw9ejoajsda

This response reveals usernames and passwords from the users table, demonstrating how union-based attacks can expose sensitive information.

2. Inferential (Blind) SQL Injection

Blind SQL injection is a more sophisticated attack method where:

• No data is directly transferred via the web application
• Attackers reconstruct information by sending specific requests and observing the application’s behavior
• Despite being “blind,” these attacks can be just as dangerous as in-band SQL injection
• They typically take longer to exploit due to the iterative nature of the attack

There are two main types of blind SQL injection:

Boolean-Based Blind SQL Injection

Boolean-based blind SQL injection relies on sending SQL queries to the database and observing the application’s response to determine whether the query returned TRUE or FALSE.

Basic Example:

URL: suhesh.com.np/app.php?id=1
Backend Query: SELECT title FROM product WHERE id=1

Payload 1 (FALSE): suhesh.com.np/app.php?id=1 AND 1=2
Result: No title displayed (FALSE condition)

Payload 2 (TRUE): suhesh.com.np/app.php?id=1 AND 1=1
Result: Title displayed (TRUE condition)

This confirms the presence of a blind SQL injection vulnerability.

Advanced Example - Data Extraction:

Let’s say we want to extract the admin password from this users table:

PAYLOAD: suhesh.com.np/app.php?id=1 AND SUBSTRING((SELECT Password FROM Users WHERE Username="admin"),1,1)="a"
BACKEND QUERY: SELECT title FROM product WHERE id=1 AND SUBSTRING((SELECT Password FROM Users WHERE Username="admin"),1,1)="a"

This query checks if the first character of the admin’s password is “a”. Since it’s not, no title is displayed.

PAYLOAD: suhesh.com.np/app.php?id=1 AND SUBSTRING((SELECT Password FROM Users WHERE Username="admin"),1,1)="d"
BACKEND QUERY: SELECT title FROM product WHERE id=1 AND SUBSTRING((SELECT Password FROM Users WHERE Username="admin"),1,1)="d"

Since the first character of the password is “d”, the title will be displayed, confirming our guess. This process can be automated to extract the entire password character by character.

Time-Based Blind SQL Injection

Time-based blind SQL injection is a technique that relies on the database pausing for a specified amount of time to infer information about the database structure and content.

How it works:

• The attacker sends a query that triggers a time delay if a certain condition is true
• By measuring the response time, the attacker can determine whether the condition was true or false
• This method is particularly useful when the application doesn’t show any visible differences in response content

Example:

URL: suhesh.com.np/app.php?id=1 AND IF(SUBSTRING((SELECT Password FROM Users WHERE Username="admin"),1,1)="a", SLEEP(10), 0)

If the first character of the admin’s password is “a”, the database will pause for 10 seconds before responding. If the response takes 10 seconds, we know the first character is “a”; otherwise, it’s not.

3. Out-of-Band (OAST) SQL Injection

Out-of-band SQL injection is the least common type of SQL injection attack, but it can be extremely powerful when other methods fail.

Characteristics:

• Involves triggering an out-of-band network connection to a system controlled by the attacker
• Used when the attacker cannot use the same channel to launch the attack and gather results
• Can utilize various protocols, with HTTP and DNS being the most common

When to use:

• When in-band techniques don’t work due to application limitations
• When the application doesn’t return database errors or visible changes
• When time-based techniques are unreliable due to network latency

How to Detect SQL Injection Vulnerabilities

Detecting SQL injection vulnerabilities requires a systematic approach. Here are the two main testing methodologies:

Black Box Testing

Black box testing involves testing the application without access to the source code. Here’s a step-by-step approach:

1. Application Mapping

• Use tools like Burp Suite with proxy enabled to map all application endpoints
• Identify all input fields, parameters, and data entry points
• Document all forms, URL parameters, and API endpoints

2. Input Fuzzing

Test each input point with SQL-specific characters and payloads:

Basic Character Testing:

• Submit single quotes ('), double quotes ("), hash symbols (#), and comment sequences (--)
• Observe the application’s response for errors or anomalies

Boolean Condition Testing:

• Submit conditions like OR 1=1 and OR 1=2
• Compare responses to identify behavioral differences

Time Delay Testing:

• Submit payloads designed to trigger time delays
• Monitor response times to detect time-based vulnerabilities

Out-of-Band Testing:

• Submit OAST (Out-of-Application Security Testing) payloads
• Use tools like Burp Collaborator to detect out-of-band interactions

White Box Testing

White box testing involves examining the application’s source code and infrastructure:

1. Enable Comprehensive Logging

• Web Server Logging: Enable detailed logging to capture SQL errors and anomalies
• Database Logging: Configure database logging to see which queries and characters reach the database layer

2. Application Mapping and Code Review

• Functionality Mapping: Document all visible application features and data flows
• Code Analysis: Use regex searches to find all instances where the application interacts with the database
• Code Path Analysis: Follow the complete data flow from user input to database query execution

3. Systematic Testing

• Test each identified input point with SQL injection payloads
• Verify that input validation and sanitization are properly implemented

SQL Injection Exploitation Techniques

Once you’ve identified a SQL injection vulnerability, the next step is exploitation. Here are the main exploitation techniques for each type:

Error-Based Exploitation

Error-based exploitation leverages database error messages to extract information:

1. Character Testing: Submit SQL-specific characters to trigger errors
2. Error Analysis: Different characters generate different types of errors, revealing database information
3. Information Gathering: Use error messages to understand database structure and syntax

Union-Based Exploitation

Union-based exploitation requires careful preparation and follows these key principles:

Prerequisites:

• The number and order of columns must be the same in all queries
• Data types must be compatible between the original query and the injected query

Step-by-Step Exploitation Process:

1. Determine the Number of Columns

Method 1: Using ORDER BY clause

Original: SELECT title, cost FROM product WHERE id=1
Test: SELECT title, cost FROM product WHERE id=1 ORDER BY 1--
Test: SELECT title, cost FROM product WHERE id=1 ORDER BY 2--
Test: SELECT title, cost FROM product WHERE id=1 ORDER BY 3--

Continue incrementing until you get an error like “Position 3 is out of range”

Method 2: Using NULL values

Test: ' UNION SELECT NULL--
Error: "All queries combined using UNION must have equal number of expressions"

Test: ' UNION SELECT NULL, NULL--
Success: No error (indicates 2 columns)

2. Determine Data Types

Test whether columns can hold string data:

Test: ' UNION SELECT 'a', NULL--
Error: "Conversion failed converting varchar value 'a' to datatype int"
(This tells us the first column expects an integer)

Test: ' UNION SELECT NULL, 'a'--
Success: (This tells us the second column accepts strings)

3. Extract Data

Payload: ' UNION SELECT username, password FROM users--

Blind SQL Injection Exploitation

Boolean-Based Exploitation

1. Submit a Boolean expression that evaluates to FALSE, then one that evaluates to TRUE
2. Note the differences in application response
3. Automate the process to ask the database a series of TRUE/FALSE questions
4. Gradually extract data character by character

Time-Based Exploitation

1. Submit payloads that cause the application to pause for a specific time period
2. Measure response times to determine if conditions are true or false
3. Automate the process to systematically extract data

Out-of-Band Exploitation

1. Submit OAST payloads designed to trigger out-of-band network connections
2. Use various methods (HTTP, DNS) to exfiltrate data
3. Monitor external systems for incoming connections containing extracted data

Warning Take care when injecting the condition OR 1=1 into a SQL query. Even if it appears to be harmless in the context you’re injecting into, it’s common for applications to use data from a single request in multiple different queries. If your condition reaches an UPDATE or DELETE statement, for example, it can result in an accidental loss of data.

Automated Exploitation Tools

Several tools can help automate SQL injection testing and exploitation:

1. SQLMap

SQLMap is the most popular and comprehensive SQL injection testing tool:

• Automatic detection and exploitation of SQL injection vulnerabilities
• Support for multiple database management systems
• Advanced features like database fingerprinting and data extraction
• Built-in tamper scripts to bypass various protection mechanisms

2. Web Application Vulnerability Scanners

• Burp Suite: Professional web application security testing platform
• OWASP ZAP: Free and open-source web application security scanner
• Acunetix: Commercial web vulnerability scanner
• Wapiti: Open-source web application vulnerability scanner
• Arachni: Web application security scanner framework
• w3af: Web application attack and audit framework

SQL Injection Prevention

Preventing SQL injection requires a multi-layered approach. Here are the most effective defense strategies:

Primary Defenses

1. Use Prepared Statements (Parameterized Queries) ⭐ MOST IMPORTANT

Prepared statements are the most effective defense against SQL injection:

-- Vulnerable code
String query = "SELECT * FROM users WHERE username = '" + username + "'";

-- Secure code using prepared statements
String query = "SELECT * FROM users WHERE username = ?";
PreparedStatement pstmt = connection.prepareStatement(query);
pstmt.setString(1, username);

How it works:

• The application specifies the query structure with placeholders for user input
• User input is treated as data, never as executable code
• The database engine separates SQL logic from data

2. Use Stored Procedures (When Implemented Correctly)

Stored procedures can provide protection when implemented properly:

• Group SQL statements together and store them in the database
• Important: Must still be called in a parameterized way to be effective
• Not a complete solution on their own

3. Whitelist Input Validation

Define exactly what input values are authorized:

• Everything else is considered unauthorized and rejected
• Particularly useful for values that cannot be parameterized (like table names)
• Should be used as a secondary defense, not the primary one

4. Escaping User Input (Last Resort Only)

• Should only be used when other methods aren’t feasible
• Database-specific escaping functions must be used
• Error-prone and not recommended as a primary defense

Additional Defenses

1. Enforce Least Privilege

• Use database accounts with minimal necessary permissions
• Remove or disable unnecessary default database functionality
• Apply CIS (Center for Internet Security) benchmarks for your database system
• Separate database accounts for different application functions

2. Defense in Depth

• Implement multiple layers of security controls
• Use whitelist input validation as a secondary defense
• Regular security testing and code reviews
• Web Application Firewalls (WAF) as an additional layer

Additional Resources

Diving deeper?…. here are some excellent resources:

Learning Platforms

1. PortSwigger Web Security Academy - SQL Injection - Comprehensive interactive labs
2. OWASP WebGoat - Hands-on practice environment
3. SQLi Labs - Practice SQL injection on vulnerable applications

Documentation and Guides

4. OWASP - SQL Injection - Official OWASP documentation
5. OWASP SQL Injection Prevention Cheat Sheet - Developer-focused prevention guide
6. PentestMonkey SQL Injection Cheat Sheet - Payload reference

Books

7. The Web Application Hacker’s Handbook - Chapter 9: Attacking Data Stores
8. The Tangled Web by Michal Zalewski - Advanced web security concepts

Tools and Practice

9. SQLMap Documentation - The definitive SQL injection testing tool
10. Burp Suite - Professional web application security testing