-1

I'm having trouble with a regular expression.

I'm trying to use preg_match on a query string. I want it to exclude any sequence of drop or truncate regardless of the case (?i) that is between ' or ` and has any number of characters between the sequence and the two signs, but I want to catch any of those two words that is NOT between the two signs.

If you haven't guessed it yet, I want to detect whether or not an input is trying to DROP | TRUNCATE anything, but there is no easy way of doing that without excluding those words from non malicious input.

e.g.

I want this not to trigger: 

SELECT * FROM `drop`
SELECT * FROM 'drop_truncate' -- drop drop

but this to trigger:

DROP TABLE `users`

EDIT: So my question was about regular expressions and I received answers on database security, no doubt there was something about the question that made you think that, sorry for misleading! No, I don't care about database security here for that I already handled, all I wanted here was to check for any INTENT of using either drop or truncate and log it somewhere. sigh

Andrei Cristian Prodan
  • 1,114
  • 4
  • 17
  • 34
  • 5
    The real solution is to NOT take user input *verbatim* and run it. You aren't looking for a regex, you're looking for a way to take user input and construct a query that won't do something dangerous (there are more than just DROP and TRUNCATE to worry about) - so why ask this question? Just ask the real question. – Wesley Murch May 15 '12 at 16:01
  • 1
    you are feeding user-submitted sql to your db? – AD7six May 15 '12 at 16:01
  • If a user should not be able to do something, then the database should prevent it using proper permissions – Brian May 15 '12 at 16:10

2 Answers2

3

You're handling the security of your database entirely in the wrong manner.

This statement here but there is no easy way of doing that is false. You are just looking to 'secure' this input in an extreamly narrow mindset of tunnel vision.

Do NOT allow users to mangle your query by inputting user defined SQL!!

Please research prepared statements at the utter least. PHP.net has a great starter on explaining its PDO library. Though I highly **highly* advise against "use preg_match to prevent malicious query intent", see AD7six's answer if you really think that is your best course of action. (HINT: Its not).

Community
  • 1
  • 1
PenguinCoder
  • 4,335
  • 1
  • 26
  • 37
1

It would appear that you're going to run application queries and user generated queries under the same login. This forces you to sanity check the user defined queries.

Trust the numerous answers and comments here. You will fail. Whatever clever sanity check you come up with, some-one will find a way of sneaking something dangerous past it.

So, assume that they do get something past your sanity checks. How can you protect yourself?
- Restrict the permissions in the database itself.


Instead of running all queries through the same login, have a separate login dedicated to user generated queries.

Deny CREATE and DROP and EXECUTE other permissions to that login. Deny SELECT permission to data you don't want them to SELECT. Use whatever your DB allows you to do to control what the new login can and/or can't do.

By having at least two different logins, you get...
- The application's login can still do everything it needs to
- The user_generated_query login can't do anything you don't want it to

MatBailie
  • 83,401
  • 18
  • 103
  • 137