Sql with LIKE ESCAPE '\' breaks replacing named parameters to '?' and leads to a sql error

[vavra]

See attached test. If as a like escape character is used '~' everything is OK. If one replaces ~ to \ than an sql error occurs:

An exception occurred while executing

SELECT NAME FROM DOCUMENT
WHERE (ID_FLDR_DOCU = ?)			
	AND (NAME LIKE ? ESCAPE '\')
	AND (
		(A = :p2_A)		
		OR (
			B IS NULL
			OR B = ''
			)
		)
	AND (
		C IS NOT NULL
		AND C <> ''
		)		
ORDER BY NAME ASC OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY

with params [3, "%1\_\\\_5bfff30585b261.25683875.pdf"]:

SQLSTATE [42000, 102]: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near ':'.

Because :p2_A is not translated into ?, counted parameter.

test_like_escape_char.zip

Tested on doctrine/dbal v 2.8.0 and 2.9.x-dev

[morozov]

The problem seems to be in SQLParserUtils::expandListParameters() which considers \ an escaping character regardless of the platform, however it's only true for MySQL.

[morozov]

This must have been fixed by #4397, no longer reproducible on 3.1.x. In the ANSI mode, the query in question is parsed as:

SELECT NAME FROM DOCUMENT
WHERE (ID_FLDR_DOCU = {:folder_id})			
	AND (NAME LIKE {:p1_NAME} ESCAPE '\')
	AND (
		(A = {:p2_A})		
		OR (
			B IS NULL
			OR B = ''
			)
		)
	AND (
		C IS NOT NULL
		AND C <> ''
		)		
ORDER BY NAME ASC OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY

The curly braces identify what has been recognized as a parameter (see Doctrine\DBAL\Tests\SQL\ParserTest#testAnsiEscaping()).

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.