Page 1 of 1

SQL syntax error Problem

Posted: Thu Jun 19, 2003 2:39 pm
by Swede78
I'm trying to polish up my code here to make it work with as few problems as possible. In an INSERT statement to the MySQL database, I'm getting an "SQL syntax error" when I try to run it.

Everything works fine, as long as I don't use "/\" (forward slash and back slash next to each other in that order).

I use "addslashes" before inserting the text. I though that's suppose to fix it.

The chances are slim that someone would put those two characters in there like that, but you never know.

Any help would be appreciated.

(PHP 4.3, MySQL 3.23, IIS 5)

Posted: Thu Jun 19, 2003 2:42 pm
by McGruff
Try echoing out the mysql query string before performing the query to let you see exactly what you're getting.

Posted: Thu Jun 19, 2003 3:14 pm
by Swede78
If I submit "Bla bla /\" in a text field to a page with this code:

Code: Select all

$ID = 'ABC123';
$Desc = $_POST['Desc'];

if( !get_magic_quotes_gpc() )
{
    $Desc = addslashes($Desc);
}

$query = "UPDATE table SET Description = '$Desc' WHERE ID = '$ID'";
$result = mysql_query($query) or die(mysql_error());
I get this error message:
You have an error in your SQL syntax near 'Bla bla' at line 1


If I echo $query, I get:
UPDATE table SET Description = 'Bla bla /'' WHERE ID = 'ABC123'

Posted: Thu Jun 19, 2003 3:30 pm
by daven
That is because a backslash (\) escapes the next character. Check out mysql_escape_string(). It should do what you need.

Posted: Fri Jun 20, 2003 2:07 pm
by Swede78
Do I do this mysql_escape_string() in addition to addslahes or magic_quotes or do I use it by itself?

Posted: Fri Jun 20, 2003 2:11 pm
by nielsene
magic_quotes only deals with escaping quotes, not slashes. addslashes will handles quotes, backslashes, and null-bytes. mysql_escape_string is similar.

Your code isn't working I would assume, because magic_quotes in on, so you never call the nested addslashes.

Posted: Fri Jun 20, 2003 7:32 pm
by Swede78
I would have agreed with you 15 minutes ago. I thought it was because of magic_quotes being on and ignoring the addslashes statement. However, I just tested my pages on a real server, and not my localhost. It worked as expected.

Takin from php.net:

magic_quotes_gpc boolean
Sets the magic_quotes state for GPC (Get/Post/Cookie) operations. When magic_quotes are on, all ' (single-quote), " (double quote), \ (backslash) and NUL's are escaped with a backslash automatically.

Note: If the magic_quotes_sybase directive is also ON it will completely override magic_quotes_gpc. Having both directives enabled means only single quotes are escaped as ''. Double quotes, backslashes and NUL's will remain untouched and unescaped.

You're right, if you have the magic_quotes_sybase directive ON.