Error when reading some words using percentage wildcard character (%)

Question

Hello. I’m making some queries to the database that uses the PDO. When I make the appointment without the % at the beginning of the condition, the results return correctly.

Ex.:

$localizacoes = $localizacao->find("nome LIKE :nome", "nome={$consultaLocalizacao}%")->order('nome')->limit($paginator->limit())->offset($paginator->offset())->fetch(true);

However, when I just add the % at the beginning of the condition, in some searched words return error.

Ex.:

$localizacoes = $localizacao->find("nome LIKE :nome", "nome=%{$consultaLocalizacao}%")->order('nome')->limit($paginator->limit())->offset($paginator->offset())->fetch(true);

Error:

Fatal error: Uncaught PDOException: SQLSTATE[22021]: Character not in repertoire: 7 ERROR: invalid byte sequence for encoding "UTF8": 0xba in /opt/lampp/htdocs/sioepsul/vendor/coffeecode/datalayer/src/DataLayer.php on line 224

This happens when I search for words starting with: ba, be, ca, ce, fa, fe, among others. Modifies only the byte Quence to: 0xba, 0xbe, 0xca 0x25, 0xce 0x25, 0xfa, 0xfe. The Datalayer (Coffeecode) component only does the execute() from the PDO, I don’t believe it’s a problem in it.
How can I solve this problem?

Answer 1

The behavior is caused by find. In the code it uses the function parse_str (source).

In the function manual it has the following description:

Converts str as if it had been passed via URL ...

This means it will "parse" the string using percent encoding. Thus, when the parser find the % sign it takes the next two characters and interprets as if it were a hexadecimal (%ba turns 0xba).

This is associated with php work with strings in binary form, without "caring" if it represents a valid character in the encoding. So it sends a sequence of bytes to Postgresql where it is validated and confirmed that there is no UTF-8 character starting with the past byte.

You can test it using the urldecode. For example, in the browser, urldecode("nome=%ba%") returns "nome=�%".

Solution 1 - use the % in url encoding %25:

$localizacoes = $localizacao->find("nome LIKE :nome", "nome=%25{$consultaLocalizacao}%25")->order('nome')->limit($paginator->limit())->offset($paginator->offset())->fetch(true);

Solution 2 - do not use LIKE, use ~ or ~* (Handbook):

$localizacoes = $localizacao->find("nome ~* :nome", "nome={$consultaLocalizacao}")->order('nome')->limit($paginator->limit())->offset($paginator->offset())->fetch(true);

In solution 2 the query behaves as a regular expression case insensitive, but it can give you some headache if the search contains characters that are used in regular expressions, such as ., *, (, ) etc..