Regex for SQL string to allow SELECT queries on variable named tables only?

Question

The table names are variable, but what is certain is that SELECT only is allowed and certain tables are excluded (ie Users, Log). I'm making a reporting form where a user can just enter sql queries to make template reports.

SELECT 'field1' As 'foo', 'field2' as 'bar'.. 'fieldn' 
FROM 'table1',..'tablen'
JOIN ... ON ...
WHERE CONDITION

Although I'm thinking I can have the table names in a html select list of existing tables.

Also make a user reporter_appname@localhost with SELECT access only to all tables except Users and Log? In that case I won't need to bother with a regex check of the query?

(This would be in PHP)

(Ideally I just wanted a single textarea where the admin can just type their query, my report function would then take the output and present it nicely etc.)

your question is too vague. How do you perform the regexp? What language do you use for your form? Why don't you use a drop down menu for the allowed tables? — didierc
– didierc, Commented Mar 28, 2013 at 12:17

user1919238 · Accepted Answer · 2013-03-28 12:32:07Z

I suggest you re-think your design.

Identifying valid select statements (and excluding all other statements) is basically impossible without completely parsing SQL. A regex is not going to be up to the task.
Even if you allow only select statements, users could perform denial-of-service attacks on your database. It is very easy to create select statements that run forever (we've all done it). A malicious user could crash your site in a hurry. And even well-intentioned users might do this by accident.

It would be much better to give the users more limited options for creating reports. Let them select certain tables and columns from a list, and create the appropriate query for them.

There is probably free MySQL reporting software out there that could serve as a good starting point, though I don't have any experience with this myself.

ionutab · Accepted Answer · 2013-03-28 12:41:36Z

0

I think that you should rethink the design of your application. The Users and Log tables should be on one database and the tables with the data for the reports should be on another database.

If you have them all in one database already just create another database, link them and then create synonyms from one database to another only for the tables that the user can access via his queries.

The user will run his queries on the database you have just created and he will be limited to those tables that have synonyms on it.

I do not know if this would be the best option because your description of the case is relatively vague but based on the information I have this could be a solution.

answered Mar 28, 2013 at 12:41

ionutab

4445 silver badges22 bronze badges

Collectives™ on Stack Overflow

Regex for SQL string to allow SELECT queries on variable named tables only?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related