Error in your SQL syntax

[niza]

I've made a classified ads website. I have 2 fields in my database:
adTitle which is varchar 255
adInfo which is varchar 255

Whenever I try to post an ad that contains the character ' in the title I get an error.

So for example if the title of my ad is: 12' pencil
I get the following error

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 'pencil','nick','32','accessories','2007-10-16','Deonne','2007-12-15

I don't understand because the 2 fields are both varchar 255 but if I use the ' symbol in the adInfo fields, I get no error. Only in adTitle.

Anyone knows what's wrong?

Thanks

[eelixduppy]

You must escape your string. I assume that the info is coming from a webform in which case you should be using mysql_real_escape_string [php.net]. There are examples on that page that show you how to use it. This is a very important thing to have in regards to security. Your database can be hacked if you don't escape you variables.

[niza]

Will mysql_real_escape_string not add the ' character to the database? If that is the case I do not want that. People that post ad use ' as the abbreviation for inches. So is there anothe way around this?

[eelixduppy]

It will escape it by adding a slash before it. So for your example, it will look like this:

12\' pencil 

[CWebguy]

Will mysql_real_escape_string not add the ' character to the database? If that is the case I do not want that. People that post ad use ' as the abbreviation for inches. So is there anothe way around this?

Escaping a string doesn't mean that it is removed. It will still remain. But if you don't escape a single quote (') then everytime mysql hits that single quote it will consider it the ending of the data being entered (since the single quote tells mysql that everything in between single quotes is info to store).

so if you have:

mysql_query("Insert into database (title) values ('He's a good man')");

only 'He' will be read by mysql and there rest will cause an error.

In PHP (and mysql) you have to escape the (') character.

print 'He's a good man'; won't work, you must do

print 'He\'s a good man';

which will out put correctly

He's a good man

[edited by: CWebguy at 7:54 pm (utc) on Oct. 16, 2007]

[niza]

I understand now. Thanks you guys.

One last question: how will I be using the mysql_real_escape_string intro an INSERT query?

Here's my query:

$sql = "INSERT INTO ad(adName,adPrice) VALUES ('$adName','$adPrice');

$adName is the one with the problem.

I really appreciate it.

[eelixduppy]

$sql = "INSERT INTO ad(adName,adPrice) VALUES ('".mysql_real_escape_string($adName)."','".mysql_real_escape_string
($adPrice)."')";

$adName is the one with the problem.

Although you are only having issues with that string, all user-defined strings in queries must be escaped; that is why I escaped both strings and not just the one.

[niza]

Never mind, I figured it out by myself.

thanks you once again