getUnquotedStatementFragments breaks on ESCAPE '\'

[rullzer]

I encountered an issue where not all the parameters would be replaced correctly.

The query in question is:

SELECT "f".*
FROM "oc_filecache" "f" 
WHERE 
(
  (
    ("f"."storage" = :dcValue1) AND ("f"."path" LIKE :dcValue2 ESCAPE '\')
  ) OR (
    ("f"."storage" = :dcValue3) AND ("f"."path" LIKE :dcValue4 ESCAPE '\')
  ) OR (
    "f"."storage" = :dcValue5
  )
) AND (
  ("f"."mimetype" <> :dcValue6) OR ("f"."size" = 0)
) AND (
  "f"."path" NOT LIKE :dcValue7
) AND (
  "f"."path" NOT LIKE :dcValue8
)
ORDER BY "f"."mtime"
DESC LIMIT 500

Manually replacing all the values runs this like a charm.
However it seems the split in getUnquotedStatementFragments doesn't like ESCAPING on \.

What happens is that :dcValue1 and :dcValue2 are properly found.
However then the regex interpets the \' as an escaped quote. Thus ignoring until the next escape statement.

The replaces statement thus looks like

SELECT "f".*
FROM "oc_filecache" "f"
WHERE 
(
  (
    ("f"."storage" = ?) AND ("f"."path" LIKE ? ESCAPE "\")
  ) OR (
    ("f"."storage" = :dcValue3) AND ("f"."path" LIKE :dcValue4 ESCAPE "\")
  ) OR (
    "f"."storage" = :dcValue5
  )
) AND (
  ("f"."mimetype" <> :dcValue6) OR ("f"."size" = 0)
) AND (
  "f"."path" NOT LIKE :dcValue7
) AND (
  "f"."path" NOT LIKE :dcValue8
)
ORDER BY "f"."mtime"
DESC LIMIT 500

I'll try to dig a bit deeper or to come up with a PR that at least provides you with failing test cases to verify.

[SenseException]

Thank you for reporting this issue and creating the test case 👍

[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 "f".*
FROM "oc_filecache" "f" 
WHERE 
(
  (
    ("f"."storage" = {:dcValue1}) AND ("f"."path" LIKE {:dcValue2} ESCAPE '\')
  ) OR (
    ("f"."storage" = {:dcValue3}) AND ("f"."path" LIKE {:dcValue4} ESCAPE '\')
  ) OR (
    "f"."storage" = {:dcValue5}
  )
) AND (
  ("f"."mimetype" <> {:dcValue6}) OR ("f"."size" = 0)
) AND (
  "f"."path" NOT LIKE {:dcValue7}
) AND (
  "f"."path" NOT LIKE {:dcValue8}
)
ORDER BY "f"."mtime"
DESC LIMIT 500

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.