php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #69736 Correct query which contains double quotes gives error
Submitted: 2015-05-31 21:22 UTC Modified: 2015-06-04 13:33 UTC
From: Jan_Oonk at hotmail dot com Assigned: cmb (profile)
Status: Not a bug Package: PDO ODBC
PHP Version: 5.5Git-2015-05-31 (Git) OS: Windows 7 SP1
Private report: No CVE-ID: None
 [2015-05-31 21:22 UTC] Jan_Oonk at hotmail dot com
Description:
------------
Both queries below work inside Microsoft Access 2013:
[1] select * from movie where moviename like 'batman'  
[2] select * from movie where moviename like "batman"

But when I try to run both queries with PHP 5.5.12 and PDO/ODBC only query 1 works as expected. Query 2 throws an error.

I made a testscript varying with different escape characters/methods and using different string quotes.
Especially the last 2 testcases #5 and #6 throw a strange error description. It looks like the double quotes are changed into square brackets: [ and ].

Test script:
---------------
include "../include/connectDB.php";

//Testcases, comment all but one
//Testcase 1: works
$sql="select * from film where titel like 'Batman'";

//Testcase 2: works
$sql='select * from film where titel like \'Batman\'';

//Testcase 3: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
$sql="select * from film where titel like \"Batman\"";

//Testcase 4: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
$sql='select * from film where titel like "Batman"';

//Testcase 5: Syntax error (missing operator) in query expression 'titel like \[Batman\] 
$sql='select * from film where titel like \"Batman\"';

//Testcase 6: Syntax error (missing operator) in query expression 'titel like []Batman[]
$sql='select * from film where titel like ""Batman""';

$result=$dbDB->query($sql);
$rows=$result->fetchAll(PDO::FETCH_ASSOC);  
$result->closeCursor();

foreach($rows as $row) {
  echo $row["TITEL"]."\n";
  echo "<br>";
}

include "../include/closedbs.php";

Expected result:
----------------
It should return 1 record with all details about the movie Batman.

Actual result:
--------------
//Testcase 1: works
$sql="select * from film where titel like 'Batman'";

//Testcase 2: works
$sql='select * from film where titel like \'Batman\'';

//Testcase 3: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
$sql="select * from film where titel like \"Batman\"";

//Testcase 4: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
$sql='select * from film where titel like "Batman"';

//Testcase 5: Syntax error (missing operator) in query expression 'titel like \[Batman\] 
$sql='select * from film where titel like \"Batman\"';

//Testcase 6: Syntax error (missing operator) in query expression 'titel like []Batman[]
$sql='select * from film where titel like ""Batman""';


Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2015-05-31 21:49 UTC] Jan_Oonk at hotmail dot com
In MySQL you can use a single and double quote string delimiter.
So testcases 1-4 works and 5-6 fails as expected.

In Oracle you can only use a single quote string delimiter.
So testcases 1-2 works and 3-6 fails as expected.

As Access also use a single and double quote string delimiter why is it then that testcase 3 and 4 don't work? Is this a bug or is there a way to run a query, on an Access database using PDO/ODBC, containing a double quote string delimiter? I can't use bind parameters, prepared statements or rewrite the query because the query is totaly unknown and given by the user and can also contain syntax error(s). I can't modify this user query and just want it to be executed by PDO/ODBC but how?
I have read that the quote() function is not implemented for PDO/ODBC. Does this has something to do with it?
 [2015-05-31 23:03 UTC] requinix@php.net
-Status: Open +Status: Feedback
 [2015-05-31 23:03 UTC] requinix@php.net
Thank you for this bug report. To properly diagnose the problem, we
need a short but complete example script to be able to reproduce
this bug ourselves. 

A proper reproducing script starts with <?php and ends with ?>,
is max. 10-20 lines long and does not require any external 
resources such as databases, etc. If the script requires a 
database to demonstrate the issue, please make sure it creates 
all necessary tables, stored procedures etc.

Please avoid embedding huge scripts into the report.

Please provide a repro script that isn't dependent on your connectDB.php code.
 [2015-06-01 06:37 UTC] Jan_Oonk at hotmail dot com
-Status: Feedback +Status: Open
 [2015-06-01 06:37 UTC] Jan_Oonk at hotmail dot com
Shorter independent reproducable testscript. 
You only need an Access (.mdb/.accdb) database with at least one table called 'film' and a column 'titel'.
Put some records in it. At least 1 with 'Batman'.


//also using a older Access version of the database "film.mdb" didn't work
//be sure to use full/absolute pathname
$dbnameFile="C:\\wamp\\www\\elearning2\\databases\\film.accdb";
$username="user1";
$password="secret";
$accessdriver="{Microsoft Access Driver (*.mdb, *.accdb)}";
$dbDB = new PDO("odbc:Driver=$accessdriver;Dbq=$dbnameFile", $username, $password, 
                array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));

//Testcases, comment all but one
//Testcase 1: works
$sql="select * from film where titel like 'Batman'";

//Testcase 2: works
$sql='select * from film where titel like \'Batman\'';

//Testcase 3: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
$sql="select * from film where titel like \"Batman\"";

//Testcase 4: COUNT field incorrect: -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
$sql='select * from film where titel like "Batman"';

//Testcase 5: Syntax error (missing operator) in query expression 'titel like \[Batman\] 
$sql='select * from film where titel like \"Batman\"';

//Testcase 6: Syntax error (missing operator) in query expression 'titel like []Batman[]
$sql='select * from film where titel like ""Batman""';

$result=$dbDB->query($sql);
$rows=$result->fetchAll(PDO::FETCH_ASSOC);  
$result->closeCursor();

foreach($rows as $row) {
  echo $row["TITEL"]."\n";
  echo "<br>";
}

$dbDB=null;
 [2015-06-03 11:25 UTC] Jan_Oonk at hotmail dot com
//You can leave the username and password empty:
$username="";
$password="";

//Also you can use a different older accessdriver. This depends on your configuration
$accessdriver="{Microsoft Access Driver (*.mdb)}";

//In that case make sure it's an older Access .mdb format and check file extension is .mdb
$dbnameFile="C:\\wamp\\www\\elearning2\\databases\\film.mdb";
 [2015-06-04 11:57 UTC] cmb@php.net
-Status: Open +Status: Not a bug -Assigned To: +Assigned To: cmb
 [2015-06-04 11:57 UTC] cmb@php.net
I have created a simplified test script and recorded the ODBC
trace: <https://gist.github.com/cmb69/6f090511bd4349161539>. PHP
passes the SQL to SQLPrepare() as is, but the driver[1] considers
the first statement to contain a parameter. The second statement
is mangled by the driver ("" => []). These are obviously
limitations of the driver; I wouldn't call them bugs, because it
is not clear which exact SQL-Syntax is supported by the driver (at
least I have not been able to find the specification).

With regard to your concrete problem: just tell the users that
they must not use double-quotes, but single-quotes (apostrophs).
Treat everything else as syntax error.

[1] Microsoft Access Driver (*.mdb) 6.01.7601.17632
 [2015-06-04 13:00 UTC] Jan_Oonk at hotmail dot com
Thanks for the reply. Obviously I can do nothing about this so I have to accept this. Indeed the only work around is to use single quotes which is fine.

Also I found out that you CAN use double quotes but not as a delimiter for a string inside a query. So this is perfectly fine:
SELECT plantnaam, 1+1 as [bla1 column],2+2 as bla2,3+3 as [bla3] FROM Plant
where plantnaam like '%""%'

gives the following record:
plantnaam  bla1 column  bla2  bla3
RO""RO"RO  2            4     6

Also you see that in Access square brackets are optionally used to define a new columnname, but that it is required when you use a space in the new columnname. In other SQL languages double quotes are used to define a new columnname. For example using Oracle with PHP/PDO I can use:
SELECT plantnaam, 1+2 as "bla1 column",2+2 as bla2,3+3 as "bla3" FROM plant;

Maybe this explains the mangling?
 [2015-06-04 13:33 UTC] cmb@php.net
> Maybe this explains the mangling?

That's possible. The developers of the Microsoft Jet Enginge (Jet
Red) most likely have deeper insights. :)
 
PHP Copyright © 2001-2021 The PHP Group
All rights reserved.
Last updated: Thu Dec 02 11:03:35 2021 UTC