SQL Injection Prevention — How to Write Safe Queries

1. How SQL Injection Works — the ' OR '1'='1 Demo

Start with an ordinary login lookup. The application takes an email address from a form and runs:

SELECT id, email, password_hash
FROM users
WHERE email = 'user@example.com';

A normal user submits user@example.com and the database treats everything between the two single quotes as one string literal. Now the attacker submits this as the email:

' OR '1'='1' --

If the application builds the SQL by string concatenation, the statement that actually reaches the parser becomes:

SELECT id, email, password_hash
FROM users
WHERE email = '' OR '1'='1' --' ;

Three things just happened, and they are the whole attack:

• The literal was closed early. The attacker's leading ' ends the string that was supposed to contain the email, so the text after it is no longer data — it is SQL.
• A tautology was injected. '1'='1' is always true, so the WHERE clause matches every row instead of one. The login check that expected zero or one row now returns the entire user table.
• The rest was commented out. -- turns the trailing ' AND password = ... into a comment, so the password condition never runs. In MySQL a -- comment needs a trailing space, which is why you usually see -- in payloads; PostgreSQL and SQL Server also accept -- and block comments.

The same mechanism scales from a login bypass to full data theft. Because the injected text can contain UNION SELECT, an attacker can append a second result set and read arbitrary tables:

' UNION SELECT username, password_hash, NULL FROM admin_users -- 

And where stacked statements are enabled (common on MySQL/MariaDB drivers and SQL Server), the payload can stop being a query at all:

'; DROP TABLE audit_log; -- 

The reason this works is not that the database is broken. The parser is doing exactly what it was told: it received one string, and it cannot tell which characters the developer intended as code and which the user supplied as data. That distinction is the entire fix, and it is what parameterized queries restore.

2. Why String Concatenation Is the Real Bug

Look at the vulnerable login check in three common stacks. The APIs differ, the languages differ, and the bug is identical: a query string assembled from a prefix, untrusted input, and a suffix.

Python — vulnerable

email = request.form["email"]
password = request.form["password"]

sql = "SELECT id FROM users WHERE email = '" + email + "' AND password = '" + password + "'"
cursor.execute(sql)          # <<< injection lives here

# Payload:   ' OR '1'='1' --
# Becomes:   SELECT id FROM users WHERE email = '' OR '1'='1' --' AND password = ''

PHP — vulnerable

$email = $_POST['email'];
$password = $_POST['password'];

$sql = "SELECT id FROM users WHERE email = '$email' AND password = '$password'";
$result = mysqli_query($conn, $sql);   // <<< same flaw, different syntax

Java — vulnerable

String email = request.getParameter("email");
String password = request.getParameter("password");

String sql = "SELECT id FROM users WHERE email = '" + email + "' AND password = '" + password + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);   // <<< concatenated SQL

The language is irrelevant. What all three share is that the shape of the statement is decided by the data. As soon as a value can introduce a quote or a keyword, the attacker controls the query's structure, not just its content.

The same three queries, done safely

# Python (DB-API placeholder style used by psycopg2 / MySQL Connector)
sql = "SELECT id FROM users WHERE email = %s AND password = %s"
cursor.execute(sql, (email, password))   # values passed separately, never pasted in

<?php
// PHP with PDO bound parameters
$sql = "SELECT id FROM users WHERE email = ? AND password = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$email, $password]);
?>

// Java with a PreparedStatement
String sql = "SELECT id FROM users WHERE email = ? AND password = ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, email);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();

Notice what did not change: the SQL text is identical in intent. What changed is that the query no longer contains the user's data at all. The ? and %s tokens are placeholders the database driver replaces with bound values at execution time. Because the payload never becomes part of the parsed statement, there is no quote for an attacker to close and no keyword for them to inject.

3. Parameterized Queries — the Root Fix

A parameterized query (also called a prepared statement) sends the SQL text and the values to the database as two separate things. The server parses and plans the statement with placeholders still in it, then binds the values into those slots at execution. The values are never parsed as SQL, so no payload can change the statement's structure — by construction, not by filtering.

What it looks like at the SQL level

You can see the mechanism directly. In MySQL and PostgreSQL you can prepare a statement and execute it with values supplied separately:

PREPARE find_user (text) AS
  SELECT id, email FROM users WHERE email = $1;

EXECUTE find_user('user@example.com');
-- The parameter stays a parameter. Passing  ' OR '1'='1  as the value
-- returns zero rows; it is compared as a literal string, not parsed.

Placeholder syntax by stack

Every stack has a placeholder convention. Using the wrong one is a common porting bug, and putting quotes around a placeholder is a common security bug.

Three gotchas that reintroduce the bug

Parameterization only works if you let it do its job. These three patterns quietly undo it:

1. Quoting the placeholder. This is the most common mistake. If you wrap a placeholder in quotes, the driver binds your value as a string that sits inside a literal — and now the value can contain a quote that breaks out again:

-- WRONG: quotes around the placeholder turn it back into a string literal
sql = "SELECT * FROM users WHERE email = '%s'" % email   # still vulnerable

-- RIGHT: the placeholder stands alone, no surrounding quotes
sql = "SELECT * FROM users WHERE email = %s"
cursor.execute(sql, (email,))

The same trap appears in PHP ("... email = '$email'" is concatenation even if $email looks like a variable) and in Java ("... email = '" + email + "'"). The placeholder token must be the entire value slot.

2. LIKE and wildcards. Parameters bind cleanly into LIKE, but the wildcard characters % and _ that come from user input should be escaped first if you do not want them treated as patterns. This is a correctness issue, not an injection one — the SQL is still safe:

sql = "SELECT * FROM products WHERE name LIKE %s"
pattern = raw_term.replace("%", "\\%").replace("_", "\\_") + "%"
cursor.execute(sql, (pattern,))

-- Or use the ESCAPE clause explicitly
SELECT * FROM products WHERE name LIKE :term ESCAPE '\\';

3. Identifiers cannot be parameters. Table names, column names, sort directions, and keywords are part of the SQL grammar. You cannot bind ORDER BY ? and expect it to work — the driver would quote your column name as a string, producing invalid SQL or ignoring it. When user input must influence an identifier, validate it against a fixed allowlist:

SORTABLE = {"created_at", "total", "status"}   # never trust the raw string

sort_col = request.args.get("sort", "created_at")
if sort_col not in SORTABLE:
    sort_col = "created_at"                     # fall back, do not interpolate input

direction = "DESC" if request.args.get("dir") == "desc" else "ASC"
sql = f"SELECT id, total, status FROM orders ORDER BY {sort_col} {direction} LIMIT 50"

4. Why Escaping, Blacklists, and Sanitizing Fall Short

Before prepared statements were widely available, the standard advice was to escape dangerous characters with functions like mysql_real_escape_string() or addslashes(). Those functions are not useless, but they are a fragile foundation for a security control, and treating them as the fix is how injection survives in modern code.

Escaping depends on context

Escaping only makes sense for string literals, and only if you know the exact context and charset. Two well-known failure modes:

• Charset mismatch. If the connection charset and the escaping routine disagree (for example a GBK connection where an attacker crafts a byte sequence that consumes the escape character), the escape can be neutralized and a quote slips through.
• Wrong position. Escaping does nothing for a numeric slot — WHERE id = 1 OR 1=1 needs no quotes at all — and nothing for identifiers, LIMIT, or ORDER BY.

Blacklists are an arms race you lose

Blocking known-bad substrings like OR 1=1, --, or UNION fails because SQL has far more syntax than a filter can enumerate. Attackers swap case (UnIoN), insert comments inside keywords (UN/**/ION), use equivalent operators, encode payloads as hex (0x4F52), or lean on database-specific operators such as PostgreSQL's || string concatenation and :: casts. A filter that catches the payload you thought of will miss the one you did not.

Sanitizing destroys data

Stripping quotes from input to "make it safe" corrupts legitimate values — an honest customer named O'Brien becomes OBrien, and every downstream report is wrong. Security controls should not silently rewrite user data. Parameterization preserves the value exactly while keeping it out of the SQL grammar, which is the outcome you actually want.

The takeaway is not that escaping is forbidden — it is that escaping is a second line of defense that only helps if you never make a single mistake, in every code path, forever. Parameterization holds even when you are careless, because there is no string to get wrong.

5. Your ORM Is Not a Free Pass

ORMs and query builders generate parameterized SQL for you, which is why applications built entirely with the ORM API rarely have injection bugs. The trouble is the escape hatch: every ORM lets you write raw SQL, and that raw SQL is your responsibility. Reported "ORM injection" incidents are almost always raw strings with user input concatenated in.

Raw SQL inside an ORM — vulnerable

# Django: raw() with % formatting defeats the parameter binding
User.objects.raw("SELECT * FROM users WHERE email = '%s'" % email)

# Sequelize: string concatenation is passed straight through
sequelize.query("SELECT * FROM users WHERE email = '" + email + "'")

// Hibernate HQL: concatenated criterion
session.createQuery("from User where email = '" + email + "'").list();

// EF Core: FromSqlRaw with an interpolated string is NOT parameterized
db.Users.FromSqlRaw($"SELECT * FROM Users WHERE Email = '{email}'").ToList();

The safe form of each

# Django: pass parameters as a separate argument, never with %
User.objects.raw("SELECT * FROM users WHERE email = %s", [email])

# Sequelize: use replacements so values are bound
sequelize.query("SELECT * FROM users WHERE email = :email",
                { replacements: { email }, type: QueryTypes.SELECT });

// Hibernate: named parameters
session.createQuery("from User where email = :email")
       .setParameter("email", email)
       .list();

// EF Core: FromSqlInterpolated treats {email} as a bound parameter
db.Users.FromSqlInterpolated($"SELECT * FROM Users WHERE Email = {email}").ToList();

The pattern is always the same shape: a variant of the API that binds values (an argument list, replacements, setParameter, FromSqlInterpolated) versus a variant that treats your string as the final SQL. Learn which is which in your framework — the difference between a safe API and a vulnerable one is often a single method name.

The other ORM weak spots

• Dynamic ordering. ORDER BY cannot be parameterized, and ORMs that accept a raw string there (Django's order_by(), Sequelize's order option) can be steered by user input. Map to a fixed set of allowed columns.
• Filter expression builders. Django's extra(where=[...]) and RawSQL() take raw fragments; treat them as reviewed, high-risk code, not as convenience helpers.
• Stored procedures. A procedure that builds dynamic SQL internally with EXEC, EXECUTE IMMEDIATE, or PREPARE ... FROM CONCAT(...) is just concatenation in a different file. Audit the procedure body, not just its signature.

6. Defense in Depth: Least Privilege, Errors, and WAFs

Parameterized queries stop injection, but mature systems assume something else will slip — a legacy endpoint, a report generator, a nightly script. The following controls do not prevent injection; they limit how much an attacker gains if they find a hole that slipped through review.

Least privilege: make the account boring

The database user your web application connects as should be able to do its job and nothing more. If a poorly reviewed query is ever exploited, the attacker inherits exactly those powers — so keep them small.

-- A web app account that reads and writes one schema, and cannot reshape it
CREATE USER 'webapp'@'10.0.0.%' IDENTIFIED BY 'use-a-secrets-manager';

GRANT SELECT, INSERT, UPDATE ON shop.orders      TO 'webapp'@'10.0.0.%';
GRANT SELECT, INSERT         ON shop.order_items TO 'webapp'@'10.0.0.%';
GRANT SELECT                 ON shop.products    TO 'webapp'@'10.0.0.%';

-- Deliberately withheld:
--   DROP, ALTER, CREATE   -> an injected DROP TABLE fails with a permission error
--   FILE, SUPER, PROCESS  -> no filesystem writes, no privilege escalation
--   GRANT OPTION          -> the account cannot widen its own access

Two habits make this stick. First, use a separate account per service — the reporting service and the web app should not share credentials, so a bug in one does not expose the other's data. Second, never connect the application as root, sa, or a schema owner "just for now"; that single shortcut turns any future injection into a full server compromise.

Error messages are reconnaissance

Detailed database errors tell an attacker the schema shape, column names, and version — everything they need to aim a payload. Showing them to users is free intelligence. Return a generic message and log the detail server-side:

# Vulnerable: the raw driver error reaches the browser
try:
    cursor.execute(sql, params)
except DatabaseError as e:
    return f"Query failed: {e}", 500     # leaks table/column names and SQL dialect

# Safe: generic response, full detail in the server log
try:
    cursor.execute(sql, params)
except DatabaseError as e:
    app.logger.exception("order lookup failed")   # detail stays on the server
    return "Something went wrong. Please try again.", 500

Make sure the generic branch also covers unhandled exceptions and any framework debug mode. A stack trace page in production leaks the same information as a raw SQL error, plus your file paths and dependency versions.

WAFs reduce noise, they do not fix the bug

A web application firewall is a signature and heuristic filter sitting in front of your app. It is genuinely useful for cutting scanner noise and catching lazy automated attacks, but it is not a substitute for correct code, for three reasons: signatures lag behind novel and obfuscated payloads; the same payload may be trivially re-encoded to slip past; and a single missed variant is a breach, because the WAF is a filter, not a boundary. Treat a WAF as monitoring and defense in depth — never as the control that lets you skip parameterization.

7. Practical Hardening Checklist

Run this against a real codebase. Every "no" is a finding.

If you take one thing from this article, make it item 1. The rest are insurance; parameterized queries are the fix. Get every query to pass bound values, wrap dynamic identifiers in allowlists, and the classic ' OR '1'='1 stops being an attack and becomes what it always should have been — a weird email address that matches nobody.

FAQ — SQL Injection Prevention