ID: 49381 User updated by: eprayner at gmail dot com Reported By: eprayner at gmail dot com Status: Bogus Bug Type: PDO related Operating System: Linux PHP Version: 5.2SVN-2009-08-27 (SVN) New Comment:
I just tried to submit the following as a note to http://us.php.net/manual/en/pdo.prepare.php However I got this message saying that I was a spammer, according to one of the lists at: http://www.dnsbl.info/index.php?IP=123.2.13.168 Checking that site, my IP is not listed as a spamming IP according to any of their lists. Perhaps this is another bug? I do disagree with setting the status of this bug to bogus. It is _at least_ a documentation omission. There is also the point that it would be really useful to be able to see the actual SQL string. My note (please feel free to submit this anyone.) ---------------------------- Watch out for the following when using pdo prepare (with mysql) //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); You'd expect to see: "value1" displayed, as you'd expect for the statement: "SELECT col1 FROM myTable WHERE id=1" 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; Apparently this is not an error as PDO can't work out that $myColumn should be a column name, and so not quoted. Errors like this are hard to diagnose as it is not possible to 'see' the underlying SQL string. I hope this note saves you the time and frustration I experienced. Previous Comments: ------------------------------------------------------------------------ [2009-09-23 15:59:33] sjo...@php.net I am setting this bug to Bogus: the passed strings are properly escaped and PDO can not figure out that the passed strings are column names. ------------------------------------------------------------------------ [2009-09-18 16:21:51] u...@php.net "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. ------------------------------------------------------------------------ [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). ------------------------------------------------------------------------ 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
