Preventing SQL injection in PHP

A question asked privately:

What techniques can be used to prevent SQL injection?

There are at least two.

A newer and more radical one is to prepare SQL statements for execution.  This used to be one of functionalities implemented in database abstraction layers, but now it is available in the MySQLi (MySQL improved) extension.  See documentation for mysqli_prepare().

An older and more ad-hoc technique is putting all user input through mysql_real_escape_string() before including it in a query.  When using this approach, it’s important to check for the value of the magic_quotes_gpc configuration directive and process the input accordingly.  Here’s a possible way to do it:

function db_defuse($arg) {
  if (get_magic_quotes_gpc()) {
    return mysql_real_escape_string(stripslashes($arg));
  } else {
    return mysql_real_escape_string($arg);
  }
}

Usage example:

$u = db_defuse($_POST['user']);
$q = "SELECT * FROM users WHERE user = '$u'";
$r = mysql_query($q);

* * * * *

This entry was posted in Answers, Technology. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *