Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Support server side prepared statements with PDO?

Given something like

DB()->prepare("SELECT * FROM mysql.general_log WHERE user_host LIKE ?");

$statement->execute( array('%console%') );

foreach($statement as $record){
    var_dump($record);
}

Contents of general_log is

*************************** 1. row ***************************
event_time: 2011-04-20 14:27:59
user_host: REDACTED[REDACTED] @ REDACTED [192.168.56.101]
thread_id: 30
server_id: 0
command_type: Connect
argument: REDACTED@REDACTED on REDACTED
*************************** 2. row ***************************
event_time: 2011-04-20 14:27:59
user_host: REDACTED[REDACTED] @ REDACTED [192.168.56.101]
thread_id: 30
server_id: 0
command_type: Query
argument: SELECT * FROM mysql.general_log WHERE user_host LIKE '%console%'

I'm working inside of an abomination framework ( no unit-tests, no documentation, no ryhme or reason ) so is it possible that somewhere someone explicitly disabled MySQL prepared statements forcing PDO to use emulated mode... or is this expected behavior?

PHP is PHP Version 5.2.10-2ubuntu6 PDO Driver for MySQL, client library version 5.1.41

Update: PDO() is constructed with the the following attributes

PDO::ATTR_PERSISTENT => false
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true

I went through the PDO documentation, but unfortunately there doesn't seem to be mention of a flag like the one written about in this similar question

like image 520
David Avatar asked Apr 20 '11 20:04

David


People also ask

Does PDO use prepared statements?

PDO will emulate prepared statements/bound parameters for drivers that do not natively support them, and can also rewrite named or question mark style parameter markers to something more appropriate, if the driver supports one style but not the other.

What do you mean by PDO?

Protected Denomination of Origin: a geographical indication defined within European Union law in order to protect regional agricultural products and foodstuffs. Collins English Dictionary. Copyright © HarperCollins Publishers.

How does PDO prepare work?

How PDO Prepared Statements Work. In layman's terms, PDO prepared statements work like this: Prepare an SQL query with empty values as placeholders with either a question mark or a variable name with a colon preceding it for each value. Bind values or variables to the placeholders.

What type of object is returned by PDO :: prepare ()?

PDO::query() prepares and executes an SQL statement in a single function call, returning the statement as a PDOStatement object.


1 Answers

Some PDO drivers don't support native prepared statements, so PDO performs emulation of the prepare. It also lets you manually enable this emulation.

Check the PDO::ATTR_EMULATE_PREPARES attribute. It's poorly documented in the current PDO manual. By poorly documented, I mean that it appears only in comments on the site, not in the manual itself.

Generally you want to use native prepared statements whenever possible. In the case of MySQL, if you are taking advantage of the query cache, you might actually want to disable native prepared statements in PDO! The MySQL manual has more information, but the short version is that versions prior to 5.1.17 don't run prepared statements through the query cache, and subsequent versions only use the query cache under certain specific (but common) conditions.

(Some people recommend turning off the query cache entirely. Using large cache sizes can actually be a major performance hit.)

like image 171
Charles Avatar answered Sep 29 '22 08:09

Charles