php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login

go to bug id or search bugs for

Bug #35795 MySQL driver quotes strings incorrectly with ANSI SQL mode
Submitted: 2005-12-24 18:58 UTC Modified: 2005-12-25 21:31 UTC
From: spaze-bugs at exploited dot cz Assigned: wez (profile)
Status: Closed Package: PDO related
PHP Version: 5.1.1 OS:
Private report: No CVE-ID: None
View Developer Edit
 [2005-12-24 18:58 UTC] spaze-bugs at exploited dot cz 
Description:
------------
I'm running MySQL in ANSI SQL mode [1], which includes the ANSI_QUOTES mode. That means

  /Treat ?"? as an identifier quote character (like the ?`? quote character) and not as a string quote character./

When I use ie. prepared statements I get these queries in the general query log

  INSERT INTO "t_images" ("hash", "width", "height", "imageformat_id") VALUES ("ff2204530628d3c589843ef0b37d344a", "500", "500", NULL)

Which is bad, the strings (the hash) in the VALUES (...) section should be quoted by the ' character. Don't know what would be the best solution, but I think some documented MySQL specific PDO attribute would be Ok.

Thanks for reviewing this issue.

[1] http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html

Reproduce code:
---------------
$dbh = new PDO('mysql:host=mysql41;dbname=test', 'root', '');
$dbh->exec("SET SESSION sql_mode='ANSI'");
echo $dbh->quote('foo');


Expected result:
----------------
'foo'

Actual result:
--------------
"foo"

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2005-12-24 19:04 UTC] wez@php.net 
When you issue queries that change the database session environment like that, PDO has no way to know what you've done without performing all kinds of checks on each query.
There's no reason to slow down the common case for everyone else.

All your problems are solved by using real prepared statements, where explicit quoting is not required.
 [2005-12-24 21:54 UTC] spaze-bugs at exploited dot cz 
PDO has no way to know, what I've done, you're right. But I don't have a way to tell PDO that the environment got changed and that it should quote a little different. I don't mean that it should do runtime checks (like SELECT @@sql_mode), but some specific attibute, as I've already written.

Thanks for pointing me to the native prepared statements, but as I've read the source a little, I see that whether to use native prepared statements or not, is a compile-time option and it's not exposed by ie. phpinfo(). So some kind of end-user has no way to know, if native prepared statements are used or not, especially if he's using some precompiled binary ie. Windows distribution. Am I right?

Well, it seems to me that a solution to my problem with quoting in ANSI mode is turning off the ANSI mode (and quote column names with backtick) more than native prepare statements.
 [2005-12-25 13:19 UTC] spaze-bugs at exploited dot cz 
According to MySQL manual

  A string is a sequence of characters, surrounded by either single quote (?'?) or double quote (?"?) characters. [...] If the server SQL mode has ANSI_QUOTES enabled, string literals can be quoted only with single quotes.

So the quoter should use single quotes with emulated prepared statements (instead of double quotes) to be compatible with both SQL modes.

Thus, reopening.
 [2005-12-25 17:37 UTC] sniper@php.net 
Assigned to the PDO maintainer.
 [2005-12-25 20:27 UTC] wez@php.net 
It's not a compile time option.
If your environment supports native statements, they will work.  If it doesn't, PDO will emulate them.
Using prepared statements is strongly recommended over manually building queries, for performance and readability, and because it reduces the risk of SQL injection.
 [2005-12-25 21:31 UTC] iliaa@php.net 
MySQL driver now uses ANSI complain quoting style by default.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved. 		Last updated: Fri Dec 27 02:01:29 2024 UTC