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

Asked

Viewed 49 times

0

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?

  • The problem is that the value is being passed literally. It needs to escape %in the definition of the pair. It would be as if %: find("nome LIKE CONCAT('%',:nome,'%') but it only masks another problem. Whenever you post, instead of your original snippet, provide a [mcve] of the problem to detect the source of the problem. To better enjoy the site worth reading What is the Stack Overflow and the Stack Overflow Survival Guide (summarized) in Portuguese.

  • The problem was solved using solution 1 presented by @Duardo-bissi

  • I’m glad you solved it. Anyway, see the links passed to facilitate a faster feedback in the next posts, understand and better enjoy the philosophy of the site. And beware of third-party libs, they often generate more problem than medium-term solution.

1 answer

0


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..

  • Gee, I used solution 1 and it worked perfectly and you made me understand the root of the problem. Thank you, Eduardo.

  • Glad you could help. In the meantime, I was thinking here that depending on how you treat $consultaLocalizacao, if the user includes a % sign in the query it may be that the problem appears again. Run your tests.

  • @Eduardobissi and need to see if there is no interpolation or direct concatenation of strings, to ensure that there is no surface for Injection. As you’ve seen details of the lib, I think you already have a way to assess. If it was a pure bind of direct value in DB, would have no problem, but being by PDO + libs extra, never know.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.