Question Mark in Field Alias Issue

[sma27]

Bug Report

Q	A
Version	4.2.2

pdo_mysql driver

Summary

Positional parameter at index 0 does not have a bound value error for question marks used within quotes.

Current behavior

MySQL query like the following:
SELECT my_question AS 'Why do you like turtles?'
FROM...

Causes an error: "Positional parameter at index 0 does not have a bound value." despite being enclosed in single quotes.

Modifying the query as follows:
SELECT my_question AS 'Why do you like turtles??'
FROM...

Returns two question marks in the result instead of one. As far as I can tell there is no way to use question marks in field aliases.

Expected behavior

I would expect the question mark to be treated as a literal within single quotes. Or, if that is not the case, I'd expect the escaped question mark to return a single question mark.

P.S. I am not using the query builder to perform this query. The code and query ran fine in Laminas DB. I am currently porting to Doctrine DBAL.

[sma27]

It looks like the problem is actually the apostrophe mixed with a question mark.

SELECT my_question AS 'Why you''d like turtles?'
FROM...

Seems to work

But

SELECT my_question AS 'Why you\'d like turtles?'
FROM...

Causes an exception, shouldn't either work?

The problem seems to be caused with the combination of "\'" and "?" if I remove the question mark the problem also goes away. It must trip some strange case when parsed.

[morozov]

The SQL parser supports MySQL and ANSI literal escaping syntaxes. It looks like MySQL (the database) supports both of them:

SELECT ''''; -- ANSI
SELECT '\''; -- MySQL

Both of the above queries yield a single quote on MySQL.

But the parser only supports the MySQL syntax with MySQL. A proper solution would be to rework the parser to support both syntaxes on MySQL. A workaround would be to use the MySQL syntax with MySQL.

If you need to run the same queries on various platforms and use the ANSI syntax, you can use a custom MySQL platform class that overrides createSQLParser() until this issue is fixed.

[morozov]

Could you try reproducing the issue with the ParserTest by adding a new element to this data provider?

dbal/tests/SQL/ParserTest.php

Lines 390 to 391 in 35741d6

	public static function mySQLParametersProvider(): iterable
	{

The following doesn't seem to fail the test:

        yield 'ANSI syntax' => [
            <<<'SQL'
SELECT my_question AS 'Why you\'d like turtles?'
SQL
            ,
            <<<'SQL'
SELECT my_question AS 'Why you\'d like turtles?'
SQL
            ,
        ];