CODEWITHSUNDEEP
javasqlsql-servervalidation
Maddy.Shik
Maddy.Shik

Reputation: 6787

How to validate sql query syntax?

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

Upvotes: 5

Views: 26309

Answers (6)

James
James

Reputation: 51

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/

Upvotes: 0

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

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

Upvotes: 9

erikkallen
erikkallen

Reputation: 34401

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.

Upvotes: 2

Gareth Davis
Gareth Davis

Reputation: 28059

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.

Upvotes: 2

Peter
Peter

Reputation: 5798

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

Upvotes: 6

jitter
jitter

Reputation: 54605

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.

Upvotes: 3

Related Questions