Menu
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?
342
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).
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.
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.
To select data from a table using PDO, you can use: The query() method of a PDO object. Or a prepared statement.
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
82
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
