SQL query does not work when there are accents

Question

SQL query does not work when there are accents

Navigation

#1 by (3 votes)

2

I have in my database a category defined as TERRENO / ÁREAS that when doing the following SQL query does not return any results from the existing 18.

SELECT * FROM property WHERE 1 = 1 AND CATEGORY IN ('LAND / AREAS')

This query already works:

SELECT * FROM property WHERE 1 = 1 AND CATEGORY IN ('APARTMENT')

Does anyone familiar with SQL know why the first statement does not return a result? Is it a problem with characters?

Update

The IN is because the values obtained come from an array

// se $tipo for um array e for maior que 0
if (is_array($tipo) && count($tipo) > 0) {
   $where .= " CATEGORIA IN ('".implode("','", $tipo)."') AND ";
} else {
   $where .= " CATEGORIA = '{$tipo}' AND ";
}

And it is AAS even, a term coming from a web service whose software is very poorly done and needs to be inserted into the database in this way.

mysql sql

asked by anonymous 06.10.2014 / 23:08

1 answer

Segmentation Fault in array of Structs C How to configure Nginx for nodejs and php?

score 3 · Accepted Answer

Text literal accenting as a database search filter is a problem because the encoding may be different between the application and the DBMS - you would have to ensure the synchronization between these settings.

Options:

Use parameters instead of concatenating the value in the SQL command (in this case dynamically construct the query using OR instead of IN ).
Convince non-use of special characters in this field (best applicable if the field is an identifier and not from a register).

In your case, since the text comes from an external service and needs both to be persisted and researched, I suggest "normalizing" the text always before it persists and before searching.

See:

link

By doing this, you protect yourself from future fixes that your service provider might make in your strings encoding.