' syntex error from mysql

[m1bzm]

Hi i've got this bit of code that looks for the ' and adds \ infront of it where needs be when inserting into the database. I've used this bit of code loads in the past, however on this server i'm hosting this site with i get this error

ERROR 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 's do' WHERE id = '1'' at line 1


here is the code that i'm using

$description2 = str_replace("/", "'", $row_array[3]);

What i don't get is this has worked really well in the past, it's short and sweet, but why not now?

If anyone could help it would be much appreciated

 

[Foamcow]

The error you are getting is to do with the query you are submitting to MySQL.

The code you give is nothing to do with that.

Check your query.

http://www.foamcow.com

 

[m1bzm]

now i thought that, but i've used the standard syntax for my INSERT and UPDATE, unless i'm making some blind mistake, lol. Howevere here is my mysql query

$result=mysql_query("UPDATE events SET displayuntil='$displayuntil', headline='$headline2' WHERE id = '$id' ");

 

[vbkris]

in databases u escape a ' with ''

ie:
'asdasdasdsad''asdasdasd'

will take the value:
asdasdasdsad'asdasdasd

so try replacing the ' with ''...

Known is handfull, Unknown is worldfull

 

[Foamcow]

also in PHP you can use the function addslashes(); to escape any characters that may cause problems in SQL.

Do $displayutil or $headline2 contain apostrophes, quotes or any other special characters?

http://www.foamcow.com

 

[DRJ478]

Some thoughts:
1. Why on earth would anyone name a table using a name containing a single quote. That is like asking for trouble. (thinking of vbkris' post). Best advice: don't.

2. mysql_escape_string() is a MySQL specific function that can be used to escape any possible offending characters in a value that is to be posted to the database.

3. Also note that you need not quote numeric values:
...WHERE id = $id ... is perfectly ok as long as id is a numeric value.

Overall:

http://www.php.net/mysql_escape_string

 

[m1bzm]

thanks drj478. My question leading from this is, when you're pulling the data from the DB does will i have to strip the \' to get ' instead of \' in the text? Does that make sense? lol, i hope so, lol.

thanks a lot everyone so far!!!

 

[DRJ478]

When you retrieve data from the database there should not be any escaped characters in there.
I recommend you inspect the data through a database admin tool, such as PHPMyAdmin or MySQL Control Center. If the slashes are in there then they were put in by accidental double escaping.
Also be aware that when you pass values through POST etc. an automatic escaping might happen depending on the setting of the magic_quotes_gpc

http://www.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc

 

[m1bzm]

yeah it is inputting the \ into the database. but not to worry i'm just stripping it when i'm retrieving it. Well thank you everyone, esp DRJ478 you've saved a lot of grief!!

)