Allow SQL queries but not anything harmful

[Drakkon]

Is there anyway to let a user submit full SELECT statements to a mysql database without allowing anything harmful to come up (such as lists of all table names, changing passwords, etc)?
I've thought of checking for more than one semicolon, only allowing SELECT (not update, create, etc). not allowing more than 1 WHERE, not allowing more than 1 FROM. Is there something out there that will kinda filter an SQL query for this sorta stuff and make it safe?

 

[Woosta]

Use prepared statements?

http://dev.mysql.com/tech-reso...epared-statements.html

 

[Markbnj]

You can also be creative at the interface, for example by allowing the user to select choices in a series of lists rather than accepting typed queries.

 

[Drakkon]

Originally posted by: Markbnj
You can also be creative at the interface, for example by allowing the user to select choices in a series of lists rather than accepting typed queries.

The interface is just a flash program that is used to show the database - its a freshman level training course for DBA's. The intention is to teach them how to type in simple SQL statements and see the results they get back (in a tabular format). So they need to type the FULL SQL query then have it submit to the database.

 

[imported_Dhaval00]

I am not sure about how permissions/roles are setup in MySQL, but SQL Server allows object level permissions for everything and has built in user roles that allow only SELECTs - an attempt to insert/drop/update will raise an exception. Can't you do something similar?

 

[Drakkon]

Originally posted by: Dhaval00
I am not sure about how permissions/roles are setup in MySQL, but SQL Server allows object level permissions for everything and has built in user roles that allow only SELECTs - an attempt to insert/drop/update will raise an exception. Can't you do something similar?

I assume most injection attacks are INSERT/UPDATE/CREATE driven so that might work...thanks

 

[imported_Dhaval00]

Originally posted by: Drakkon

Originally posted by: Dhaval00
I am not sure about how permissions/roles are setup in MySQL, but SQL Server allows object level permissions for everything and has built in user roles that allow only SELECTs - an attempt to insert/drop/update will raise an exception. Can't you do something similar?

I assume most injection attacks are INSERT/UPDATE/CREATE driven so that might work...thanks

Were you being sarcastic or really thanking? LOL. So much for e-communications.

 

[Drakkon]

haha - yes i was honestly thanking - seemed like a good solution to my problem as mysql does have roles. I just have to make sure the person who sets up the DB knows to restrict the account accessing this one page i guess.