This week's book giveaways are in the Java EE and JavaScript forums.
We're giving away four copies each of The Java EE 7 Tutorial Volume 1 or Volume 2(winners choice) and jQuery UI in Action and have the authors on-line!
See this thread and this one for details.
The moose likes JDBC and the fly likes Validating the SQL Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Validating the SQL Query" Watch "Validating the SQL Query" New topic
Author

Validating the SQL Query

Gagan Tiwari
Ranch Hand

Joined: Jun 10, 2008
Posts: 71
Hi All,
In my JSP Page, user is creating the SQ Query.

Now I have to check whether the SQL QUERY Formed by the use is Valid or not.

Is there any way by which I can Validate the SQL Query (Without firing it)?
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Gagan Tiwari wrote:
Is there any way by which I can Validate the SQL Query (Without firing it)?


The easiest method is to execute your query in this way:

But this only checks the syntax.
There is only a one way to 100% validation ... firing the query
Some errors could be found only on runtime, for example when scalar subquery returns more than on rows
or when the query tries to insert duplicate values into index.

BTW: giving users possibility to execute SQL code makes your application vulnerable to SQL injection attacks.
Peter Johnson
author
Bartender

Joined: May 14, 2008
Posts: 5823
    
    7

In my JSP Page, user is creating the SQ Query.

This is a huge security hole! You should never let users create SQL statements, and you should never create a SQL statement by concatenating user input with SQL text (use a prepared statement with parameters instead). Otherwise you are granting full control over your database to the user.


JBoss In Action
Craig Jackson
Ranch Hand

Joined: Mar 19, 2002
Posts: 405
I agree with the responses above there are many ways a SQL statement can be invalid as well as giving a users access to execute raw SQL statements can be destructive to your database.

if you must go this route. I would suggest you provide a series of drop down lists where you have more control on what the users can query i.e. table, columns and only queries of the database, no insert or updates if possible.

Updates and Inserts can be handled by another JSP (form) page where the user will enter information to be added to the database.

Once the users make their selections you will put everything together and build the SQL statement behind the scenes.

Just one suggestion.
Gagan Tiwari
Ranch Hand

Joined: Jun 10, 2008
Posts: 71
Hi All,
Many Thanks for the suggestion.
Just an update from my side, The USER will only be QUERING the Tables i.e NO UPDATE or INSERT.

From a List of Tables (Pre Defined ) he will select Column, Join with some other Table (If Needed) and QUERY the data.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30361
    
150

Gagan Tiwari wrote:From a List of Tables (Pre Defined ) he will select Column, Join with some other Table (If Needed) and QUERY the data.

If everything is pre-defined, wouldn't the query be valid by definition? You still have to check the user didn't pick values not in the list. But that is a simpler problem to solve.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Validating the SQL Query