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: stackoverflow.com/questions/660609/sql-parser-library-for-java — Michael Lloyd Lee mlk
– Michael Lloyd Lee mlk, Commented Oct 21, 2009 at 8:59

Erich Kitzmueller · Accepted Answer · 2009-10-21 10:49:36Z

9

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

answered Oct 21, 2009 at 10:49

Erich Kitzmueller

37.1k5 gold badges85 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

18446744073709551615 Over a year ago

Unfortunately, this requires a connection, this will not work in unit tests.

Erich Kitzmueller Over a year ago

@18446744073709551615 The question is not about unit tests.That said, why can't you have a database connection when doing unit tests?

Peter · Accepted Answer · 2009-10-21 09:53:53Z

6

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

answered Oct 21, 2009 at 9:53

Peter

5,79823 silver badges23 bronze badges

Comments

jitter · Accepted Answer · 2009-10-21 09:05:41Z

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, 2009 at 9:05

jitter

54.8k11 gold badges114 silver badges130 bronze badges

1 Comment

Maddy.Shik Over a year ago

its not specific.table name and column can be anything with some conditions or joins depending upon type of field.

Gareth Davis · Accepted Answer · 2009-10-21 10:03:20Z

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, 2009 at 10:03

Gareth Davis

28.1k12 gold badges77 silver badges107 bronze badges

2 Comments

Maddy.Shik Over a year ago

how to get explain plan of a query?

Gareth Davis Over a year ago

that's a very good question. haven't a clue how to do it on sql-server, sorry.

erikkallen · Accepted Answer · 2009-10-21 10:42:44Z

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, 2009 at 10:42

answered Oct 21, 2009 at 10:06

erikkallen

34.6k14 gold badges90 silver badges121 bronze badges

Comments

James · Accepted Answer · 2012-03-28 09:25:17Z

0

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/

answered Mar 28, 2012 at 9:25

James

513 bronze badges

Collectives™ on Stack Overflow

How to validate sql query syntax?

6 Answers 6

2 Comments

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related