SQL Injection Prevention: ID’s in the Query String
In the past year or so, I’ve helped solve three or four problems with data on a web site site being inserted/changed through SQL injection. Not a fun thing to have happen.
I used to think of SQL injection attacks coming through web forms, where people try to enter things into forms that cause the attacks. This can be a very dangerous type of attack, because in some cases, it will allow the attacker to log in as an administrator of the web app, therefore being able to see/do whatever they want in the application.
However, the attacks I’ve seen have all come through query strings, on pages that have URL’s like webapp.myserver.com/showpage.cfm?ID=8 What they do is simply add a semicolon ( ; ) to the end of the URL, then append SQL commands to it. If the application simply uses the query string variable “ID” to build a SQL command, the application is vulnerable.
The cases I’ve worked on have all been old, legacy code, and have been in two or three different programming languages. Bad code can be written in any language. These applications were written back when we didn’t hear much about SQL injection, therefore application developers didn’t always practice good programming practices. In fact, one of the applications I helped fix recently was originally written by a commercial firm that did some programming for one of our departments.
In each case, the way I fixed the problem was to simply convert the ID to an integer, then used this integer to build the where clause of the SQL command. By doing so, real requests for the web page work just fine, and one of three things happen the bogus hits come in:
- The server throws an error because the ID can no longer be converted to an integer.
- The conversion returns a default integer (such as zero), in which case you probably don’t have a ID=0 in your database, giving some kind of page not found message.
- The first part of the string is converted, up until it finds a character that is not a digit. In the example above, it would return the page where ID=8.
In all these cases, converting the query string variable ID to an integer, prevents the SQL injection attacks from being successful because it doesn’t pass the whole ID string along to the database.