How to check or limit direct SQL input from a user?

Question

I am going to provide an SQL console such as data.stackexchange.com for developers on one of the sites that I have. The problem is that I cannot allow them to execute all type of SQL. So there will be some limitations. Here are some examples of restrictions.

• There is no COUNT() function in the request
• Each request must contain a LIMIT with a maximum value of 100
• The number of columns cannot be more than 5
• Some spreadsheet data is not available due to privacy.
• Only SELECT tags are allowed.

What am I doing right now?

I use regex to filter them. We also plan to call EXPLAIN or a similar query to determine its effect on db before execution.

Is there a better way than regex? How is this done on data.SE? Is there anything else I should worry about?

Update 1

These two rules can be applied by restricting user rights on the database server.

• Some spreadsheet data is not available due to privacy.
• Only SELECT tags are allowed.

So the only problem is SQL validation

Update 2

I know. If I write an SQL parser, this will be done. It will be like translating a JS interpreter, just remove eval() as your bad practice. Therefore, writing an SQL parser from scratch is not an option. here.

Answer 1

You can create a new grammar for yacc that contains a subset of SQL. Than you can check input SQL with this grammar. SQL lex yacc grammar