Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 342
Vlada Katlinskaya Avatar asked Feb 15 '17 08:02

Vlada Katlinskaya


People also ask

What does the prepare method of a PDO object return when called successfully?

Return Values ¶ If the database server successfully prepares the statement, PDO::prepare() returns a PDOStatement object. If the database server cannot successfully prepare the statement, PDO::prepare() returns false or emits PDOException (depending on error handling).

Why use prepare in PDO?

Prepared statements are so useful that they are the only feature that PDO will emulate for drivers that don't support them. This ensures that an application will be able to use the same data access paradigm regardless of the capabilities of the database.

How PDO works?

PDO—PHP Data Objects—are a database access layer providing a uniform method of access to multiple databases. It doesn't account for database-specific syntax, but can allow for the process of switching databases and platforms to be fairly painless, simply by switching the connection string in many instances.

How to SELECT data in PDO?

To select data from a table using PDO, you can use: The query() method of a PDO object. Or a prepared statement.


1 Answers

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

like image 82
Your Common Sense Avatar answered Nov 14 '22 23:11

Your Common Sense