wood burning stoves 2.0*
The moose likes JDBC and the fly likes SQL validation! Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL validation!" Watch "SQL validation!" New topic
Author

SQL validation!

Akhilesh Trivedi
Ranch Hand

Joined: Jun 22, 2005
Posts: 1527
I have a requirement wherein i wish to allow my customer to specify sql query by himself (assuming the fact that customer is say half sql-sensitive).

He should be able to save a query and later run the same query if needed. I wish to validate the query without hitting db. Is there any sql-syntax-checker api available?
[ November 11, 2008: Message edited by: Akhilesh Trivedi ]

Keep Smiling Always — My life is smoother when running silent. -paul
[FAQs] [Certification Guides] [The Linux Documentation Project]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Any sql validator will atmost validate the syntax, what about if a user puts a name of table which at all does not exists.

I would rely on database to validate sql. for instance if user puts a query select column1,cloumn2 from mytable

I will change this query to
select column1,cloumn2 from mytable where 1=2 .

This way I will ensure that my query does not return any row after execution, and query will be validated.



I have found a similar thread on javaranch itself, I guess you might have checked this
http://www.coderanch.com/t/324923/java/java/Programatically-validate-SQL-Syntax

Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Akhilesh Trivedi
Ranch Hand

Joined: Jun 22, 2005
Posts: 1527
Thanks buddy.


I could rely on db as you said and come back with error messages at caught-exceptions if any, but i think hints would still be not sufficient to indicate user what is/where exactly things are wrong. Thus I am opting for a static-syntax-validator.

I did not check out the "EasySQL Checker " for Jeanne had mentioned two things about it, first it was shareware and second "it may help if you don't actually need the code."

However SQLJ seems of interest. I was not aware it was from oracle. From few places, i was directed here (doesn't work) for the sqlj-jars.

Let me work around at the options. Thanks.
[ November 12, 2008: Message edited by: Akhilesh Trivedi ]
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

on the other thought if you are not relying on database, so whenever provide this feature to your users, restrict user as much as possible to type anything.

Ask user to select tables/columns from list/dropdowns, provide user to list of keywords to choose.

I recall a mathmatical formula builder was written by one of my friend in same way.

Shailesh
Akhilesh Trivedi
Ranch Hand

Joined: Jun 22, 2005
Posts: 1527
You have opened further more options. Ultimately, we only need the right query before it hits db, either we have a wrong one in say text-box and parse it right or have a right one framed through the options provided in gui.
We will have to check with customer if he will be happy with, one power-pack text-box or easy-clicking list-boxes, damn! i probably know what would be his answer...
 
Don't get me started about those stupid light bulbs.
 
subject: SQL validation!