MySQL query validation in PHP

Question

I have a form where I post a MySQL query. Which is processed further with PHP. My problem is, if the user types 'DROP DATABASE mydb', this will be a big problem. So, I would like to know if there is a way by which I can validate before posting the query so that the user does not harm any other database. We can use regular expressions for sure. But is there any other better way to do it?

Thank you

"We can use regular expressions for sure" --- you're overestimating regular expressions — zerkms
– zerkms, Commented Jun 11, 2013 at 10:35

oezi · Accepted Answer · 2013-06-11 10:45:44Z

3

don't parse the query with php (or regular expressions... or just anything else than the database itself) to see if it does harmful things, as this might become really complicated (you can't just filter out "bad words"²).
it would be better to create a database-user for those user-generated querys and only grant privileges for things your users should be able to do (probably only SELECTs, no UPDATEs, DELETEs or table-structure changes).

²for example: SELECT 'DROP DATABASE is a bad thing' FROM mytable; would be absolutely ok and doesn't harm anything. if you just look for "DROP DATABASE" in your querys, you'll just annoy a user in this case...

edited Jun 11, 2013 at 10:45

answered Jun 11, 2013 at 10:34

oezi

51.9k10 gold badges102 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rishi Over a year ago

What if I want 1 user to do anything to 1 database and not to any other database? So that, the user can give query 'drop database mydb' but not 'drop database anyotherdb'.

oezi Over a year ago

you can grant privileges on a per-database or even on a per-table basis. just take a look at the documentation: dev.mysql.com/doc/refman/5.1/en/grant.html

Sarwar Erfan · Accepted Answer · 2013-06-11 10:36:23Z

1

You are allowing users to directly enter raw query to execute. So, this does not fall into SQL injection.

What you need to consider here is mysql user permissions. For that specific database user (create a separate user for this purpose) and do not grant him permissions like drop, delete etc. Give only the permissions you are willing to allow.

http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

https://kb.mediatemple.net/questions/788/HOWTO%3A+GRANT+privileges+in+MySQL#dv

https://www.digitalocean.com/community/articles/how-to-create-a-new-user-and-grant-permissions-in-mysql

answered Jun 11, 2013 at 10:36

Sarwar Erfan

18.1k5 gold badges48 silver badges57 bronze badges

Collectives™ on Stack Overflow

MySQL query validation in PHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related