Query Problems in PDO in PHP

As you should already know, SQL queries are vulnerable to SQL injections. The PDO extension is designed to combat injections, but by default, it does not do this automatically.

Let's look at the problem with an example. Suppose we have the following query, into which a variable is inserted:

<?php
	$sql = "SELECT * FROM users WHERE id=$id";
	$res = $pdo->query($sql);
?>

Suppose our variable comes from somewhere external, for example, from a GET parameter. For simplicity, however, let's just set its value manually. We expect some numeric value, for example:

<?php
	$id = 1;
?>

A malicious hacker, however, can send the following value (considering that we have a field role):

<?php
	$id = '-1 OR role="admin"';
?>

It turns out that we wanted the following query:

<?php
	$sql = "SELECT * FROM users WHERE id=1";
	$res = $pdo->query($sql);
?>

But we got the following one, which pulls the site administrator:

<?php
	$sql = "SELECT * FROM users WHERE id=-1 OR role="admin"";
	$res = $pdo->query($sql);
?>

To avoid such a situation, PDO provides a special mechanism, which is called prepared statements. We will analyze them in the next lesson.

Intentionally perform an SQL injection on your database.