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
– eis, Commented Jul 3, 2012 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
– user892134, Commented Jul 3, 2012 at 12:39
I would only be replicating the existing answers so i'll just highly suggest reading this article/tutorial net.tutsplus.com/tutorials/php/… — Brian
– Brian, Commented Jul 3, 2012 at 12:40
@eis Don't remember about that, I always set the charset after connecting (both in PDO and before in mysql_*)... — jeroen
– jeroen, Commented Jul 3, 2012 at 12:40
@eis:So do PDO/prepared statements 100% of the time? How exactly does PDO/prepared statements prevent SQL injection? — user892134
– user892134, Commented Jul 3, 2012 at 12:41

prodigitalson · Accepted Answer · 2012-07-03 12:35:35Z

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 3, 2012 at 12:35

prodigitalson

60.6k10 gold badges106 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mattygabe Over a year ago

This is precisely what you asked for OP, so kudos to prodigitalson, but @user892134 I'd caution you to read up on PDO: 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.

eggyal · Accepted Answer · 2012-07-03 12:36:01Z

3

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);

answered Jul 3, 2012 at 12:36

eggyal

127k18 gold badges221 silver badges245 bronze badges

2 Comments

user892134 Over a year ago

So the second example would be for a static variable and the third example would be for a dynamic variable?

eggyal Over a year ago

@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.

FatalError · Accepted Answer · 2012-07-03 12:37:54Z

2

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

answered Jul 3, 2012 at 12:37

FatalError

98414 silver badges33 bronze badges

Comments

McCarvill · Accepted Answer · 2012-07-03 12:41:02Z

0

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
}

answered Jul 3, 2012 at 12:41

McCarvill

411 silver badge3 bronze badges

Collectives™ on Stack Overflow

PHP changing from mysql_real_escape_string to PDO prepared statements

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related