SetContent is not case-insensitive in all databases

[RicardoEPRodrigues]

When loading content using setcontent (https://docs.bolt.cm/3.6/templating/content-fetching#the-like-option), string searches are not always case-insensitive depending on the used database. If you use MySQL it should just work, but PostgreSQL does not return the expected results.

Details

Question	Answer
Relevant Bolt Version	3.7.2
Install type	Composer install
BC Break	yes/no ???
PHP version	7.1
Web server	Apache
For UX/UI issues	Browser name and version

Reproduction

This issue has been discussed or addressed previously in #6109 (which fixes this issue if we are to use /search) and #924 (where it never got solved).

The issue refers to inconsistencies between databases, depending on if they are case-sensitive or not, and regards the search done to the database to retrieve content for setcontent calls. While the documentation states that search is always case-insensitive, that is not true because the search in SQL used by Bolt makes use of the comparison LIKE which is not case-insensitive in PostgreSQL.

I fixed the issue in my end by changing the following code in line 2000, from LIKE to ILIKE. Of course, it only works for PostgreSQL.

bolt/src/Legacy/Storage.php

Lines 1999 to 2001 in 0bf0e00

	} elseif ($first == "%" || substr($value, -1) == "%") {
	$operator = "LIKE";
	}

A better solution would be to have something like:

if (database == postgresql) {
  $operator = "ILIKE";
} else {
  $operator = "LIKE";
}

Or one could always try to LOWER() every string.

If you wish to try it out, we have a website live with this issue. If you visit the link below that includes the search you will see that no game is shown, if you change the search to Circuit (with the upper case C) it will show a result (second link).

https://labjogos.tecnico.ulisboa.pt/en/game?search=circuit
https://labjogos.tecnico.ulisboa.pt/en/game?search=Circuit

Steps to reproduce

1. Visit https://labjogos.tecnico.ulisboa.pt/en/game ;
2. Search the keyword circuit without any upper case;
3. Press enter.

Expected result

A game with the name Circuit Slimes should appear.

Actual result

After the previous search see that no game is shown. (Search Circuit to see the expected result.)