Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4679

PARSER - SANDBOX - parse queries without executing and report what happened

Details

    • Task
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Won't Fix
    • None
    • None
    • None

    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

      Attachments

        Issue Links

          Activity

            jb-boin Jean Weisbuch added a comment -

            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.

            jb-boin Jean Weisbuch added a comment - 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.

            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

            rspadim roberto spadim added a comment - 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

            maybe a UDF?

            rspadim roberto spadim added a comment - maybe a UDF?

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

            rspadim roberto spadim added a comment - hi guys maybe we could close this MDEV: https://github.com/adrpar/mysql_validateSQL/tree/master/src

            People

              Unassigned Unassigned
              rspadim roberto spadim
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.