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.

Sometimes I just cannot believe the audacity of some people. I've held off writing this post in the hope that I'd calm down, but every time I read this article my blood pressure goes through the roof. Here is the guy behind that fantastic game Half Life 2, and it's associated trojan Steam, slating the entire gaming industry whilst the whole time almost wrenching his arm out of his socket patting himself on the back.

I've actually written about his software before, in my article No Nasty Surprises, but I didn't then want to identify the offender as Valve and Half Life 2. These are a bunch of guys who dishonestly label the Half Life 2 box as containing a game, which then turns out to basically just contains a spyware tool that downloads the game for you, if you jump through a series of hoops. Oh, fair enough, the CD does come with a download cache on it, so you don't have to download the WHOLE game, just a huge update. Then, to add insult to injury, it only allows you to play the game if you're connected to the Internet so it can verify that a person who actually went to the trouble to go to a store to buy this useless box isn't a cheat.

It's a sad day in computing when legitimate customers have to jump through more hoops than people who rip off the software. After my experiences I will NEVER use Steam again. If I want to play the next version of Half Life, I'll maybe buy the box, and then get myself a cracked copy, and play that. I had a friend who was up and running on his cracked copy within 2 hours of release. Me, the schmuck, was only running 3 days after purchasing a legal copy of the game.

Some illustrative quotes from Gabe Newell's interview:

"There were two sort of big sort of painful periods for Steam. One was when it went from being optional, to being the way that everybody needed to get updates. That was pretty painful for people. And then the Half Life 2 launch when we got swamped with not having enough capacity."

Oh, really, “pretty painful” was it? Being treated like a criminal for having the idiocy to give you my money was “pretty painful”? And as for not having enough capacity, you sure as hell had enough capacity for shipping boxes, did you really think that people were just going to buy these things and put them on a shelf for a few days?

"People are actually running two versions of Steam right now. They may not realize that, but they're running Steam 2 and Steam 3 alongside of each other. The nice thing about having a system like this, is there's no reason not to have your old system and new system coexist while you're migrating functionality from one to the other."

Great, wonderful, if I'd kept this thing on my desktop, I'd have TWO pieces of software trying to surreptitiously connect to the Internet the whole time, and TWO pieces of software using up my RAM and processor cycles to no good effect (for me anyway). Great advertisement guys.

"I've never written a single line of multi-threaded code, ever."

A game developer? And this is the tool that's fulminating on about how Microsoft and Sony are making his life difficult? They're acually having the audacity to say “hey guys, Moore's Law is slowing down, new improvements in performance are going to have to come from concurrency”, and this guy is whinging about how that makes his life harder. Ag shame!

“So with Steam we're saying, 'Here's a set of tools that software developers need, focused on solving the problems that we have with this next generation of games.' And that includes billing, updates, product support, connecting our customers to one another, and things like that. So it'll be interesting to see how important that functionality is to other developers.“

How's that, one of the problems with the next generation of games is billing? Not multi-threading, oh no, billing. Funny, I don't see why that's a problem, but hey that's just me. Let me try and help Gabe out:

Step 1: Valve concludes agreement with distributer.

Step 2: Distributer creates lots and lots of boxes, and sells them to stores.

Step 3: Stores sell boxes to consumers, and return empties.

Step 4: Distributer deducts costs (including returns), a profit margin, and hands money over to Valve.

I know it's complicated, perhaps even more complicated than multi-threading, but we've only been doing this retail stuff for a few thousand years, so perhaps it's understandable that Gabe hasn't picked it up yet.

As for updates, you know many games now have a nice little button that says “Check for updates”, and the game will happily go along and start a download if you press the button. Gabe, I'm pretty sure that many of your customers are switched on enough to figure out that little problem. If you think they're such utter nincompoops, perhaps you can pop up a little message box that says “Do you want to check for updates?” if they try to play the game without updating it.

But, noooo, you guys won't even allow them to play without updates. Normally, when I get a new game, I kick off any update downloads in the background, and then play the game while the updates are coming down. If this guy has his way, that freedom will be a thing of the past. I am hereby starting my Wall of Tools, and Gabe Newell is number one on the list.