Regex for detecting SQL injection in WinForms

Question

i uwant to cach input, which seems to be like an SQL injection. So I wrote a 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 it seems that I made some mistakes because my code does not detect injections. What am I doing wrong? I think there is something wrong with defining regex expressions?

Answer 1

Do not try to do this with RegEx - there are too many of them. See this classic SO RegEx parsing answer - it's HTML specific, but still applicable.

You have to use Parameters , they are in BCL and have built-in anti-SQL injection measures.

Update: (following comments)

If you really need to parse SQL, do not use RegEx for the reasons indicated in the related article. RegEx is not a parser and should not be used as one.

Use SQL parser - this should help in disinfection. Here alone , here yet .

You can continue your scientific research with them.

Answer 2

Do not use string parsing or regular expressions to handle this kind of thing. The SQL syntax is too complex for reliable parsing with regular expressions.

Instead, use parameterized queries with placeholders and avoid string concatenation. This will defeat the 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 your "not very experienced programmers", you better try to find out when they do embedded sql in their code. It should not be too difficult to write an FxCop rule to define it. If you enable it as part of the message assembly process, or if you have a command system, set a rule to fail the assembly, they will receive it soon.

Answer 4

The problem with SQL injection is that user input is used as part of an SQL statement. Using prepared statements, you can force user input as parameter content (and not as part of an SQL command). Query parameters help to avoid this risk by separating literal values ​​from SQL syntax.

Most client APIs (including .NET) support query parameterization. This allows you to embed user input as parameters. Parameters are placeholders for a user-entered value that is replaced at run time. Thus, the user cannot enter SQL code, since the entire user record is considered as a value for the parameter, and not as a string added to the query.

parameterization is the best solution for SQL injection attacks.