Regular Expression to Sanitize Data Against SQL injection

[Bohica69]

A week ago, we noticed in our error logs that some one was using SQL injection to walk our database tables - they didn't get far, as our operations group has alerts that notify them when things of this nature happen, and they cut the attack off at the fire wall about 5 minutes after it started - the attacker got some table names and that was it. The ops group also fixed the problem by changing the web.config file (it was set to show errors to remote users instead of sending them to the nice error screen oops).

Our development group is going to also going to impliment some sanity checks on querystring values at critical points in the code - we can't rewrite the entire system to use stored proceedures and paramaterized queries at this time - as we work on old areas of the code however, we are making these changes and all new code is coded using paramterized queries.

So my question is this, I need a Regular Expression that will allow any valid character 0-9 A-Z (and lower case) _ (Underscore) and . (period) but NOT allow the words SELECT, DELETE, UPDATE.

I've found this regular expression that would get a match on SELECT, DELETE, UPDATE and SCRIPT and some others

(script)|(<)|(>)|(%3c)|(%3e)|(SELECT) |(UPDATE) |(INSERT) |(DELETE)|(GRANT) |(REVOKE)|(UNION)|(<)|(>)

What I would like to do is combine that so it says take any character that's valid and the characters don't match SELECT etc.... Is this even possible? If I have to I can do it in two steps.. but one would be prefereable.

Thanks in advance.

Rick

 

[mikeblas]

Your use of regular expressions has got some problems--you'll need to make them case insensitive, for one. And there's always a few more keywords you want to protect against. Why aren't you searching for DROP or TRUNCATE, for example?

The correct fix is to use parameter binding. If you're not doing that, you're just putting a band-aid on the problem.

 

[Bohica69]

Yes, I am going to make it case insenitive (and thanks for the point about DROP and TRUNCATE)

As for it being a band-aid, we know it is however, we don't have the time to go fix every single query. However, we can do the sanitizing in the Application_PreRequestHandlerExecute in the global.asax and if the sanitizing fails then we will throw an error and not allow the page request to go any farther. Will this not work?

 

[mikeblas]

It'll only be as good as your regular expression. My point wasn't about DROP or TRUNCATE; my point was about all the other keywords you're not filtering. Once you start filtering for DROP, what will you do if a customer named "John Drop" wants to update his records?

If you don't have time to do it right, how will you find the time to restore your data after you're hacked again? How will you find the time to do it over?

 

[svet-am]

If you don't have time to do it right, how will you find the time to restore your data after you're hacked again? How will you find the time to do it over?

QFT! It's very easy to sit back and say 'we're so busy with Item X that we dont have time to do it properly right now. But, one day when rainbows shine and birds sing, we'll have time to fix it all right.'

The problem is that, as developers, things NEVER get slack or easier. There will always be another problem just around the bend that will forever prevent you from going back and fixing bugging/defective code. The best option, espescially with something as severe as SQL injection, is to be honest with your bosses and let them know that you REALLY need to spend some dedicated time to fix this properly.

 

[doh]

You're going about the solution in the wrong way. First examine the ways in which a user can escape out of a query and block that. Escaping double quotes will go a long ways.

 

[mikeblas]

Escapes are only part of it; if I can enter non-numeric data, then I can do whatever I want, even without quotes.

SELECT * FROM Foo WHERE Dept = 34

can become

SELECT * FROM Foo WHERE Dept = 34; DROP DATABASE OnlineOrders;

Parameter binding is the right way to prevent SQL Injection attacks.

 

[maw]

also, it helps if you restrict what the credentials used to access the database can do. for example, if your connection string has username="public" to connect to the DB, then in your database, make sure public can only do what is absolutely necessary and nothing else (e.g. can SELECT but not UPDATE, DELETE, DROP, CREATE, etc...)

 

[BBowermaster]

Here's a function I found and modified a little to sanitize user input:

//Borrowed from [url]http://www.askbee.net/articles/php/SQL_Injection/sql_injection.html[/url], added htmlentities to function. 
function sql_quote($value)
{
	$value = htmlentities($value);
    if( get_magic_quotes_gpc() )
    {
          $value = stripslashes($value);
    }
    //check if this function exists
    if( function_exists("mysql_real_escape_string") )
    {
          $value = mysql_real_escape_string($value);
    }
    //for PHP version < 4.3.0 use addslashes
    else
    {
          $value = addslashes($value);
    }
    return $value;
}

This will prevent them from escaping out of any of your code with single quotes, or any other tricks, such as the ', '1'='1' and other methods of passing validation checks.

 

[mikeblas]

This will prevent them from escaping out of any of your code with single quotes, or any other tricks, such as the ', '1'='1' and other methods of passing validation checks.

What's it do for "OR 1=1", without quotes? It's incomplete; it only partially works on some versions of one database back-end. The right way to do this is with parameter binding; otherwise, you're just asking for trouble.

You can save lots of trouble by not asking for any.

 

[BBowermaster]

What's it do for "OR 1=1", without quotes? It's incomplete; it only partially works on some versions of one database back-end. The right way to do this is with parameter binding; otherwise, you're just asking for trouble.

You can save lots of trouble by not asking for any.

it escapes single and double quotes. So ' OR '1'='1' becomes \' OR \'1\'=\'1\'. It gets read as a part of the string the injection was trying to escape, instead of as an escape from the string and an additional SQL command. I can see what your saying, though, if the original statement doesn't use quotes, like SELECT password WHERE user_id = 1, then there is no quotes to escape, and it would just come out as SELECT password WHERE user_id = 1 OR 1=1. Of course this example wouldn't do anything, but you get my point.

If you care to post an easy to understand link to parameter binding, I'd appreciate it.

 

[mikeblas]

Parameter binding is language specific, so the details will depend on which client language you're using. It simply means you tell the interface between your application and the database about the parameters directly, and never have to concatenate strings or escape special characters. Ever, at all.

You might code "SELECT Salary FROM Employees WHERE EmployeeID = @1", for example. You prepare this statement, which tells the database you're thinking about executing it. You further tell the database that the parameter for @1 comes from a variable named "Fred", and then you execute the statement. At execution time, the database will get the value from the variable named "Fred" and use it directly.

The syntax for the parameter and the specific calls vary depending on the language.

 

[maw]

Parameter binding is language specific, so the details will depend on which client language you're using. It simply means you tell the interface between your application and the database about the parameters directly, and never have to concatenate strings or escape special characters. Ever, at all.

You might code "SELECT Salary FROM Employees WHERE EmployeeID = @1", for example. You prepare this statement, which tells the database you're thinking about executing it. You further tell the database that the parameter for @1 comes from a variable named "Fred", and then you execute the statement. At execution time, the database will get the value from the variable named "Fred" and use it directly.

The syntax for the parameter and the specific calls vary depending on the language.

thanks so much for explaining this. i'm going to give it a try as it seems to offer a much better alternative than trying to remember to escape strings and checking for magic_quotes.

 

[mikeblas]

Glad to hear it. It's actually easier than screwing around with filtering and escaping (and then getting it wrong anyway, then having to explain to your customers why their personal data is all over the Internet).

 

[doh]

Parameter binding is language specific, so the details will depend on which client language you're using. It simply means you tell the interface between your application and the database about the parameters directly, and never have to concatenate strings or escape special characters. Ever, at all.

You might code "SELECT Salary FROM Employees WHERE EmployeeID = @1", for example. You prepare this statement, which tells the database you're thinking about executing it. You further tell the database that the parameter for @1 comes from a variable named "Fred", and then you execute the statement. At execution time, the database will get the value from the variable named "Fred" and use it directly.

The syntax for the parameter and the specific calls vary depending on the language.

In Rails one can do:

Model.find(:all, :conditions => [ "name = ?", somevariable ])

Or

Model.find(:all, :conditions => [ "name = :name", { :name => somevariable } ] )

Both of these will intelligently escape.

 

[mikeblas]

Both of these will intelligently escape.

Based on what rules, for what database back end? How do they react to strings that include commands to change the quoting rules, or quoting characters? What "intelliegence" do the routines exhibit?

 

[buji]

Just out of curiosity, how would one do parameter binding using PHP & MySQL? I have done this using ASP & SQL Server, but I never figured out how to do it for PHP & MySQL.

 

[maw]

Just out of curiosity, how would one do parameter binding using PHP & MySQL? I have done this using ASP & SQL Server, but I never figured out how to do it for PHP & MySQL.

here's what works for me. Note: I JUST started playing with this so YMMV

$iconn = new mysqli('server,'user','gpassword','dbname) OR DIE("Failed to connect");
$SQLstr = "SELECT col1,col2,col3 FROM tbl WHERE ID = ?"; //the query with ID field as the parameter
 
 $query = $iconn->prepare($SQLstr); //prepare the query
 $query->bind_param('i',$id); //define the type of value to use for the paramenter (integer)
 $id = $_POST['id']; //get the value for the parameter
 $query->execute(); //execute the query (i.e. run the select query)
 
 
 $query->bind_result($col1,$col2,$col3); //bind the results to these variables
 
 while ($query->fetch()) //fetch each row from the query
 {
 	echo("<p>" . $col1 . $col2 . $col3 . "</p>\n"); //display the results
 }

//clean up
$query->close();
$iconn->close();

 

[doh]

Based on what rules, for what database back end? How do they react to strings that include commands to change the quoting rules, or quoting characters? What "intelliegence" do the routines exhibit?

Give http://manuals.rubyonrails.com/read/chapter/43#page123 a read.

 

[mikeblas]

Give http://manuals.rubyonrails.com/read/chapter/43#page123 a read.

I don't see an answer there. This is as close as I get:

If the argument for find_all is an array instead of a string, ActiveRecord will insert the elements 2..n of the array for the ”?” placeholders in the element 1, add quotation marks if the elements are strings, and quote all characters that have a special meaning for the database adapter used by the Email model.

It doesn't say what quotes or escapes are used, or what characters are considered "meta characters". Thing is, these vary from database to database; and can vary among configurations of the same database, or even from statement to statement. "Meta characters" aren't all you need to a binding go awry, anyway. Quotes and ticks and backticks and backslashes are terrible, but percent signs and underlines also can be harmful.

Which characters do those functions handle, and how are they escaped?

 

[doh]

Which characters do those functions handle, and how are they escaped?

You would have to check the source cos I don't know offhand.

 

[buji]

here's what works for me. Note: I JUST started playing with this so YMMV

...

Thanks! I'll have to give it a try.

 

[maw]

Thanks! I'll have to give it a try.

oh, forgot to mention, you need to have PHP5 and enable the mysqli extension in php.ini

 

[MadJuggla9]

mysql_real_escape_string() -- Escapes special characters in a string for use in an SQL statement

Description
string mysql_real_escape_string ( string unescaped_string [, resource link_identifier] )

Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.


http://us3.php.net/manual/en/function.mysql-real-escape-string.php