23

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?

Wesley Murch
  • 101,186
  • 37
  • 194
  • 228
Karem
  • 17,615
  • 72
  • 178
  • 278

2 Answers2

36

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

SW4
  • 69,876
  • 20
  • 132
  • 137
  • Hi thank you. I know this already, so I do not need to $animal_id = mysql_real_escape_string($_POST['blabla']), but just can do $animal_id=$_POST['blabla'] now? – Karem Dec 06 '10 at 09:09
  • If you are using a prepared statement- you can directly bind the variable without the need to escape it as a string...using PDO, it executes/handles it as a string anyway, preventing any injection attempted – SW4 Dec 06 '10 at 09:10
  • So- to more simply answer, yes! – SW4 Dec 06 '10 at 09:11
  • Yes great, I am using prepared statements, just wants to be sure. Thank you! – Karem Dec 06 '10 at 09:14
  • Glad to help, its definitely the best approach – SW4 Dec 06 '10 at 09:21
  • 1
    @SW4 could you edit your answer to explain the sanitisation occurs at $dbh->prepare... not $stmt->bindParam(... being new to PDO this was not very clear. – Guesser Apr 09 '14 at 16:59
  • @user1209203 - added clarification of this – SW4 Apr 09 '14 at 17:17
  • @SW4 prepared statements only works for the `WHERE` clause in the sql query. Developers should always sanitize inputs from clients using preferably white-list-filters. – Luc Giffon May 01 '16 at 16:55
11

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

fluminis
  • 3,575
  • 4
  • 34
  • 47