Efficiently Protect Your Company from SQL Injection

Posted on September 18, 2014 Updated on September 18, 2014

Why Don’t Most Folks Protect Themselves?

Most programmers think that hacker attacks are rare, and defending is work, so they’ll hope they get lucky. Sometimes I think they’re just burying their heads in the sand. The problem is, they aren’t thinking about how devastating one lucky hacker attack can be. A hacker attack can easily put a company out of business; just think what would happen if all your credit card info was stolen, or your database backups were destroyed!

The other mistake they make is assuming they are protected by technology X; usually they put all their trust in their firewall or https. Sorry, hackers have defeated those technologies plenty of times. Furthermore, most attacks from from disgruntled former employees or business associates. They already know your passwords, so they use one weak app to get through the fire wall, then exploit their loop hole to attack other apps. Bottom line: the best security is to never assume any technology is foolproof, always use defense-in-depth.

I’ll guarantee that the latest company who got hacked had that attitude; i.e. the guys who screwed-up assumed that they didn’t need to use precautions because technology X was enough.I hope they like sleeping in their cars! I know you don’t like sleeping in your car, so spend some time to prevent a catastrophe.

My Defense Task: Prevent SQL Injection

SQL Injection is when an attacker submits SQL in a string that you use to run as a query. Generally you are vulnerable when you build an ad hoc query and run it against your database. For example, if you append the contents of a textbox to your query, the attacker can turn part of your intended query into a comment and run his query after a truncated version of your query runs. Note: any string from the outside world is a potential threat, even XML from your corporate partners. They could have disgruntled employees, or they could be hacked, or someone could spoof their transmission.

Normally, you can protect yourself by using parameterized queries. My problem: our ERP controls access to our database, and I can’t force it to use parameterized queries. Solution: scrub all input to sanitize SQL key words. For example, if the user input contains the words ‘drop table foo’, you can inject a space, or other character, into either ‘drop’ or ‘table’. In other words, substitute ‘dr op table’ whenever you find ‘drop table’. That way, legitimate  text is still readable, but your database won’t run the query.

Scrub Efficiently with Regular Expressions!

Your naive coworker will conduct six different search-and-replace operations. I assert that this is six times too slow. Instead, be smart, use regular expressions: then you can only make one pass through your text!

Naive Approach:

public string SlowScrub(string cleanMe) {
  string result = cleanMe.Replace("Drop Table", "D rop Table")
                         .Replace("Select ", "S elect ")
                         .Replace("Update ", "U pdate ")
                         .Replace("Delete ", "D elete ")
                         .Replace("Insert ", "I nsert")
                         .Replace("Truncate Table ", "T runcate Table ");
  return result;
}

There are three problems with the code above:

1. It makes 6 passes through your string, that slows-down your app
2. It is case sensitive (and the Replace method won’t allow you to change that problem)
3. It won’t catch Truncate Table or Drop Table when split across two lines

Regular Expressions to the Rescue

Here’s how I used a regular expression to make 1 pass through my string. The regular expression is a pattern to replace in the string. I also used a lambda (anonymous function) that .NET executes on every match it finds – how cool is that!

public string ScrubSql(string cleanMe) {
  string pattern = @"(          #Start our capture group
            (?:drop\stable)     #Non-capturing group 1; the word 'drop', space, then 'table'
            |                   #or
            (?:select\s)        #Non-capturing group 2: select' followed by whitespace
            |                   #Or
            (?:update\s)        #Non-capturing group 3; update' followed by whitespace
            |                   #or
            (?:delete\s)        #Non-capturing group 4; 'delete' followed by whitespace
            |                   #or
            (?:insert\s)        #Non-capturing group; the word 'insert' followed by whitespace
            |
            (?:truncate\stable) #'truncate table'
          )";

  string result = Regex.Replace(cleanMe, pattern, (m) => {
                    //This anonymous function is run on every match
                    string danger = m.Groups[1].Value.ToLower();
                    return danger.StartsWith("dr") ? "d rop t able "
                         : danger.StartsWith("s") ? "s elect "
                         : danger.StartsWith("u") ? "u pdate "
                         : danger.StartsWith("de") ? "d elete "
                         : "i nsert";
		 },
		RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace 
		| RegexOptions.Multiline | RegexOptions.Singleline);
  
  return result;
}

Notes:

• I include comments in my regular expression; that helps make it easier to maintain. # is the comment char
• This only works if I also specify ‘IgnorePatternWhitespace’ and ‘MultiLine’
• My Search Pattern consists of six different choices, the same choices I showed you in my naive implementation.
• The pipe character | means ‘or’ when used in a regular expression; it separates the for choices
• Note that I supply a function (a ‘MatchEvaluator’) that .NET runs for every match it finds
• My MatchEvaluator serves to identify which dangerous text it identified in the input string
• And then it returns an appropriate sanitized string
• Note that I also specified ‘IgnoreCase’
• Also: the RegexOption ‘SingleLine’ is a bit confusing. It basically means “match your text as if it were a single line”, with the effect that, when your pattern seeks 2 words, it will find them even if on separate lines.

Bottom Line

You can easily plug-in my little function to your code. Every string you get from the outside world, use my funciton to scrub that string. The job you save may be your own!

Download the Code!

Download sample