Database Security: Guarding Against SQL Injection

In our last article we introduced the subject of database security. In this article we will continue to explore various attacks that can be made on a database and how to prevent these attacks. We will also build the last page of our site. This article is the conclusion to an eight-part series.

The Welcome Script

This page is the first page that visitors to our site will see once they’ve been authenticated. It is a very simple HTML page. Here’s the code for it:


<?

session_start();

if(!isset($_SESSION['username'])){

header("localtion:login.php");

}



?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"><!– InstanceBegin template="../Templates/was.dwt.php" codeOutsideHTMLIsLocked="false" –>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<!– InstanceBeginEditable name="doctitle" –>

<title>Untitled Document</title>

<!– InstanceEndEditable –>

<!– InstanceBeginEditable name="head" –>

<style type="text/css">

<!–

.style1 {

font-size: 16px;

font-weight: bold;

}

–>

</style>

<!– InstanceEndEditable –>

<link href="../Templates/was.css" rel="stylesheet" type="text/css" />

</head>



<body>

<table width="99%" border="1">

<tr>

<td bgcolor="#333333" class="header">Web Secure</td>

</tr>

 

 

<tr>

<td><!– InstanceBeginEditable name="main" –>

<table width="200%" border="1">

<tr>

<td width="4%">&nbsp;</td>

<td width="63%">&nbsp;</td>

<td width="33%"><img src="images/icon_user.gif" width="24" height="27" />

<? if(isset($_SESSION['username'])){

echo $_SESSION['username'];

}else{

echo "David Web";}

?>

 

</td>

</tr>

<tr>

<td height="58" colspan="3"><span class="style1">Welcome</span> </td>

</tr>

<tr>

<td height="83">&nbsp;</td>

<td>&nbsp;</td>

<td>&nbsp;</td>

</tr>

</table>

<!– InstanceEndEditable –></td>

</tr>

<tr>

<td class="copy">&copy;2008</td>

</tr>

</table>

</body>

<!– InstanceEnd –></html>

The only part worth explaining here is the PHP portion. The rest is fairly easy to understand and probably very familiar to you by now. Basically the code kicks off by starting a session for the page. Then it checks to see if this user has been authenticated. If the user has been authenticated, a session variable called username will be set; otherwise, the user is not authenticated:

session_start();

if(!isset($_SESSION['username'])){

header("localtion:login.php");

}


{mospagebreak title=SQL Injections}

SQL injection is one of the most common vulnerabilities in PHP applications. I was a victim of this particular attack, very recently; this is one of the reasons I’m writing about PHP security. What is so surprising about this particular attack is that it is all down to the developer. When we fail to properly filter data as it enters the application (the previous article talks about this), and when we don’t escape data that is inserted into the database, we leave ourselves wide open for an SQL injection attack. Neither of these crucial steps should ever be omitted, and both steps deserve particular attention in an attempt to minimize errors.

So how does SQL injection take place? SQL injection typically requires some speculation and experimentation on the part of the attacker. It is necessary to make an educated guess about your database schema (assuming, of course, that the attacker does not have access to your source code or database schema). Consider a simple login form:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml"><!– InstanceBegin template="/Templates/was.dwt.php" codeOutsideHTMLIsLocked="false" –>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />

<!– InstanceBeginEditable name="doctitle" –>

<title>WebSecure::Login</title>



<script language="javascript" type="text/javascript">

function checkform(pform1){

if(pform1.uname.value==""){

alert("Please enter a username")

pform1.uname.focus()

return false

}

if(pform1.pw.value==""){

alert("Please enter a password")

pform1.pw.focus()

return false

}



if(pform1.pw.value=="" && pform1.uname.value==""){

alert("Please make sure that you have entered your username and password")

return false

}

return true

}

</script>

<!– InstanceEndEditable –>

<!– InstanceBeginEditable name="head" –>

<!– InstanceEndEditable –>

<link href="../Templates/was.css" rel="stylesheet" type="text/css" />

</head>



<body>

<table width="99%" border="1">

<tr>

<td bgcolor="#333333" class="header">Web Secure</td>

</tr>

 

 

<tr>

<td><!– InstanceBeginEditable name="main" –>

<form name="form1" method="post" action="" onSubmit="return checkform(this)">

<table width="41%" border="0" align="center" cellpadding="0" cellspacing="3">

<tr class="listtop">

<td colspan="3"> </td>

</tr>

<tr>

<td width="9%">Username</td>

<td width="41%"><input name="uname" type="text" id="uname" size="50"></td>

<td width="50%" rowspan="4">&nbsp;</td>

</tr>

<tr>

<td>Password</td>

<td><input name="upass" type="password" id="upass" size="50"></td>

</tr>

<tr>

<td>&nbsp;</td>

<td><a href="../password.php">Forgotten your password?</a>|<a href="register.php">Register</a></td>

</tr>

<tr>

<td>&nbsp;</td>

<td><input type="submit" name="submit" value="Login"></td>

</tr>

</table>

</form>

<!– InstanceEndEditable –></td>

</tr>

<tr>

<td class="copy">&copy;2008</td>

</tr>

</table>

</body>

<!– InstanceEnd –></html>


An attacker will look at this form and start to think about what kind of query you are using to verify the username and password. The attacker will be aided by looking at the HTML source, to make reasonably accurate guesses about your naming conventions. Why is this so? Because, believe it or not, developers usually match the names on their forms to the names in their database table.

For example, you might have a table called users with fields called username and password. You use the same names in the form: username and password. This of course helps the attacker, but don’t be fooled into thinking that by differing the names on the form from those in your database table, you will stop an attacker in his tracks. It will make the attacker’s guess work more difficult, yes, but it will not stop him.

{mospagebreak title=The Attacker’s Approach to SQL Injections}

The attacker then guesses that you use the following query to validate your user:

<?php



$hashedpass = md5($_POST['password']);

$query = "SELECT count(*) FROM users WHERE username = ‘{$_POST['username']}’

AND password = ‘$hashedpass’";



?>

The attacker will have a lot of time on his hands, and so does not need to guess the correct schema on the first try. He or she will have to experiment constantly.

For example, the most common way to trick a script into revealing the contents of a database table would be to enter a single quote as the username “ ‘ ”. This usually exposes important information that will aid the attacker in his goal. Also, a lot of developers use the mysql_error() function whenever an error is encountered during the execution of the query:

<?php

$query=”SELECT * from users WHERE …”

$result=mysql_query($query);

if(!$result){

echo mysql_error();

}

?>

While this approach is very helpful during development, it can expose vital information to an attacker. If the attacker provides a single quote as the username and mypass as the password, the query becomes:

<?php



$query = "SELECT *

FROM users

WHERE username = ”’

AND password = ‘a029d0df84eb5549c641e04a9ef389e5’";



?>


If this query is sent to MySQL as illustrated in the previous example, the following error is displayed:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ‘WHERE username = ”’ AND

password = ‘a029d0df84eb55


With very little work, the attacker already knows the names of two columns (username and password) and the order in which they appear in the query. In addition, the attacker knows that data is not being properly filtered (there was no application error mentioning an invalid username) nor escaped (there was a database error), and the entire WHERE clause has been exposed. Knowing the format of the WHERE clause, the attacker can now try to manipulate which records are matched by the query.

From this point, the attacker has many options. One is to try to make the query match regardless of whether the access credentials are correct by providing the following username:

myuser’ or ‘foo’ = ‘foo’ –

Assuming mypass is used as the password, the query becomes:

<?php

$query = "SELECT * FROM users WHERE username = ‘myuser’ or ‘foo’ = ‘foo’ — AND password = ‘a029d0df84eb5549c641e04a9ef389e5’";

?>

This allows an attacker to log in successfully without knowing either a valid username or password.

If a valid username is known, an attacker can target a particular account, such as John :

John’ —


As long as John is a valid username, the attacker is allowed to take control of the account because the query becomes the following:

<?php

$query = "SELECT * FROM users WHERE username = ‘John’ — AND password =a029d0df84eb5549c641e04a9ef389e5’";

?>


{mospagebreak title=How to Avoid SQL Injection}

Luckily, SQL injection is easily avoided. As we’ve discussed in the previous article you should always filter input and escape output.

While neither step should be omitted, performing either of these steps eliminates most of the risk of SQL injection. If you filter input and fail to escape output, you’re likely to encounter database errors (the valid data can interfere with the proper form of your SQL query), but it’s unlikely that valid data is going to be capable of modifying the intended behavior of a query. On the other hand, if you escape output but fail to filter input, then escaping will ensure that the data does not interfere with the format of the SQL query and can protect you against many common SQL injection attacks.

Of course, both steps should always be taken. Filtering input depends entirely on the type of data being filtered, but escaping output when data is sent to a database generally requires only a single function. For MySQL users, this function is mysql_real_escape_string( ) :

<?php

$surname = “‘web;”

$cleansurname=mysql_real_escape_string($surname);

$query = "INSERT INTO user (last_name) VALUES (‘$cleansurname’)";

?>

Use an escaping function native to your database if one exists. Otherwise, using addslashes( ) is a good last resort. With all of the data used to create an SQL query properly filtered and escaped, there is no practical risk of SQL injection.

To recap:

SQL injection is made possible when developers fail to properly filter their data.

When inserting data into the database, use the mysql_real_escape_string() function. And when retrieving data, escape it by using functions like stripslashes() and trim().

As a means of making it difficult for attackers to access your data, don’t use plain text in your database. A lot of us are guilty of this particular habit. Instead, use some kind of encryption scheme.

Conclusion

A quick note about the MD5 algorithm: it is not considered safe to use the MD5 of a user’s password. Recent discoveries have revealed weaknesses in the MD5 algorithm, and many MD5 databases minimize the effort required to reverse an MD5. I have been advised that a better approach to protecting user passwords is to salt it, using a string that is unique to an application:

<?php

$salt = ‘SHIFLETT';

$hashedpass = md5($salt . md5($_POST['password'] . $salt));

?>

[gp-comments width="770" linklove="off" ]

chat