Bug #69736 [Com]: Correct query which contains double quotes gives error
Edit report at https://bugs.php.net/bug.php?id=69736&edit=1
ID: 69736
Comment by: Jan_Oonk at hotmail dot com
Reported by: Jan_Oonk at hotmail dot com
Summary: Correct query which contains double quotes gives
error
Status: Open
Type: Bug
Package: PDO ODBC
Operating System: Windows 7 SP1
PHP Version: 5.5Git-2015-05-31 (Git)
Block user comment: N
Private report: N
New Comment:
//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";
Previous Comments:
------------------------------------------------------------------------
[2015-06-01 06:37:41] 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-05-31 23:03:07] 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-05-31 21:49:49] 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 21:22:16] 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""';
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=69736&edit=1
Thread (8 messages)