How to restrict arbitrary mysql query to only SELECT command?

M

MaxUp2012-10-23 11:01:34

PHP

MaxUp, 2012-10-23 11:01:34

The task is to give users the opportunity to write a full-fledged mysql query for a selection, but at the same time limit any modification commands.
What is the best way to do this? Maybe someone met php libraries that would parse the query and restrict only SELECT?

Thank you!

update: delimitation by accesses, unfortunately, is not possible in this case.

Reply

Answer the question

In order to leave comments, you need to log in

8 answer(s)

V

Vampiro, 2012-10-23
@Vampiro

IMHO grant

J

jonie, 2012-10-23
@jonie

make a grammar parser and run queries through it option?
Ready-made grammars (even for mysql) are available, for example, here www.antlr.org/grammar/list . Yes, and you can write it yourself based on the requirements ... Regexps here, IMHO, are a thankless task ...

S

script88, 2012-10-23
@script88

Why not create a user in mysql and give him privileges only on SELECT ?!

T

TimID, 2012-10-23
@TimID

It won't help.
There is, for example, a " SELECT source INTO destination " query that creates a new table or inserts data into an existing one.

V

vsespb, 2012-10-23
@vsespb

It also happens that several requests are separated by commas - “select ...; delete”
But in principle, IMHO, it is quite possible to write code that will validate the request.

V

vsespb, 2012-10-23
@vsespb

Hm. in general
dev.mysql.com/doc/refman/5.0/en/ansi-diff-select-into-table.html
SELECT source INTO destination is not supported.
IMHO check with regexp that the request starts as "\A*select", configure the API to not allow multiple statements through ';'
Although here you need to do more research and make sure that it is safe.

S

Skull, 2012-10-23
@Skull

Why not write a regular expression that, when INSERT, UPDATE, DELETE, TRUNCATE, ALTER, INTO, SET, VALUES, will swear?

F

FloppyFormator, 2012-10-23
@FloppyFormator

Grant for selection, of course, is the most adequate solution.
As a forced measure, you can try to analyze the entered query and, based on the result, pass or not pass it to the DBMS.
But I would suggest the reverse procedure: construct a query from the available data entered in any convenient way, including parsing the original query. The main thing is to transfer to the DBMS not data entered from outside, but a specially generated request, in which there are guaranteed to be no “wrong” constructions. And if there are any in the original request, throw an error.