php - Why PDO doesn't allow multiple placeholders with the same name?

Question

I'm using PHP, and MySQL with PDO. Sometimes I need to prepare a statement with one variable (placeholder) used more than once in this query.

Example:

SELECT * FROM messages WHERE from_id = :user OR to_id = :user

However if I will try to prepare this statement I will have an error so I need to do this in a way like this:

SELECT * FROM messages WHERE from_id = :user1 OR to_id = :user2

To call this statement I will need to have an array like this:

array('user1'=>$user_id, 'user2'=>$user_id);

It looks so stupid for me! Why MySQL (PDO?) don't allowing me to use one place holder more than once and forcing me to use extra variables which requires more control?!

This can be handled easy if the query is relatively simple (like I posted above), but now I built a query with 5 (!!!) uses of single variable. Each time I add the placeholder I need to check the code in many places to make it OK.

Is there any setting or a tweak to bypass this?

Answer 1

Is there any setting or a tweak to bypass this?

Yes, there is. You can turn emulation mode ON and be able to use the same placeholder multiple times.

So the described behavior is observed only when the emulation is turned OFF. I don't really understand why it is so but here is an explanation from Wez Furlong (the PDO author):

The change was made for two reasons; first and foremost, if you re-use the same variable in a bind, it is possible to induce a crash when using some drivers. It’s not possible to guarantee to do the right thing, and having a way to trigger a crash can sometimes be used as an attack vector for a security exploit.

The second reason is that of portability. Some drivers would internally perform this check and error out. If you code against the drivers that don’t enforce this, then your code won’t work on those that don’t.

http://paul-m-jones.com/archives/243#comment-740