The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

How do I solve database errors caused by quotes/apostrophes?

Consider the following SQL statement:

INSERT INTO articles SET title='The PHP Anthology';

Perhaps the PHP script that made this query contained something like this:

<?php      
$title = "The PHP Anthology";      
     
$sql = "INSERT INTO articles SET title='$title';";      
     
$result = mysql_query($sql, $dbConn);      
?>

No problem so far, but look what happens if we change the title:

$title = "PHP's Greatest Hits";

Notice the apostrophe in the title? When we place this in the SQL statement, the query MySQL receives will be as follows:

INSERT INTO articles SET title='PHP's Greatest Hits';

See the problem? When MySQL reads that statement, it will only get as far as this:

INSERT INTO articles SET title='PHP'

The rest of the statement will cause a syntax error and the query will fail. It's easy enough to avoid this problem when you write the title yourself, but what happens when your script gets the value from user input?

The Great Escape

The solution is to escape the apostrophe character by adding a backslash before the apostrophe. The following query, for example, will work:

INSERT INTO articles SET title='PHP\'s Greatest Hits';

Backslashes and the ANSI SQL Standard

Note that using the backslash as an escape character is not standard ANSI SQL. If MySQL is the only database you'll ever use, the backslash may be acceptable, but the same SQL statement run on another database may well fail. According to ANSI SQL we should escape apostrophes with another single apostrophe:

INSERT INTO articles SET title='PHP''s Greatest Hits';

The question is, how do we make sure all our apostrophes are escaped when we build a query on the fly in PHP? Dealing with this situation has become rather confusing due to the number of alternative solutions.

First we have the php.ini setting magic_quotes_gpc. Magic quotes is a feature of PHP which, when turned on, automatically escapes single and double quotes, as well as backslashes and null characters found in incoming GET, POST and cookie variables, by adding backslashes to the strings. This may sound great, but in practice it quickly makes for trouble, typically where forms are involved.

Say you have a form which is used for editing articles. Your script takes the text the user enters and inserts it into MySQL. Now, if the user fails to complete some important field, you might want to re-display the details that have been entered in the form so far. With magic quotes on you'd have to strip out all the slashes it added to the values (with PHP's stripslashes function)!

Then, what if you wanted to run the code on a server where magic_quotes_gpc is disabled? Your code would then have to check to see if magic quotes is switched on and bypass the use of stripslashes. Headaches are inevitable, and if you make a mistake and end up with spurious backslashes stored in your database, you may have a painful cleanup process ahead of you. It continually amazes me how many professionally designed sites fail to handle character escaping properly! Keep an eye out for unexpected backslashes in your own Web travels. See Chapter 1, PHP Basics for my advice on how best to avoid this on your own sites.

Indeed, magic quotes is discussed in some detail in Chapter 1, PHP Basics. If you do switch off magic_quotes_gpc as I advise, you should be aware of the potential risks to security. See the section called "SQL Injection Attacks" below and Appendix C, Security Checklist.

Next, we have the PHP function addslashes. Applied to any string, addslashes will use backslashes to escape single quotes, double quotes, backslashes and null characters. This makes it an effective means to escape strings for use in queries.

If magic quotes is on, of course, you must not use addslashes, or characters would be escaped twice! To solve this conflict, you can check if magic quotes is enabled with the function get_magic_quotes_gpc, which returns TRUE if magic quotes is enabled and FALSE if it isn't. You can bundle up this test with a function as follows:

<?php      
function safeAddSlashes($string)      
{      
 if (get_magic_quotes_gpc()) {      
   return $string;      
 } else {      
   return addslashes($string);      
 }      
}      
?>

The third way, which is very similar to addslashes, uses the function mysql_escape_string or mysql_real_escape_string (the latter was added in PHP 4.3.0). These functions use the underlying MySQL C++ API (provided by the developers of MySQL, rather than the developers of PHP) to escape special characters.

These functions escape null characters, line feeds, carriage returns, backslashes, single quotes, double quotes, and end-of-file characters. Since PHP 4.3.0, both functions have used the current character set for the connection between PHP and MySQL. There is, therefore, no difference between these two functions in the latest PHP versions, so feel free to stick with the shorter of the two, mysql_escape_string, if your server is up-to-date.

As this method is, in effect, the built-in mechanism provided by MySQL for escaping strings, I recommend it over addslashes or magic_quotes_gpc. Of course, if you want your SQL to port well to other databases, you may want to consider "hiding" the function call within a class method, which allows you to swap out the class—including the escape mechanism—when moving to a different database.

Again, if you do not otherwise handle the magic quotes issue, you'll have to check whether magic_quotes_gpc is on:

<?php      
function safeEscapeString($string)      
{      
 if (get_magic_quotes_gpc()) {      
   return $string;      
 } else {      
   return mysql_real_escape_string($string);      
 }      
}      
?>

The scripts in this book make use of the MagicQuotes/strip_quotes.php include file introduced in Chapter 1, PHP Basics and included in the code archive to effectively switch off magic quotes on servers where it is enabled, so the solutions that follow will use mysql_real_escape_string freely. I'd encourage you to do the same in your own scripts if you feel confident escaping quotes and other special characters yourself.

SQL Injection Attacks

An SQL injection attack occurs when an attacker exploits a legitimate user input mechanism on your site to send SQL code that your unsuspecting script will pass on to the database to execute. The golden rule: escape all data from external sources before letting it near your database. That rule doesn't just apply to INSERT and UPDATE queries, but also to SELECT queries.

No doubt many PHP developers have been saved from the worst SQL injection attacks by the limitations of MySQL, which will only allow a single SQL statement to be performed with each call to mysql_query. On other databases, the effect of an SQL injection can be disastrous, as an attacker can send a second query that, for example, deletes the entire contents of a table. With MySQL, however, problems can still occur, as the following code demonstrates:

$sql = "SELECT * FROM users        
       WHERE username='" . $_POST['username'] . "'      
       AND password='" . $_POST['password'] . "'";      
     
echo 'Query: ' . $sql . '<br />';      
     
$result = mysql_query($sql);      
     
$rows = mysql_num_rows($result);      
     
if ($rows > 0) {      
 echo 'You are logged in!<br />';      
} else {      
 echo 'You are not allowed here!<br />';      
}      
?>      
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">      
<input type="text" name="username" /><br />      
<input type="text" name="password" /><br />      
<input type="submit" />      
</form>

A savvy attacker could simply enter the following in the form's password field:

' OR username LIKE '%

Assuming magic quotes is disabled on your server, and you have no other measures in place to prevent it, this clever attack alters the meaning of the query:

SELECT * FROM users      
WHERE username='' AND password='' OR username LIKE '%'

The modified query will select all records in the user table! When the script checks whether any users matched the supplied user name and password combination, it will see this big result set and grant access to the site!

This can be prevented if we escape the incoming variables:

$sql = "SELECT * FROM users        
   WHERE username='" . safeEscapeString($_POST['username']) . "'      
   AND password='" . safeEscapeString($_POST['password']) . "'";

In some cases, depending on the circumstances, this may not be necessary. But if you value your sleep, remember that golden rule: escape all data from external sources.