How PHP Prepared Statements Prevent SQL Injection

PHP's prepared statements are the most effective method to prevent SQL injection, relying on parameterized queries that separate SQL code from user input.

A prepared statement can be viewed as a compiled SQL template that can be customized with variable parameters.

The two main advantages are: (1) the query is parsed and optimized only once, allowing repeated execution with different parameters and improving performance; (2) parameters are automatically handled by the driver without needing quotes, ensuring that injection cannot occur unless other parts of the query are built from unescaped input.

When the PDO driver does not support native prepared statements, it emulates them: the emulator escapes input based on the DSN charset, concatenates a full SQL string, and sends it to MySQL. Correct escaping by the emulator is therefore critical for preventing injection.

In PHP versions prior to 5.3.6 the DSN ignores the charset parameter by default, so even PDO's local escaping may still be vulnerable.

Frameworks such as Laravel mitigate this by setting PDO::ATTR_EMULATE_PREPARES = false , ensuring that SQL and parameters are not parsed by PHP before reaching MySQL.

// Query example $calories = 150; $colour = 'red'; $sth = $dbh->prepare('SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour'); $sth->bindValue(':calories', $calories, PDO::PARAM_INT); $sth->bindValue(':colour', $colour, PDO::PARAM_STR); $sth->execute();

// Insert / Update / Delete example $preparedStmt = $db->prepare('INSERT INTO table (column) VALUES (:column)'); $preparedStmt->execute(array(':column' => $unsafeValue));

Laravel's underlying implementation uses the same principles. For selecting data it prepares the query, binds values, executes, and fetches all results; for affecting statements it prepares, binds, executes, checks the affected row count, and returns it.

public function select($query, $bindings = [], $useReadPdo = true) { return $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) { if ($this->pretending()) { return []; } $statement = $this->prepared( $this->getPdoForSelect($useReadPdo)->prepare($query) ); $this->bindValues($statement, $this->prepareBindings($bindings)); $statement->execute(); return $statement->fetchAll(); }); } public function affectingStatement($query, $bindings = []) { return $this->run($query, $bindings, function ($query, $bindings) { if ($this->pretending()) { return 0; } $statement = $this->getPdo()->prepare($query); $this->bindValues($statement, $this->prepareBindings($bindings)); $statement->execute(); $this->recordsHaveBeenModified(($count = $statement->rowCount()) > 0); return $count; }); }

Tips: The PHP Chinese community offers an online training class covering these topics, with live streaming, recorded sessions, source code downloads, and support for learners.