Skip to content
Tutorials Center
My learning

Topics

Lesson 89 of 147 ยท Databases

Prepared Statements and SQL Injection

See SQL injection happen in a safe demo, then stop it with PDO prepared statements: prepare and execute, named and positional placeholders, binding types, IN lists and LIKE.

So far every query in this chapter has been a fixed string. Real applications are different: they look up the user who just typed an email address, search for the words in a search box, load the product whose ID is in the URL. The values come from outside, and how you get them into your SQL decides whether your application is secure.

The wrong way is to paste them into the SQL string. That opens the door to SQL injection, for decades one of the most common and most damaging web vulnerabilities: attackers type SQL into your form fields and your database runs it. The right way is a prepared statement, where the SQL and the values travel separately, so a value can never change what the query does.

In this lesson you'll first watch an injection attack succeed against an in-memory demo database (it's completely safe to run), then fix it with prepared statements and learn everything they can do: named and positional placeholders, binding types, reusing statements, IN lists, LIKE searches and dynamic column names. The SQL Injection lesson of our SQL tutorial looks at the same attack from the database side.

SQL injection, step by step

Here's a login check written the dangerous way. It builds the SQL by concatenating the user's input into a string:

PHP An injectable login check (do not copy)

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Look at the second "SQL sent" line. The attacker's quote character closed the string literal early, and the rest of their input became SQL: WHERE email = 'nobody' OR '1'='1'. Since '1'='1' is always true, the condition matches every row, and fetch() returns the first one, which is the admin. If this were a login form, the attacker would now be logged in as the administrator without knowing any email or password.

That's the core of SQL injection: data is interpreted as code. Once an attacker can add SQL, they can do much more than bypass a login:

  • Read other tables, for example by appending UNION SELECT email, password_hash FROM users.
  • Change or delete data, where the database allows several statements or the query is an UPDATE.
  • Find out the table structure from error messages, then extract everything step by step.

Escaping quotes by hand (addslashes(), str_replace("'", "''", ...)) is not a fix. It's easy to forget once, it fails for numbers that aren't quoted at all (WHERE id = $id with $id = '0 OR 1=1'), and some escaping functions have had character-set bugs. The real fix removes the problem at its root.

The fix: prepare, then execute

A prepared statement splits the job in two:

  1. prepare() sends the SQL with placeholders where values will go. The database parses it and fixes its structure.
  2. execute() sends the values. They're treated purely as data, so no matter what characters they contain, they can't change the structure.
PHP The same lookup with a prepared statement

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The attack now returns false: the database searched for a user whose email is literally nobody' OR '1'='1, and there is none. And there's a bonus: legitimate data with quotes, like the email o'[email protected] or the name O'Brien, works without any escaping, while the concatenated version would have crashed with a syntax error.

This is the rule to take from this lesson: every value that isn't a constant in your source code goes through a placeholder. That includes values from forms, URLs, cookies, headers, files, APIs and even your own database (data stored earlier can contain an attack too, which is called second-order injection).

Named and positional placeholders

PDO supports two placeholder styles. You can't mix them in one statement.

Positional placeholders are question marks, filled in order from a list:

PHP
$statement = $pdo->prepare('INSERT INTO products (name, price_cents, stock) VALUES (?, ?, ?)');
$statement->execute(['Desk lamp', 2499, 12]);

Named placeholders start with a colon, filled from an associative array (the colon in the array key is optional):

PHP
$statement = $pdo->prepare(
    'UPDATE products SET price_cents = :price WHERE id = :id'
);
$statement->execute(['price' => 1999, 'id' => 7]);

Named placeholders are easier to read once a query has more than two or three values, and the order of the array doesn't matter. Positional ones are compact for short queries. Both are equally safe.

Reusing a statement

Preparing once and executing many times is also efficient: the database parses the SQL once. That's ideal for importing rows:

PHP One prepared statement, many executions

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

When a row array's keys match the placeholder names exactly, you can pass it straight to execute(). Mismatches behave differently per driver, as the end of the demo shows. The SQLite driver doesn't complain when a placeholder gets no value. It keeps whatever was bound in the previous execution (or NULL on the first one), which is why the Ruler was stored with a stock of 3, copied from the fountain pen. The misspelled stok key fails only with a cryptic column index out of range. MySQL and PostgreSQL reject a missing value with SQLSTATE[HY093]: Invalid parameter number. The lesson: always pass a complete set of values, and consider building each array explicitly instead of passing whatever arrived from outside. For importing thousands of rows, wrap the loop in a transaction; it can make it many times faster.

Binding values with types

execute($array) binds every value as a string (and null as SQL NULL). Databases usually convert strings to numbers where a number is needed, so this works almost everywhere. When you need control over types, bind each value yourself:

  • bindValue($placeholder, $value, $type) binds a value right now.
  • bindParam($placeholder, $variable, $type) binds a variable by reference: its value is read each time execute() runs.
Constant Sends the value as
PDO::PARAM_STR A string (the default)
PDO::PARAM_INT An integer
PDO::PARAM_BOOL A boolean
PDO::PARAM_NULL SQL NULL
PDO::PARAM_LOB Binary data, possibly from a stream
PHP bindValue() and bindParam()

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

LIMIT and OFFSET are the classic place where types matter. With MySQL and emulated prepares, binding them as strings through execute([...]) produces LIMIT '2', which is a syntax error, so bind them with PDO::PARAM_INT (or turn emulation off as recommended in PDO Basics). SQLite accepts either, but typed binding keeps the code portable.

bindParam() is a reference to $id, so the foreach loop changing $id changes what each execute() sends. It's rarely needed in modern code (passing an array to execute() is clearer), but you'll see it in older projects and in MySQLi, whose bind_param() works the same way.

One NULL detail: WHERE price_cents = NULL never matches anything in SQL, even if you bind null. Comparisons with NULL need IS NULL (see NULL Values).

IN lists

A placeholder stands for exactly one value. WHERE id IN (?) with a list of IDs doesn't work, because the whole list would be bound as one string like '3,7,9'. Instead, generate one placeholder per value. Only the question marks are built dynamically; the values are still bound:

PHP A safe IN list

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

array_fill(0, count($ids), '?') makes a list of question marks, and implode() joins them with commas. The empty-list check matters, since IN () is invalid SQL. The last part validates the input too. The binding alone would already make '2 OR 1=1' harmless, but dropping values that aren't integers keeps your results predictable. (array_values() matters because execute() with positional placeholders expects a list starting at index 0.)

LIKE searches and wildcards

Binding protects you from injection, but inside a LIKE pattern the characters % (any text) and _ (any single character) still act as wildcards, even in a bound value. If a user searches for 50%, they mean the literal text "50%", not "50 followed by anything". Escape the wildcards and tell SQL which escape character you used:

PHP Escaping LIKE wildcards

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Unescaped, 50% also matches "500 points bonus" and NEW_ also matches NEWXUSER. Escaped, each search finds only the literal text. The ESCAPE '\' clause names the backslash as the escape character; MySQL uses backslash by default, but writing the clause keeps the query portable. In the PHP source, '\\' inside a single-quoted string is one backslash. (The $column interpolated into this SQL is fixed by our own code, never by the user, which leads to the next topic.)

Column names, table names and ORDER BY

Placeholders can only stand for values. You can't bind a column name, a table name, ASC/DESC or any other part of the SQL structure. ORDER BY ? binds a constant string, so it sorts by nothing at all. When users choose the sort column, map their choice onto an allow-list of SQL fragments you wrote:

PHP Sorting by a whitelisted column

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The attack string falls through to default, so the query sorts by id and nothing else happens. The match expression is a neat fit here: it's strict, and the default arm makes the safe fallback explicit. The same pattern works for choosing between tables, columns in a report, or filter operators: users choose from options, and your code supplies the SQL. Pagination, Search and Database Patterns builds a full search page on this idea.

Quick self-check for any query: does a variable appear directly inside the SQL string? If it holds a value, replace it with a placeholder. If it holds a column name, direction or other SQL keyword, make sure it can only be one of your own hard-coded strings.

How safe is "prepared"?

Prepared statements are the main defense against SQL injection, but keep these limits in mind:

  • Emulated prepares. With MySQL, PDO by default emulates prepared statements: it escapes the values itself and sends one complete SQL string. With charset=utf8mb4 in the DSN this is safe, but turning emulation off (PDO::ATTR_EMULATE_PREPARES => false) makes the server handle the values separately, which removes a whole class of edge cases.
  • Least privilege. The database user your app connects with shouldn't be allowed to drop tables or read the mysql system schema. If something goes wrong anyway, the damage is limited (Users and Permissions).
  • Stored procedures and dynamic SQL inside the database can be injectable too, if they concatenate their arguments.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What makes "SELECT * FROM users WHERE email = '$email'" dangerous?

    Concatenating input into SQL lets data be interpreted as code. A quote in the input closes the literal, and the rest runs as SQL.
  2. 2. Which code safely looks up a user by email?

    Only the prepared statement sends the value separately from the SQL. Manual escaping with addslashes() is fragile and not a real fix.
  3. 3. How do you safely query WHERE id IN (...) for a list of three IDs?

    Each placeholder holds one value. Build the right number of question marks, then bind the values. Handle an empty list separately.
  4. 4. A user searches for the text "50%". What should the bound LIKE pattern be?

    Binding stops injection, but % and _ are still wildcards inside LIKE. Escape them and declare the escape character.
  5. 5. Why does ORDER BY ? not sort by the column you bind?

    Identifiers and keywords can't be bound. Map the user's choice to hard-coded SQL fragments with match or an array.

Summary

  • SQL injection happens when input is pasted into SQL text, so data runs as code: ' OR '1'='1 can turn a login check into "match everyone". Manual escaping is not a fix.
  • Use prepared statements: prepare() the SQL with placeholders, then execute() with the values. Every value that isn't a constant in your code goes through a placeholder.
  • Placeholders are positional (?, filled from a list) or named (:email, filled from an associative array); don't mix them. Prepare once and execute many times for imports.
  • execute([...]) binds values as strings (and null as NULL). Use bindValue() with PDO::PARAM_INT/PARAM_BOOL when types matter, such as LIMIT with MySQL; bindParam() binds a variable by reference.
  • For IN lists, generate one ? per value and handle empty lists. In LIKE patterns, escape %, _ and the escape character and add ESCAPE '\'.
  • Placeholders can't hold column names or keywords: map user choices onto an allow-list. Add least-privilege database users and disable emulated prepares on MySQL for defense in depth.

Next: CRUD with PDO