PHP PHP MySQLi Escaping Strings


Escaping strings is an older (and less secure) method of securing data for insertion into a query. It works by using MySQL's function mysql_real_escape_string() to process and sanitize the data (in other words, PHP is not doing the escaping). The MySQLi API provides direct access to this function

$escaped = $conn->real_escape_string($_GET['var']);
// OR
$escaped = mysqli_real_escape_string($conn, $_GET['var']);

At this point, you have a string that MySQL considers to be safe for use in a direct query

$sql = 'SELECT * FROM users WHERE username = "' . $escaped . '"';
$result = $conn->query($sql);

So why is this not as secure as prepared statements? There are ways to trick MySQL to produce a string it considers safe. Consider the following example

$id = mysqli_real_escape_string("1 OR 1=1");    
$sql = 'SELECT * FROM table WHERE id = ' . $id;

1 OR 1=1 does not represent data that MySQL will escape, yet this still represents SQL injection. There are other examples as well that represent places where it returns unsafe data. The problem is that MySQL's escaping function is designed to make data comply with SQL syntax. It's NOT designed to make sure that MySQL can't confuse user data for SQL instructions.