How to Understand (and Prevent) SQL Injection Attacks

Despite many security breaches caused by SQL injection, it’s still a too-common way for malicious hackers to break into corporate websites. This overview simplifies the nature of the threat and offers suggestions for ways to protect your code against it.

Application insecurity in web applications has cost companies billions of dollars in losses and harmed data confidentiality, integrity, and authentication. Among the causes is SQL injection. Although SQL injections are a well-known way to attack a site, plenty of companies fall prey to such flaws every year. The breach of a Web server that housed payment card data for a New York tourism company's website led hackers to cardholder information for 110,000 credit cards. In 2009, a security vulnerability in Moscow-based Kaspersky Lab's U.S. website was made public after a hacker launched a SQL injection attack. And — well, there are plenty of other examples, sad to say.

These incidents imply a lack of understanding of security measures available when it comes to building secure web applications interacting with backend databases. What else can explain that injection flaws, especially SQL Injections, were crowned the #1 threat for the year 2010 by the Open Web Application Security Project (OWASP). But the solutions to handle this threat are very simple and easy to implement.

SQL injection is, basically, a type of web based attack. The website has a form which the developer designed to accept some data, process it, and return some output. A common user enters what the program expects. But a hacker uses the opportunity of a web form (or a malformed URL) to execute malicious SQL code with the intention of breaking into, altering, or damaging the back-end database, or in an attempt to retrieve confidential information from the system.

Hackers are on the lookout for any pages on your site that accept user input. This could be anything: a login page, a search page, a feedback page. Look at the contents between the <FORM> </FORM> tags in a HTML page with a critical eye. Doing so can reveal useful information on the possible fields that could be exploited.

Don’t look only at static HTML pages. SQL injections can be used on pages using ASP, JSP, CGI, or PHP which take parameters like:

http://example.com/index.asp?id=4

It’s easiest to show how this works with a specific illustration. (My examples are from a Microsoft SQL Server point of view; it is not very different on other platforms).

Let’s take a simple example of a webpage where a user enters credentials to log on. The login could be processed in many different ways, such as:

string strQry = "SELECT Count(*) FROM Users WHERE UserName='" +
txtUsrName.Text + "' AND Password='" + txtPasswd.Text + "'";

When this dynamically generated query is created and executed, the variables txtUsrName.txt and txtPasswd.txt are concatenated to the query and then passed to the SQL database. The important word here is concatenated. If txtUsrName.Text and txtPasswd.Text contain any value other than a legal username and password, it is blindly passed to the SQL database. This is the exploit in a nutshell. Dynamic SQL instructions are the most vulnerable candidates for SQL injection.

A sunny day scenario of a user entering a valid username and password yields a statement like this:

[php]string strQry =  "SELECT Count(*) FROM Users WHERE UserName='Sammy' AND Password='myPass';[/php]



Another sunny day scenario which can break the query is a name like O’Hara. The query string would look like this:

string strQry =  "SELECT Count(*) FROM Users WHERE UserName='O'Hara' AND Password='myPass';

Because O’Hara has a single quote, it leads to a syntax error. For example, here is a SQL server error message generated by this command:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'H'. /test/example.asp, line 45

Such an issue has to be handled, even if a programmer believes her code is SQL-injection safe, simply because there are people in the world with the last name of O’Hara.

But our focus is on how this can be misused. Let us look at what a hacker could possibly do with that login page. A hacker starts by trying the same single quote to break the system by inserting malicious code. A prevalent first step is for the hacker to enter into the user name field:

' OR 1=1 --

Now the query string generated looks like this:

string strQry =  " SELECT Count(*) FROM Users WHERE UserName = '' OR 1=1 -- AND Password = 'myPass';

1=1 will always evaluate to True, and the double-hyphen (--) is the standard form of commenting in SQL. As a result, everything after the “1=1” is effectively commented out. That leaves the query string as below, which is a legal SQL command:

string strQry =  " SELECT Count(*) FROM Users WHERE UserName='' OR 1=1 --

Note that the hacker doesn’t have a valid user name and password. Despite that, assuming that there is at least one row in the table of user logins, the hacker has succeeded in getting a count of records. Now the malicious weasel knows how many users or customers your company has; that’s annoying all on its own.

Further, if the hacker enters a string like this:

' OR 1=1; DROP TABLE USERS –-

…Then he can successfully drop the table if he has the requisite privileges. Unfortunately, that’s not difficult to achieve. Too often, web applications are run with admin level privileges. Running an application that connects to the database using the database's administrator account has the potential for an attacker to perform almost limitless commands with the database. Anything an administrator can do, so can an attacker.

Another exploit may be in the form of a string like the one below:

' UNION SELECT id, name, '', 0 FROM sysobjects WHERE xtype ='U' –

Here the UNION statement is used to join the results of one query onto another. Again, this query might be successful if access to important system tables like sysobjects and sycolumns is not restricted to the web application general login. The output of such a query gives access to internal database structures which can be exploited to manipulate entire tables. Protecting against this might seem like common sense, but these types of attacks are very common.

The examples above are the simplest form of attacks that could be executed on a website. But even the most sophisticated attack can be evaded by following some very simple measures:

1.  Use parameterized queries. This is one of the best ways to prevent SQL injection. When you do so, variables are not concatenated to a query string but are sent as parameters. The values of the parameters are searched in the table for a match. So if the query we used in the earlier example should be re-written as:

string strQry =
            "SELECT Count(*) FROM Users WHERE UserName=@username " +
            "AND Password=@password";

The values of @username and @password are compared against the values of the field in the table as against being concatenated. If the hacker typed in “a' or 1=1 --”, the result of the SQL statement is compared against the values in the table. That returns a value of False as there is no user with a name “a' or 1=1 --”.

2. Trust no data that comes through the system. Make no assumptions about data size, type, or content received by the application. Validate all input. This involves checking the length of data to avoid instances of buffer over runs. If a field expects strings, ensure that the data received is string, not binary data or special characters. This can be done by maintaining and comparing against a white list rather than trying to figure what is bad data. Anything not present in the white list is rejected. Some white list validator packages available are Apache Common Validator (http://jakarta.apache.org/commons/validator) and OWASP ESAPI Validator (

3. Restrict permissions to web user logins to access only the table in question. If the site is exploited, this measure restricts damage to just one table. In short, use a least privileged database account.

4. Use escape routines for all user supplied input. Parameterized SQL is the way to go at times. But if for some reason, the only available option is Dynamic SQL, input characters that have a special meaning to SQL Server should be handled to safeguard the input. Escape routines add an escape character to characters that have special meaning to SQL Server, thereby making them harmless. For example, to escape a single quote (') you can use the following routine:

[php]private string CleanSqlStmt(string inputSQL)&lt;br /&gt;&lt;br /&gt;{&lt;br /&gt;&lt;br /&gt;return inputSQL.Replace("'", "''");&lt;br /&gt;&lt;br /&gt;}[/php]



More examples and details can be found on (the Microsoft MSDN website).

5. Build strong exception handling routines: Exception handling routines should not provide any information that might assist the hacker in his efforts. On one website, I got this error message when I provided the input “a’ or 1=1 --”.

XmlPackage Exception: Exception=Last Trace Point=[].
An error occurred during a call to extension function 'XmlStringAsXml'.
See InnerException for a complete description of the error.
Data at the root level is invalid. Line 1, position 1.
System.ArgumentException: Last Trace Point=[].
An error occurred during a call to extension function 'XmlStringAsXml'.
See InnerException for a complete description of the error.
Data at the root level is invalid. Line 1, position 1.
at AspDotNetStorefrontCore.XmlPackage2.TransformString() at AspDotNetStorefrontCore.AppLogic.RunXmlPackage(XmlPackage2 p, Parser UseParser, Customer ThisCustomer, Int32 SkinID, Boolean ReplaceTokens, Boolean WriteExceptionMessage)

A seasoned hacker will find a loop hole to exploit with such a message. Error messages should never be disclosed. Have a generic error message for all exceptions.

Simple precautions can shield web applications from SQL injection. Making application security a priority at the time of design, and including security testing as part of the non-functional requirements are the beginning of good programming practices. Testing each of the points listed above should safeguard your software against SQL injection.


Close

By submitting this form, you agree to our
Terms of Use and Privacy Policy

Thanks for Subscribing

Keep an eye on your inbox for more great content.

Continue Reading

Add a little SmartBear to your life

Stay on top of your Software game with the latest developer tips, best practices and news, delivered straight to your inbox