Forums Register Login

Prepared Statement not preparing

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
Post the relevant code and the complete error you are getting.
These information are needed to find the exact cause.
+Pie Number of slices to send: Send


That prepareStatement does't throw any exception
+Pie Number of slices to send: Send
I don't think prepared statements check your syntax. All they do is make sure that user provided data is sanitized.
+Pie Number of slices to send: Send
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 ?
+Pie Number of slices to send: Send
Shouldn't it throw an exception when you try to run it, rather than when you instantiate it?
+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
 

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?
+Pie Number of slices to send: Send
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.

 
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
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 :-)
+Pie Number of slices to send: Send
 

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.
Why should I lose weight? They make bigger overalls. And they sure don't make overalls for tiny ads:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 714 times.
Similar Threads
Connection.prepare statement performance.
question about deleting a table column
DB2 UDB 701 Certification
How long should a callable statement take to be prepared?
Difference between Statement and PreparedStatement
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 00:27:04.