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
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 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?
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.
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.
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.
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.