First Post disaster

So there you are, fresh faced, writing your first script to insert some text into a MySQL database and wham:

You have an error in your SQL syntax near 's first post'

This article is intended to solve this problem once and for all, so all code you write will work on any web server, no matter how PHP is configured.

So what was the problem here? Apostrophes - the are used to denote a string value in MySQL query syntax. So when your user typed in;

This is Bob's first post!

...MySQL read This is Bob, got to the ' character and regarded it as the end of the string, then wondered what to do with the rest. Lets say the syntax of the MySQL query in the PHP script was:

$sql = "INSERT INTO posts SET post = '" . $_POST['user_message'] . "'";

Well when $_POST['user_message'] is handed Bob's text, this is what happens;

$sql = "INSERT INTO posts SET post = 'This is Bob's first post!'";

See that quote in the middle of Bob's post? That's the problem.

The Great Escape

So what can you do about it? You need an escape character - the backslash. By putting a backslash before the apostrophe, you tell MySQL to regard it as a normal character, not to be treated as part of the query syntax. The backslash in this case is called an escape character. Note that MySQL won't store the backslash escape character in the database - it discards it.

And PHP comes with a handy function to help you deal with this: addslashes(). By passing $post through addslashes, before you INSERT / UPDATE MySQL, the apostrophes are escaped for you.

'Easy!' you think, generating yet more apostrophes that need escaping. But not so fast. PHP provides a configuration option "magic_quotes_gpc", which automatically adds slashes to any submitted HTML form data or cookies (gpc = GET/POST/COOKIE), before it gets passed onto your script.

From the PHP manual:

"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."

magic_quotes once seemed like a good idea but have turned out to be a menace, because if you take code where someone is using addslashes() and run it on a server where magic_quotes_gpc is on, this is what will be inserted into the database;

This is Bob's first post! - we wanted just This is Bob's first post!

Both magic_quotes_gpc then addslashes have had a go at placing a backslash before the apostrophe. But remember, they also escape any backslashes they find so before it got inserted into the database, $post actually looked like this;

This is Bob's first post! - three backslashes!

MySQL was kind enough to ignore two of the backslashes, inserting only the escaped characters (one backslash, one apostrophe). But this is not a good place to be - the string in MySQL now no longer matches the string returned to your script.

What we've got now is a recipe for backslash mayhem. 'But I know how to stop this!', you cry, 'with stripslashes()!'. Well yes but… in general you've now got to double check everything and you're headed down the road to nightmares.

What if, for example, in a different script, you're simply doing a search with a query like;

$sql = "SELECT * FROM posts WHERE post LIKE 'This is Bob's first post!'";?

What are you going to do? Addslashes? How many? Is the search string submitted by a user? How will MySQL treat the syntax? How many more problems like this are lurking? Expect hair loss fast!

Never stripslashes!

That's the golden rule. You should never have to stripslashes. Ever.

PHP will tell you if magic_quotes_gpc is on, with the function get_magic_quotes_gpc(). For example;

<?php

highlight_string ( "<?php

if (get_magic_quotes_gpc()==1) {

echo ( "Magic quotes gpc is on" );

} else {

echo ( "Magic quotes gpc is off" );

}

?>" );
?>

So what we want (for now and forever more) is a function that detects if magic_quotes_gpc is off, and adds slashes if that's the case;

<?php
highlight_string ( "<?php

function myAddSlashes( $string ) {

if (get_magic_quotes_gpc()==1) {

return ( $string );

} else {

return ( addslashes ( $string ) );

}

}

?>" );
?>

If you do this, your code will run everywhere, no matter whether magic_quotes_gpc is on or off, and you will never have to stripslashes, because you stored the string in the database correctly first time.

Magic Quotes are Evil!

While you sleep, they whisper in your dreams -

'Use magic quotes. You know you want to. Go on. It's just so easy'.

Do not succumb!. They may seem like a good idea to start with, saving you from extra work, but one day you will regret it. If you have to move host for example, or give your code to someone less capable than yourself, you will pay the price!

Also remember: magic_quotes_gpc is adding slashes to all variables passed to your script, before your code gets it's hands on them. This is a basic loss of control over what's happening and instinctively you should mistrust any process that tries to usurp power from you.

In retrospect, the PHP crew probably wish they'd never provided magic_quotes...

So act now and join the War Against Magic Quotes!. Turn em off on your development server. Spam your hosting company with a script that sends them a mail every minute tell them to turn them off. Burn those magic_quotes where ever you may find them. They are the bane of PHP!

If you're using Apache, you can put a .htaccess file in the web directory where the script runs from containing;

For php4

<?php
highlight_string ( "<IfModule mod_php4.c>
php_flag magic_quotes_gpc off
</IfModule>" );
?>

For php3

<?php
highlight_string ( "<IfModule mod_php3.c>
php3_flag magic_quotes_gpc off
</IfModule>" );
?>

If you have access to your php.ini file, edit it to set magic_quotes_gpc = Off and restart apache.

Final word

Watch out for magic_quotes_runtime being on (rare);

"If magic_quotes_runtime is enabled, most functions that return data from any sort of external source including databases and text files will have quotes escaped with a backslash."

If magic_quotes_gpc is evil, magic_quotes_runtime is the Dawn of Apocalypse! Kill it now! You can check using phpinfo())! Thankfully magic_quotes_runtime can be switched off in a script using set_magic_quotes_runtime () like this;

<?php
highlight_string ( "<IfModule mod_php3.c>
php3_flag magic_quotes_gpc off
</IfModule>" );
?>" ); ?>

The only magic quotes that is your friend (kind of) is magic_quotes_sybase, only changing the nature of addslashes (or the evil magic quotes), telling it to escape a single quote with another single quote, rather than a backslash. This is only needed for people connecting Sybase to PHP.