php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #66434 PDO not binding when columns with quote
Submitted: 2014-01-07 14:13 UTC Modified: 2017-04-24 19:25 UTC
From: immobilia at hotmail dot com Assigned:
Status: Open Package: PDO related
PHP Version: Irrelevant OS: Windows/Linux
Private report: No CVE-ID:
Have you experienced this issue?
Rate the importance of this bug to you:

 [2014-01-07 14:13 UTC] immobilia at hotmail dot com
Description:
------------
In certain cases, binding is not working.
In my case, it's due to the quotes : one in a column name, and two others in DATE_FORMAT

"SELECT d1, ROUND( SUM(`col1 with space and' quote`) / SUM(`col1 with space`) * 100 , 2) AS `Percent`
FROM test WHERE d1 BETWEEN :condition1 AND :condition2
GROUP BY DATE_FORMAT(d1, '%Y-%m')"
// FAILLED : KO on execute : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':condition1 AND :condition2 GROUP BY DATE_FORMAT(d1, '%Y-%m')

"SELECT d1, ROUND( SUM(`col1 with space and' quote`) / SUM(`col1 with space`) * 100 , 2) AS `Percent`
FROM test WHERE d1 BETWEEN :condition1 AND :condition2
GROUP BY DATE_FORMAT(d1, " . '"%Y-%m")'
// SUCCESS : OK on execute

Tried with : 
WINDOWS + PHP Version 5.4.17 + PDO : Client API version mysqlnd 5.0.10 - 20111026
LINUX + PHP Version 5.3.10 + PDO : Client API version 5.5.34

Test script:
---------------
/*
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `col1 with space` int(11) NOT NULL,
  `col1 with space and' quote` int(11) NOT NULL,
  `d1` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
*/

$dsn = 'mysql:host=localhost;dbname=mydb';
$username = 'sql_user';
$password = 'sql_pass';
$driver_options = array(
		PDO::ATTR_TIMEOUT => 3 ,
		PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
);
$pdo = new PDO($dsn, $username, $password, $driver_options);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$condition1 = '2010-01-01';
$condition2 = date("Y-m-d");

$query = "SELECT d1, ROUND( SUM(`col1 with space and' quote`) / SUM(`col1 with space`) * 100 , 2) AS `Percent`
FROM test WHERE d1 BETWEEN :condition1 AND :condition2
GROUP BY DATE_FORMAT(d1, '%Y-%m')"
;	// KO on execute : SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':condition1 AND :condition2 GROUP BY DATE_FORMAT(d1, '%Y-%m')

// $query = "SELECT d1, ROUND( SUM(`col1 with space and' quote`) / SUM(`col1 with space`) * 100 , 2) AS `Percent`
// FROM test WHERE d1 BETWEEN :condition1 AND :condition2
// GROUP BY DATE_FORMAT(d1, " . '"%Y-%m")'
// ;	// OK on execute

$stmt = $pdo->prepare($query);
$stmt->bindParam(':condition1', $condition1);
$stmt->bindParam(':condition2', $condition2);
$stmt->execute();

Expected result:
----------------
execute() working with standard binding.

Actual result:
--------------
execute() has no replaced binding and throw an exception : 
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':condition1 AND :condition2 GROUP BY DATE_FORMAT(d1, '%Y-%m')

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2017-04-24 19:25 UTC] adambaratz@php.net
The SQL parser gets confused when it sees quotes. A workaround would be to bind a parameter with the date format string. It might also be worth trying to disable emulated prepares for better performance.
 
PHP Copyright © 2001-2017 The PHP Group
All rights reserved.
Last updated: Fri Apr 28 02:01:36 2017 UTC