Bug #62528 [Com]: PDO disregards SQL comments and throws parameter number exceptions
Edit report at https://bugs.php.net/bug.php?id=62528&edit=1
ID: 62528
Comment by: michael dot clase at kaleidescape dot com
Reported by: robin at industrialwebs dot nl
Summary: PDO disregards SQL comments and throws parameter
number exceptions
Status: Open
Type: Bug
Package: PDO related
Operating System: Independent
PHP Version: Irrelevant
Block user comment: N
Private report: N
New Comment:
The same bug also causes problems when a comment in the SQL contains an apostrophe. I'd guess
the parsing code is treating everything from that apostrophe to the next single quote as a string
literal, and doesn't parse the parameters in that section of the text.
Works as expected when there are an even number of single quotes in the comment or no single quotes
in the SQL after the comment.
Seen in PHP 5.5.10.
Here is a very simple example:
message 'SQLSTATE[HY093]: Invalid parameter number: :x;
SQL: SELECT x
FROM ( VALUES ('foo'), ('bar') ) AS t(x)
-- you'll be surprised that this fails
WHERE x = :x OR x = 'bar'; Bindings: {":x":"foo"}
Previous Comments:
------------------------------------------------------------------------
[2021-05-28 11:28:23] cmb@php.net
This is a known issue with emulated prepares; consider to use
native prepares instead.
------------------------------------------------------------------------
[2014-08-15 20:48:25] stephen at chomadoma dot net
This still occurs in PHP 5.5.0 with MySQL
------------------------------------------------------------------------
[2012-07-11 07:54:23] robin at industrialwebs dot nl
Description:
------------
Description can also be found here:
http://stackoverflow.com/questions/11415314/pdo-invalid-parameter-number-parameters-in-comments/
The problem is simple: PDO throws an exception when using named or positional parameters in SQL
comments. This is unexpected behaviour and costed me quite a while to figure out.
The thrown exceptions for named and positional parameters are, respectively:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number:
Warning: PDOStatement::execute() [pdostatement.execute]: SQLSTATE[HY093]: Invalid parameter number:
mixed named and positional parameters
Test script:
---------------
Try executing this query using PDO:
SELECT
x
FROM
y
WHERE
-- CHECKING IF X = ? --
x = :y
AND
1 = 2
Or this one:
SELECT
x
FROM
y
WHERE
-- CHECKING IF X = :Z --
x = :y
AND
1 = 2
Expected result:
----------------
This should execute the query with only :Z as bound parameter.
Actual result:
--------------
Exceptions because parameters in comments get parsed.
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=62528&edit=1
Thread (4 messages)