Bug #78534 [Opn->Ana]: Query that contains '?' and 'string\' returns less records than must be

Edit report at https://bugs.php.net/bug.php?id=78534&edit=1 ID: 78534 Updated by: requinix@php.net Reported by: mnvx at yandex dot ru Summary: Query that contains '?' and 'string\' returns less records than must be -Status: Open +Status: Analyzed Type: Bug Package: PDO PgSQL Operating System: Ubuntu 18.04 PHP Version: 7.3.9 Block user comment: N Private report: N New Comment: PDO::query() uses the prepared statement system. When pdo_pgsql prepares the statement, it has the standard PDO query parser [1] transform the query to use named ($X) placeholders instead of positional (?). Problem is that PDO assumes backslashes escape quotes, so when it finds 'CHACTECHNOLOGICO\' it thinks the second quote was escaped. That effectively "inverts" what are strings and what are not in the rest of the query, thus the quoted '?' looks unquoted and it gets replaced to '$1'. The query doesn't come with any parameters so PHP doesn't try to send them to the server, and the rewritten SQL is still valid as far as PostgreSQL is concerned, so there's nothing to trigger any errors to indicate a problem. Illia's comment on SO about the question mark is right, however it's just a symptom of the real problem because PDO is smart enough to not transform question marks in strings - or at least not in what it thinks are strings. The real problem is the backslash+quote that tricks the query parser into the wrong state, so removing the backslash makes the query work. A second option is to break the backslash+quote apart, which works in this case because of the nature of the query: SELECT 'CHACTECHNOLOGICO\\ ' as ie_clean A third option usable in the general case is to add a comment with another quote: SELECT 'CHACTECHNOLOGICO\\' as ie_clean -- ' The parser knows about and normally skips comments, however it doesn't recognize that one as such because it thinks it's parsing inside a string; the quote ends the string and the parser continues as it originally should have. [1] https://github.com/php/php-src/blob/php-7.4.0beta2/ext/pdo/pdo_sql_parser.re#L44 Previous Comments: ------------------------------------------------------------------------ [2019-09-13 12:54:30] mnvx at yandex dot ru Description: ------------ Script below returns one record (incorrect) instead of two (expected). This script executed directly in PostgreSQL console returns two rows (correct). For thinking. After replacing 'CHACTECHNOLOGICO\\' to 'CHACTECHNOLOGICO' OR "?" to "-" in both places script returns two records (it is correct). Discussion on stackoverflow: https://stackoverflow.com/questions/57858915 Test script: --------------- <?php $pdo = new \PDO('pgsql:host=localhost;dbname=postgres', 'postgres', 'postgres'); $sql = <<<SQL SELECT * FROM ( SELECT 'CHAC TECHNOLOG*' as alias UNION SELECT 'KINDERY LIGHTING SALES DE?T*' ) m JOIN ( SELECT 'CHACTECHNOLOGICO\\' as ie_clean UNION SELECT 'KINDERYLIGHTINGSALESDEPT' as ie_clean ) t ON t.ie_clean ILIKE REPLACE(REPLACE(REPLACE(m.alias, '*', '%'), '?', '_'), ' ', '') ORDER BY ie_clean; SQL; echo $sql . PHP_EOL . PHP_EOL; $stmt = $pdo->query($sql); print_r($stmt->fetchAll(PDO::FETCH_ASSOC)); Expected result: ---------------- Array ( [0] => Array ( [alias] => CHAC TECHNOLOG* [ie_clean] => CHACTECHNOLOGICO\ ) [1] => Array ( [alias] => KINDERY LIGHTING SALES DE?T* [ie_clean] => KINDERYLIGHTINGSALESDEPT ) ) Actual result: -------------- Array ( [0] => Array ( [alias] => CHAC TECHNOLOG* [ie_clean] => CHACTECHNOLOGICO\ ) ) ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=78534&edit=1