SQL injection attacks

There is a not so new attack increasingly making the rounds these days, popularly called SQL injection attacks. Any relational database connected to the network is theoretically susceptible, regardless of operating system or database engine. Perhaps worse, it is one that developers and DBAs have to set aside their rivalries in order to reliably protect against.

How it works

Say your site takes text input from a user. To keep things simple, let's talk about a login page that requests a user name and password. To validate the user, you then pass the input from that form to a dynamic SQL statement something like this, where sUserName and sPassword hold the values entered by the user: sSQL = "SELECT UserLevel FROM Users WHERE UserName = ''" + sUserName + "' AND Password = '" + sPassword + "'"I'm using SQL Server's Transact SQL syntax here, but it's going to be pretty similar for other databases. This results in a SQL statement that looks something like this: SELECT UserLevel FROM Users WHERE UserName = 'don' AND Password = 'w0asDf!4iea'The app then takes the recordset and validates the user if a row is returned with her user level, or invalidates the user if no data is returned. Neither the only way to do validation nor one of the prettiest, but fairly typical for Web pages.

The problem arises when the programmer doesn't carefully validate what the user has typed in, so it is simply concatenated into the SQL statement. Imagine that a mischief-maker enters this password: ';DROP TABLE tblBigWebOrders; -- (That's a single apostrophe at the beginning, with a space and two dashes at the end.) The resulting SQL looks like this: SELECT UserLevel FROM Users WHERE UserName = 'don' AND Password = '';DROP TABLE tblBigWebOrders; --' The result is two statements. One searches for user don with an empty password (which hopefully it won't find) and the second deletes your Web order entry table. Not good.

Don't panic yet

The database server has to be configured in a particular way for the table to actually get dropped. Notably the user under which the statements are executing must have permission to drop a table, something no Web app login should be allowed to do, unless it is a database maintenance app. It also requires that the user know the name of a table, in this case tblBigWebOrders.

Feeling better? Don't. This is just a very simple example of how someone can probe your database for vulnerabilities, and the way the Web app handles errors determines how hard or easy it is to attack your system. Have you ever gone to a Web site, entered some information, and got back a frame that looked something like this?

Microsoft OLD DB Provider for SQL Server error '800xxxxx' [Microsoft][SQL Server Provider][SQL Server]Unclosed quotation mark before the character string 'aaa' AND password = ''. /login.asp, line 42.

Now the attacker has the information to guide the next step of the attack. With persistence and these kinds of helpful guidance, an attacker may be able to break into system tables and get to the juicy information.

Also, don't take too much comfort that the login user under which the SQL is executing won't have permissions to drop a table or other objects. Far too many Web apps out there use the sa user to save the time of carefully designing security for a database application.

Next week, I'll talk about some of the things you can do to protect your database.

Join the newsletter!


Sign up to gain exclusive access to email subscriptions, event invitations, competitions, giveaways, and much more.

Membership is free, and your security and privacy remain protected. View our privacy policy before signing up.

Error: Please check your email address.

More about Microsoft

Show Comments