How can I prevent SQL-injection in PHP?

I constantly see this question asked and the answer is so simple yet people ignore or outright refuse to use it. The answer is prepared statements with the PDO extension. There is simply no need or a solid valid argument to use mysqli or even worse mysql, mysql_* where deprecated in PHP 5.5.0 and removed completely in PHP 7.0.* so stop using it!

So the PDO extension and prepared statements, whats the deal with SQL-Injection? The idea is your query is prepared with placeholders and then those placeholders are used to inject sanitized data into the query just before its sent to the database server. The great thing about this as the programmer is you don’t have to do anything extra the prepared statement does all the formatting for you before passing user input to the database.

Just to note prepared statements in PHP are not to be confused with prepared statements run natively on RDBMS although they both have the same end goal.

$query = $pdo->prepare('SELECT * FROM posts WHERE id = :id');
$query->execute(['id' => $id]);
$post = $query->fetch();

In the above example we select a post from the posts table where the id matches, a pretty standard query for any kind of blog or CMS system. The difference here is we first prepare the statement and add in a placeholder denoted by the colon followed by a name for the placeholder, in this case we call it id but we could call it what ever we wanted to, the name is not important here. Then we execute the query and pass it an array of data. The array consists of key value pairs, the key being the placeholder name and the value obviously being the value you want to use in the query. The final line we fetch the results of the query.

As with everything in PHP there are many ways to achieve something but when it comes to database access PDO is so easy to use and covers you automatically when using prepared statements there really is no excuse not to use it.

Leave a Reply

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