SQL Quoting And PHP

The Problem

By default, PHP uses "magic quoting". This means that any variable passed via POST, GET, or cookies, will have all single quotes turned into backslash single quotes. That is, for these variables, all ' characters get turned in \'.

Now, suppose $_POST['inputfield'] (obtained from an HTML form) contains the string "asdf'qwer". If you attempt to write it into a database, like so:

   $formfield = $_POST['inputfield'];    //  Contains asdf'qwer
   $phpfield  = "hello world";

   $query = "
      INSERT INTO mytable ( form_data, php_data )
      VALUES ( '" . $formfield . "', '" . $phpfield . "' );";

   sqlite_query($db, $query)
      or die('Error A: ' .
      sqlite_error_string(sqlite_last_error($db)));

Here's what gets queried:

      INSERT INTO mytable ( form_data, php_data )
      VALUES ( 'asdf\'qwer', 'hello world' );

That backslash comes from magic quotes. But according to the SQL standard, you quote single quotes with a double single quote:

''      // Proper way to escpae a single quote.

not with a backslash:

\' // Not proper way to escape a single quote.

Here, "proper" means "according to the SQL standard". The problem is, if you're using a PHP variable, magic quotes DOES the right thing:

   $my_variable = 'This is \'properly\' quoted.';

But according to the SQL standard, it does the WRONG thing. In other words:

\' is a properly quoted quote for PHP \' is an improperly quoted quote for SQL

Therefore, $my_variable will work in all PHP situations except for the very important situation where you use the variable in any kind of SQL.

Why Is This A Problem For Sqlite?

For better or worse, sqlite follows the SQL standard. \' has no special meaning. In particular, it's not an escaped quote. It's a backslash followed by a quote character. This is why $my_variable will cause an error if you use it with sqlite without taking special care to properly quote the single quote. Therefore, magic quoting does NOT benefit Sqlite.

Why Is This Not A Problem For MySQL?

MySQL has extensions to SQL. One of these extensions is the fact that you CAN quote single quotes with a backslash. In other words, to MySQL, \' is a properly quoted single quote. This is why $my_variable will NOT cause an error if you use it with mysql. Therefore, magic quoting DOES benefit MySQL.

Ref

What About PostgreSQL?

As with MySQL, PostgreSQL does work with backslash-escaped strings. Ref.

How To Resolve The Problem

MySQL users should stop reading because they have no problems here. :) There are two things that need to be done:

• Strip out the quotes that magic quoting inserted.
• Properly quote the single quote.

Stripping Magic Quoting

Some people have waged war against magic quoting. It's highly recommended that everyone turn off magic quoting. Presumably, this setting is in /etc/php4/php.ini or something like that.

Not stripping the magic quotes leads to spurrious backslashes in your output, like:

Where\'s the beef?

A good way to eliminate the effects of magic quoting:

   // Is magic quotes on?
   //
   if ( get_magic_quotes_gpc() )
   {
      // Yes? Strip the added slashes
      //
      $_REQUEST = array_map('stripslashes', $_REQUEST);
      $_GET = array_map('stripslashes', $_GET);
      $_POST = array_map('stripslashes', $_POST);
      $_COOKIE = array_map('stripslashes', $_COOKIE);
   }

Properly Quoting the Single Quote

Sqlite provides a function to properly escape characters that need to be escaped. I believe single quotes, double quotes and backslashes will be properly escaped by it:

$formfield = sqlite_escape_string($formfield);

Conclusion

By stripping magic quoting, and then properly quoting the single quote, you:

1. Eliminate the infamous spurrious backslash problem.
2. Eliminate SQL logic errors caused by quotes in variable names.
3. Eliminate SQL injection vulnerabilities. Reference: http://www.sitepoint.com/article/sql-injection-attacks-safe
4. Provide a cross-database interface by following the SQL standard.