SQL syntax error Problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

SQL syntax error Problem

Post 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)
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Try echoing out the mysql query string before performing the query to let you see exactly what you're getting.
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post 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'
User avatar
daven
Forum Contributor
Posts: 332
Joined: Tue Dec 17, 2002 1:29 pm
Location: Gaithersburg, MD
Contact:

Post by daven »

That is because a backslash (\) escapes the next character. Check out mysql_escape_string(). It should do what you need.
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post by Swede78 »

Do I do this mysql_escape_string() in addition to addslahes or magic_quotes or do I use it by itself?
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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.
Swede78
Forum Contributor
Posts: 198
Joined: Wed Mar 12, 2003 12:52 pm
Location: IL

Post 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.
Post Reply