Check SQL statements within PHP for bad words such as DROP or DELETE

Question

Hi!

I would like to allow some admins to manually enter SQL statements in a textfield (to get statistic data etc.). On the database layer, I protected the data by creating a user which can only select but not update/delete etc.

I would like to add a second security by checking the inserted SQL for bad words such as DROP, DELETE or UPDATE. I figured out how to do that with preg_match but I would love to have a smart script which can distiguish between a DROP and something like WHERE name = 'DROP 1'

EDIT: I would like to log if an admin tries to uses such statements. That's why I need this second level security check. This is for clarification.

Does anyone know a code snipped which fits this example?

Thanks!!

Answer 1

New Response

Since you updated your question to clarify what you're looking for, I wanted to offer a different solution. Don't worry about what the user is submitting as a query, again, MySQL will spit back an error if they don't have sufficient privs to do something.

Capture that error with mysql_error(), and the error number with mysql_errno(). If the error message says "DROP command denied..." or the error number is 1142 then you know they tried being naughty. Looking for terms within this error is better than looking for terms in their query, as you rightly pointed out that their query can legitimately use the bad words.

Original Response

If your user can only SELECT, then they cannot DROP or DELETE. Looking for these words doesn't add any more security to the mix since the database will reject any queries that aren't explicitly allowed for that given user.

Answer 2

Why don't you just create the commands for the stats data and allow the admins to call those commands? Isn't that easier?

Not saying the admins will do harm intentionally but it could happen!

Answer 3

This is definitely the wrong place to put this level of security in. So long as you have set the database up so the only have SELECT privileges then they cannot change the data in any way. However is you give them any privileges that can change data then no amount of semantic checking on SQL statements will give you security.

Answer 4

I wouldn't use regular expressions for something as complex as SQL.

I'd start by looking at what SQL parsers are available for PHP, then use one of those to parse and analyze the SQL statements being used.

Try one of these:

http://pear.php.net/package/SQL_Parser

http://sourceforge.net/projects/osqlp/