MySQL Error - Please Help

**Waldo** · 02-02-2006, 09:14 PM

Hi,

When a user signs up for my image Hosting service they

UPDATE users SET name='ZOMG, it's Cynn', username='usersemail@email.com' , password='their_password', email='usersemail@email.com' WHERE other_id='2341' 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 Cynn', username='usersemail@email.com', in line #317

**GiantDomains** · 02-02-2006, 09:16 PM

Check line 317

**VisualDigits** · 02-02-2006, 09:34 PM

You have an apostrophe in name='ZOMG, it's Cynn', you gotta use double quotes i.e. name="ZOMG, it's Cynn". Enjoy!

**diverge** · 02-02-2006, 10:06 PM

Originally Posted by DomainMaster

You have an apostrophe in name='ZOMG, it's Cynn', you gotta use double quotes i.e. name="ZOMG, it's Cynn". Enjoy!

This would work as long as you know they are going to use a single quote, but because you don't know whether the user is going to to use a single quote in their name or even a double quote: (i.e., Sam "the Man"), it's best to simply escape the variable...

Code:

name='".addslashes($name))."',

... or (less desirable), replace any single quotes with double single quotes..

Code:

name='".str_replace("'", "''", $name).",

**Waldo** · 02-06-2006, 10:29 AM

If Someone would like to earn $3 via PayPal to fix this PM me.

Thanks,
--Brian R.

**Bender** · 02-06-2006, 10:31 AM

Code:

UPDATE users SET name='ZOMG, it\'s Cynn', username='usersemail@email.com' , password='their_password', email='usersemail@email.com' WHERE other_id='2341'

**JuniperPark** · 02-06-2006, 11:05 AM

Not sure about MySQL, but in MS SQL it's this:

UPDATE users SET name='ZOMG, it''s Cynn', username='usersemail@email.com' , password='their_password', email='usersemail@email.com' WHERE other_id='2341'

All incoming text MUST be protected by doubling any quotes (it will be a single quote in the DB.

Code:
name = replace(name,''','''')

If other_id is numeric you don't use quotes.

**diverge** · 02-06-2006, 03:07 PM

JuniperPark, IT Web Team, DomainMaster and myself have all answered your question: You have to deal with the possibility of a single quote in your form.

If you still need help, PM me your source code relating to the construction of the SQL statement, and I will send it back showing you where to escape the value to avoid this faux pas.

If your code is too large for PM you can email me at domains at diverge dot com.