[php-src] Issue #12417: False syntax error in prepared statements in edge case
| From: | Wiikend92 | Date: | Wed, 11 Oct 2023 12:27:54 +0000 |
| Subject: | [php-src] Issue #12417: False syntax error in prepared statements in edge case | ||
| Groups: | php.bugs | ||
| Request: | Send a blank email to php-bugs+get-245545@lists.php.net to get a copy of this message | ||
Issue: https://github.com/php/php-src/issues/12417
Author: Wiikend92
### Description
### Description
I hit a problem yesterday that I can only assume is a bug in the PDO parser. According to my
testing, the error seems to occur when you have these three things inside your query **(order is
important)**:
1. A comment denoted by
#
2. A named placeholder (:myPlaceholder)
3. An apostrophe (') (in my case, a string literal)
When these three things occur in sequence inside the query, you get a false positive error message
for a syntax error that doesn't exist. They don't have to be in direct sequence one after
the other, but they have to be in order.
### Example
Here's a minimal, self-contained example (pay special attention to the SQL inside
prepare()):
```php
<?php
declare(strict_types = 1);
$dbName = "database name"; // Obfuscated
$host = "hostname"; // Obfuscated
$port = 3306;
$charset = "utf8mb4";
$collation = "utf8mb4_danish_ci";
$dsn = "mysql:dbname=" . $dbName . ";host=" . $host . ":" . $port .
";charset=" . $charset;
$username = "username"; // Obfuscated
$password = "password"; // Obfuscated
$options = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // Throw exceptions on error
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ, // Fetch objects by default
PDO::ATTR_EMULATE_PREPARES => false, // Prepare queries natively using the DB driver
PDO::ATTR_STRINGIFY_FETCHES => false, // Do not convert all values to string
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES " . $charset . " COLLATE " .
$collation, // Set charset and collation
];
$db = new PDO(
$dsn,
$username,
$password,
$options
);
$db->exec(
<<<SQL
drop table if exists test;
SQL
);
$db->exec(
<<<SQL
create table test (
id int primary key auto_increment,
first_name varchar(50) null,
last_name varchar(50) null,
age int null
)
SQL
);
$db->exec(
<<<SQL
insert into test
(first_name, last_name, age)
values
('Jack', 'Anderson', 31),
('Lucy', 'And', 9),
('Gregory', 'Jackson', 14),
('Hilda', 'Kopf', 53),
('Brent', 'Birmingham', 48)
SQL
);
// Find all people that could be the child of a given person in the table based on their last name
$stmt = $db->prepare(
<<<SQL
select
*
from
test
where
# This comment's apostrophe is at fault I believe
last_name = concat(:firstName, 'son')
SQL
);
$firstName = "Jack";
$stmt->bindParam(":firstName", $firstName);
$stmt->execute();
foreach ($stmt as $row) {
echo print_r($row, true) . PHP_EOL;
}
```
Resulted in this output:
```
PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You
have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version
for the right syntax to use near ':firstName, 'son')' at line 7 in
<path/to/file>.php:59
Stack trace:
#0 <path/to/file>.php(59): PDO->prepare('select\r\n *\r\nfrom\r\n
test\r\nwhere\r\n # This comment's apostrophe is at fault I believe\r\n last_name =
concat(:firstName, 'son')')
#1 {main}
thrown in <path/to/file>.php on line 59
```
But I expected this output instead:
```
(
[id] => 3
[first_name] => Gregory
[last_name] => Jackson
[age] => 14
)
```
### Workarounds
You can work around this issue by either
- Changing the comment notation from # to --
- Removing the apastrophe from the comment
- Changing the SQL so that you don't need
### PHP Version
PHP 8.1.18 and PHP 8.2.11
### Operating System
Windows 11