PDO - mysql_real_escape_string

PHP
1

OK. Just started looking into PDO and converting my old mysql_connects to PDO. One thing I cant seem to find is if PDO has a method similar to mysql_real_escape_string. Does it even need one?

2

It doesn’t have one (or atleast I’ve never seen or used one) - generally because it should never need one.

When you make the move over to PDO, use parameter binding instead of just concatenating strings together to make a statement.

3

Mmmmm… Can I have an example please?

Does that mean use something like sprintf?

4

When you use PDO, to get the full benefit you should also be using the PDOStatement class.

http://fr3.php.net/manual/en/ref.pdo.php

http://fr3.php.net/manual/en/function.PDOStatement-bindParam.php

5

PDO->quote escapes a string, but as already mentioned, you rarely need it with PDO.
Bound parameters are indeed quite similar to sprintf. The usage is like:


$db = new PDO(...);
$stmt = $db->prepare("SELECT * FROM foo where name = :name");
$stmt->execute(array(':name' => $name));
foreach ($stmt as $row) {
  var_dump($row);
}
6

OK. I think I’ve got the basics now, but I may have jumped into the deep end a bit!

If I had this array


$arr = array ('id' => null, 'email' => 'foo@bar.com', 'password' => 'iheartpdo', 'active' => 1);

How would I go about inserting the array $values into field $key?


$sql = "INSERT INTO `table` (?,?,?,?) VALUES (?,?,?,?)";
$result = $con->prepare($sql);

$i = 1;
foreach(array_keys($arr) as $k) {
   $result->bindParam($i, $k);
   $i++;
}

foreach($arr as $v) {
   $result->bindParam($i, $v);
   $i++;
}
$result->execute();

Am I close?

7

OK. Had another go because the above code expects 4 parameters. and didn’t work anyway! So…


$prep = '';
$field = '';

foreach(array_keys($arr) as $k) {
	$prep .= ':'.$k.', ';
	$field .= $k.', ';
}	

$prep = substr($prep, 0, -2);
$field = substr($field, 0, -2);

$sql = "INSERT INTO `TABLE` (".$field.") VALUES (".$prep.")";

$result = $con->prepare($sql);

foreach ($arr as $k => $v) {
	$result->bindParam(':'.$k, $v);
}

$result->execute();

Tell me, am I kicking the **** out of this? I’m really hoping there is an easier solution (like using old fashioned mysql_real_escape_string!) :blush:

8

OK. I think I’m probably the only person who cares about this now, but finally did it!

Replace


foreach ($arr as $k => $v) {
    $result->bindParam(':'.$k, $v);
}

$result->execute();

With


foreach ($data as $k => $v) {
        $ins[':'.$k] = $v;    
}

$result->execute($ins);

There. Done! 100% working. I think I’ve just answered my own question though.

9

You’ll get used to it in the end, generally it ends up with more readable code, and there’s no chance of you forgetting to escape stuff and ending up with SQL injection problems…

10

OK. I think I’m probably the only person who cares about this now

Nah. don’t think that.

The benefits may not be obvious at first sight, but it had the effect on me of really starting to separate my db access code from the rest of my PHP - both in my mind and in my scripts.

Absolutely critical if, like me, you then go on to (try and) study good practices of OOP and ‘layering’ your applications.

PDO is a really good ally.

11

I’m starting to get the hang of it now. Such as getting the above script working (It’s written now so I never have to do it again! :stuck_out_tongue: ).

Still having problems with binding parameters.

Eg.


$sql = "SELECT * FROM `books` ORDER BY :order ASC";
$result = $con->prepare($sql);
$result->bindParam(':order' , $order);

$order = 'title';

$result->execute();

The query works except for the order part, which is always by ID and not $order (title OR author)

12

That’s because your binding :order, which is a column name. You can’t bind column names – Only values.

13

@Kyber - LOL, yeah, lost a good couple of hours getting to the bottom of that one myself …

14

Gotcha! Thanks everyone for your help. Finally starting to get my head round it!

15

Yeah. It makes more sense, when you realise how it works internally. What happens is, that the statement and the parameters are send separately to the database server. Because they are separate, the database server can reuse the statement, with different parameters. This gives a slight performance boost, so if you are going to be executing the same query many times, and can be a good idea to prepare the statement once, and then execute() it inside the loop. This design also makes for a better security, since there is no way to do injection, as the parameters are never sent to the parser, inside the database.

Some times, you don’t know the columns at runtime though, so you need to construct the query, as fatnic has done here. I’ve made a wrapper around PDO, with a couple of additions. Take a look, for inspiration.