php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #49381 PDO mysql prepare incorrectly quoting
Submitted: 2009-08-27 03:31 UTC Modified: 2009-09-23 23:42 UTC
Votes:1
Avg. Score:4.0 ± 0.0
Reproduced:1 of 1 (100.0%)
Same Version:0 (0.0%)
Same OS:0 (0.0%)
From: eprayner at gmail dot com Assigned:
Status: Not a bug Package: PDO related
PHP Version: 5.2SVN-2009-08-27 (SVN) OS: Linux
Private report: No CVE-ID: None
 [2009-08-27 03:31 UTC] 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.

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2009-08-27 03:35 UTC] 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-09-18 08:19 UTC] uw@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-09-18 10:46 UTC] 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 12:42 UTC] uw@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 15:35 UTC] 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 16:21 UTC] uw@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-23 15:59 UTC] sjoerd@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-23 23:39 UTC] eprayner at gmail dot com
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.
 [2009-09-23 23:42 UTC] eprayner at gmail dot com
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 a bug 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.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Fri Apr 19 21:01:30 2024 UTC