How do I sanitize input with PDO?

Question

Do I need to use mysql_real_escape_string() on my input (such as $_POST and $_GET) when I use the PDO library?

How do I properly escape user input with PDO?

Answer 1

If you use PDO you can parametize your queries, removing the need to escape any included variables.

See here for a great introductory tutorial for PDO.

Using PDO you can seperate the SQL and passed parameters using prepared statements, this removes the need to escape strings, as because the two are held seperately then combined at execution, the parameters are automatically handled as stings, from the above source:

   // where $dbh is your PDO connection     $stmt = $dbh->prepare("SELECT * FROM animals WHERE animal_id = :animal_id AND animal_name = :animal_name");     /*** bind the paramaters ***/    $stmt->bindParam(':animal_id', $animal_id, PDO::PARAM_INT);    $stmt->bindParam(':animal_name', $animal_name, PDO::PARAM_STR, 5);     /*** execute the prepared statement ***/    $stmt->execute(); 

Note: sanitization occurs during variable binding ($stmt->bindParam)

Other resources:

http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/

http://www.phpeveryday.com/articles/PDO-Prepared-Statement-P550.html

http://php.net/manual/en/pdo.prepared-statements.php

Answer 2

The important point when using PDO is:

PDO will only sanitize it for SQL, not for your application.

So yes, for writes, such as INSERT or UPDATE, it’s especially critical to still filter your data first and sanitize it for other things (removal of HTML tags, JavaScript, etc).

<?php $pdo = new PDO(...); $stmt = $pdo->prepare('UPDATE users SET name = :name WHERE id = :id'); $id = filter_input(INPUT_GET, 'id', FILTER_SANITIZE_NUMBER_INT); // <-- filter your data first $name = filter_input(INPUT_GET, 'name', FILTER_SANITIZE_STRING); // <-- filter your data first $stmt->bindParam(':id', $id, PDO::PARAM_INT); // <-- Automatically sanitized for SQL by PDO $stmt->bindParam(':name', $name, PDO::PARAM_STR); // <-- Automatically sanitized for SQL by PDO $stmt->execute(); 

Without sanitizing the user input, a hacker could have saved some javascript into your database and then, when output it into your site you would have been exposed to a threat!

http://www.phptherightway.com/#pdo_extension