Right, currently all queries in Zend_Db are prepared with the
PDO::prepare() method (if you use one of the PDO adapters).
As far as I know, this issue cannot be fixed in the Zend Framework. It
would have to be fixed in PDO.
A workaround exists: you can gain access to the PDO connection and then
run queries without the prepare()/execute() separation, if your SQL
statement conflicts with this PDO issue.
$sql = "SELECT 'string containing :colon'";
$stmt = $db->getConnection()->query($sql); // skips the prepare()
$stmt->fetchAll();
...
Regards,
Bill Karwin
Tautvydas Andrikys wrote:
So this means that it is unsafe to use
Zend_Db_Select::where($cond, $val)
Zend_Db_Select::orWhere($cond, $val)
...
Because these methods bind params before sending queries to
PDO::prepare, am I right?
If so will this be fixed in Zend_Framework or maybe in PDO?
Bill Karwin wrote:
Okay, I think I know what is happening now.
Zend_Db does a PDO->prepare() call on the string you send. The
strings ':pdo' and ':xe' are recognized by PDO as named parameter
placeholders. The MySQL PDO driver substitutes the '?' character
because that is what MySQL understands.
This is intended to work this way:
$pdoStmt = $pdo->prepare('SELECT * FROM table WHERE column = :param');
$pdoStmt->execute(array(':param' => 'some value'));
See http://www.php.net/manual/en/function.PDO-prepare.php for full
explanation.
This is behavior of PDO. Zend Framework only calls PDO->prepare()
with the same string. PDO->prepare() is making the substitution. If
you use PDO->prepare() followed by PDOStatement->execute() you get
the same issue. When you use PDO->query() in the way that you showed
in your example, it does not scan for parameters, and you don't get
this issue.
PDO seems to be a little too enthusiastic regarding recognizing
parameter placeholders versus other strings that contain the ":"
colon character. One workaround is to make sure that strings
containing the colon character are themselves sent as parameterized
values:
$pdoStmt = $pdo->prepare('SELECT * FROM table WHERE column = :param');
$pdoStmt->execute(array(':param' => 'break:pdo \\ xe:xe'));
The parameter value is never scanned for parameter placeholders.
Regards,
Bill Karwin
Tautvydas Andrikys wrote:
Code sample with Zend_Db.
class SomeTable extends Zend_Db_Table{
public function testPDO(){
$result = $this->_db->fetchOne(
'SELECT '.$this->_db->quote('break:pdo \\ xe:xe')
);
Zend::dump($result);
// Dumps: break? \ xe?
// Expected result was: break:pdo \ xe:xe
// Looks like PDO replases ':pdo', ':xe' to '?', although no
variables ar bound
exit;
}
}
And as I wrote earlier MySQL mysql_query(); works fine
Bill Karwin wrote:
Tautvydas Andrikys wrote:
Noticed this while doing query with
Zend_Db_Adapter_Pdo_Mysql::fetchOne(...). After digging deeper
into code I've found out that there is something wrong with PDO
query execution. PDO somehow strangely executes this query:
SELECT 'break:pdo \ xe:xe'
Your code samples do not use Zend_Db and they seem to work as
expected.
That is, in MySQL, SELECT 'quoted string' should return the quoted
string literally.
Can you please supply a sample that uses Zend_Db and behaves
strangely. Show the code where you invoke
Zend_Db_Adapter_Pdo_MySQL::fetchOne(). Explain what results you
expected, and show what results you got that you did not expect.
Regards,
Bill Karwin
Tautvydas Andrikys aka esminis
Tautvydas Andrikys aka esminis
