Security and SQL Injection

Let me start by saying I hate security. I hate everything about it. I don't like the policies, I don't like the procedures and I don't like the hoops I have to jump through to prove that the database or application is secure. Mostly though I don't like the people who like security. And truth be told, it isn't the people in most cases. It is the job they have to do which is to put in place and enforce the procedures and policies I so detest. Wow that was pretty circular and not easy to follow and a little on the negative side. So let me try and be more positive. I really like the people who find the security holes. I love people who want at the data so bad they dream of ways to get at it. I always hope they don't use the info to harm anyone or anything, but I love the idea of taking something as simple as a login screen and manipulating it through simple input characters and being able to "break into" the db.

And truth be told, "breaking in" is all an injection attack is. An sql injection attack is a means of gaining access to a db by "injecting" special characters, queries and commands via an input field through any user input field. For our purposes here, we will assume it is a web application connected to a MS SQL server db. It should be noted however that injection attacks are not limited to SQL server nor is it limited to ASP.net. Any database that excepts any form of user input can be vulnerable. Here is a great example of how an sql injection could be performed courtesy Mitchell Harper and his article on sql injections attacks:

Microsoft SQL Server has its own dialect of SQL, which is called Transact SQL, or TSQL for short. We can exploit the power of TSQL in a number of ways to show how SQL injection attacks work. Consider the following query:

select userName from users where userName='' having 1=1

If you're an SQL buff, then you'll no doubt be aware that this query raises an error. We can easily make our login.asp page query our database with this query by using these login credentials:

Username: ' having 1=1 --- Password: [Anything]

When I click on the submit button to start the login process, the SQL query causes ASP to spit the following error to the browser:

Microsoft OLE DB Provider for SQL Server (0x80040E14)
Column 'users.userName' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
/login.asp, line 16

Well well. It appears that this error message now tells the unauthorized user the name of one field from the database that we were trying to validate the login credentials against:

users.userName. Using the name of this field, we can now use SQL Server's LIKE keyword to login with the following credentials:

Username: ' or users.userName like 'a%' --- Password: [Anything]

Once again, this performs an injected SQL query against our users table:

select userName from users where userName='' or users.userName like 'a%' --' and userPass=''

When we created the users table, we also created a user whose userName field was admin and userPass field was wwz04ff. Logging in with the username and password shown above uses SQL's like keyword to get the username. The query grabs the userName field of the first row whose userName field starts with a, which in this case is admin:

Logged In As admin

Now I realize this doesn't cover every possible situation and that there are many ways to prevent this depending on how you log in, but think of it as a big picture use case. Any user input field can be used to inject SQL commands that can be used for evil. Now you can depend on the good nature of the human spirit and hope no one who comes to your site or uses your application would want to do evil things which is how I like to view the world. Or you can step into reality and take some steps to prevent it. And as Grandma Dillon always used to say, "An once of prevention is worth a pound of cure."

The best two ways to approach prevention are described in the following paragraph Microsoft:

All Input is Evil
The first principle listed is extremely important: assume that all user input is evil! You should never use unvalidated user input in a database query. The ASP.NET validation controls, especially the RegularExpressionValidator control, are a good tool for validating user input.

There are two basic approaches to validation: disallow troublesome characters or only allow a small number of required characters. While you can easily disallow a few troublesome characters, such as the hyphen and single quote, this approach is less than optimal for two reasons: first, you might miss a character that is useful to hackers, and second, there is often more than one way to represent a bad character. For example, a hacker may be able to escape a single quote so that your validation code misses it and passes the escaped quote to the database, which treats it the same as a normal single quote character. A better approach is to identify the allowable characters and allow only those characters. This approach requires more work but ensures a much tighter control on input and is more safe. Regardless of which approach you take, you'll also want to limit the length of the entry because some hacks require a large number of characters.

GoodLogin.aspx (also found in the code download) contains two regular expression validator controls, one for user name and the other for password, with the following ValidationExpression value that limits entries to between 4 and 12 characters of digits, alphabetic characters, and the underscore: [\d_a-zA-Z]{4,12}

You may need to allow the user to enter potentially injurious characters into a textbox. For example, users may need to enter a single quote (or apostrophe) as part of a person's name. In such cases, you can render the single quote harmless by using a regular expression or the String.Replace method to replace each instance of the single quote with two single quotes.

For example: string strSanitizedInput = strInput.Replace("'", "''");

Avoid Dynamic SQL
The SQL injection attacks I have demonstrated in this article are all dependent on the execution of dynamic SQL -that is, SQL statements constructed by the concatenation of SQL with user-entered values. Using parameterized SQL, however, greatly reduces the hacker's ability to inject SQL into your code.

Parameterized SQL is great if you absolutely must use ad hoc SQL. This might be necessary if your IT department doesn't believe in stored procedures or uses a product such as MySQL which didn't support them until version 5.0.

The full microsoart article, along with some great examples can be found at

http://msdn.microsoft.com/msdnmag/issues/04/09/SQLInjection/

So there you have it, a quick overview of sql injection attacks and some methods of prevention. I have now fulfilled my duty of talking about security, which seems to be a required topic on any technology blog, and should allow me to keep my membership to the DBA jelly of the month club. Oops, that was supposed to be kept secret. Now everyone is going to want to be a DBA.