• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Prepared Statement not preparing

 
Al Zed
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • 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
 
Tapas Chand
Ranch Hand
Posts: 583
8
BSD Java
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator


That prepareStatement does't throw any exception
 
Stephan van Hulst
Bartender
Pie
Posts: 6503
83
  • Mark post as helpful
  • send pies
  • 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
  • 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
Bartender
Pie
Posts: 6503
83
  • Mark post as helpful
  • send pies
  • 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
  • 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
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • 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
Bartender
Pie
Posts: 6503
83
  • Mark post as helpful
  • send pies
  • 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.
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • 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
  • 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
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic