Prevent SQL INJECTION in PHP

Use prepared statements and parameterized queries.

These are SQL statements that are sent to and analyzed by the database server distinctly from any parameters. This way it is not possible for an invader to insert malicious SQL.

You basically have two options to achieve this:

Using PDO (for any supported database driver):

$stmt = $pdo->prepare('SELECT * FROM employees WHERE name = :name');

$stmt->execute(array('name' => $name));

foreach ($stmt as $row) {

    // Do something with $row

}

Using MySQLi (for MySQL):

$stmt = $dbConnection->prepare('SELECT * FROM employees WHERE name = ?');

$stmt->bind_param('s', $name); // 's' specifies the variable type => 'string'

$stmt->execute();

$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {

    // Do something with $row

}

If you're linking to a database other than MySQL, there is a driver-specific 2^nd choice that you can refer to (for example, pg_prepare() and pg_execute() for PostgreSQL). PDO is the universal option.

Correctly setting up the connection

Reminder, that when using PDO to access a MySQL database actual prepared statements are not used by default. The solution is that, you have to disable the emulation of prepared statements. An example of making a connection using PDO is:

$dbConnection = new PDO('mysql:dbname=dbtest;host=127.0.0.1;charset=utf8', 'user', 'password');

$dbConnection->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$dbConnection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

In the above example the error mode isn't strictly necessary, but it is advised to add it. By this technique the script will not break with a Fatal Error when something goes wrong. And it gives the developer the opportunity to detect any error(s) which are thrown as PDOExceptions.

What is required, though, it is the 1^st setAttribute() line, which states PDO to disable emulated prepared statements and use real prepared statements. This makes sure the statement and the values are not parsed by PHP before sending it to the MySQL server (giving a probable attacker no chance to insert malicious SQL).

Though you can set the charset in the choices of the constructor, it's important to note that 'older' versions of PHP (before 5.3.6) silently overlooked the charset parameter in the DSN.

Explanation

The SQL statement you prepare is parsed and compiled by the database server. By specifying parameters (either a? or a termed parameter like: name in the illustration above) you state the database engine where you want to filter on. Then when you call execute, the prepared statement is joined with the parameter values you specify.

The key thing here is that the parameter values are joined with the compiled statement, not an SQL string. SQL injection works by deceiving the script hooked on together with malicious strings when it makes SQL send to the database. So by sending the real SQL individually from the parameters, you bound the possibility of ending up with rather you didn't intend.

Any parameters you send when using a prepared statement will just be seen as strings (though the database engine may do some refinement so parameters may end up as numbers also, of course). In the illustration above, if the $name variable holds 'Sarah'; DELETE FROM employees the outcome would basically be a search for the string "'Sarah'; DELETE FROM employees", and you will not end up with an empty table.

An additional advantage of using prepared statements is that if you execute the same statement several times in the same session it will only be analyzed and compiled once, giving you some speed gains.

Oh, and since you questioned about how to do it for an insert, here's an example (using PDO):

$preparedStatement = $db->prepare('INSERT INTO table (column) VALUES (:column)');

$preparedStatement->execute(array('column' => $unsafeValue));

Can prepared statements be used for dynamic queries?

While you can still use prepared statements for the query parameters, the form of the dynamic query itself cannot be parametrized and some of the query features cannot be parametrized.

For these exact situations, the best thing to do is use a whitelist filter that limits the probable values.

// Value whitelist

// $dir can only be 'DESC', otherwise it will be 'ASC'

if (empty($dir) || $dir !== 'DESC') {

   $dir = 'ASC';

}