Data Management

Don't get stung by SQL Injection attacks

SQL Injection is no new arrival on the scene, yet high-profile attacks still occur. Alfonso Barreiro lists the basic measures to take to help mitigate the threat.

Despite being known for some time, SQL Injection attacks continue to be one of the most frequent ways a website can be compromised. It has been highly publicized recently as the main tool used in the attacks of high profile organizations such as Sony, PBS, and HBGary, but many misconceptions about it still remain.

What is SQL Injection?

SQL Injection basically inserts ("injects") a SQL command to be run at a backend database by using input data that has not been properly validated in an application. For example, a login web page usually requires a username and a password. The web application uses a SQL command to validate the username/password combination and provides the user with information. The normal SQL command could take the following form:

SELECT * FROM Table WHERE UserName = ‘$User' AND Password = ‘$Pass';

In the previous example, $User is the username and $Pass is the password submitted on the web page by a user. The quotes surrounding both inputs are part of the query and set them up as literal text strings.

A regular user entering the username of "Michael" with the password "password" would result in the following command being executed:

SELECT * FROM Table WHERE UserName = ‘Michael' AND Password = ‘password';

The command would return all fields from the record in the table where the username and password match. An attacker however, could enter a single quote (´) in the username field and since the input is actually being parsed literally, the resulting command would be executed instead:

SELECT * FROM Table WHERE UserName = ´´´ AND Password = ‘';

This would then generate a syntax error. This is the proof the attacker would need to confirm that this particular login web page is vulnerable to injection attacks. The attacker could then insert the following in the username field:

hacker' OR 1=1; --

The resulting command would then become:

SELECT * FROM Table WHERE UserName = ‘hacker' OR 1=1; --' AND Password = ‘';

Here the pair of hyphens (--) represent the start of a comment, so from that point forward the text is ignored. The real command would be:

SELECT * FROM Table WHERE UserName = ‘hacker' OR 1=1;

The expression 1=1 is always evaluated as true for all records and since a logical OR expression is always evaluated as true when at least one of the components of the expression is true, this SQL command will always return data from the table (assuming it's not empty).

This is a basic example, but an attacker could be far more creative and include different SQL commands to be executed such as insert, update or delete.

How to protect your applications

There are several ways to reduce your web applications' exposure to SQL injection attacks, but as with many security measures, a layered approach in which all of the available mitigations are in place would be ideal.

Configure error handling

In the previous example, a syntax error revealed that the web form could be vulnerable to SQL Injection. Ensuring that error messages are consistent and do not contain debugging information (the default for many programming frameworks) could deny an attacker the information needed to refine an attack.

However, by only using generic error messages and not applying other defense mechanisms, it only makes a SQL injection attack more difficult, not impossible. This is known as Blind SQL Injection, which is identical to a normal SQL Injection attack, but it does not rely on error feedback from the server for refinement. This may slow down an attacker, but the end result is the same and just as dangerous.

Sanitize the input

Ensure that the user inputs do not contain dangerous characters or code. The recommended method is to create a "whitelist" of accepted inputs, depending on what should be the expected response. This not only helps in protecting against SQL Injection, but also avoids typos or other errors from being stored in your database. Note that this is not always practical since sometimes there are some fields where a "dangerous" character could be valid. For example, in a "last name" field, O'Brian includes a quote, but in the context of the entry, it's a valid character.

Use parameterization

Though input sanitation helps, it still means that the user input could be considered part of a SQL command. Using prepared statements, parameterized queries or stored procedures (which are all supported in some way by essentially all databases), allows for a clear separation between data (the user input) and the command itself (the SQL programming). The input would be considered just "data" and some proper validation would be applied to it.

When using stored procedures remember that within it you can still create SQL commands dynamically, defeating its purpose in protecting against SQL Injection.

Apply the principle of least privilege and role separation

Whenever possible, create accounts that have the minimum amount of privileges needed for the task they need to perform. It shouldn't be necessary to run a database application during day to day operations as a database administrator. This way a successful attack will not immediately grant access to the rest of the data or the server environment.

This is not a comprehensive guide on SQL Injection, as there are other potential avenues for attacks. The mitigation techniques described here are specific for countering SQL Injections, but as always, do not underestimate other basic layers of security to protect your data.

About

I am a technology specialist with over 10 years of experience performing a variety of corporate IT functions, including desktop and server operations, application development, and database administration. My latest role is in information security, fo...

7 comments
victor.gutzler
victor.gutzler

Couldn't a stored procedure use the web input on a virtual table containing some bogus records, and if the web input results in anything but an empty record, then it should be considered an invalid query and reprompt the user for another login attempt?

Duke E Love
Duke E Love

Most web app platforms have an ORM or a database abstraction layer that takes SQL out of the picture. The ones I have used have a built in data sanitizing component. If not I am sure that there is a data sanitizing library that you can implement with nominal effort.

pgit
pgit

I imagine a white list would be a huge chore and subject to error, or potentially limit the nature of passwords that could be used. I seem to recall reading about a method of sanitizing input that stripped it of command characters somewhere along the processing line, or did something to the user input before actually inputting it to the server as a query. I didn't dive in to it too deeply as I'm not the db guy, but if anyone knows what I'm talking about (if there is such a maneuver) I'd like to know more about it. ...or at least get this dumb idea out of my head if I'm remembering wrong. Could it be maybe that any special characters or commands typed by a user (including a cracker) are stripped out and can never make it to the interpreter? I'd imagine the user data would have to be reformatted with commands reinserted in the process, but how to tell what part of whatever someone has keyed in is the valid data? As you can see I'm clueless. I should have paid more attention, I'm pretty sure it was a tech republic article. I'm not sure how savvy some of the db managers I occasionally work with are.

joncowden
joncowden

This is very dangerous stuff... I am so overwhelmed at how many people have SQL vulnerable web-sites PUBLIC facing... I can think of at least 4 German auto maker's websites who's databases are wide-open for reading potentially secret documentation. I know there are .GOV sites within our own NATION that are vulnerable to SQL Injections. That is quiet scary... Of course, proper persons were notified before damage was done.. Another big issue in today's world is DotNetNuke attacks. While that is a basic defacement, it still makes your company look completely incompetent...

auogoke
auogoke

you should "sanitize" it *before* you use it anywhere -- e.g., as input to your database. In his book "Build Your Own Database Driven Web Site Using PHP & MySQL", SitePoint, Kevin Yank recommends using the PHP htmlspecialchars() functions for this purpose. The htmlspecialchars() function accepts 3 parameters: [a] the variable name for the input that you intend to output to your web page -- or perhaps' insert in your database -- e.g., $fname; [b] the PHP-defined constant, ENT_QUOTES; and, [c] the string "utf-8" sample usage: echo 'Welcome to out school, ' . htmlspecialchrs($fname, ENT_QUOTE, 'UTF-8') . '!'; If you are using PHP, Yank also recommends that you include code to detect and disable PHP's magic quotes -- if they are enabled. Thereafter, you can use the mysqli_real_escape_string() function to store a "query safe" version of the input variable the data before you insert it into your MySQL database. If this is what you wanted to know, I strongly recommend that you acquire and read Mr. Yanks book for the complete detail. Good luck!

seunweb
seunweb

SQL injection attacks are possible due to a lack of filtering and escaping. filtering input properly and escaping the output for SQL will eliminate the risk of attack. To escape output for an SQL query, use the driver-specific *_escape_string() function for your database. If possible, use bound parameters. With PHP, you can use htmlentities() to escape output intended for web browsers. You can also use array to store output www.unicomserver.com

Editor's Picks