Preventative Measures for your Website against an SQL Injection Attack

SQL Injection was one of the top 10 web vulnerabilities in 2007 and 2010. Lately DOSarrest has been noticing an increase in the number of SQL Injection attempts against our clients. Whether this attack technique continues to increase in frequency, it remains to be seen. Regardless, there are mitigation steps you can take to increase your protection against an SQL Injection attack.

Attack Methodology

In an SQL Injection attack the attacker attempts to trick your database into running some malicious commands supplied via interaction with your dynamic web pages. The goal is to modify a query to include the user specified data in such a way that it fundamentally alters the functionality of the query.

For example, imagine you used the following code to retrieve a user account from a login screen:

dim dbconn=New OleDbConnection("Provider=Some.db.OLE;data source=" 
	& server.mappath("path/to/db"))
dbconn.Open()
statement="Select * from Users where username = '"& txtUser.Text 
	& "' and password = '"& txtPass.Text &'"
dim dbCom=New OleDbCommand(sql,dbconn)
dim dbRS=dbCom.ExecuteReader()

Now, with the above code, if someone submitted the following credentials:

Username: Admin
Password: asdf' or '1'='1

this would cause the database to evaluate the statement as:

"select * from users where username = 'Admin' 
	and password = 'asdf' or '1'='1'"

The above query will return all user rows, and the software likely will log the hacker in as the first user in the list. With some patience the hacker could use a limit statement to iterate through the accounts until they find the one the are looking for.

If the attacker had a penchant for vandalism imagine how attempting to log in as user:

asdf'; Drop table Orders; --

would cause the system to react. The possible attacks are limited only by the imagination of the attacker.

The methodologies to introduce these nefarious commands are myriad: URL querystring, post variables, cookies, even http server variables. Fortunately the same defence will work regardless of the injection vector.

Preventative Measures

There are several ways to mitigate SQL injection attacks, with varying degrees of success. The most fundamental protection you have against SQL injection attacks is data validation. By validating the users data against certain criteria it is harder for the attacker to submit malicious code into your database. When validating user data for security reasons, all validation should be done server side so that it cannot be bypassed, or altered before submitting.

The most basic type of data validation is data typing. For example. If you are asking for the customer’s age you can verify that the data being submitted is in fact an integer. There are also data typing functions in most programming languages to test for dates, numbers, strings, and many other datatypes. Unfortunately this is not foolproof, as there are ways to circumvent the protection offered. For example the hexadecimal value 0x633A5C626F6F742E696E69 will be treated by php as numeric, but will be evaluated by mysql as c:\boot.ini.

A stronger type of data validation would be to use regular expressions to validate incoming data against certain patterns. This can get quite exhaustive, as you will have to either match to only allow certain patterns, or match to deny certain patterns. In either case it is impossible to predict the scope of possible inputs, and the more exact you make your rules the more likely you will either filter out valid input or allow malicious input.

Another approach to take is to assume that all data is malicious and attempt to render the code inoperative by altering the data before submitting it to the database. A prime example of this is adding escape strings. In the case above where the attacker was able to get admin access by a carefully chosen password, by escaping out the single quotes the code would have been ineffectual.

dim dbconn=New OleDbConnection("Provider=Some.db.OLE;data source=" 
	& server.mappath("path/to/db"))
dbconn.Open()
dim strSafeUser = replace(txtUser.Text, "'", "''");
dim strSafePass = replace(txtPass.Text, "'", "''");
statement="Select * from Users where username = '"& strSafeUser 
	& "' and password = '"& strSafePass &'"
dim dbCom=New OleDbCommand(sql,dbconn)
dim dbRS=dbCom.ExecuteReader()

the SQL query now reads:

"select * from users where username = 'Admin' 
	and password = 'asdf'' or ''1''=''1'"

and the database will look for a record with username admin and password asdf’ or ‘1’=’1. While this is very effective in nullifying the above attack there is a long list of characters that would need to be replaced. Consider a site that determines what product to show based on the querystring parameter ID. By navigating to:

http://site.org?ID=1%3B%20Drop%20table%20products

the above replace would not help as there are no single quotes to nullify. Furthermore other prebuilt functions like the php function mysql_real_escape_string, which has become a defacto function for combatting sql injection in php, does not neuter the above malicious code.

A more comprehensive way of rendering the malicious code inoperable is to use a specific API that allows data binding. Data binding allows you to submit the user input as scalar information.

$mysqli = new mysqli('localhost', 'my_user', 'my_password');
$stmt = $mysqli->prepare("INSERT INTO Cart values (?,?,?,?)");
$stmt->bind_param('dddd', $userID, $orderID, $qty, $productID);
$userID =
...
$stmt->execute();

It takes the entirety of the supplied data and binds it to one particular column in the database so that it cannot modify the query. The downside here is that the data will be stored as submitted, and requires further data validation to ensure that the data being stored is valid according to business logic.

Conclusion

While there are a multitude of commands that could be used to attack your database, you can at the very least thwart and hamper a would be attacker by observing some of these basic principles in your coding. Understanding how your database is architected and what data elements need to be populated or searched for will help define your data validation techniques.

Sean Power

Security Operation Center Manager

DOSarrest Internet Security