Regex to Detect SQL Injection

Regex To Detect Basic SQL Injections, But Not As A Means to Prevent SQL Injections

This link should give you the patterns to start with.

http://larrysteinle.com/2011/02/20/use-regular-expressions-to-detect-sql-code-injection/

Text Blocks

'(''|[^'])*'

SQL Statements

\b(ALTER|CREATE|DELETE|DROP|EXEC(UTE){0,1}|INSERT( +INTO){0,1}|MERGE|SELECT|UPDATE|UNION( +ALL){0,1})\b

RegEx to Detect SQL Injection

Don't do it. You're practically guaranteed to fail. Use PreparedStatement (or its equivalent) instead.

Regex for detecting SQL Injections in WinForms

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.

validating user input using regex to prevent an sql injection

I will begin by saying that you absolutely should be using PHP Prepared Statements here. Do not try to handle SQL injection yourself, and besides this problem was solved a long time ago.

Your pattern might block certain types of SQL injection. For example, let's say you had the following SQL query:

SELECT col1, col2 FROM some_table WHERE col = ?;

Your regex pattern would prevent someone from injecting 'value'; DELETE FROM some_table into the query. This is because your regex pattern doesn't allow for semicolon.

However, there are other types of injection attacks which don't involve chaining on additional (malicious) statement. Union attacks can also happen, and your current regex does allow for this. Consider injecting the following fragment:

'value' UNION ALL SELECT username, password FROM users

This would give the following full SQL query:

SELECT col1, col2 FROM some_table WHERE col = 'value'
UNION ALL
SELECT username, password FROM users;

While it would probably be unlikely that the attacker would be able to pull this off, it could happen, and if it did, the attacker could get every username and password from a totally different user table.

Use prepared statements and forget about handling this problem yourself.

Will this regex patterns catch all the needed SQL injections?

A blacklist is the wrong approach. There will always be things you haven't thought of, which the attacker will think of.

What programming language / database are you using? They all have methods of passing parameters to SQL statements. For example:

String userName = .... ; // from your GET or POST parameter
String sql = "SELECT id FROM user where user_name=?";
ResultSet rs = executeSql(sql, userName);

See http://en.wikipedia.org/wiki/SQL_injection#Parameterized_statements

SQL Injection Detection - Have compiled regexes... looking for test injections

You might want to check out the PHPIDS test suite, for example this one

---

Related Topics