Skip to content
Tutorials Center
My learning

Topics

Lesson 99 of 125 ยท Database Systems

SQL Injection and Secure Queries

See how SQL injection works with live examples, then learn to prevent it with parameterized queries, allowlists, safe dynamic SQL and least privilege in every language.

SQL injection happens when an application builds a SQL statement by gluing user input into the query text. If the input contains SQL syntax, such as a quote, a comment or a UNION, the database can't tell the developer's code from the attacker's, and runs both. It's one of the oldest web vulnerabilities, and still one of the most damaging: injection bugs have leaked millions of passwords and credit cards, deleted data and handed attackers full control of servers.

The fix has been known for decades and is simple: never put input into SQL text; pass it as a parameter. In this lesson you'll first watch injection happen in a real SQLite database, so you understand exactly why string building fails, and then learn the secure patterns for application code, dynamic SQL and database permissions.

Only test systems you own. The techniques here are shown so you can recognize and prevent them. Probing other people's websites for injection without permission is illegal in most countries.

How a query gets built from input

Imagine a shop page where customers look up their account by email. A careless developer writes this Python:

Python
email = request.args["email"]          # whatever the visitor typed
sql = "SELECT id, first_name, city FROM customers WHERE email = '" + email + "';"
rows = conn.execute(sql).fetchall()     # DON'T do this

With a normal email, the program sends a normal query. You can watch the string being assembled with SQLite's || operator. Each row below is one possible input, and the query_sent column is the exact text the database would receive:

SQL What the app sends to the database

Press Run (or Ctrl + Enter) to see the result here.

(Inside a SQL string, '' stands for one single quote, which is why the inputs look doubled here.) The second input is x' OR '1'='1. Its first quote closes the string the developer opened, and the rest becomes SQL. The finished query is:

SQL
SELECT id, first_name, city FROM customers WHERE email = 'x' OR '1'='1';

Attack 1: making WHERE always true

Run that query against the shop data and see what the "lookup one account" page returns:

SQL The injected OR condition ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

All 40 customers come back. email = 'x' is false for everyone, but '1'='1' is true for every row, and OR needs only one true side. The same trick against a login form turns "find the user with this name and password" into "find any user".

Attack 2: commenting out the rest

Attackers don't even need to balance the quotes. A comment (-- in standard SQL, also # in MySQL) throws away whatever the developer wrote after the injection point. Here's a login check that compares a username and a password hash:

SQL Bypassing a login with a comment

Press Run (or Ctrl + Enter) to see the result here.

The query returns the admin row even though the hash is wrong, because everything after -- is a comment, including the password check. The app thinks the login succeeded.

Attack 3: UNION and reading other tables

The worst injections don't just change which rows come back; they read other tables. UNION appends the results of a second query, as long as it has the same number of columns. Attackers find the column count by trial and error, then list the schema and dump whatever looks valuable:

SQL Stealing data with UNION ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

(The lone ; lines only end each statement in this demo: the comment swallowed the app's own closing ';.) The first result lists all six table names, including staff. The second returns the staff usernames and password hashes, displayed by the page as if they were customer names and cities. Every database has a catalog like sqlite_schema (information_schema in PostgreSQL, MySQL and SQL Server), so attackers always know where to look.

Attack 4: stacked queries and numbers

If the database driver accepts several statements in one call, an attacker can end the query with ; and add a new one. SQL Server batches and PostgreSQL's simple query protocol allow this; Python's sqlite3 execute() refuses more than one statement. Our in-browser runner happily runs several, so you can see the damage:

SQL A stacked DELETE ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The lookup returns nothing, the DELETE removes all 292 order items, and the check shows 0 left. And quotes aren't required at all when input lands in a numeric position:

SQL Injection without quotes ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Every product matches (LIMIT 5 just keeps the output short). This is why "escape the quotes" is not a defense: there were no quotes to escape.

In-band, error-based and blind injection

Security people sort injections by how the attacker gets the answer back. You have already seen the first kind; the other two matter when the page shows nothing useful.

  • In-band (UNION-based): the stolen data appears in the normal page, as in Attack 3. It is the easiest attack, but it needs a page that prints query results.
  • Error-based: the page doesn't print results, but it does print database error messages. The attacker crafts input that makes the database fail with a message that contains the secret, for example by converting a text value to a number: SQL Server complains Conversion failed when converting the nvarchar value 'pbkdf2$a1f9c0e7' to data type int, and the value is right there in the message. Turning off detailed error output in production closes this hole, but it is not a fix for the injection itself.
  • Blind: the page shows neither data nor errors, only whether something happened. The attacker asks yes/no questions and reads the answer from the page's behavior.

Blind attacks come in two flavors. Boolean-based blind injection watches whether the page changes (results appear or not). Time-based blind injection makes the database sleep when the answer is "yes" (pg_sleep(5) in PostgreSQL, WAITFOR DELAY '0:0:5' in SQL Server, SLEEP(5) in MySQL) and measures the response time. Each question reveals one bit, but tools automate thousands of questions, so a whole password hash falls in minutes. The same idea works in the runner below: the "page" is a customer search, and we only look at how many rows come back.

SQL Boolean-based blind injection ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

A result of 40 rows means "yes" and 0 rows means "no": the first hash character is p, not q, and the role is longer than ten characters. The page never printed a single secret, yet the attacker learned two of them. The defense is the same for all three kinds: keep user input out of the SQL text with parameterized queries, as the next section shows. Hiding errors or filtering a few keywords only changes which technique the attacker picks.

The fix: parameterized queries

A parameterized query (also called a prepared statement or bound parameters) sends the SQL text and the values separately. The SQL contains placeholders such as ?, $1 or :email; the values travel alongside it and are never parsed as SQL. Whatever the visitor types, quotes and UNION included, it can only ever be compared as a value.

Python
# Python (sqlite3; psycopg uses %s placeholders instead of ?)
row = conn.execute(
    "SELECT id, first_name, city FROM customers WHERE email = ?",
    (email,),
).fetchone()
PHP
// PHP with PDO
$stmt = $pdo->prepare('SELECT id, first_name, city FROM customers WHERE email = :email');
$stmt->execute(['email' => $email]);
$customer = $stmt->fetch();
Java
// Java with JDBC
String sql = "SELECT id, first_name, city FROM customers WHERE email = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, email);
    try (ResultSet rows = statement.executeQuery()) {
        // read rows
    }
}
JavaScript
// Node.js with node-postgres
const { rows } = await pool.query(
  'SELECT id, first_name, city FROM customers WHERE email = $1',
  [email],
);
C#
// C# with SqlClient
using var command = new SqlCommand(
    "SELECT id, first_name, city FROM dbo.customers WHERE email = @email", connection);
command.Parameters.Add("@email", SqlDbType.NVarChar, 320).Value = email;

ORMs and query builders use parameters for you, as long as you stay on their normal API. The danger is the "raw" escape hatches. In Laravel, for example:

PHP
// Safe: the query builder binds $email
$customer = Customer::where('email', $email)->first();
$rows = DB::select('SELECT id FROM customers WHERE email = ?', [$email]);

// UNSAFE: input interpolated into raw SQL
$customer = Customer::whereRaw("email = '$email'")->first();

// Safe raw SQL: still pass bindings
$customer = Customer::whereRaw('lower(email) = lower(?)', [$email])->first();

To see why parameters work, compare the attack string used as data. Here the quotes inside it are properly doubled, which is what a correctly bound parameter amounts to:

SQL The same input, treated as a value ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Zero rows: nobody's email is literally x' OR '1'='1. A real driver goes further than quoting: with true prepared statements the value is never placed into SQL text at all, so there is nothing to get wrong.

What placeholders can't do

Parameters stand in for values only. You can't bind a table name, a column name, a keyword like ASC, or a list. Those cases need care:

Identifiers and sort orders: use an allowlist. Map user choices to SQL fragments you wrote yourself, and reject anything else:

Python
SORT_OPTIONS = {
    "name": "name",
    "price_low": "price ASC",
    "price_high": "price DESC",
    "newest": "added_on DESC",
}
order_by = SORT_OPTIONS.get(request.args.get("sort"), "name")
rows = conn.execute(f"SELECT name, price FROM products ORDER BY {order_by}").fetchall()

The f-string is safe here only because order_by can only be one of four strings from your own code.

Lists: generate one placeholder per item.

Python
ids = [3, 8, 15]
placeholders = ", ".join("?" for _ in ids)       # "?, ?, ?"
rows = conn.execute(f"SELECT name FROM products WHERE id IN ({placeholders})", ids).fetchall()

LIKE patterns: escape the wildcards. A bound value is safe from injection, but inside LIKE the user's % and _ still act as wildcards. Searching for _ would match everything:

SQL Wildcards inside a parameter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Without escaping, all 26 products match; with it, none do, because no product name contains an underscore. In application code you do the replace calls on the term before binding it. More in Pattern Matching with LIKE.

Dynamic SQL inside the database

Stored procedures can be injectable too, when they build SQL strings and execute them. Use the database's own parameter mechanism there as well:

SQL
-- SQL Server: vulnerable
DECLARE @sql NVARCHAR(MAX) =
  N'SELECT id FROM dbo.customers WHERE email = ''' + @email + N'''';
EXEC (@sql);

-- SQL Server: safe, the value is a real parameter
EXEC sp_executesql
  N'SELECT id FROM dbo.customers WHERE email = @email',
  N'@email NVARCHAR(320)',
  @email = @email;

-- SQL Server: identifiers must be quoted with QUOTENAME
SET @sql = N'SELECT COUNT(*) FROM dbo.' + QUOTENAME(@table_name) + N';';
SQL
-- PostgreSQL (PL/pgSQL): %I quotes an identifier, USING passes values
EXECUTE format('SELECT count(*) FROM %I WHERE status = $1', p_table_name)
  INTO v_count
  USING p_status;

See Stored Procedures and Functions for the rest of the procedural syntax.

Why escaping by hand fails

"Just escape the quotes" sounds reasonable, but hand-written escaping breaks in many ways:

  • Numeric and unquoted contexts (WHERE id = 7 OR 1=1, ORDER BY clauses) contain no quotes to escape.
  • Dialects differ. MySQL, by default, also treats a backslash as an escape character inside strings. An escaper that only doubles quotes turns the input \' into \'', which MySQL reads as an escaped quote followed by a closing quote, and the injection is back.
  • Character sets. Some multi-byte encodings once let attackers hide a quote inside a "character" that a byte-level escaper didn't recognize.
  • Second-order injection. Data that was stored safely, such as a username admin'--, is later read from the database and concatenated into another query by code that "trusts" its own data.
  • It's easy to forget. One missed call among hundreds of queries is enough.

Parameters avoid all of these because the value never becomes SQL. Treat any string concatenation that produces SQL as a red flag in code review.

Defense in depth

Parameters are the fix. These extra layers limit the damage if something slips through:

Layer What it does
Least privilege The app account can't DROP tables or read other databases (Users, Roles and Permissions)
Validate input Reject an "age" that isn't a number, an email that isn't an email; useful but not a substitute for parameters
Hide error details Show users a generic message; log the database error server-side, because detailed errors help attackers map your schema
One statement per call Prefer drivers or settings that refuse stacked queries
Static analysis and review Linters and scanners flag concatenated SQL; reviewers search for raw-query APIs
Monitoring and a WAF Can detect or block common payloads, but attackers routinely bypass them

Attackers also use blind injection when the page shows no query results: they ask yes/no questions (AND substr(password_hash, 1, 1) = 'a') and watch whether the page changes or takes longer to respond. So "the page doesn't display the data" doesn't make a vulnerable query safe.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. The app builds "... WHERE email = '" + input + "'". Which input makes it return every row?

    The first quote closes the developer's string, and OR '1'='1' is true for every row.
  2. 2. In the login demo, why does the username admin'-- log in without the right password?

    Everything after -- is ignored, so the query only checks the username.
  3. 3. Why is escaping quotes not enough to stop WHERE id = " + product_id?

    In numeric or other unquoted positions, attackers don't need quotes at all. Bound parameters (or casting to an integer) solve this.
  4. 4. How should you let users choose the sort column?

    Placeholders only stand for values, not identifiers. An allowlist guarantees only known-good SQL reaches the query.
  5. 5. In the UNION demo, why must the injected SELECT have exactly three columns?

    The original query selects three columns, so the attacker's query must match that count for the UNION to run.

Summary

  • SQL injection happens when input is concatenated into SQL text and changes the query's structure.
  • Classic attacks: always-true conditions (' OR '1'='1), comments that cut off checks (--), UNION to read other tables, stacked queries, and unquoted numeric injection.
  • Always use parameterized queries (?, $1, :name, @name); ORMs do this unless you use raw-SQL escape hatches carelessly.
  • Placeholders only replace values: use allowlists for identifiers and sort orders, one placeholder per list item, and escape %/_ in LIKE terms.
  • Inside procedures, use sp_executesql, EXECUTE ... USING, QUOTENAME and format('%I'), never string concatenation.
  • Hand-written escaping fails in many ways; add least privilege, input validation, hidden error details and code review as extra layers.

Next: Encrypting Data in the Database.