check syntax of query

Discussion in 'Java' started by pet0etie, Nov 29, 2006.

  1. pet0etie

    pet0etie Guest

    hello,

    i'm facing this problem :

    suppose i ha a single query that is entered by a user
    before saving the query i want to check if its syntax is good

    is there an easy way to do this ?

    the easiest way would be something like :

    String query = "select * from table1 where field1 > 0";
    try {
    Class.forName(DB_CLASS);
    Connection conn =
    DriverManager.getConnection(DB_DRIVER,DB_USER,DB_PASSW);
    Statement stmt = conn.createStatement();
    ResultSet rslt = stmt.executeQuery(query);
    rslt.close();
    stmt.close();
    conn.close();
    } catch ( Exception e ) {
    //error in query
    }

    but the problem is that the executeQuery() could take very long (with more
    complex query's)
    is there an alternative solution for this ?

    thanks in advance,
    pet0etie
     
    pet0etie, Nov 29, 2006
    #1
    1. Advertising

  2. pet0etie

    Chris Smith Guest

    pet0etie <> wrote:
    > suppose i ha a single query that is entered by a user
    > before saving the query i want to check if its syntax is good


    There is no SQL syntax checker built into JDBC, so you can't check
    there. However, there are third-party SQL grammars for ANTLR and
    JavaCC. You could just run it against a parser generated by one of
    those two products. Note that this would only check syntax, and not
    whether the referenced tables and columns actually exist, so it would
    accept the query in your example below.

    > String query = "select * from table1 where field1 > 0";


    --
    Chris Smith
     
    Chris Smith, Nov 29, 2006
    #2
    1. Advertising

  3. pet0etie

    Oliver Wong Guest

    "Chris Smith" <> wrote in message
    news:...
    > pet0etie <> wrote:
    >> suppose i ha a single query that is entered by a user
    >> before saving the query i want to check if its syntax is good

    >
    > There is no SQL syntax checker built into JDBC, so you can't check
    > there. However, there are third-party SQL grammars for ANTLR and
    > JavaCC. You could just run it against a parser generated by one of
    > those two products. Note that this would only check syntax, and not
    > whether the referenced tables and columns actually exist, so it would
    > accept the query in your example below.


    I've also heard (but don't know SQL well enough to confirm for myself)
    that the SQL language is Turing Complete. Basically what this means is that
    it's mathematically impossible to be 100% sure that the queries constructed
    are "good". You can catch many mistakes, but not all of them.

    - Oliver
     
    Oliver Wong, Nov 29, 2006
    #3
  4. "Oliver Wong" <> writes:

    > "Chris Smith" <> wrote in message
    > news:...
    >> pet0etie <> wrote:
    >>> suppose i ha a single query that is entered by a user
    >>> before saving the query i want to check if its syntax is good

    >>
    >> There is no SQL syntax checker built into JDBC, so you can't check
    >> there. However, there are third-party SQL grammars for ANTLR and
    >> JavaCC. You could just run it against a parser generated by one of
    >> those two products. Note that this would only check syntax, and not
    >> whether the referenced tables and columns actually exist, so it would
    >> accept the query in your example below.

    >
    > I've also heard (but don't know SQL well enough to confirm for myself)
    > that the SQL language is Turing Complete. Basically what this means is that
    > it's mathematically impossible to be 100% sure that the queries constructed
    > are "good". You can catch many mistakes, but not all of them.



    I don't believe those two statements are the same. A
    language being Turing complete doesn't imply that you
    can't validate the syntax of the language, it just means
    that you can't provide an algorithm to determine whether
    or not any given program will halt. (And all that implies.)


    Anyway -- that wouldn't be my biggest concern on a project
    like this. The thoughts that would keep me up at night go
    something like

    'DELETE FROM unshipped_orders' is syntactically correct.

    --
    Mark Jeffcoat
    Austin, TX
     
    Mark Jeffcoat, Nov 29, 2006
    #4
  5. pet0etie

    Oliver Wong Guest

    "Mark Jeffcoat" <> wrote in message
    news:...
    > "Oliver Wong" <> writes:
    >
    >> "Chris Smith" <> wrote in message
    >> news:...
    >>> pet0etie <> wrote:
    >>>> suppose i ha a single query that is entered by a user
    >>>> before saving the query i want to check if its syntax is good
    >>>
    >>> There is no SQL syntax checker built into JDBC, so you can't check
    >>> there. However, there are third-party SQL grammars for ANTLR and
    >>> JavaCC. You could just run it against a parser generated by one of
    >>> those two products. Note that this would only check syntax, and not
    >>> whether the referenced tables and columns actually exist, so it would
    >>> accept the query in your example below.

    >>
    >> I've also heard (but don't know SQL well enough to confirm for
    >> myself)
    >> that the SQL language is Turing Complete. Basically what this means is
    >> that
    >> it's mathematically impossible to be 100% sure that the queries
    >> constructed
    >> are "good". You can catch many mistakes, but not all of them.

    >
    >
    > I don't believe those two statements are the same. A
    > language being Turing complete doesn't imply that you
    > can't validate the syntax of the language, it just means
    > that you can't provide an algorithm to determine whether
    > or not any given program will halt. (And all that implies.)


    I misread the problem statement as checking whether the "query is good",
    rather than the "syntax is good". Syntax checking can be done with parsers,
    yes.

    >
    > Anyway -- that wouldn't be my biggest concern on a project
    > like this. The thoughts that would keep me up at night go
    > something like
    >
    > 'DELETE FROM unshipped_orders' is syntactically correct.


    Yes, obviously testing the validity of a query by actually running it
    against a live DB is probably a bad idea.

    - Oliver
     
    Oliver Wong, Nov 29, 2006
    #5
  6. pet0etie

    Chris Smith Guest

    Oliver Wong <> wrote:
    > I've also heard (but don't know SQL well enough to confirm for myself)
    > that the SQL language is Turing Complete. Basically what this means is that
    > it's mathematically impossible to be 100% sure that the queries constructed
    > are "good". You can catch many mistakes, but not all of them.


    SQL is not Turing-complete. The most famous example of a decidable
    problem that is unsolvable by SQL is to compute the transitive closure
    of a graph. Most procedural extensions to SQL, including all that I've
    seen of the many different languages that go by the name PL/SQL, are
    Turing-complete.

    However, I very much expect that there are indeed undecidable problems
    involved in the verification of correctness of SQL queries. Correctness
    verification is, generally speaking, mostly undecidable for anything
    more complex than a finite automaton.

    --
    Chris Smith
     
    Chris Smith, Nov 30, 2006
    #6
  7. pet0etie

    Chris Uppal Guest

    Chris Smith wrote:

    > SQL is not Turing-complete.


    I think that, technically, it is T-C -- just model the tape as a table. Not a
    very helpful observation, I admit ;-)


    > However, I very much expect that there are indeed undecidable problems
    > involved in the verification of correctness of SQL queries. Correctness
    > verification is, generally speaking, mostly undecidable for anything
    > more complex than a finite automaton.


    It's an especially vexing problem when different DBMSs implement different
    flavours of SQL. I suspect that the nearest one could conveniently get to a
    reliable general-purpose check would be to replicate the entire DB, including
    stored procedures, permissions, indexes, etc, etc, etc, but without any data in
    the "real" tables. Then run the query against that. If it returns no data
    then it's probably OK, if it signals an error then it's definitely not.

    Parsing the SQL independently would help, of course, but I would be reluctant
    to trust my parser to the extent that I would completely forbid the users from
    submitting queries it couldn't parse.

    -- chris
     
    Chris Uppal, Nov 30, 2006
    #7
  8. pet0etie

    Chris Smith Guest

    Chris Uppal <-THIS.org> wrote:
    > > SQL is not Turing-complete.

    >
    > I think that, technically, it is T-C -- just model the tape as a table. Not a
    > very helpful observation, I admit ;-)


    Sure you can simulate the tape with a table. The difficulty is
    simulating the state transitions; i.e., the logic. Are you thinking of
    using some other language to implement the logic and issuing a sequence
    of SQL queries from there to modify the "tape"? That combination could
    be (probably is, depending on the language used to implement the logic)
    Turing complete. SQL itself is not, though.

    --
    Chris Smith
     
    Chris Smith, Nov 30, 2006
    #8
  9. pet0etie

    Chris Uppal Guest

    Chris Smith wrote:

    > > > SQL is not Turing-complete.

    > >
    > > I think that, technically, it is T-C -- just model the tape as a table.
    > > Not a very helpful observation, I admit ;-)

    >
    > Sure you can simulate the tape with a table. The difficulty is
    > simulating the state transitions; i.e., the logic. Are you thinking of
    > using some other language to implement the logic and issuing a sequence
    > of SQL queries from there to modify the "tape"?


    No, or at least not quite. I think the current state of a Turing machine can
    be completely encoded in one table, the state of the tape in another, and the
    transition from one state to the next can be completely expressed as pure SQL
    queries and updates. I'm not sure whether all the updates relating to one
    state transition can be expressed in a single (pure) SQL query; but that
    doesn't strike me as particularly important anyway.

    There would be a need for an external driver program to invoke the query(ies)
    in an unconditional infinite loop (or until some stopping condition --
    expressed in pure SQL -- was met). To my mind that doesn't affect the validity
    of the claim (after all a "real" Turing machine also needs a meta-level
    execution loop); you may not agree.

    -- chris
     
    Chris Uppal, Dec 1, 2006
    #9
  10. pet0etie

    Chris Smith Guest

    Chris Uppal <-THIS.org> wrote:
    > There would be a need for an external driver program to invoke the query(ies)
    > in an unconditional infinite loop (or until some stopping condition --
    > expressed in pure SQL -- was met). To my mind that doesn't affect the validity
    > of the claim (after all a "real" Turing machine also needs a meta-level
    > execution loop); you may not agree.


    Ah. I'm not sure it makes much sense to talk about whether it affects
    the validity of the claim, because it's clear now that we are making
    different claims. A computational model in which an SQL query (or
    finite sequence of different SQL queries) is indefinitely issued in a
    loop until some halt condition is reached is probably Turing-complete.
    A computational model in which a single SQL query is issued to the
    database is not Turing-complete.

    [I should clarify that apparently SQL99 can solve transitive closure
    now, which was my original example. The relevant feature -- recursive
    queries -- isn't reliably implemented in actual DBMS products, and is
    not part of the "core" subset, so I wasn't thinking of it. I don't know
    personally whether full SQL99 is now Turing-complete, but random
    people's claims found by Google suggest that it is equivalent to
    Datalog, and Datalog is definitely not Turing-complete.]

    --
    Chris Smith
     
    Chris Smith, Dec 1, 2006
    #10
  11. pet0etie

    Chris Uppal Guest

    Chris Smith wrote:

    [me:]
    > [...] To my mind that
    > > doesn't affect the validity of the claim (after all a "real" Turing
    > > machine also needs a meta-level execution loop); you may not agree.

    >
    > Ah. I'm not sure it makes much sense to talk about whether it affects
    > the validity of the claim, because it's clear now that we are making
    > different claims.


    Fair enough.

    -- chris
     
    Chris Uppal, Dec 3, 2006
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. gabriele renzi
    Replies:
    2
    Views:
    221
    gabriele renzi
    Dec 31, 2005
  2. Ken Bloom
    Replies:
    3
    Views:
    224
  3. Good Night Moon
    Replies:
    9
    Views:
    292
    Rick DeNatale
    Jul 25, 2007
  4. Jacob Grover
    Replies:
    5
    Views:
    326
    Jacob Grover
    Jul 18, 2008
  5. Mark Richards
    Replies:
    3
    Views:
    324
    Tad McClellan
    Nov 18, 2007
Loading...

Share This Page