How to validate sql query syntax?

Question

java 1.4 Sql server 2000

i am taking input of sql query (for validation of field value against values retrieved by executing sql query) from admin user which will be stored in database and later i will executing sql query corresponding to field.Before inserting sql query in database i want to validate its syntax in java code.

Fields         Sql Query

stateCode      select statecode from states
district code  select district code from districts

Similar question: http://stackoverflow.com/questions/660609/sql-parser-library-for-java — Michael Lloyd Lee mlk, Oct 21 '09 at 08:59

score 8 · Answer 1 · answered Oct 21 '09 at 10:49

8

Create a PreparedStatement with the query string; if this works, the query string is ok (but nothing is executed yet)

answered Oct 21 '09 at 10:49

Erich Kitzmueller

35,434
6
77
99

Unfortunately, this requires a connection, this will not work in unit tests. – 18446744073709551615 Nov 01 '19 at 21:07
1

@18446744073709551615 The question is not about unit tests.That said, why can't you have a database connection when doing unit tests? – Erich Kitzmueller Nov 04 '19 at 07:44

score 6 · Accepted Answer · answered Oct 21 '09 at 09:53

dont think there is any (easy) way to validate sql

Sql syntax is complex and allows for alot of different ways to enter a statement.

Think you best shot would be to just execute the sql statent and if you have a SQl exception see if its a bad syntax thats causing it.

you can prepend some sql to avoid from actually executing the query

in sybase it would be SET NOEXEC ON

score 3 · Answer 3 · answered Oct 21 '09 at 09:05

3

Why would you let them enter whole sql-statements?

Just provide to fields and let them enter either the statecode or the districtcode.

Then check if the entered value is a number. And run the appropriate query with the entered value.

answered Oct 21 '09 at 09:05

jitter

52,771
11
107
123

its not specific.table name and column can be anything with some conditions or joins depending upon type of field. – Maddy.Shik Oct 21 '09 at 09:10

score 2 · Answer 4 · answered Oct 21 '09 at 10:03

2

A possible solution would could be to get the explain plan of the query, if it manages to explain the query I guess it must be valid. Down side is that it won't like parametrised queries.

answered Oct 21 '09 at 10:03

Gareth Davis

27,264
12
72
105

how to get explain plan of a query? – Maddy.Shik Oct 21 '09 at 10:39
that's a very good question. haven't a clue how to do it on sql-server, sorry. – Gareth Davis Oct 21 '09 at 14:09

erikkallen · Answer 5 · 2009-10-21T10:42:44.760

2

You could do SET FMTONLY ON and then execute the query and see if it works. Just remember to do SET FMTONLY OFF in a finally block, since it's a connection-level setting.

edited Oct 21 '09 at 10:42

answered Oct 21 '09 at 10:06

erikkallen

32,795
13
83
117

score 0 · Answer 6 · answered Mar 28 '12 at 09:25

You may need a full SQL Parser to do such a vendor-specific offline SQL syntax check.

Take a look at this demo which including some Java and C# code:

http://www.dpriver.com/blog/list-of-demos-illustrate-how-to-use-general-sql-parser/vendor-specific-offline-sql-syntax-check/

How to validate sql query syntax?

6 Answers6

Linked