php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #62528 PDO disregards SQL comments and throws parameter number exceptions
Submitted: 2012-07-11 07:54 UTC Modified: 2021-05-28 11:28 UTC
Votes:7
Avg. Score:4.0 ± 1.3
Reproduced:7 of 7 (100.0%)
Same Version:3 (42.9%)
Same OS:2 (28.6%)
From: robin at industrialwebs dot nl Assigned:
Status: Open Package: PDO related
PHP Version: Irrelevant OS: Independent
Private report: No CVE-ID: None
 [2012-07-11 07:54 UTC] 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.

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2014-01-01 12:36 UTC] felipe@php.net
-Package: PDO related +Package: PDO Core
 [2014-08-15 20:48 UTC] stephen at chomadoma dot net
This still occurs in PHP 5.5.0 with MySQL
 [2017-10-24 08:29 UTC] kalle@php.net
-Package: PDO Core +Package: PDO related
 [2021-05-28 11:28 UTC] cmb@php.net
This is a known issue with emulated prepares; consider to use
native prepares instead.
 [2022-04-05 15:22 UTC] michael dot clase at kaleidescape dot com
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"}
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Thu Oct 31 23:01:28 2024 UTC