ID: 49381 Updated by: u...@php.net Reported By: eprayner at gmail dot com Status: Open Bug Type: PDO related Operating System: Linux PHP Version: 5.2SVN-2009-08-27 (SVN) New Comment:
PDO is an API abstraction layer. It neither does abstract SQL differences nor does PDO pay much attention to provide a unified type system. Users need to pay attention to differences between SQL dialects on their own. I understand that it would be helpful to have summary on SQL differences somewhere but on the other hand I would understand the documentation team to just link to any such document but keep details itself out of the core documentation. Just my thoughts... I am not sure what you mean by "efficiency payoff". A client side emulation of PS has different properties than server side PS. IMHO there is no clear line on what is preferrable. The PDO SQL parser is provided by the PDO core. This is a tricky design decision because it is one SQL parser for all the different SQL dialects. The PDO SQL parser is very generic and you can find edge cases in the bug system where it fails. Even if the client side emulation may give you a feature you want, you should be aware of the overall design and not forget about its limitations. What happens in this case is that PDO parses your statement, recognizes a placeholder and tries to replace it with the bound value. To prevent SQL inject attacks, PDO asks MySQL to escape the value that the PDO parser will insert for the placeholder. The PDO parser would need to learn that the placeholder is an identifier and you don't want escaping to happen. Two solutions come to my mind: either you allow users to hint PDO that nothing shall be escaped or you take the safe but stony road of improving the PDO parser and teach it (for all SQL dialects!) what an identifier is. Both solutions would require changing the core of PDO. Previous Comments: ------------------------------------------------------------------------ [2009-09-18 10:46:45] eprayner at gmail dot com OK. At http://dev.mysql.com/doc/refman/5.1/en/prepare.html it says 'Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.' So either this is a restriction for php PDOs, in which case it should be explained in the documentation, or it is a problem with php's 'PDO prepared statement emulation parser', as you say. It is nice to know, at least, that even if php PDOs were 'improved' to handle 'column parameter markers', there would be no efficiency payoff (with mysql at least). ------------------------------------------------------------------------ [2009-09-18 08:19:53] u...@php.net It is not a MySQL bug. MySQL native prepared statements to not support using bind variables as identifiers. http://dev.mysql.com/doc/refman/5.1/en/prepare.html At most it is a bug of the PDO prepared statement emulation parser. ------------------------------------------------------------------------ [2009-08-27 03:35:02] eprayner at gmail dot com MYSQL Server version: 5.0.67-0ubuntu6 (Ubuntu) >From reading other bugs, I'm beginning to think this is a MySQL bug, rather than a PHP bug. ------------------------------------------------------------------------ [2009-08-27 03:31:03] eprayner at gmail dot com Description: ------------ When using PDO prepare for mysql, quotes are incorrectly inserted around column names, resulting in errors or unexpected results. This problem would have been _much_ easier to diagonise if there was a way of seeing the actual statement. Something like: $string PDOStatement::executeString()---returns the statement that would have been executed by PDOStatement::execute(). Reproduce code: --------------- //given a mysql connection $pdo //and a database table 'myTable' with columns: id, col1, col2, col3 //with a row: 1, value1, value2, value3. $stmt=$pdo->prepare("SELECT ? FROM myTable WHERE id=?"); $myColumn = 'col1'; $stmt->execute(array($myColumn, 1)); $row=$stmt->fetch(); print_r($row); Expected result: ---------------- I'd expect to see: "value1" displayed, as you'd expect for the statement: "SELECT col1 FROM myTable WHERE id=1" Actual result: -------------- What is displayed is: "col1", as you'd expect for the statement: "SELECT 'col1' FROM myTable WHERE id=1" Other statements result in errors. Example: $stmt=$pdo->prepare("UPDATE myTable SET ?=? WHERE id=?"); $stmt->execute(array($myColumn, $myValue, $myId)); is a syntax error, as is the SQL: UPDATE myTable SET 'col1'=3 WHERE id=1; This problem means that I cant use prepare and execute statements at all. ------------------------------------------------------------------------ -- Edit this bug report at http://bugs.php.net/?id=49381&edit=1
