• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Prepared Statement not preparing

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello everyone.
I'm connecting to a DB2 LUW 10.1 using db2jcc driver type 4.
Everything works fine but I noticed a strange thing I wasn't used to during my whole experience with Db2 for i
When i prepare a statement, I can write anything in it but the db2 doesn't prepare it. Doesn't throw any syntax error.
Errors are only returned when I execute the statement. Seems like the driver doesn't support precompilation.
Any hint ?
thanks
 
Ranch Hand
Posts: 624
9
BSD Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Post the relevant code and the complete error you are getting.
These information are needed to find the exact cause.
 
Al Zed
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


That prepareStatement does't throw any exception
 
Saloon Keeper
Posts: 15510
363
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't think prepared statements check your syntax. All they do is make sure that user provided data is sanitized.
 
Al Zed
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Java Toolbox driver checks the syntax, 100% sure. Don't remember others
Actually I guess it should, the statement should be prepared.
This also goes against secutiry reasons of using prepared statement: if the statement is always executed you are exposed to sql injection.
Am I wrong ?
 
Stephan van Hulst
Saloon Keeper
Posts: 15510
363
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Shouldn't it throw an exception when you try to run it, rather than when you instantiate it?
 
Al Zed
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Well, now i don't know :-)
I was hoping someone had the answer or at least tell me if it was a driver configuration thing
 
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Stephan van Hulst wrote:Shouldn't it throw an exception when you try to run it, rather than when you instantiate it?


IIUC, preparation of a SQL statement precompiles the statement, and is a DB call. That is, preparation is a database function, not a java function. This can be tested by checking the SQL cache (usually in a DB table, which a user with proper rights can read).

SQL injection is only a problem when variables are used. Instead, use placeholders. Placeholders are used in the prepare and the values are passed at execution time. If a placeholder is used without preparation, the statement will fail because it is not valid.

When the statement is valid, does it prepare and execute properly?
 
Stephan van Hulst
Saloon Keeper
Posts: 15510
363
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
From the Connection.prepareStatement() documentation:

Java™ Platform, Standard Edition 8 API Specification wrote:Note: This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.

 
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'm not sure all that many drivers actually do precompile.
Pretty sure the Oracle one doesn't, but it will keep the handle to the statement if you don't close the PS (again, off the top of my head).

Precompilation has no effect on SQL Injection, though.
Not sure where that idea comes from.
 
Brian Tkatch
Bartender
Posts: 598
26
Oracle Notepad Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Dave Tolls wrote:I'm not sure all that many drivers actually do precompile.
Pretty sure the Oracle one doesn't


Really? That seems odd, as the database supports it. This can be tested easily by checking the SQL cache after a prepare.

As for DB/2, their drivers version are pretty specific to the database version, or at least used to be. Using the wrong one can do strange things. Years ago, i crashed the server (not the client or session, but the actual RDBMS) four different ways. The server had to be brought back up in restore mode. The diagnosis was the driver was the wrong version.
 
Al Zed
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you guys for you answers.
Ok, now I'm sure db2jcc driver prepared statement actually does not precompile it.
On the other hand if you run the PREPARE command



This way it's compiled and syntax checked.
About the injection: i was thinking about the parameter markers. But actually it's true that it doesn't involve pre compiling.

ou can close this thread, i solved my problem :-)
 
Dave Tolls
Rancher
Posts: 4801
50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Brian Tkatch wrote:

Dave Tolls wrote:I'm not sure all that many drivers actually do precompile.
Pretty sure the Oracle one doesn't


Really? That seems odd, as the database supports it. This can be tested easily by checking the SQL cache after a prepare.



It may support it, but the thin driver doesn't (IIRC).
I'm sure I've checked on this before now.

Here's an SO thread on it. Not quite where I thought it tied in, but close.
 
reply
    Bookmark Topic Watch Topic
  • New Topic