SQL LIKE comparisons in SQLite: Escaping doesn't work

[marcelklehr]

Steps to reproduce

1. Install bookmarks app (I believe the bug lies much deeper, but I'll demonstrate it with a concrete example. It's probably pretty easy to adapt the steps here to utilize other database entries/tables)
2. Add two bookmarks, edit the first one's title to read foo_ and the second one's fooo

Variant 1:
Run:

$title = 'foo_';
$qb = $c->query('ServerContainer')->getDatabaseConnection()->getQueryBuilder();
$qb->select('title');
$qb
    ->from('bookmarks', 'b')
    ->where($qb->expr()->iLike('b.title', $qb->createNamedParameter('%'.$this->db->escapeLikeParameter($title).'%')););
$results = $qb->execute()->fetchAll();
assert(count($results) === 1); // fails, $results is empty

Variant 2:

$title = 'foo_';
$qb = $c->query('ServerContainer')->getDatabaseConnection()->getQueryBuilder();
$qb->select('title');
$qb
    ->from('bookmarks', 'b')
    ->where($qb->expr()->iLike('b.title', $qb->createNamedParameter('%'.$title.'%')););
$results = $qb->execute()->fetchAll();
assert(count($results) === 1) // fails, $results is now 2

Originally I stumbled onto this in nextcloud/bookmarks#432

Server configuration

Operating system: Ubuntu

Web server: apache2

Database: SQLite

PHP version: 7.1.8

Nextcloud version: 12.0.2

Updated from an older Nextcloud/ownCloud or fresh install: fresh install

Where did you install Nextcloud from: docker

Signing status:

Signing status

No errors have been found.

[juliusknorr]

@marcelklehr Can you post the generated SQL statement? My guess would be that SQLite requires adding ESCAPE '\\' to the WHERE clause.

[marcelklehr]

Looks like you're right, the query for the above is SELECT "title" FROM "oc_bookmarks" WHERE LOWER("title") LIKE LOWER(?)

while the query in the bookmarks app that has the same problem looks like this: SELECT "id", "url", "title", "user_id", "description", "public", "added", "lastmodified", "clickcount", GROUP_CONCAT("t"."tag") AS "tags" FROM "oc_bookmarks" b LEFT JOIN "oc_bookmarks_tags" t ON "t"."bookmark_id" = "b"."id" WHERE "user_id" = ? GROUP BY "id", "url", "title", "user_id", "description", "public", "added", "lastmodified", "clickcount", "lastmodified" HAVING LOWER("tags") LIKE LOWER(?) ORDER BY "lastmodified" DESC

[juliusknorr]

The escaping was added for like() in a65652f but it seems you have moved to iLike() in the bookmarks app in the past, because of some issues with the escape? nextcloud/bookmarks#368

[marcelklehr]

Ah, I knew we've had issues with this before. This time, it doesn't seem to change anything whether we use iLike() or like(), though, I just checked :/

[marcelklehr]

% signs also cause problems.

Edit: Maybe we need two backslashes? e.g. "ESCAPE '\\\\'"

[SomePoorBastard]

For others looking into the issue, the behavior is documented here: "The LIKE, GLOB, REGEXP, and MATCH operators", and was discussed in detail here: nextcloud/bookmarks#368 (comment)

Looks like the escape clause is limited to a single character. See implementation here: https://github.com/mackyle/sqlite/blob/3d751bdb5cd9487268268afb65d7be4cd82d5ea6/src/func.c

[marcelklehr]

@nickvergessen kindly helped with debugging and created a pr over here that should fix this: doctrine/dbal#3104