Inoculating Yourself Against SQL Injection

By Larry Seltzer  |  Posted 2003-04-28

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.

Rocket Fuel