Hi, I got a question from one of our DB engineer about the use of prepared statements. According to him, or a thread in AskTom, it is always preferred to use PreparedStatement instead of Statement whenever possible. http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11_QUESTION_ID:1993620575194
As far as I looked at the code, PreparedStatement is not used other than DBDatabaseDriver class and the method is not used from other code. My understanding is that creation of PreparedStatement has certain overhead, but statement pooling introduced in JDBC 3.0 mitigates the impact especially from application server point of view. We use Oracle, and the DB engineer explained that the use of statement floods the library cache in SGA and reduce the hit rate of pre-compiled statements so it has negative impact on entire db, and using PreparedStatement simply reduces the cost of hard parse. Another aspect is about SQL injection prevention. I noticed single quotes are escaped at DBDatabaseDriver#getValueString() method, but the preferred way to prevent SQL injection is to use PreparedStatement according to OWASP website. http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet Would you tell me the design philosophy or reasons not to use or provide the option to use prepared statement? Is it possible, or have a plan to support PreparedStatement? Thanks, Kenji Nakamura
