0

I have a table with all the cities in my country, but they are in uppercase. Im trying to convert the first letter to uppercase and the rest to lower case.

Some of them have the single quote accent (Example: Sao Martinho D'oeste) and they are the only ones that give me an error when i try to update the table after converting them.

$cidadeNome = strtolower($cidade['desc_cidade']);
$cidadeNome = ucwords($cidadeNome);

$sql = "UPDATE cidades SET desc_cidade = '".$cidadeNome."' WHERE cidade_id = ".$cidade['cidade_id']."";

$atualizado = $db->query($sql);

if (!$atualizado)
{   
    echo "Erro (" . $db->errno . ") " . $db->error . "\n";
    $db->close();
    exit;
}

My code is very simple. The error i get is

Erro em (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'oeste' WHERE cidade_id = 88382' at line 1

My code work for every city, unless it has an single quote.

Adrian
  • 315
  • 2
  • 3
  • 17
  • That's because you are making a bad sql... Either start using prepared statements and parameter binding or escape your string using mysqli_real_escape_string(); – Naruto Mar 10 '16 at 13:42
  • You have to escape the special characters - especially the quotes - in your variables. Otherwise the query will obviously fail. – DerVO Mar 10 '16 at 13:43
  • http://stackoverflow.com/questions/20283870/special-characters-in-sql-query?rq=1 – Hamza Zafeer Mar 10 '16 at 13:45
  • Amigo, you should use prepared statements! – rray Mar 10 '16 at 13:45
  • `Sao Martinho D'oeste` its because of `'` in your name. Use prepared statement as adviced by @rray – Fakhruddin Ujjainwala Mar 10 '16 at 13:46
  • [Are PDO prepared statements sufficient to prevent SQL injection?](http://stackoverflow.com/q/134099/1342547) there are some questions in [portuguese version of SO](http://pt.stackoverflow.com/) too. – rray Mar 10 '16 at 13:49
  • Oh yeah. Sorry about that guys i forgot the basic. I do use mysqli_real_escape_string but only when im receiving a value from POST or GET. Since this was a one time script i didnt though about using it. Thanks a lot! – Adrian Mar 10 '16 at 13:52
  • I did solve my problem by using `$cidadeNome = filter_var ($cidadeNome, FILTER_SANITIZE_STRING);` . – Adrian Mar 10 '16 at 14:12

0 Answers0