PHP: How do you prevent SQL injection?

  • August 28, 2026
  • 5-minute read
  • Last updated: August 28, 2026

If user input goes into an SQL query unchanged, your application is vulnerable to SQL injection. The answer is prepared statements. In this article we look at how exactly they work, and why escaping is not the right fix.

The problem

Say you look up a user by name, and the name comes from a form:

<?php
  $name = $_POST['name'];
  $pdo->exec("SELECT id, name FROM users WHERE name = '$name'");
?>

That looks harmless. The point is, though, that whatever the user types becomes part of the SQL statement. If they enter x'; DROP TABLE users; -- , your application sends this to the database:

SELECT id, name FROM users WHERE name = 'x'; DROP TABLE users; -- '

The single quote ends the string, the semicolon ends the statement, and a second statement follows. The two dashes at the end comment out the rest of the line so that no syntax error occurs. The result: your table is gone.

I tried this against a real database (PHP 8.3, MariaDB 11.4). The output of the test script:

   before : 3 row(s): Alice, Bob, Carol
   sql    : SELECT id, name FROM users WHERE name = 'x'; DROP TABLE users; -- '
   after  : TABLE GONE (HY000)

Losing data is not even the most common case. Far more often the point is to get at data that is none of the attacker’s business. If the user enters ' OR '1'='1, the query looks like this:

SELECT name FROM users WHERE name = '' OR '1'='1'

The condition is now always true:

   result : 3 row(s) returned -> Alice, Bob, Carol

If that were a login check, anyone would get in.

The solution: prepared statements

The key idea is to separate the data from the SQL. With a prepared statement you first send the statement with placeholders to the database, which parses and compiles it. Only then do you send the values. Those values are combined with the already compiled statement, not with an SQL string, so the SQL parser never sees them.

With PDO

<?php
  $stmt = $pdo->prepare('SELECT id, name FROM users WHERE name = :name');
  $stmt->execute(['name' => $name]);

  foreach ($stmt as $row) {
    // do something with $row
  }
?>

Exactly the same input as above, this time passed as a parameter:

   input  : x'; DROP TABLE users; --
   result : 0 row(s) -- the input was searched for as text
   after  : 3 row(s): Alice, Bob, Carol

The table is still there. The database simply looked for a user named x'; DROP TABLE users; -- and did not find one.

With MySQLi

<?php
  $stmt = $mysqli->prepare('SELECT id, name FROM users WHERE name = ?');
  $stmt->bind_param('s', $name); // 's' is the type string
  $stmt->execute();
  $result = $stmt->get_result();
?>

Since PHP 8.2 you can also do this in a single call:

<?php
  $result = $mysqli->execute_query('SELECT id, name FROM users WHERE name = ?', [$name]);
?>

Setting up the connection correctly

One detail that is easily overlooked: with MySQL, PDO emulates prepared statements by default. That means PHP assembles the query itself after all, before sending it to the server. If you want real prepared statements you have to turn that off explicitly:

<?php
  $dsn = 'mysql:host=db;dbname=shop;charset=utf8mb4';
  $pdo = new PDO($dsn, 'user', 'password', [
    PDO::ATTR_EMULATE_PREPARES => false,
    PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION,
  ]);
?>

The second line is not strictly necessary, but it is highly recommended. Without it PDO swallows errors silently. With MySQLi you achieve the same with:

<?php
  mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
?>

In both cases you should also set the character set (charset=utf8mb4 in the DSN, or $mysqli->set_charset('utf8mb4')).

What placeholders cannot do

A placeholder always stands for one complete value. You cannot use one to replace table names, column names or the sort direction, because those are part of the structure of the statement. Trying anyway ends in a syntax error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in
your SQL syntax; ... near '?' at line 1

For those cases you need an allow list. You check the value against a fixed list of permitted ones:

<?php
  // $dir can only become 'DESC', anything else becomes 'ASC'
  if (empty($dir) || $dir !== 'DESC') {
    $dir = 'ASC';
  }
?>

Searching with LIKE is another common stumbling block. The wildcards belong to the value, not to the statement:

<?php
  // Wrong, the placeholder has to replace the whole value
  $stmt = $pdo->prepare("SELECT * FROM users WHERE name LIKE '%?%'");

  // Correct
  $stmt = $pdo->prepare('SELECT * FROM users WHERE name LIKE ?');
  $stmt->execute(["%$name%"]);
?>

Why not just escape?

You often come across the advice to defuse user input with mysql_real_escape_string() or addslashes(). That is not a good idea. OWASP does list escaping as a possible defence, but explicitly calls it “STRONGLY DISCOURAGED”. The approach is error-prone and depends on the database and the character set. There is no guarantee that it covers every case.

On top of that, mysql_real_escape_string() belongs to the ext/mysql extension, which has not existed since PHP 7.0. We have described why you should not be using those functions at all in a separate article.

Prepared statements solve the problem at the root instead of treating the symptoms. And they have one pleasant side effect: if you run the same statement several times in one session, the database only has to parse and compile it once.

Summary

  • Never build user input into a query by string concatenation.
  • Use prepared statements, with either PDO or MySQLi.
  • With PDO, turn off emulation (PDO::ATTR_EMULATE_PREPARES => false).
  • Turn on exceptions so that errors do not pass you by.
  • Table and column names need an allow list; there are no placeholders for them.
  • Escaping is not an alternative to prepared statements.

About Netcup (advertisement)

The German host Netcup offers, among other things, affordable and powerful web hosting packages, KVM-based root servers and dedicated servers. With our voucher codes you can save even more (6€ off your first order, 30% off all KVM-based root servers, ...).

php mysqlsecurity