SQL Injections with Microsoft SQL & Sybase
Posted by: Adam Plocher on March 10, 2006
INTRODUCTION
This is a follow-up to my previous article, SQL Injections in PHP with MySQL. I will recap on certain stuff from the previous article, if it applies to Microsoft SQL or Sybase.
SQL injections are caused when a programmer allows the end user to insert values into an SQL query string, generally through superglobals such as $_POST or $_GET. Values that are inserted into a query string that can be manipulated by end-users must be validated before executed. If they are not, users may be able to insert their own custom SQL code which could be used to do some really bad things.
Microsoft SQL and Sybase use a different method to escape quotes from strings than most other SQL servers. Instead of escaping a quote with a backslash, like \' or \", you must escape the quote with two quotes, such as '' (that's two single quotes, not one double quote). Because of this, I have written an article specifically related to SQL Injections with Microsoft SQL and Sybase.
VERIFYING NUMERIC DATA
Numeric data is very easy to validate and the way we validate it applies to all types of SQL servers, not just Microsoft SQL and Sybase. PHP has a function called is_numeric() which will return true if the input is a number, otherwise false. The following code demonstrates how to easily validate a number before it goes into a SQL query:
VERIFYING NON-NUMERIC DATA (TEXT)
Text can be quite a bit harder to validate. PHP has a feature called "magic_quotes_gpc" which, when enabled, will cause PHP to automatically escape any quotes found in superglobals ($_GET, $_POST) with a slash. This is a fairly common setting to have enabled, but unfortunately it won't do the trick for what we need, since MSSQL/Sybase requires you to escape a quote with another quote (' would be escaped with ''). PHP has another setting called "magic_quotes_sybase" and when enabled along with "magic_quotes_gpc", will cause all single quotes to be escaped with double-single quotes. But how do we know if it's enabled? Well there's a function called get_magic_quotes_gpc() which will return true if that's enabled. To determine if "magic_quotes_sybase" is enabled we have to use the ini_get function. The following code should determine if Magic Quotes is enabled and convert the input string into a MSSQL/Sybase-safe string:
OUTPUTTING BACK TO THE PAGE
If you convert a string into a safe string and wish to output it back to the page, you will need to convert it back to the unescaped string. This means you'll basically need to do the reverse of what we just did:
PUTTING IT ALL TOGETHER
Now we'll build a function to handle all verifications. This function has an optional $forceInt parameter which, when set to true, will cause the function to kill the script when a numeric value isn't passed in. This parameter should be set to true if you know the input value is numeric, like when using an ID. You will have to remember to set the $input value to the return value of this function, for example:
CORRECT:
$id = verifyInput($id, true);
$name = verifyInput($name);
WRONG:
verifyInput($id, true);
verifyInput($name);
This is a follow-up to my previous article, SQL Injections in PHP with MySQL. I will recap on certain stuff from the previous article, if it applies to Microsoft SQL or Sybase.
SQL injections are caused when a programmer allows the end user to insert values into an SQL query string, generally through superglobals such as $_POST or $_GET. Values that are inserted into a query string that can be manipulated by end-users must be validated before executed. If they are not, users may be able to insert their own custom SQL code which could be used to do some really bad things.
Microsoft SQL and Sybase use a different method to escape quotes from strings than most other SQL servers. Instead of escaping a quote with a backslash, like \' or \", you must escape the quote with two quotes, such as '' (that's two single quotes, not one double quote). Because of this, I have written an article specifically related to SQL Injections with Microsoft SQL and Sybase.
VERIFYING NUMERIC DATA
Numeric data is very easy to validate and the way we validate it applies to all types of SQL servers, not just Microsoft SQL and Sybase. PHP has a function called is_numeric() which will return true if the input is a number, otherwise false. The following code demonstrates how to easily validate a number before it goes into a SQL query:
Figure 1
<?PHP
$id = $_GET['id'];
if (!is_numeric($id))
{
// if it's not numeric, kill the script
die("ID Must be a Number!");
}
// (or sybase_query())
$query = mssql_query("SELECT * FROM News WHERE ID=". $id;
?>
VERIFYING NON-NUMERIC DATA (TEXT)
Text can be quite a bit harder to validate. PHP has a feature called "magic_quotes_gpc" which, when enabled, will cause PHP to automatically escape any quotes found in superglobals ($_GET, $_POST) with a slash. This is a fairly common setting to have enabled, but unfortunately it won't do the trick for what we need, since MSSQL/Sybase requires you to escape a quote with another quote (' would be escaped with ''). PHP has another setting called "magic_quotes_sybase" and when enabled along with "magic_quotes_gpc", will cause all single quotes to be escaped with double-single quotes. But how do we know if it's enabled? Well there's a function called get_magic_quotes_gpc() which will return true if that's enabled. To determine if "magic_quotes_sybase" is enabled we have to use the ini_get function. The following code should determine if Magic Quotes is enabled and convert the input string into a MSSQL/Sybase-safe string:
Figure 2
<?PHP
$title = $_GET['title'];
if (get_magic_quotes_gpc() && trim(ini_get("magic_quotes_sybase")) == "")
{
$title = stripslashes($title);
$title = str_replace("'", "''", $title);
}
elseif (!get_magic_quotes_gpc())
{
$title = str_replace("'", "''", $title);
}
$query = "SELECT * FROM News WHERE Title='". $title ."'";
// (or sybase_query())
mssql_query($query);
?>
OUTPUTTING BACK TO THE PAGE
If you convert a string into a safe string and wish to output it back to the page, you will need to convert it back to the unescaped string. This means you'll basically need to do the reverse of what we just did:
Figure 3
<?PHP
// Code from Figure 2
$title = $_GET['title'];
if (get_magic_quotes_gpc() && trim(ini_get("magic_quotes_sybase")) == "")
{
$title = stripslashes($title);
$title = str_replace("'", "''", $title);
}
elseif (!get_magic_quotes_gpc())
{
$title = str_replace("'", "''", $title);
}
$query = "SELECT * FROM News WHERE Title='". $title ."'";
// (or sybase_query())
mssql_query($query);
// New Code
// this will change the quotes back.
echo str_replace("''","'",$title);
?>
PUTTING IT ALL TOGETHER
Now we'll build a function to handle all verifications. This function has an optional $forceInt parameter which, when set to true, will cause the function to kill the script when a numeric value isn't passed in. This parameter should be set to true if you know the input value is numeric, like when using an ID. You will have to remember to set the $input value to the return value of this function, for example:
CORRECT:
$id = verifyInput($id, true);
$name = verifyInput($name);
WRONG:
verifyInput($id, true);
verifyInput($name);
Figure 4
<?PHP
function VerifyInput ($input, $forceInt = false)
{
if (is_numeric($input))
{
return $input;
}
elseif (!$forceInt)
{
if (get_magic_quotes_gpc() && trim(ini_get("magic_quotes_sybase")) == "")
{
$input = stripslashes($input);
$input = str_replace("'", "''", $input);
}
elseif (!get_magic_quotes_gpc())
{
$input = str_replace("'", "''", $input);
}
return $input;
}
elseif ($forceInt)
{
die("Invalid Input");
}
}
?>
Comments temporarily disabled
