Search code examples
phpmysqlpdobindvalue

Why should I use bindValue / Param vs a normal variable?

I can see many discussions on why is better to use PDO bindValue vs bindValue. But what I could not find was precice information on why should I use that vs a simple variable in the query. Could anyone tell me? thanks

$sex = 'male';
$s = $dbh->prepare('SELECT name FROM students WHERE sex = :sex');
$s->bindParam(':sex', $sex); 
$s->execute();

VS

$sex = 'male';
$s = $dbh->query('SELECT name FROM students WHERE sex = $sex'); // OR PREPARE, The question is how to pass the variable.
Solution

  • Like others have said, you want to use PDO to prevent SQL injection. Say a user with bad intentions types something like ';drop table students;-- into an input box on your site. Without PDO, the final query will look like this:

    SELECT name FROM students WHERE sex = '';drop table students;--'

    See what would happen? The select statement would run and not find any results (unless you have students in your table with no sex specified). Then, the drop statement would run, and your entire students table would be deleted.

    This is just one example—there are all kinds of SQL injection attacks, many of them more dangerous than this. Using PDO prevents pretty much all of them. Internally, the bound variables are kept separate from the query itself.