check syntax of query

[pet0etie]

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

 

[Chris Smith]

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.

 

[Oliver Wong]

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

 

[Mark Jeffcoat]

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.

 

[Oliver Wong]

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

 

[Chris Smith]

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 Uppal]

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 Smith]

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 Uppal]

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 Smith]

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 Uppal]

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