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:
"The 'efficiency payoff' I was talking about is mentioned in various prepared statement documentation. The DB engine is meant to be able to save some prep time. Obviously mysql would not be saving time if 'column parameter markers' were allowed in PDO." Not every database supports prepared statements. And depending on the MySQL version not all statements can be prepared. Some databases offer named parameters others don't. MySQL doesn't. To overcome all the differences between the various databases PDO has to have an emulation layer. If the emulation gets used, you don't have server side prepared statments. Its kind of client side prepared statements. The two concepts are different. And, yes, with client side prepared statements you don't benefit from certain DB caches. However, different DB engines, different benefits of prepared statements vs. "regular" statements. In case of MySQL the time from getting a statement to start of query execution is rather short. The benefit of the prepare can be small. Previous Comments: ------------------------------------------------------------------------ [2009-09-18 15:35:44] eprayner at gmail dot com A few comments about that. I think the PDO should attempt to be clear about what is supported and what isn't. If something doesn't work everywhere, that should be noted. The 'efficiency payoff' I was talking about is mentioned in various prepared statement documentation. The DB engine is meant to be able to save some prep time. Obviously mysql would not be saving time if 'column parameter markers' were allowed in PDO. 'The PDO SQL parser is provided by the PDO core.' Really? I thought it'd just translate to mysql prepared statements!?! Sure I read that somewhere. Either way, it could be fixed. '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.' I don't mind 'escaping', the final string should not be quoted though. '...take the safe but stony road of improving the PDO parser and teach it (for all SQL dialects!) what an identifier is.' Surely you've got to translate to an SQL dialect at some point. The trick is to do it right!--Not quote mysql identifiers for example. ------------------------------------------------------------------------ [2009-09-18 12:42:12] u...@php.net 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. ------------------------------------------------------------------------ [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. ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/49381 -- Edit this bug report at http://bugs.php.net/?id=49381&edit=1
