Inoculating Yourself Against SQL Injection

By Larry Seltzer  |  Posted 2003-04-28 Print this article Print

The next great frontier for script kiddies can do some serious damage, writes Security Supersite Editor Larry Seltzer. How can your data-driven Web apps fend off the threat?

Ive been guilty of this one myself, but like so many practices that now are widely known to be unsafe, who knew at the time?

The problem is SQL injection. The very short form of the description is that the attacker finds a data-driven Web site where forms are used to provide data to a back-end program (such as a Perl script or Microsoft Active Server Page) that uses the form variables to construct SQL statements to pass to a SQL server. Instead of using real form variables, the attacker attempts to insert commands to the SQL server to do things unintended by the site programmer. Contemplate the following:

$SQL="SELECT * FROM STUDENTS WHERE LNAME EQ " . $query->param("lastname") . "";

That last line is Perl code that someone might use in a CGI script to construct a SQL statement that queries a database based on a value passed in a form field. (I think its proper Perl code; its been a couple of years since I wrote a lot of Perl.) The presumption is that the user will type in "Smith" or "Buttafuco" or something like that. But what if he enters "; EXEC xp_cmdshell net stop sqlserver, no_output"? It constructs two separate SQL statements in the same string, the second of which shuts down a Microsoft SQL Server.

A well-designed attack probably wont use the actual input fields in the browser, but a script in Perl or some other programming environment. This allows the attacker to fine-tune the attack and to save it for attempts on other sites. But it doesnt stop there. There are tools such as WebGoat for automating such attacks.

The attack above is specific to Microsoft SQL Server, but its not too hard to come up with attacks tailored to other database servers, such as using the Oracle UTF_FILE stored procedure to write to operating system files. A few points worth mentioning: For these attack to work, the server administrator and programmers would have to make certain casual errors. For example, the database server itself should be running in a user context that would disallow operations such as shutting down the server or modifying system configuration files.

Other SQL injection attacks may attempt to compromise the security of the data in the database by retrieving data not intended for the user. As a recent SecurityFocus discussion thread explored, it may be possible to use encryption in the database to protect against such attacks.

Apart from good administrative practices, the real answer to SQL injection is that your data-driven Web applications have to do some user-input validation. Microsoft has a decent set of guidelines for you to follow in writing SQL Server applications, although most of them are generally applicable to Web-facing SQL applications. (For example, check to see if that ZIP code the user sent is actually a ZIP code and not a 5MB MPEG file.) Some other guidelines to follow: Implement error handling, including a general error message for errors not specifically handled. Try not to program by constructing SQL statements but instead by using stored procedures and other proprietary constructs like ADO command objects.

These guidelines are somewhat discouraging in that they could mean a lot of work for you to make your applications safe. Oh well. Nobody said its easy to make your site secure, its just easy to crack it.

Im unhappy to report that there are lots of papers out there that describe how to use these techniques to attack sites and tools for doing it. So at the very least you need to assess your situation. This sort of attack is just a little too easy to ignore.

Security Supersite Editor Larry Seltzer has worked in and written about the computer industry since 1983.
Larry Seltzer has been writing software for and English about computers ever since—,much to his own amazement—,he graduated from the University of Pennsylvania in 1983.

He was one of the authors of NPL and NPL-R, fourth-generation languages for microcomputers by the now-defunct DeskTop Software Corporation. (Larry is sad to find absolutely no hits on any of these +products on Google.) His work at Desktop Software included programming the UCSD p-System, a virtual machine-based operating system with portable binaries that pre-dated Java by more than 10 years.

For several years, he wrote corporate software for Mathematica Policy Research (they're still in business!) and Chase Econometrics (not so lucky) before being forcibly thrown into the consulting market. He bummed around the Philadelphia consulting and contract-programming scenes for a year or two before taking a job at NSTL (National Software Testing Labs) developing product tests and managing contract testing for the computer industry, governments and publication.

In 1991 Larry moved to Massachusetts to become Technical Director of PC Week Labs (now eWeek Labs). He moved within Ziff Davis to New York in 1994 to run testing at Windows Sources. In 1995, he became Technical Director for Internet product testing at PC Magazine and stayed there till 1998.

Since then, he has been writing for numerous other publications, including Fortune Small Business, Windows 2000 Magazine (now Windows and .NET Magazine), ZDNet and Sam Whitmore's Media Survey.

Submit a Comment

Loading Comments...
Manage your Newsletters: Login   Register My Newsletters

Rocket Fuel