SQL Injection: How It Works and How to Stop It

Why SQL Injection Still Exists

SQL injection was first documented in 1998. It has appeared on every OWASP Top 10 list since the list's inception. It has been responsible for some of the largest data breaches in history — TalkTalk (4 million records), Heartland Payment Systems (130 million card numbers), and countless others.

The reason it persists is not complexity. SQL injection is simple to understand and, critically, simple to prevent. It persists because developers concatenate user input into SQL queries without thinking about the consequences, often under time pressure and without security training.

This tutorial shows exactly how it works and the specific techniques that eliminate it.

How a SQL Query Normally Works

Consider a login form. The application takes a username and password and checks them against the database:

SELECT * FROM users WHERE username = 'alice' AND password = 'secretpass';

If the query returns a row, the user is authenticated. This is normal and expected behavior.

The vulnerability arises when a developer constructs this query by concatenating strings directly from user input:

# Vulnerable Python code
query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "';"

The assumption is that users will supply normal values. Attackers do not.

The Attack: Breaking Out of the String

SQL uses single quotes to delimit string values. If an attacker supplies a username containing a single quote, they can break out of the intended string and inject their own SQL.

Attacker's input:

• Username: ' OR '1'='1
• Password: anything

Resulting query:

SELECT * FROM users WHERE username = '' OR '1'='1' AND password = 'anything';

Because OR '1'='1' is always true, this query returns all users. The login succeeds for the first user in the database, typically an administrator.

Bypassing password check entirely:

• Username: admin'--
• The -- is a SQL comment; everything after it is ignored

SELECT * FROM users WHERE username = 'admin'--' AND password = 'anything';
-- Becomes:
SELECT * FROM users WHERE username = 'admin';

Authentication is bypassed. The password field is never checked.

Beyond Authentication: Extracting Data

SQL injection is not limited to authentication bypass. More serious attacks extract data from the entire database.

UNION-Based Injection

The SQL UNION operator combines results of two SELECT statements. An attacker can use it to retrieve data from other tables:

-- Original query
SELECT name, description FROM products WHERE id = 1;

-- Injected
SELECT name, description FROM products WHERE id = 1
UNION SELECT username, password FROM users--;

This appends all usernames and passwords to the product results returned to the attacker.

Blind SQL Injection

Many applications do not display database errors or query results to users. Blind SQL injection extracts data one bit at a time by asking true/false questions:

-- Is the first character of the admin password 'a'?
SELECT * FROM users WHERE id = 1 AND SUBSTRING(password, 1, 1) = 'a';

If the page behaves differently when the condition is true versus false, the attacker can infer the answer. Automated tools like sqlmap can extract entire databases through blind injection in minutes.

Out-of-Band and Stacked Queries

On some database systems, attackers can:

• Execute multiple statements (stacked queries): drop tables, create backdoor accounts
• Read files from the server filesystem (LOAD_FILE() in MySQL)
• Write files to the server (potentially creating a web shell)
• Trigger network connections to exfiltrate data out-of-band

SQL injection in the worst case means complete server compromise, not just data exposure.

The Defenses That Actually Work

Defense 1: Parameterized Queries (Prepared Statements)

This is the primary defense and it eliminates SQL injection, not merely reduces it.

With a parameterized query, SQL code and data are sent to the database separately. The database compiles the SQL template first, then binds the user-supplied values. The values can never be interpreted as SQL — they are always treated as data.

Python with parameterized query:

# Safe
cursor.execute(
    "SELECT * FROM users WHERE username = %s AND password = %s",
    (username, password)
)

Java with PreparedStatement:

PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username = ? AND password = ?"
);
stmt.setString(1, username);
stmt.setString(2, password);

No matter what the user puts in the username field — including ' OR '1'='1 — it is treated as a literal string value, not SQL. The injection is impossible.

Defense 2: ORMs and Query Builders

Object-Relational Mappers (ORMs) like SQLAlchemy, Hibernate, ActiveRecord, and Django ORM use parameterized queries internally. Using an ORM correctly prevents SQL injection in almost all cases:

# Django ORM — safe
User.objects.filter(username=username, password=password)

Caution: most ORMs have escape hatches for raw SQL (raw(), execute()) that reintroduce injection risk if user input is concatenated. Treat raw SQL methods with the same care as manual query construction.

Defense 3: Stored Procedures

Stored procedures can be safe if implemented correctly — they must use parameterized inputs internally. A stored procedure that concatenates strings internally is still vulnerable.

Defense 4: Principle of Least Privilege

The database account used by the web application should have only the permissions it needs:

• A read-heavy application needs only SELECT
• No application should run as root, sa, or DBA
• Different application components can use different database accounts with different permissions

This limits the impact of successful injection: an attacker who can only SELECT cannot drop tables or write files.

Defense 5: Input Validation

Validate that inputs match their expected format:

• A user ID should be a positive integer — reject anything else before it reaches the query
• A product name should not contain SQL keywords — allowlist expected characters

Input validation is a defense in depth measure, not a primary control. It reduces attack surface but should not be the only protection.

Defense 6: WAF (Web Application Firewall)

A WAF can detect and block common SQL injection patterns. It is an additional layer, not a substitute for parameterized queries. WAFs can be bypassed with obfuscation and encoding tricks. Do not rely on them as your only defense.

Testing for SQL Injection

Manual testing basics:

1. Supply a single quote (') in every input field — database error messages often reveal injection points
2. Try 1=1 and 1=2 in numeric parameters and compare responses
3. Try '; SELECT SLEEP(5);-- — if the response delays, blind injection is present

Automated tools:

• sqlmap — the standard tool for detection and exploitation (only against systems you are authorized to test)
• Burp Suite — intercept and manipulate requests; has a SQL injection scanner in Pro edition
• OWASP ZAP — free scanner with SQL injection detection

Remediation Checklist

• Replace all string concatenation in database queries with parameterized queries
• Audit all raw SQL calls in ORM code
• Apply least-privilege database accounts
• Enable database error suppression in production (errors expose schema information)
• Review and test stored procedures
• Add WAF rules as additional layer
• Run sqlmap or equivalent against staging environment before each release

SQL injection has a complete, well-understood fix. There is no reason for it to appear in new code. The only barrier is awareness and discipline during development.