Strike Back at SQL Injections

What do Lady Gaga, PBS and the British Royal Navy have in common?

They all own websites that were hacked over the past two years by structured query language (SQL) injections.

Although the IT world has understood the methods and vulnerabilities for years, the attacks continue to increase for many reasons -- including the arrival of tools that enable hackers to automate some of their processes.

To help enterprises and other organizations avoid becoming the next victim, we sought out Paul Litwin, programmer manager at the Fred Hutchinson Cancer Research Center in Seattle and owner of Deep Training, a .NET training company.

Here's his advice for identifying and thwarting SQL injection attacks.

Q: Give us an example of how simply entering a malformed SQL statement in a website's textbox gives a hacker access to an underlying database.

Litwin: Many applications use a form to authenticate users. For example, in a typical insecure ASP.NET application, when a user clicks a login button, a method might authenticate that user by running a query. This query might calculate the number of records in a database table that match the username and password entered in the form's textbox controls.

By entering text that seems harmless, such as "Or 1=1 --," it's possible for a hacker to form a syntactically correct query. For example, this might be the query behind the ASP.NET page:

string strQry = "SELECT Count(*) FROM Users WHERE UserName='" +

txtUser.Text + "' AND Password='" + txtPassword.Text + "'";

Now, when a "good" user enters a name of "Paul" and a password of "password," strQry becomes:

SELECT Count(*) FROM Users WHERE UserName='Paul' AND Password='password'

But when the hacker enters "' Or 1=1 --" the query instead becomes:

SELECT Count(*) FROM Users WHERE UserName=" Or 1=1 --' AND Password='"

And because in SQL, a pair of hyphens indicate the beginning of a comment, the query becomes:

SELECT Count(*) FROM Users WHERE UserName=" Or 1=1

But the expression 1=1 is always true for every table row, and a true expression or'd with another expression will always come back as true. So if there's at least one row in the table, this SQL will always produce a nonzero record count and get the hacker authenticated into the application.

Q: Is SQL Server the only product that's vulnerable to SQL injection attacks?

Litwin: No. DB2, Oracle, MySQL and Sybase are examples of other databases that are equally vulnerable. That's because the SQL language has several features that are designed to make it powerful and flexible, but these also create risks. One example is the ability to use a pair of hyphens to embed comments in an SQL statement. Another is the ability to string together multiple SQL statements and then batch-execute them.

Basically, the more powerful the SQL dialect, the more vulnerable that database is. That's why SQL Server is so frequently targeted.

And keep in mind that SQL injection attacks target more than just ASP.NET applications. Classic ASP, Java, JSP, Ruby on Rails and PHP applications, and even desktop applications, are vulnerable too.

Q: What do you recommend for preventing SQL injection attacks?

Litwin: First and foremost, implement multiple layers of protection. That way, if one safeguard is breached, others still stand in the hacker's way.

Here are five tips:

1. Don't trust user input -- ever.

Use validation controls, regular expressions, code and other methods to validate every single textbox entry.

2. Avoid dynamic SQL.

Instead, use parameterized SQL or stored procedures.

3. Never link a database to an admin-level account.

Always use a limited access account to connect to the database.

4. Encrypt or hash passwords and connection strings.

Never leave this kind of sensitive information as plain text.

5. Keep error messages at a high level.

The more information those messages have, the more clues they can provide to hackers.

For more tips, check out Paul Litwin's article "Stop SQL Injection Attacks Before They Stop You."

Photo Credit: @iStockphoto.com/kr7ysztof

 

Tim Kridel has been covering all things tech and telecom since 1998 for a variety of publications and analyst firms. Based in Columbia, Mo., he still enjoys the childhood hobby that led to a career writing about technology: ham radio.

 

Twitter: @ihavenet

 

 

Copyright © 2011 Studio One Networks. All rights reserved.