I was reading this article recently on InfoWorld, and even with all of the best practices and options available to avoid it, SQL injection attacks still make up 20% of the world's hacks and are the single largest attack vector in use. I thought of all the times I was mocking up a project and passed values straight through, only to have to go back later and cleanse the inputs. How many people mean well, but forget to go back and address their //TODOs? There are a few ways to mitigate this:
- Only allow database access through stored procedures
- Only allow database access through a DAL that strips out injection attacks
- Cleanse all text inputs of common injection code before passing them through
I have a client whose site is was filled with myriad opportunities for an attack of this nature to proceed. The original site architect never designed a DAL, had business logic mixed throughout the code-behind pages, and constructed most SQL commands as:
"SELECT * FROM customers WHERE customers_username = '" + txtUsername.Text + "' AND customers_password = '" + txtPassword.Text + "'";
Since rewriting the entire site using stored procedures was outside of the scope of the project, I created the following function to strip out possible attacks.
public class Common {
public static void CleanSQLInputs(ref string sToClean)
{
string[] blackList = {"/*","*/","--",";-",";","@@","cursor
","declare ","delete ","drop ","execute ","insert ","select
","sysobjects","syscolumns","xp_"};
for (int i = 0; i < blackList.Length; i++) {
sToClean = sToClean.Replace(blackList[i], "");
}
sToClean = sToClean.Replace("'", "''");
sToClean = sToClean.Replace("\"", "''");
}
}
Now, anywhere I need to clean an input, I just call
Common.CleanSQLInputs(sUsername);
before passing sUsername into the SQL command. I've seen other solutions that implement this as a function returning a string, but I prefer to do it this way at the beginning of each method for any strings that are being passed in from the UI to the database so I have to keep track of what's been cleansed.
Luckily, the site in question had never been exploited. But past precedent is no substitute for real security, and the site owner was relieved to hear that this was in place.
Have you ever been hit with a SQL injection attack? What steps have you taken to shore up your code from attacks?