Will this regex patterns catch all the needed SQL injections?

Question

We changed our firewall rules (REGEX) to the following:

Name
 Type
 Context
 Severity
 Pattern

CS:select_into
 signature
 http-url
 critical
 .*\[select\]\s+.*\[into\].*

CS:select_from
 signature
 http-url
 critical
 .*\[select\]\s+.*\[from\].*

CS:insert_into
 signature
 http-url
 critical
 .*\[insert\]\s+.*\[into\].*

CS:drop_database
 signature
 http-url
 critical
 .*\[drop\]\s+.*\[database\].*

CS:drop_table
 signature
 http-url
 critical
 .*\[drop\]\s+.*\[table\].*

CS:delete_from
 signature
 http-url
 critical
 .*\[delete\]\s+.*\[from\].*

CS:drop_view
 signature
 http-url
 critical
 .*\[drop\]\s+.*\[view\].*

CS:exec
 signature
 http-url
 critical
 .*\[exec\].*(%28|\().*(%29|\)).*

CS:update_set
 signature
 http-url
 critical
 .*\[update\](%20|\+)(%20|\+|.)*\[set\].*

Will this block all SQL injection attempts? For example, is it possible to drop a view using multiple spaces?

Answer 1

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

Answer 2

Trying to prevent sql injection by filtering out certain words is not going to work - there will always be something you miss and will be very time consuming to try and find everything to cover.

You should look at things like how you query the database - if you're building SQL on the fly and concatenating values from the client directly into the statement, then that's going to be an important area to focus on - switch to using parameterised SQL / stored procedures. Stored procedures will also give you an added layer of security as you can grant permissions to execute those without giving direct permissions on the underlying tables.

Answer 3

You shouldn't use regex for input filtering.

You should filter your input one by one before you give them to the sql server.

If you insert a string (or anything wich is between apostrophes in the sql statement) you should use your sql server's escape function, wich will prevent any attacks there.

If your data is some type of number (integer or float) then you should check if the data is really a number (you can't do sql injection without letters). The best way to do this depends on the programming language you use, but mostly type checks, or forced typecastings.

You should never insert any untrusted(anything is from a user is untrusted) string-like data into an sql statemen, where you can't place apostrophes around it, like for a table or a column name.

Answer 4

You should make sure that any SQL is only executed via a limited privileges account that has no permissions except those the app explicitly needs, rather than attempt to catch every possible permutation of hostile SQL.