SQL Injections in PHP with MySQL
Posted by: Adam Plocher on February 26, 2006
INTRODUCTION
SQL injections are a major security risk in many PHP applications. Injections are caused when a web developer allows the end-user to manipulate a variable that is being inserted into a database query string, generally through the $_GET, $_POST or $_SESSION superglobals. When a value isn't verified, major problems can occur. Since MySQL is the most commonly used database platform for PHP applications, it seemed appropriate to write an article specifically related to that.
Let's take the following code for example:
Assuming that the value of $_GET['id'] will always be a numeric value is a very bad assumption to make. The end-user could always change that value, and what happens if they change the value to something like: "0; DELETE FROM news;"? Your query now becomes:
SELECT news_title, news_text FROM news WHERE news_id=0; DELETE FROM news;
Remember kids, don't try this at home. This query will produce some nasty results.
VERIFYING NUMERIC DATA
Numeric values are the easiest to verify. PHP has a function built in called is_numeric() which will return true if the value passed into it is a number. This function is not MySQL-specific in any way, so it can be used to verify numbers with any database platform. Here's an example of how to make the query from Figure 1 secure:
VERIFYING NON-NUMERIC DATA
Non-numeric data can be a little tricky to verify. PHP has a feature built in called Magic Quotes. When this is enabled, PHP will automatically escape all backslashes (\), double-quotes ("), single-quotes (') and NULL characters passed through the $_GET and $_POST superglobals. This should make the strings safe for using against your database. The problem is, not all servers have this enabled so you must make your script smart enough to take that variable into account. In fact, there are safer ways to escape strings for MySQL, even for the servers that DO have this feature enabled.
To see if magic quotes are enabled, you can use the get_magic_quotes_gpc() function, which will return true if magic quotes are enabled, otherwise false. To safely escape a string before inserting it into a MySQL query, you can use the mysql_real_escape_string() function.
Here is an example of safely escaping a string before inserting into PHP and MySQL. This example undoes what Magic Quotes does (if enabled), so that it can use the mysql_real_escape_string() function, which should be safer than magic quotes.
OUTPUTTING BACK TO THE PAGE
The problem with using these techniques is that your strings will not look right when you output it to the page. All of your quotes and backslashes will be escaped (meaning extra backslashes will be displayed). This is necessary for inserting into the database, but can cause a problem when you're also trying to output that value to your website. To output the value properly to your website, use the stripslashes() function that is built in to PHP.
PUTTING IT ALL TOGETHER
Now that you are familiar with the techniques involved in building a safe MySQL query string in PHP, let's put these together to create one simple function. Note, you will still need to use stripslashes for output to your web page.
REFERENCES
www.php.net/get_magic_quotes_gpc
www.php.net/mysql_real_escape_string
www.php.net/stripslashes
www.php.net/is_numeric
SQL injections are a major security risk in many PHP applications. Injections are caused when a web developer allows the end-user to manipulate a variable that is being inserted into a database query string, generally through the $_GET, $_POST or $_SESSION superglobals. When a value isn't verified, major problems can occur. Since MySQL is the most commonly used database platform for PHP applications, it seemed appropriate to write an article specifically related to that.
Let's take the following code for example:
Figure 1
<?PHP
$query = "SELECT news_title, news_text ";
$query .= "FROM news";
$query .= "WHERE news_id=". $_GET['id'];
mysql_query($query);
?>
Assuming that the value of $_GET['id'] will always be a numeric value is a very bad assumption to make. The end-user could always change that value, and what happens if they change the value to something like: "0; DELETE FROM news;"? Your query now becomes:
SELECT news_title, news_text FROM news WHERE news_id=0; DELETE FROM news;
Remember kids, don't try this at home. This query will produce some nasty results.
VERIFYING NUMERIC DATA
Numeric values are the easiest to verify. PHP has a function built in called is_numeric() which will return true if the value passed into it is a number. This function is not MySQL-specific in any way, so it can be used to verify numbers with any database platform. Here's an example of how to make the query from Figure 1 secure:
Figure 2
<?PHP
if (!is_numeric($_GET['id']))
{
// id's not numeric?
// kill the script before the query can run
die("The id must be numeric!");
}
$query = "SELECT news_title, news_text ";
$query .= "FROM news";
$query .= "WHERE news_id=". $_GET['id'];
mysql_query($query);
?>
VERIFYING NON-NUMERIC DATA
Non-numeric data can be a little tricky to verify. PHP has a feature built in called Magic Quotes. When this is enabled, PHP will automatically escape all backslashes (\), double-quotes ("), single-quotes (') and NULL characters passed through the $_GET and $_POST superglobals. This should make the strings safe for using against your database. The problem is, not all servers have this enabled so you must make your script smart enough to take that variable into account. In fact, there are safer ways to escape strings for MySQL, even for the servers that DO have this feature enabled.
To see if magic quotes are enabled, you can use the get_magic_quotes_gpc() function, which will return true if magic quotes are enabled, otherwise false. To safely escape a string before inserting it into a MySQL query, you can use the mysql_real_escape_string() function.
Here is an example of safely escaping a string before inserting into PHP and MySQL. This example undoes what Magic Quotes does (if enabled), so that it can use the mysql_real_escape_string() function, which should be safer than magic quotes.
Figure 3
<?PHP
// Fix a $_POST variable called firstName for MySQL
$firstName = $_POST['firstName'];
if (get_magic_quotes_gpc())
{
// If magic quotes is enabled - turn the string back into an unsafe string
$firstName = stripslashes($firstName);
}
// Now convert the unsafe string into a MySQL safe string
$firstName= mysql_real_escape_string($firstName);
// $firstName should now be safe to insert into a query
?>
OUTPUTTING BACK TO THE PAGE
The problem with using these techniques is that your strings will not look right when you output it to the page. All of your quotes and backslashes will be escaped (meaning extra backslashes will be displayed). This is necessary for inserting into the database, but can cause a problem when you're also trying to output that value to your website. To output the value properly to your website, use the stripslashes() function that is built in to PHP.
Figure 4
<?PHP
$firstName = $_POST['firstName'];
if (get_magic_quotes_gpc())
{
// If magic quotes is enabled - turn the string back into an unsafe string
$firstName = stripslashes($firstName);
}
// Now convert the unsafe string into a MySQL safe string
$firstName = mysql_real_escape_string($firstName);
// Safe query
mysql_query("INSERT INTO Names VALUES('". $firstName ."')");
// Page output should look proper
echo "Hello ". htmlentities(stripslashes($firstName));
?>
PUTTING IT ALL TOGETHER
Now that you are familiar with the techniques involved in building a safe MySQL query string in PHP, let's put these together to create one simple function. Note, you will still need to use stripslashes for output to your web page.
Figure 5
<?PHP
function VerifyInput($input, $forceInt = false)
{
if (is_numeric($input))
{
return $input;
}
elseif (!$forceInt)
{
if (get_magic_quotes_gpc())
{
// if magic quotes is enabled, get rid of those
// pesky slashes
$input = stripslashes($input);
}
// convert the input variable into a MySQL safe string.
$input = mysql_real_escape_string($input);
return $input;
}
else
{
// if $input not an integer and $forceInt = true,
// kill script
die("Invalid Input");
}
}
// $_POST['name'] should be a string
// $_POST['id'] should be an integer, if not the script dies
$id = $_POST['id'];
$name = $_POST['name'];
$query = "UPDATE users SET name=". VerifyInput($name) ." ";
$query .= "WHERE id=". VerifyInput($id, true);
// query should be safe to run
mysql_query($query);
?>
REFERENCES
www.php.net/get_magic_quotes_gpc
www.php.net/mysql_real_escape_string
www.php.net/stripslashes
www.php.net/is_numeric
Comments temporarily disabled
