Friday, August 5, 2011

ServerJDBCHelperAccessBean, should you or should you not ?

It is highly recommended to use EJB's for any insert\updates for the overall integrity of the data in commerce but ServerJDBCHelperAccessBean is a good friend that comes handy in
several scenarios when required to insert\update tables outside of EJB's and also do selects from multiple tables. Also the flush() method is used to flush the cache in EJB container for the DB updates.
Internally uses preparedStatement but you don't have to map each param and could send an array of params to the executeQuery method in the same order as query.

It is also useful to use if you have dynamic SQLs for any kind of search.

code Example:

sqlQuery could be dynamic query based on logic being built or static.
databaseRows is an object of vector -> vectors.

ServerJDBCHelperAccessBean jdbcHelper = new ServerJDBCHelperAccessBean();
Vector databaseRows = new Vector();
databaseRows = jdbcHelper.executeParameterizedQuery(sqlQuery, params);
for(Object obj:databaseRows) {
Vector EachRow=(Vector)obj;
Long catentryId= (Long)EachRow.get(0);
String catGroupIdentifier =(String)EachRow.get(0);
Timestamp timePlaced = (Timestamp)EachRow.get(3);
}

Note: If you are using to update a row in DB, do make sure to update the OPTCounter.

7 comments:

  1. You are saying "Internally uses preparedStatement" - are you implying that it is safe for SQL injection? Else you have to be VERY careful when you create you query string. If there is untrusted data, anything can happen.

    ReplyDelete
  2. What are the performance implications of using the ServerJDBCHelperAccessBean? Does it use the connection pool?

    ReplyDelete
  3. @Jofeemannen It is recommended as an OWASP practice to use PreparedStatements and parametrize queries, if definitely defends a lot of SQL Injection, I can't say it completely eliminates.

    ReplyDelete
  4. @Jordan, Performance wise prepared statements internally have a pool and they cache SQL queries that are optimized that being said I personally would not recommend using it where ever you could use EJBs but if EJBs is not an option or if you have multiple tables. I can't think of any huge performance issues. Also internally it uses the same connection pool and JDBC datasource and so you could use the flush method to flush the EJBCache down the pipe.

    ReplyDelete
  5. as of 2016, if I use ServerJDBCHelperAccessBean to execute an SQL Query with concatenated parameters (so not a parametrized query, because no "?" placeholder), it will prepare the statement but not protect against SQL injection. Right ?

    ReplyDelete
  6. as of 2016, if I use ServerJDBCHelperAccessBean to execute an SQL Query with concatenated parameters (so not a parametrized query, because no "?" placeholder), it will prepare the statement but not protect against SQL injection. Right ?

    ReplyDelete