ansaurus

Question

PDO: does prepare() escape all data, even if not bound?

Answer 1

+2 A:

No, prepare only escapes data that uses placeholders.

jmz 2010-09-08 13:53:06

Answer 2

+3 A:

My question is: Does prepare() escape or properly handle ALL data within? Or just data that is binded by execute /parameter bind?

While there may be security measures to prevent unescaped data from entering the system, you can't assume anything you put into the query directly gets escaped properly.

Always bind parameters for all incoming data.

Pekka 2010-09-08 13:53:29

Ouch, so this makes certain escaping impossible without a fairly heavy custom function (that mimicks mysql_real_escape_string's encoding checks).. ?

John 2010-09-08 14:00:07

@John numbers aren't a problem: Their getting enclosed in quotes doesn't hurt (plus if you want to specify a numeric type, you can use [`bindParam`](http://www.php.net/manual/en/pdostatement.bindparam.php) and specify a [data type](http://www.php.net/manual/en/pdo.constants.php)). With table names, as far as I can see (and as [previously discussed](http://stackoverflow.com/questions/3665784/php-pdo-prepared-statements-dont-work-when-creating-a-table)), I think yes.

Pekka 2010-09-08 14:03:47

Alright, I do not need to worry about table names so I'll just stick with bindParam. Thank you.

John 2010-09-08 14:08:40

@John you HAVE to worry about table names, as well as you have to worry about bad design at all.

Col. Shrapnel 2010-09-08 14:12:22

@Pekka: Treating numbers like strings will burn you if you switch to another DBMS later. Or even code against another one in another app.

R. Bemrose 2010-09-08 14:14:38

but a function that mimicks mysql_real_escape_string's encoding checks is a nightmare. Again, identifier is not data. And has nothing to do with mysql_real_escape_string and do not need any encoding checks

Col. Shrapnel 2010-09-08 14:16:41

I hate such "accepted" answers, which helps nothing and only leads to another stupid questions

Col. Shrapnel 2010-09-08 14:17:43

@R.Bemrose good point! Duly noted. @Col there was a perfectly legitimate use case for dynamic table names in the other question. I know that building applications that run under limited circumstances is beneath you, but for some people, it is not. I agree though that writing a mock escape function is horrible.

Pekka 2010-09-08 14:29:47

@Col: I mentioned it was way too late for me, I was able to indirectly clear 5-6 problems with an indirect answer.

John 2010-09-08 14:49:23

Answer 3

A:

No; AFAIK $-expansion is handled directly by PHP, and "foo $bar baz" is equivalent to "foo " . $bar . " baz".

tc. 2010-09-08 13:56:34

What does this have to do with my question about adding them directly? The question was about if `prepare()` escaped including $bar, not that $bar was taken by PDO and escaped separately.

John 2010-09-08 14:02:14

`"INSERT INTO $TABLE_NAME ..."` there you have your php variable (i.e. you've opened the door for this answer ;-)) and pdo doesn't handle this. If e.g. $TABLE_NAME contains a space pdo will not automagically quote it with grave accents.

VolkerK 2010-09-08 14:14:32

Let's try to explain with a concrete example: if $TABLE_NAME is "foo default values; drop table foo; insert into bar", prepare() *just* sees "insert into foo default values; drop table foo; insert into bar ...". There's no way for prepare() to tell the difference between `$foo = abc; prepare("$foo")` and `prepare("abc")`.

tc. 2010-09-12 02:43:29

ansaurus

tags:

views:

answers:

PDO: does prepare() escape all data, even if not bound?

related questions