PHP MySQL injection; What, how and avoid

MySQL injection is the method in which “hackers” can get unauthorized data or inject it. Good news is that its easy to fix, bad news is that PHP code with MySQL vulnerability is as common as the sun coming up each morning.

The basis of a vulnerability occurring is when the input is not escaped. Take this for an example the page users.php?id=

$id = $_GET['id'];
$sql = "SELECT * FROM user_data WHERE id = ".$id."";

//display data.......

users.php?id=44 the $sql would be : SELECT * FROM user_data WHERE id = 44

and the page would display our data for user id 44.

But what happens if someone did users.php?id=44%20or%201=1 (%20 is a URL encoded space) that would leave you open to this SELECT * FROM user_data WHERE id = 44 or 1=1 This is going to return all results. Bad bad news.

The motivation against all MySQL injections is the same, prevent the use of AND or OR by escaping the input this is done by mysqli_real_escape_string

$sql = "SELECT * FROM user_data WHERE id = '".mysqli_real_escape_string($id, $connection)."'";

Now makes an attempted injection: SELECT * FROM user_data WHERE id = '44 or 1=1' which sadly for the injector isn’t valid MySQL and wont return any rows.

Another method to avoid potential injections with PHP and MySQL is to use PDO, I wrote a post here and provided examples. You end up needing more code but you get security which is always crucial.