CODEWITHSUNDEEP
mysqlsqlregex
trafalgar
trafalgar

Reputation: 736

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

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.)

Upvotes: 1

Views: 882

Answers (2)

ionutab
ionutab

Reputation: 444

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.

Upvotes: 0

user1919238
user1919238

Reputation:

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.

Upvotes: 2

Related Questions