Coding Sanity

I just read this article by RudolfH, and am one of the people having a heart seizure in the corner. Whilst most of the points he makes are quite good, I have two main problems. One, which was also pointed out by rhanekom in the article comments is that parameterised queries can quite easily be used without stored procedures:

SELECT * FROM Customers WHERE ID = @id

The other is a far more serious implicit assumption in the article, which is that with enough validation on inputs one can ensure that a string passed to the database server is “clean”. While in theory this is indeed possible, for all but the very best security experts it is well-nigh impossible.

I am not going to assume that the code he posted was his attempt at validating input, since for strings it just wrapped the apostrophes into double apostrophes, and he said enough in the article that makes me believe that he knows that this is not sufficient. However many people may not know why, so I think I'm going to delve into the murky world of SQL injection for a bit. This may get quite long and involved, so please bear with me.

What is SQL Injection?

“SELECT * FROM Customers WHERE Name = '“ + txtName.Text + “'”

Imagine for a moment that txtName is filled in with the following text:

Sean' DROP TABLE Customers --

This happily translates into the folowing SQL statement:

SELECT * FROM Customers WHERE Name = 'Sean' DROP TABLE Customers --'

Which will indeed return Sean's details, but will also delete the Customers table. That's SQL injection, and it's bloody nasty.

Well, just replace apostrophes with double apostrophes then

Not so fast. This is indeed the solution that Rudolf demonstrates in his article, but it's by no means sufficient. For starters in certain circumstances the attacker can use the CHAR function to output an apostrophe, or could also use:

DECLARE @a char(20) SELECT @a = 0x64726f70 exec(@a)

Which executes the statement “drop”, needless to say a little bit of time with the ASCII tables and you can add in the “ table Customers” portion yourself. Also, keep in mind that Unicode defines many apostrophe characters, so if you have a Unicode-supporting database, I could just put in the Japanese apostrophe as an example. In addition, many developers only do the apostrophe replacing on text columns, and ignore it for non-text columns, even when they're passing user input directly to the server.

Okay, okay, then use stored procs, they're safe against SQL injection

Wrong again. Stored procs are of no real help here. They stop SQL injection attacks which expand the result set, such as entering “' OR 1 = 1 --“ into my txtName example above, but will not stop DDL based injection attacks. They may even make matters worse by internally calling EXEC() using data supplied by the user as all or part of it's parameter.

So what's the solution?

Well, first off, use parameterised queries at all times! The database provider will ensure that SQL injection attacks are stopped in their tracks.

If your stored procedures use the EXEC() internally to run dynamic SQL statements, see if you can use sp_executesql instead. It's very similar to EXEC(), but allows you to declare and pass in parameters:

EXEC sp_executesql 'SELECT * FROM Customer WHERE Name = @name', '@name varchar(50)', @name

This executes the statement in much the same way as a parameterised query in .NET, stopping SQL injection attacks.

Also, if you're building up dynamic SQL statements inside your stored procs, make sure you use the quotename function when adding an object name. This function puts the [] around the name, ensures that it's fully qualified and so forth:

EXEC 'SELECT * FROM ' + quotename(@myTable)

Finally, and most importantly have a good hard look at your logins and permissions. If the only permission the users have is SELECT, then they're not going to be able to do much more than look at data they're not supposed to, even if they do manage to inject a SQL statement.

The best overall approach is one of defense in depth.

1. Validate all your inputs rigorously, especially field lengths (there's not much damage you can do with a 3 char SQL statement).
2. Store your connection string somewhere VERY secure.
3. Use the OS's authentication and authorization whenever possible, when not, make your app fail if a short password is used. Make the app reject the login if the user is a sysadmin.
4. If you have to read your database connection string from a file or the registry, use Code Access Security to ensure that's all that the method can do.
5. Ensure that the user accounts have the absolute minimum priveleges required to get the job done. One way to check what these minimums are if you're using Test Driven Development is to revoke all privileges from the account, and then run your tests. Add onto the privileges until your tests passed, and you now have your minimum required privileges.
6. Use stored procs, since they help hide the implementation details from the attacker.
7. Use parameterised statements, both on the client and SQL side.
8. Strong type inputs. Just because you only allowed numeric characters to be entered does not guarantee you'll only get numerics from a text box, so cast the value to the relevant numeric type. Whidbey's TryParse methods will help a lot here.
9. Use try...finally to guarantee that you handle SQL errors gracefully.
10. Do NOT give any useful information out in the error message if you show one. I've seen messages where they give me back the entire failed SQL string. Things like that make an attacker drool.

Do all the above, and you might just have an app secure enough for a public beta. Now, think about all the programs you've released into the wild that do not fulfill most, or even any of the above conditions and you should hopefully get a shiver down your spine. I know I do when I think about mine.

Fore more security related goodies go check out Keith Browns blog. If you want even more, read Writing Secure Code 2 which is over 700 pages of security-related goodness, and of course Keiths book The .Net Developer's Guide to Windows Security.