PHP changing from mysql_real_escape_string to PDO prepared statements

Question

I currently use mysql_real_escape_string to escape a variable when querying the database to prevent SQL injection. For example,

   $keyword = mysql_real_escape_string($keyword);
        $guideline = mysql_real_escape_string($guideline);  
        mysql_query("INSERT INTO table1 VALUES('$keyword','$guideline')");

$get = mysql_query("SELECT * FROM table2 WHERE keyword='$keyword'");

  while($row = mysql_fetch_assoc($get)) {
  //code
  }

After reading about SQL injection prevention, i've read this isn't enough to stop SQL injection(so much code to go over now and correct) and i should be using PDO prepared statements? Can i have an example of how to do PDO prepared statements with the same $variables above?

@jeroen: no, it isn't. It is vulnerable to character set related injections. However, this is somewhat harder to achieve by the attacker, but it is still false to claim that one is safe from it. — eis, Jul 03 '12 at 12:36
Thanks, so many conflicting opinions. I don't use any variable to change table names i only use them to change values, mysql_real_escape_string is enough in that scenario? — user892134, Jul 03 '12 at 12:39
I would only be replicating the existing answers so i'll just highly suggest reading this article/tutorial http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/ — Brian, Jul 03 '12 at 12:40
@eis Don't remember about that, I always set the charset after connecting (both in PDO and before in mysql_*)... — jeroen, Jul 03 '12 at 12:40
@eis:So do PDO/prepared statements 100% of the time? How exactly does PDO/prepared statements prevent SQL injection? — user892134, Jul 03 '12 at 12:41
@user892134 Prepared statements are for data. If other stuff, such as table names, limits etc are added to the query, it needs to be dealt with case-by-case. Also, wildchars like % and _ might need special handling anyway. Explaining prepared statements is too big a topic for this comment - there is extensive amount of material on this online. — eis, Jul 03 '12 at 12:46
@jeroen see for example http://ilia.ws/archives/103-mysql_real_escape_string-versus-Prepared-Statements.html — eis, Jul 03 '12 at 12:48
possible duplicate of [Best way to prevent SQL Injection in PHP](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php), which has answers for this question and more. — outis, Jul 03 '12 at 18:56

score 4 · Accepted Answer · answered Jul 03 '12 at 12:35

4

Its pretty simple really:

$db = new PDO($dsn, $user, $password);
$stmt = $db->prepare('INSERT INTO table1 VALUES(?,?)');
$stmt->execute(array($keyword, $guideline));
$stmt->close();

$stmt2 = $db->prepare('SELECT * FROM table2 WHERE keyword= ?');
$stmt->execute(array($keyword));
while(false !== ($row = $stmt->fetch())) {
   // do stuff
}

Note that you can also use named placeholders which can help make your code a bit more readable though a bit more verbose:

$stmt2 = $db->prepare('SELECT * FROM table2 WHERE keyword= :keyword');
$stmt2->execute(array(':keyword' => $keyword));

answered Jul 03 '12 at 12:35

prodigitalson

60,050
10
100
114

2

This is precisely what you asked for OP, so kudos to prodigitalson, but @user892134 I'd caution you to read up on PDO: http://php.net/manual/en/book.pdo.php . Prepared statements do sanitize inputs, but there are other ways to use PDO which do not, and the manual will help you understand those. Great idea to move to using PDO, though. Not a full blown ORM, but enough of an abstraction layer that also helps add some security. – Mattygabe Jul 03 '12 at 12:38

score 3 · Answer 2 · answered Jul 03 '12 at 12:36

First you must create a PDO object:

$dbh = new PDO("mysql:dbname=$dbname", $username, $password);

Then there are different ways to associate your parameters with your queries:

As an argument to execute():

$qry = $dbh->prepare("INSERT INTO table1 VALUES(?, ?)");
$qry->execute(array($keyword, $guideline));

By binding values (retains value assigned at time of function call):

$qry = $dbh->prepare("SELECT * FROM table2 WHERE keyword = ?");
$qry->bindValue(1, $keyword);
$qry->execute();

while ($row = $qry->fetch()) {
  // code
}

By binding parameters (updates when underlying variable changes):

$qry = $dbh->prepare("SELECT * FROM table2 WHERE keyword = ?");
$qry->bindParam(1, $keyword);
$qry->execute();

while ($row = $qry->fetch()) {
  // code
}

You can even use named placeholders instead of anonymous ?:

$qry = $dbh->prepare("SELECT * FROM table2 WHERE keyword = :kw");
$qry->bindValue(":kw", $keyword);

So the second example would be for a static variable and the third example would be for a dynamic variable? — user892134, Jul 03 '12 at 12:49
@user892134: You can use either in either case. The third example is especially useful if you want to execute the query multiple times with different parameter values: then you only need to change `$keyword` and call `$qry->execute()` again -- whereas with the second example you would need to `bindValue()` each time too. — eggyal, Jul 03 '12 at 12:50

score 2 · Answer 3 · answered Jul 03 '12 at 12:37

This one makes more sense

<?php
$stmt = $dbh->prepare("INSERT INTO REGISTRY (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name);
$stmt->bindParam(':value', $value);

// insert one row
$name = 'one';
$value = 1;
$stmt->execute();

// insert another row with different values
$name = 'two';
$value = 2;
$stmt->execute();
?>

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

score 0 · Answer 4 · answered Jul 03 '12 at 12:41

Here is what I do, not claiming to be a ninja, but have been at it for a while.

//connection
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

//get Post data
$name = filter_input(INPUT_POST, $name, FILTER_SANITIZE_STRING);

//SQL
$SQL = $conn->prepare('SELECT * FROM users WHERE user_name=:name;');
$SQL->execute(array(':name' => $name));

//While Loop
while($names = $SQL->fetch(PDO::FETCH_OBJ){
 echo $names->user_email
}

PHP changing from mysql_real_escape_string to PDO prepared statements

4 Answers4