SQL Injection Prevention: Stored Procedures

There are lots of things that can be done in a web application to help minimize the threats of SQL Injection attacks. There are also some techniques that I use on the database server to reduce the threat.

First, I try to minimize the amount of sensitive data we store. For example, we are not allowed to store credit card numbers. If we do e-commerce on our servers, we only store the transaction number that we get back from the campus’s e-commerce system. Of course, social security numbers are never allowed either.

Next, I use a different user account for each database on the server, and each account only has access to a single database. This way, if for some reason, someone gains access with that account, they can only mess with one database.

The next thing I try to do is have all database interactions go through stored procedures on the database server. When doing this, we do not give the database user any read/write privileges to the database tables. We simply give it rights to execute the relevant stored procedures.

Another advantage of the stored procedures is that all data is passed into them through parameters. Because the parameters are encoded when they are passed in, it greatly reduces any chance that any attack can be successful. The data still needs to be verified that it looks like valid input, this just adds another level of protection.

A side benefit of using stored procedures is that I can modify the stored procedure without having to change the application that uses that. This works as long as the structure of the input/output doesn’t change. As an example, there have been a couple times when I’ve had to change the order of things on web pages on our productions server. I’ve been able to do that by changing the “order by” clause in the select statement of the stored procedure without changing the web application at all.

A down side of using stored procedures is that it may take a little extra time to set them up, and code the application to use them. However, we use a tool in .NET that makes writing against the stored procedures pretty easy. More about this in an upcoming post.

Although they may take a little extra time to implement, I feel the added security offered by communicating with the database through stored procedures is well worth the effort.

See CASE Statements in SQL Query for a method to write flexible stored procedures.

%d bloggers like this: