Won't enter special characters to Mysql

[dachshund]

Ok, so recently I changed my hosting provider. While moving my mysql database across (from version 4 to version 5) something has gone wrong. Everything is fine in the Mysql database itself and all the data is still there, but now it won't let me submit special characters such as & and £ to the Mysql through my self-made admin page.

 

The characters set is latin1.

 

The code in the admin page is pretty basic, just:

 

$sql = "UPDATE content SET title='$title' WHERE id=$id";

 

But if the title contains a special character it returns:

 

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 'blah blah £ blah blah'

 

Any help would be great.

 

Thanks in advance

 

[PFMaBiSmAd]

It's more likely that the error is occurring at a single-quote or a double-quote in the data.

 

Could you post an actual error message and what the actual $title value was at the time.

[dachshund]

Sure, it was:

 

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 're selling HUH. tote bags at half price, making them just £1.60 for the UK and £' at line 1

[dachshund]

By the way, this all worked fine when i was using Mysql 4

[dachshund]

Oh, and forgot the mention. The reason I think it's the special characters is that if I change the entry to just a word, like "hello", it enters it into the database without problem.

[PFMaBiSmAd]

The problem is because the string data, in this case $title, is not being escaped before being put into the query string and the single-quote (along with any null values, double-quotes, \r, \n, ...) are breaking the sql syntax and producing an error.

 

The reason this worked before on a different server is because php through it was better to let you blindly write code that worked, instead of spending 2 minutes learning how to  properly escape string data being put into a query and it attempted to escape your data for you. However, since this did not work for all character encodings, hackers were still able to inject sql.

 

So, php has now turned off this automatic (magic_quotes_gpc) escaping and it is up to your code to escape the data being put into a query.

 

See this link - mysql_real_escape_string for the function you need to use to escape string data being put into a query.

 

Edit: This problem has nothing to do with the mysql version and is not directly related to the php version, except that the setting(s) to magically get php to escape external string data was turned off by default in php 5.3 and is scheduled to be completely removed in the next major release of php. It is also possible that your new web host has the setting(s) turned off even through the php version is not the latest.

[dachshund]

Ok, thanks. So I should put the below?

 

mysql_real_escape_string($title); 
$sql = "UPDATE content SET title='$title' WHERE id=$id";

 

[PFMaBiSmAd]

mysql_real_escape_string() returns the escaped value and it must be assigned (or used directly in place of the value) -

 

$title = mysql_real_escape_string($title);