Saturday, February 3, 2007

A regular expression filtering Non-Select SQL

For a project I'm currently working on, we needed a very simple filter class. The project has the ability to allow administrators to run queries against the database. We needed to make sure that only "SELECT" queries are made and not, by mistake, an UPDATE, INSERT, ALTER or something like that.

Combining several regular expressions I found on the internet, I created the below class in C#. I really don't think its bullet proof but it should work just fine (if not: they gonna call me for sure :-).

using System;
using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace SQLSelectFilter
{
public sealed class SQLSelectOnlyFilter
{
private SQLSelectOnlyFilter() {
}

bool bIsOK = false;
string sBadPattern = "";

public SQLSelectOnlyFilter(string SQLStatement)
{
const string c_RegEx = @"
(UPDATE\s[\w]+\sSET\s[\w\,\'\=]+)|
(INSERT\sINTO\s[\d\w]+[\s\w\d\)\(\,]*\sVALUES\s\([\d\w\'\,\)]+)|
(DELETE\sFROM\s[\d\w\'\=]+)|
(ALTER(\s|\+)\w+)|
(EXEC(\s|\+)+(s|x)p\w+)
";

Regex regex = new Regex(c_RegEx, RegexOptions.IgnoreCase | RegexOptions.CultureInvariant | RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);
Match mtch = regex.Match(SQLStatement);

if (mtch.Success == true)
{
bIsOK = false;
sBadPattern = mtch.ToString();
}
else
{
bIsOK = true;
}

}

public bool OK
{
get
{
return bIsOK;
}
}

public string BadPattern
{
get
{
return sBadPattern;
}
}
}
}

No comments:

Post a Comment