[MDEV-4679] PARSER - SANDBOX - parse queries without executing and report what happened Created: 2013-06-19  Updated: 2015-08-31  Resolved: 2014-01-15

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: None

Type: Task Priority: Minor
Reporter: roberto spadim Assignee: Unassigned
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-4641 API for running SQL from inside the s... Open

 Description   

Could be nice if we could parse queries without executing
Checking if they have errors and what king of queries they are (DELETE, TRUNCATE, SELECT, UPDATE, SET, etc..)

Example:

1st query) SET sandbox_on=1;
2nd query) SELECT * FROM A WHERE B=0;
SELECT * FROM A WHERE B="";
SELEC * F A W B;
3rd query) CREATE TABLE a(B INT NOT NULL)
4rd query) CREATE TABLE b(B INT NOT NULL)
5th query) SET sandbox_on=0;
6th query) SHOW SANDBOX RESULTS

RESULTS:

ID QUERY TYPE TABLES ERROR MESSAGE
1 SELECT * FROM A WHERE B=0 SELECT A NULL
1 SELECT * FROM A WHERE B="" SELECT A NULL
1 SELEC * F A W B ERROR NULL you have a error near blablabla
2 CREATE TABLE a(B INT NOT NULL) CREATE a table a alread exists
3 CREATE TABLE b(B INT NOT NULL) CREATE b NULL

the create table could be used creating tables as TEMPORARY just to allow next queries to be executed without errors
after sandbox_on=0 all temporary tables created in sandbox must be droped (automatically), all locks must be reverted too (lock tables)

binary log is turned off in this case
lock aren't executed (but maybe they could show errors of next queries?)

it's very nice to check if we have a sql injection or test a file of queries before executing them, since we can only do it with a running database and changing tables

in a very big file is very difficult if we have a UPDATE or something that could be executed without seeking file query per query

it's nice to build query tools like in JIRA when we don't need to create a parser, just allow user to write in WHERE part of query



 Comments   
Comment by Jean Weisbuch [ 2013-06-19 ]

It looks like the PROFILE function mixed with some kind of transactionnal EXPLAIN.
Just adding a "query status" and an "error message" column (and maybe an "implicated tables" column) to the SHOW PROFILES command would be simpler/clearer than adding a totally new option/function.

ps: EXPLAIN in 10.x is supposed to support CREATE/UPDATE/DELETE, maybe it could suffice to your needs.

Comment by roberto spadim [ 2013-06-19 ]

that's the point of this 'sandbox' and not a profile

Note: often the server does not need to execute SQL, only to parse it, so these classes should allow that too.

since we will have it, why not expose to sql interface

Comment by roberto spadim [ 2013-06-19 ]

maybe a UDF?

Comment by roberto spadim [ 2015-08-31 ]

hi guys
maybe we could close this MDEV:
https://github.com/adrpar/mysql_validateSQL/tree/master/src

Generated at Thu Feb 08 06:58:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.