Thursday, March 17, 2011

Sanitize input strings against SQL injection using Regex

If SqlParameters cannot be used this is an anti-pattern that will do the job ( C# ):
        public static string Sanitize(this string stringValue)
            if (null == stringValue)
                return stringValue;
            return stringValue
                        .RegexReplace("-{2,}", "-")                 // transforms multiple --- in - use to comment in sql scripts
                        .RegexReplace(@"[*/]+", string.Empty)      // removes / and * used also to comment in sql scripts
                        .RegexReplace(@"(;|\s)(exec|execute|select|insert|update|delete|create|alter|drop|rename|truncate|backup|restore)\s", string.Empty, RegexOptions.IgnoreCase);

        private static string RegexReplace(this string stringValue, string matchPattern, string toReplaceWith)
            return Regex.Replace(stringValue, matchPattern, toReplaceWith);

        private static string RegexReplace(this string stringValue, string matchPattern, string toReplaceWith, RegexOptions regexOptions)
            return Regex.Replace(stringValue, matchPattern, toReplaceWith, regexOptions);

And some tests for input strings ( C# ) ...
        public void OnNullShouldReturnNull()

        public void OnValidStringShouldReturnTheSameString()
            Assert.AreEqual("a", "a".Sanitize());
            Assert.AreEqual("aa", "aa".Sanitize());
            Assert.AreEqual("-", "-".Sanitize());
            Assert.AreEqual("a-", "a-".Sanitize());
            Assert.AreEqual("-a", "-a".Sanitize());
            Assert.AreEqual("-a-", "-a-".Sanitize());

        public void OnMultipleDahsedShouldReturnJustADash()
            Assert.AreEqual("-", "--".Sanitize());
            Assert.AreEqual("a-", "a--".Sanitize());
            Assert.AreEqual("-a", "--a".Sanitize());
            Assert.AreEqual("-", "---".Sanitize());
            Assert.AreEqual("-a-", "----a----".Sanitize());

        public void ShouldRemoveGroupCommentsSymbols()
            Assert.AreEqual(string.Empty, "/".Sanitize());
            Assert.AreEqual(string.Empty, "*".Sanitize());
            Assert.AreEqual(string.Empty, "/**//*/*/".Sanitize());
            Assert.AreEqual("a", "/*a*/".Sanitize()); 

        public void ShouldRemoveSQLKeyWords()
            Assert.AreEqual("eXec", "eXec".Sanitize());
            Assert.AreEqual(string.Empty, ";eXec ".Sanitize());
            Assert.AreEqual(string.Empty, " eXec ".Sanitize());
            Assert.AreEqual("  ", "  eXec  ".Sanitize());


Anonymous said...

Thank you, that was really helpful.

binoj said...

Very useful

Hiblet said...

Very helpful, thanks. Note that you will need "using System.Text.RegularExpressions;" for Regex.

Salmon Riaz said...

not working for me

Anonymous said...

I love comments like that. "not working for me". It falls into that gray area where it's not important enough for you to give any details at all about what you tried... but somehow it was important enough for you to take the time to leave a comment.

Anonymous said...

This is stupid. It could remove legitimate occurrences of CREATE, BACKUP, etc. inside a user string. It also won't be "safe" in future versions when further SQL commands are added that are not in your list. I urge everybody not to use this ill-conceived hack, for the sake of your own system security.

Anonymous said...

@Anonymous "I urge everybody not to use this ill-conceived hack, for the sake of your own system security."

Intelligence is a requirement to be a software engineer. Despite the desperate pleas and warnings from your friend's and family, you thought you could call yourself one anyway. Good for you for ignoring providence because you're clearly stupid and have no conceived notion of security or separation of concerns.

Case and point, if you are allowing users to pass in commands directly to your database through user defined inputs like CREATE, BACKUP, DROP...YOU are the hack. ALL users are potentially malicious. If you allow users to pass in commands like that or even Adhoc a DBA should have the right by law to take you out back and shoot you. My DBA probably would, he's harsh, but you deserve it for your blasphemy.

This is a great simple starting point for sanitation checks of ALLOWED inputs when you have a requirement to expose that kind of functionality to the caller, so thank you for the post.

Anonymous said...

Not working in specific scenarios...

Tried with the expression...

;----delete from xxx;

The Sanitize call is resolving it to

;-delete from xxx;

Chris said...

Thanks for this great idea!

Do you think, it might be better to put the Replace("-{2,}")...-Statement at the end? As the other two removing-statements might result in single '-'-Characters being merged to new '--'-substrings.

chloven said...

Im having an error that says "'string' does not contain a definition for 'RegexReplace' and no extension method 'RegexReplace' acceptiong a first arument of type 'string' could be found."

the others are fine. thanks!