SQL Server Injection

A friend of mine has been experiencing some trouble with her websites. Someone has been making a mess off of her websites by doing SQL injection… Here’s my take on it… it won’t make you 100% safe but it will be much much better…

SQL injection is an attack to a website in which malicious code is inserted into fields that are later passed to an instance of SQL Server for execution. Any webpage that takes such SQL statements should be reviewed for injection vulnerabilities because SQL Server can execute all the queries that it receives. Some recommend that parameterized queries be used, but they can also be manipulated by a skilled attacker.
SQL injection in its most common form takes a string value field and instead of filling it with the appropriate values, SQL commands are entered. SQL Server cannot tell the difference between one and the other and executes all commands passed, executing the expected code plus the malicious code along with it. The injections process works by terminating the value in the middle of the field’s content, then attaching malicious code and finalizing the contents with "–" to comment out the remainder of the original SQL statement.
A simple case of injection can be observed here:

ASP/VB Code:
Dim strLogin, strSQL as String
strLogin = Request.form("txtLogin")
strSQL = "SELECT * FROM tblUsers WHERE vchLogin = ‘" & strLogin & "’"
The user is prompted to enter the name of the Login for which the profile will be returned. If the user enters "JDoe", then the SQL statement will look like this:
SELECT * FROM tblUsers WHERE vchLogin = ‘JDoe’
But, let’s assume that the user enters "JDoe’; DROP TABLE tblUsers–" then the SQL statement will look like this:
SELECT * FROM tblUsers WHERE vchLogin = ‘JDoe’; DROP TABLE tblUsers–‘
The SQL Statement will execute the query, then drop a table and end up parsing a commented out quote. This will happen because the semicolon ( ; ) indicates the end of one statement and the start of another. As long as the injected SQL code yields a syntactically correct SQL statement, anything that can be inserted in the field can be used to execute unauthorized injected SQL.

What can be done about it?
The most simple approach is to limit every source of input that will end up being part of the query to the absolute bare minimum allowable. All the measures I am going to mention if used by themselves can be avoided by a smart and savvy hacker. Therefore you are much better off using them in combination (even better if using all of them at once in all your pages). When all measures are used on the same web page, your code is better protected from ill intentioned hackers.

  • Limit the size of the field on the web page to the maximum size of the column in the database. Be careful: this can be circumvented if you don’t check for size when requesting the field value with Request.form into a local variable because SQL code can be also injected in the URL without size limiations.
  • Limit the size of the value requested (preventing the issue mentioned in the previous bullet). Use strLogin = Trim(Left(Request.form("txtLogin") & Spaces(10), 10)) where 10 is the expected maximum size of the field receiving the data. You can also add more lines of code instead of resolving everything in a single line such as IF Len(Request.form("txtLogin")) <= 10 Then and then show an error message or simply ignoring cases where the contents are larger than expected.
  • Test the content of your local string variables and accept only expected values: Reject entries that contain binary data, escape sequences, and comment characters. This can help prevent script injection and can protect against some buffer overrun exploits.
  • Look for ‘ (single quote): that could act as a string delimiter. If you are expecting this type of character; for example if this were a Last Name field and someone could type "O’Neal"; then replace it with ” (two single quotes) right before execution; otherwise, just reject the contents with an error message.
  • Look for ";" (semi colon): that could be used as a SQL statement delimiter. Reject it unless you are expecting such character combination within the field’s values.
  • Look for "–" (two minus signs): that will work as a SQL comment embedded in a statement. Reject it unless you expect this as a value.
  • Look for "/*" followed by "*/" with any number of characters in between. Another form of adding comments in SQL statements. Reject them unless you expect them as a part of your field’s data.
  • Look for "sp_" which could indicate the attempt to run a system procedure. Reject this entry.
  • Look for "[" which delimits escaped characters. Reject this entry or replace with "[[".
  • Look for "]" which delimits escaped characters. Reject this entry or replace with "[]".
  • Look for "%" which is used as a wildcard in "like" queries. Reject this entry or replace with "[%]".
  • Look for "_" which is used as a wildcard in "like" queries. Reject this entry or replace with "[_]".
  • Take advantage of "type safe parameters" which only allow the data type you created and will throw an exception if the data is not valid.
    For example:
    SELECT * FROM tblUsers WHERE vchLogin = @vchLogin
    Then define the parameter’s datatype as SqlDbType.VarChar
    Assign the value from the text box into the parameter. If it is not valid, an exception will be thrown.
  • If you don’t want to return validation errors, then you can simply do a "replace" and replace one character for another: "’" for "”"
  • Review and correct code and stored procedures that use "sp_executesql"

About Diego Samuilov

Editor in Chief/Founder Diego Samuilov is an executive, consultant, IT strategist and book, e-book and web published author. Diego has worked in Microsoft’s environments since 1990. Since then, he has successfully filled many positions related to the Software Development lifecycle. Having worked as a developer, analyst, technical lead, project lead, auditor and, since 1996 a project manager, manager, director and VP in the Software Development, Server, Desktop and Mobile environments. Diego is very passionate about the software development process, which has played a great part in his skills development. Since the introduction of the first ever PDA (the Apple Newton MessagePad) in 1994 and Windows CE in 1998 he has pioneered and pushed the envelope in the field of mobile software development. He has developed many solutions used in mobile markets, desktop and server environments. He participates in public and private developer community events. He actively collaborates with the community at support forums and blogs. Diego is the author of "Windows Phone for Everyone" available [HERE].