Regex for detecting SQL Injections in WinForms

Question

Hello, i uwant to cach input, which seems to be like SQL injection. So I wrote the method:

        public static bool IsInjection(string inputText)
    {


        bool isInj = false;


        string regexForTypicalInj = @"/\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix";
        Regex reT = new Regex(regexForTypicalInj);
        if (reT.IsMatch(inputText))
            isInj = true;


        string regexForUnion = @"/((\%27)|(\'))union/ix";
        Regex reUn = new Regex(regexForUnion);
        if (reUn.IsMatch(inputText))
            isInj = true;



        string regexForSelect = @"/((\%27)|(\'))select/ix";
        Regex reS = new Regex(regexForSelect);
        if (reS.IsMatch(inputText))
            isInj = true;

        string regexForInsert = @"/((\%27)|(\'))insert/ix";
        Regex reI = new Regex(regexForInsert);
        if (reI.IsMatch(inputText))
            isInj = true;

        string regexForUpdate = @"/((\%27)|(\'))update/ix";
        Regex reU = new Regex(regexForUpdate);
        if (reU.IsMatch(inputText))
            isInj = true;

        string regexForDelete = @"/((\%27)|(\'))delete/ix";
        Regex reDel = new Regex(regexForDelete);
        if (reDel.IsMatch(inputText))
            isInj = true;

        string regexForDrop = @"/((\%27)|(\'))drop/ix";
        Regex reDr = new Regex(regexForDrop);
        if (reDr.IsMatch(inputText))
            isInj = true;

        string regexForAlter = @"/((\%27)|(\'))alter/ix";
        Regex reA = new Regex(regexForAlter);
        if (reA.IsMatch(inputText))
            isInj = true;

        string regexForCreate = @"/((\%27)|(\'))create/ix";
        Regex reC = new Regex(regexForCreate);
        if (reC.IsMatch(inputText))
            isInj = true;

        return isInj;

    }

But seems I have done some mistakes, becouse my code do not detects injections. What i do wrong? I guess theres something wrong in defining Regex expressions?

Answer 1

Don't try to do this with RegEx - there are too many ways around it. See this classic SO answer about parsing with RegEx - it is specific to HTML, but still applies.

You should use Parameters, these are in the BCL and have anti SQL injection measures built in.

Update: (following comments)

If you really must parse the SQL, do not use RegEx for the reasons outlined in the linked article. RegEx is not a parser and should not be used as one.

Use a SQL parser - this should help with sanitizing attempts. Here is one, here another.

You can continue your scientific investigation with these.

Answer 2

Do not use string parsing or regular expressions to handle this sort of thing. SQL syntax is too complicated to reliably parse with regular expressions.

Instead use parametrized queries with placeholders and avoid string concatenation altogether. This will defeat SQL injection at its root.

var command = new SqlCommand(connection);
command.Text = "INSERT INTO foo (a, b, c) VALUES (@a, @b, @c)";
command.Parameters.AddWithValue("a", "this is invulnerable");
command.Parameters.AddWithValue("b", "to any sort of SQL injection");
command.Parameters.AddWithValue("c", "--'; DROP DATABASE");
command.ExecuteNonQuery();

Answer 3

If you really want to help out your "not so experienced programmers", you'd be better off trying to detect when they are doing inline sql in their code. It shouldn't be too difficult to write an FxCop rule to spot it. If you include it as part of a post build process, or if you have team system, set the rule to fail the build, they'll soon get the hang of it.