The News‎ > ‎

Easy test for SQL injection. Easier than spotting a tall guy in a crowd wearing a red hat.

posted Jun 14, 2012, 3:31 AM by Leigh Williams   [ updated Jun 14, 2012, 3:34 AM ]
Believe it or not, some people are still not sure what SQL injection is, how it works or how to prevent it. Read up on OWASP (www.owasp.org) for great insights into identifying these types or vulnerabilities and how to prevent them.
According to the Trustwave Global Security Report for 2012, 6.9% of infiltration methods were through SQL Injection. This is 6.9% or breaches that could easily have been prevented or detected.
 
Any web application (or normal application) that connects to a database, is vulnerable to SQL injection. The easiest way to test is to enter a single quote (') or a semicolon (;) into any text field (textbox) and see what the page returns. This would serve as a string terminator and the application will detect an error when trying to parse the string.
 
The following normally happens in the backend:
string __SQLQuery = "select username, password from [users] where username = ' "+unametxt.text+" ' and password = ' "+pwordtxt.text+" ' " (I added the spaces to make it more readible.)
unametxt.text and pwordtxt.text are the names of the text boxes on the website.
Please, whatever you do, do not ever in your entire life do this. It is most probably the most unsecure code for authentication. It is merely used as an illustration for how SQL Injection works, and the logic behind it. You will get beaten up, severly, schoolyard rules style (which means, no rules).
 
Now lets say you get to a logon page with 2 text boxes, one for your username and one for your password and a button which you press to log in to the site, the above SQL query might execute. Now, if you add the single quote (') to any of the text fields, the following happens to the string that the page has to parse (I will substitute the values that you entered into the names of the text boxes)
string __SQLQuery = "select username, password from [users] where username = ' ' ' and password = '' " (i added the sapce again for readibility). That bold red quote is what you entered into the username textbox, and the password text box you left blank.
 
Now what happens is the following: There is one extra single quote in the string, so there is a syntax error. You will get a message like the one below:
Unclosed quotation mark before the character string ''
 
Now, where the logic part comes in. When you enter, for example. admin, into the username field and 1'or'1'=1 into the password field, it will authenticate the admin user (if such a user account exists) because of the following:
string __SQLQuery = "select username, password from [users] where username = ' admin ' and password = '1' or '1'='1' "
We are interested in the bold red portion because that is waht you entered and it does the following:
Authenticate the user admin and the password I provided for the admin account (which is one), but the OR will ALWAYS be true (like your boss' argument) because OR 1=1 is always true. 1 will always equal 1! Unless the laws of the universe changes or everything we believed in is a lie.
 
So how can you prevent SQL Injection?
There are a few ways you can protect yourself. Following these items below should protect you from SQL Injection: (See http://msdn.microsoft.com/en-us/library/ff648339.aspx and https://www.owasp.org/index.php/Guide_to_SQL_Injection#How_to_Avoid_SQL_Injection_Vulnerabilities)
  • Constrain and sanitize input data. Check for known good data by validating for type, length, format, and range.
    • Do not do this for every textbox or data input element, you will miss something somewhere. Write a method (or function) that does the sanitization and pass all text input through this method (or function)
  • Use type-safe SQL parameters for data access. You can use these parameters with stored procedures or dynamically constructed SQL command strings. Parameter collections such as SqlParameterCollection provide type checking and length validation. If you use a parameters collection, input is treated as a literal value, and SQL Server does not treat it as executable code. An additional benefit of using a parameters collection is that you can enforce type and length checks. Values outside of the range trigger an exception. This is a good example of defense in depth.
  • Use an account that has restricted permissions in the database. Ideally, you should only grant execute permissions to selected stored procedures in the database and provide no direct table access.
  • Avoid disclosing database error information. In the event of database errors, make sure you do not disclose detailed error messages to the user.
  • Limit exposure. Do not expose a website to the entire world if the entire world shouldn't have access to it.
  • IPS and IDS. Where possible, use an IPS and/or IDS to detect maliciously crafted SQL queries if you are behind a big corporate network. (Only available to a select few of us)
As always, perform penetration tests and vulnerability scanning on your own websites before you deploy them to ensure that you didn't miss anything.
 
Remember, Google is you friend (but use TOR or your neighbors WiFi with his consent). This article covers only the basics and it gets A LOT more serious than this. So read and improve those programming and security skills!
Comments