|
| FAQ Article: SQL Injection Vulnerabilities |
A number of web site are hacked as a result of something called "user input validation/filtering" - or, to be more precise - the lack of it. This topic should be a guide to web programmers (any language, php, asp, perl etc - but we will focus on php/asp examples) on how to safely deal with user input.
I will also be talking about why this important, and giving examples of how sites that don't successfully filter user input can have accounts on the server compromised, or worse. I am not going to be explaining the SQL/php/asp code in much detail...so a basic knowledge of these languages will help you understand this topic a bit better ;)
Firstly, what is the root cause of the problem? Is it the php/asp programming languages? is it the database backend (whether it be mysql, sql server, access db, etc)? no...the problem lies with the programmer of the site, and the users visiting the site.
The problem is that when you have a form on your web site, whether it be a login form (asking for a username/password) or a comments form, or anything - you are asking the user to send information into scripts on your web site.
Although 18/20 users might use the form in the intended way - there will always be 1 or 2 malicious users who will send your scripts data they didn't expect. The moral of the story is - you simply can't trust unchecked user input.
However, let's not take my word for it...let's look at a simple login form in asp, which takes a username/password and validates them against a table called "users" in an access database (my comments are in green in the asp/php code):
Html Code:
<html>
<head>
<title>Please Login</title>
</head>
<body>
<form action="logmein.asp" method="POST">
Username: <input type="text" name="username"><br>
Password: <input type="text" name="password"><br><br>
<input type="submit" value="Log In">
</form>
</body>
</html>
ASP Code for "logmein.asp" (where the html form sends it's data):
<%
' get the username and password from the html form
User = Request.Form("username")
Pass = Request.Form("password")
' open a connection to the database
' I am using a DSN called mydatabase to make connecting a little simpler
Set Conn = server.CreateObject ("ADODB.Connection")
Conn.Open "mydatabase"
' here is the SQL statement to select the user's password from the database for comparing
SQL = "SELECT * FROM Users WHERE Username = '" & User & "' AND Password = '" & Pass & "'"
' make our database connection execute the query and store the results in the recordset rs
Set rs = Conn.Execute(SQL)
' if the username/password combo was found in the database then...
if not rs.EOF Then
' log the user in by setting the login session to "Yes" - this is how we identify logged in users
Session("login") = "Yes"
Conn.Close
' redirect the logged in user to the members page
Response.Redirect("memberpage.asp")
else
' the username/password didn't match! redirect them to the login screen again
Conn.Close
Response.Redirect("login.html")
end if
%>
And for reference...the PHP equivalent code for "logmein.asp" (where the html form sends it's data):
<?
// get the username and password from the html form
$User = $_POST["username"];
$Pass = $_POST["password"];
// open a connection to the mysql database
$conn = @mysql_connect("localhost", "wang", "somepass");
if (!$conn)
{
echo ("Unable to connect to DB server at this time");
exit();
}
if (! @mysql_select_db("mydatabase") )
{
echo ("Unable to connect to database at this time");
exit();
}
// here is the SQL statement to select the user's password from the database for comparing
$sql = "SELECT * FROM Users WHERE Username = '$User' AND Password = '$Pass'";
// make our database connection execute the query and store the results in the recordset $rs
$rs = @mysql_query($sql);
// if the username/password combo was found in the database then...
if (mysql_num_rows($rs) > 0)
{
// log the user in by setting the login session to "Yes" - this is how we identify logged in users
session_start();
$_SESSION['login'] = "Yes";
mysql_free_result($rs);
mysql_close($conn);
// redirect the logged in user to the members page
header("Location: memberpage.php");
exit();
}
else
{
// the username/password didn't match! redirect them to the login screen again
mysql_free_result($rs);
mysql_close($conn);
header("Location: login.html");
exit;
}
?>
Ok...at first glance, this code might look ok - but if you use this code, you are putting the accounts on your site at risk. The above html/asp code will allow a user to login to your site without actually knowing a valid username/password combination. Lets look at why this is possible...
The three important lines of the asp script are:
User = Request.Form("username")
Pass = Request.Form("password")
and...
SQL = "SELECT * FROM Users WHERE Username = '" & User & "' AND Password = '" & Pass & "'"
And the three important lines of the php script are:
$User = $_POST["username"];
$Pass = $_POST["password"];
and...
$sql = "SELECT * FROM Users WHERE Username = '$User' AND Password = '$Pass'";
The reason these are important is because we are taking user input from an html form, and passing it into a query - and more importantly - *no* checking on the user input has been done! So effectively, we are passing anything the user wants, straight into the sql string...and this is why this exploit works.
Now, say I filled in the username field of the html form in with "Wang" and the password field in with "qwerty" - the SQL statement executed would look like:
SQL = "SELECT * FROM Users WHERE Username = 'Wang' AND Password = 'qwerty'
This is a perfectly valid SQL statement, and is an example of a "perfect" input from the forms (i.e. an expected input, with no malicious intent). This would be executed by the database system, and providing a record in the "Users" table exists for username "Wang" with password "qwerty" - we will be logged into the web site.
However...there is a problem. What if the person entered no input into the username and password fields of the html form - and pressed submit. The SQL statement would look like:
SQL = "SELECT * FROM Users WHERE Username = '' AND Password = ''
When no input is provided, the fields are filled with an empty ''. This means, the vulnerability is limited to strings that terminate with ' - since we need the SQL code to succeed when it is executed by the Database, so we need to ensure it is well-crafted.
For example...if I entered the username in the html form as "'" (without the quotes)...the SQL string would look like:
SQL = "SELECT * FROM Users WHERE Username = ''' AND Password = ''
This means the SQL string now has too many 's - and therefore this query would fail, since it is not well-formed. This is why it will be important when we submit our malicious input to the form, to ensure we don't muck up the number of 's in the SQL string.
By the way, submitting just ' into a login form is a quick way of checking whether the code is vulnerable to this particular form of sql-injection exploit. If the server returns an SQL error...you know that the script hasn't filtered out your code...and the script is probably vulnerable.
Now, let's create our malicious string - we now enter "' or ''='" into the username field of the html form, and "' or ''='" into the password field (both without the quotes!). The SQL string would therefore look like:
SQL = "SELECT * FROM Users WHERE Username = '' or ''='' AND Password = '' or ''=''
This is a perfectly valid SQL string since all the 's have been matched, and we have successfully injected our special "or" condition. In case you were wondering, '' = '' always returns True by the database. Can you see what we have done? We are now saying (if the SQL string was in english):
Give me all the fields from all the records in the database, where the Username field = '' or '' = '' - but also make sure that the Passsword field = '' or '' = ''
Since '' always does equal '' - this query will succeed and we will be logged into the web site (technically as the first user in the database) - without actually having ever entered a real username or password.
If you are still confused....I would recommend loading up a database management system like mysql/access or whatever you have available - and playing with the query....you will see that it does work! Ok...this is fairly bad as you can log in to the site without actually knowing any valid usernames....but what about if you wanted to log in as a particular username on the server?
Easy - fill in the username field of the html form with the desired username, say "Wang" - and then complete the password field as before with "' or ''='" - therefore the SQL string looks like:
SQL = "SELECT * FROM Users WHERE Username = 'Wang' AND Password = '' or ''=''
Therefore, it matches the username Wang - and also succeeds on the password due to our always True condition '' = '' - and we get logged in as Wang! There are lots of possible SQL "injections" we can do to the html form to alter the SQL string - after all, we practically have full control what the SQL statement does.
You can even inject comments in the style of /* and */ into the SQL, which makes it comment out certain aspects of the SQL - for example, enter "'/*" into the username and "*/ OR '' = '" into the password and we get:
SQL = "SELECT * FROM Users WHERE Username = ''/*' AND Password = '*/ OR '' = ''
Since the /* and */ comment out everything between them, the SQL statement is interpreted as:
SQL = "SELECT * FROM Users WHERE Username = '' OR '' = ''
Which causes the SQL to succeed again! So, we have now identified the problems - but how can all of these problems be prevented? Simple - User input validation.
User input validation or "input filtering" as it is sometimes known, is when user input is processed by the script/server before it is passed to the database/file/script it is destined for. When I say the input is processed, I mean it is checked to ensure there is no unwanted or malicious data.
Input validation can consist of just simply removing all troublesome characters from the input (for example, as we saw above ' is a troublesome character - so during input validation, if the input is destined to be included in an SQL string - it should be filtered out!), or it can consist of checking the entire structure of the user data (for example, checking that some input contains a valid email address, or a valid URL).
In general though, it usually means filtering bad characters from a string. What are the bad characters? well - it's usually specific to what the data is going to be used for, or where it is to be stored (file/database etc) - but common characters filtered are: " * % ( ) / , ; . : # <> | \ '
However, there are better techniques - for example, if you know that a valid username should only contain a-z - then why bother filtering only certain characters from a string? just filter anything that isn't between a-z ! Most programming languages provide built in functions to do this (and even if they don't, it's usually trivial to code).
So, when should user input be checked/validated? immediately after taking the data into the script! As soon as the data is brought into a variable within the script, it should be checked before anything else happens to it.
Ok, so lets say in our asp/php example login scripts above we know that the username and password should only contain a-z and 0-9 characters. I will now provide the safe alternative asp/php script versions of the insecure ones I gave you before, so you can see how it works:
ASP Code for "logmein.asp" again, with user input validation added:
<%
' get the username and password from the html form
User = Request.Form("username")
Pass = Request.Form("password")
' Use RegExp object to filter all non a-z, 0-9, A-Z characters from the input!
Set re = Server.CreateObject("VBScript.RegExp")
re.Pattern = "[^a-zA-Z0-9]"
re.Global = true
User = re.Replace(User, "")
Pass = re.Replace(Pass, "")
' open a connection to the database
' I am using a DSN called mydatabase to make connecting a little simpler
Set Conn = server.CreateObject ("ADODB.Connection")
Conn.Open "mydatabase"
' here is the SQL statement to select the user's password from the database for comparing
SQL = "SELECT * FROM Users WHERE Username = '" & User & "' AND Password = '" & Pass & "'"
' make our database connection execute the query and store the results in the recordset rs
Set rs = Conn.Execute(SQL)
' if the username/password combo was found in the database then...
if not rs.EOF Then
' log the user in by setting the login session to "Yes" - this is how we identify logged in users
Session("login") = "Yes"
Conn.Close
' redirect the logged in user to the members page
Response.Redirect("memberpage.asp")
else
' the username/password didn't match! redirect them to the login screen again
Conn.Close
Response.Redirect("login.html")
end if
%>
And the PHP equivalent code for "logmein.asp" with user input validation added:
<?
// get the username and password from the html form
$User = $_POST["username"];
$Pass = $_POST["password"];
// filter all non a-z, 0-9, A-Z chars from the input!
$User = ereg_replace("[^a-z0-9A-Z]", "", $User);
$Pass = ereg_replace("[^a-z0-9A-Z]", "", $Pass);
// open a connection to the mysql database
$conn = @mysql_connect("localhost", "wang", "somepass");
if (!$conn)
{
echo ("Unable to connect to DB server at this time");
exit();
}
if (! @mysql_select_db("mydatabase") )
{
echo ("Unable to connect to database at this time");
exit();
}
// here is the SQL statement to select the user's password from the database for comparing
$sql = "SELECT * FROM Users WHERE Username = '$User' AND Password = '$Pass'";
// make our database connection execute the query and store the results in the recordset $rs
$rs = @mysql_query($sql);
// if the username/password combo was found in the database then...
if (mysql_num_rows($rs) > 0)
{
// log the user in by setting the login session to "Yes" - this is how we identify logged in users
session_start();
$_SESSION['login'] = "Yes";
mysql_free_result($rs);
mysql_close($conn);
// redirect the logged in user to the members page
header("Location: memberpage.php");
exit();
}
else
{
// the username/password didn't match! redirect them to the login screen again
mysql_free_result($rs);
mysql_close($conn);
header("Location: login.html");
exit;
}
?>
Please note, that are a zillion other ways of doing this, PHP especially has a number of built in functions to make input safe by adding slashes etc, or by encoding all html special characters.
Both of the above fixes use features of the language in order to remove any characters from the input which aren't a-z, 0-9, or A-Z - therefore stripping all malicious html and SQL characters.
Obviously, this may not always be perfect for your site - as sometimes you may want other characters to be allowed in the input - but this is easily fixed by just adding the characters you want to allow to the regular expression (the bit that reads [^a-z0-9A-Z]) - but remember that some characters in the regular expression will need a \ in front of them to escape them (for example to allow . and - you would need a regular expression like [^a-zA-Z0-9\.\-]).
More information on regular expression and input validation techniques can be found with a quick search in google.com - but I hope I have shown you why there is a danger, and how to go about fixing it.
|
Posted on: 19-08-2002
Article has been viewed 19058 times
|
| |
| Comments |
|
Comment by demosthenes - 19-11-2005
wow, i was searching for what sql injection scripts were and how to do them forever and this article is really good it really helped
Comment by gad - 29-09-2006
This is the simplest and easiest sql injection so far I have found on net. Thanks Wang. Keep the good work. Cheers!
Post a comment
Please use the form below to post your comments on this article. All comments will be reviewed by the admin before being published publically.
|
| |
|
