Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Validating the SQL Query

 
Gagan Tiwari
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 423
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5852
7
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Craig Jackson
Ranch Hand
Posts: 405
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic