Protecting Tables from SQL Injection Attack

A recent question in a forum that I view asked about how to ensure that even if one layer of security was compromised that the table would only ever return one row at a time so that an attacker would have to do more work to get a list of the users and passwords out of the database.

The way I see it, the best solution is not just to set up constraints, assuming that your database can add a constraint to only ever return the first row in a query, but to protect the table by not granting access to it directly. Then set up stored procedures to perform all the operations that you permit on the table. That way, if an attacker should get through the “outer defences” they cannot access the tables directly, and must use the stored procedures.

For example, say you have a database that has the user details for a website, this includes the user name and password. You don’t want an attacker to get a list of passwords or even one password. So you design the stored procedures so that you can pass a password in, but it will never put a password in a result set. The stored procedures for registering and authenticating a user for the website might be:

  • RegisterUser
  • VerifyCredentials
  • ChangePassword

RegisterUser takes the user name and password as parameters (possibly along with other information that would be useful for your website) and returns the UserID

VerifyCredentials would be used for logging into the site by accepting the user name and the password. If there was a match the UserID is returned, if not then a NULL value.

ChangePassword would take the UserID, the old password and the new password. If the userID and password match the password can be changed. A value that indicates success or failure is returned.

As you can see that the password is always contained in the database and is never exposed. The stored procedure could potentially generate a salted hash of the original password too so that should some layer of the database security be compromised that the password is still not readable.

You must also be careful when you call the stored procedure and ensure that you use parameterised queries. SQL Injection attacks are also possible when calling stored procedures if they are called by building up a SQL statement dynamically and executing it.

NOTE: This was rescued from the Google cache. The original date was Thursday, 6th January, 2005.


Original comments:

Salted hash is a pretty tricky thing to do in SQL Server 2000, but should be very simple in SQL 2005 with a CLR function that wraps System.Security.Cryptography. For the moment you should probably hash the password on the client, i.e. in the web application code.

The downside, of course, to salted hashes is that you can’t ever tell the user what their password was. You have to have a facility to allow them to reset their password by supplying some other information instead (the ‘password reset question’ technique).

1/10/2005 12:04 AM | Mike Dimmick

Leave a Comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s