Tuesday, September 11, 2012

SQL Injection,CSS attacks, Script attacks in Java or J2ee web applications


Preventing SQL Injection,CSS attacks, Script attacks  in Java or J2ee web applications.
====================================================================

To Prevent SQL Injection,CSS attacks, Script attackes  in Java J2ee based web applicataions we had to add filter which
will inspect each and every field that is submitted to the application.
But be careful as some times this filter may change the values of some inputs.
Eg: In our test app when we added this filter all  quotes like say for name was replacye by html equivalent.
The reason is there can be some fields like name say Ram'S (with an apostrophe).The apsotrophe here would be replaced
with its html Equivalent.So one way is not allow user to enter (by haveing validation in UI)  or
if you still need to allow then exclude that field from filter.
We had to exclude  few fields here for some hidden fields which is used by JSF to maintain state on client side.
The reason was when JSF was storing the client state it had some encrypted striung with quotes and
parenthisis. The filter would think that it was sql attack and would replace the quote with html Equivalent ,
As result JSF decoding of the client state would fail with "StreamCorruptedException".


package com.test.common;

import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletResponse;
import javax.servlet.http.HttpServletRequest;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 * @author reddy
 *  This class goes through the Request parameters and looks for
 *  any suspicious inputs which could be a SQL injected or some java
 *  script some user is trying to inject.
 */

public class SqlInjectionAndXSSFilter implements Filter {
 private static Logger LOGGER = LogManager.getLogger(SqlInjectionAndXSSFilter.class);
 private static Map<String,String>  excludeFieldsMap = new HashMap<String,String>();
 
 public void init(FilterConfig config) throws ServletException {
  //Looking for sql-xss-exclusion-filter.properties
  String propertiesFilePath = config.getInitParameter("properties_file");
  if (propertiesFilePath == null || propertiesFilePath.length() == 0) {
   LOGGER.warn("The properties_file parameter in web.xml for SqlInjectionAndXSSFilter is not specified.");

  } else {
    
      //If you  want to inspect every request parameter
      Properties props = new Properties();
      try {
   props.load(getClass().getClassLoader().getResourceAsStream(propertiesFilePath));

   Iterator<Object> itr = props.keySet().iterator();   

       while (itr.hasNext()) {
        String key = (String)itr.next();
        String value = (String)props.get(key);
        LOGGER.debug("Adding key="+key +" value="+value);
        excludeFieldsMap.put(key,value);
       }

       

       //Send this map to SqlInjectionAndXSSRequestWrapper. I made it static intentionally.
       SqlInjectionAndXSSRequestWrapper.excludeFieldsMap = excludeFieldsMap;
       
            } catch (IOException e) {
                LOGGER.fatal("Could not load properties file: " + propertiesFilePath, e);
                throw new ServletException("Could not load properties file: " + propertiesFilePath);
            }
  }
    }
 
 public void destroy() {



 }



 public void doFilter(ServletRequest request, ServletResponse response,

   FilterChain chain) throws IOException, ServletException {

  chain.doFilter(new SqlInjectionAndXSSRequestWrapper((HttpServletRequest) request),

    response);

 }

 

}



sql-xss-exclusion-filter.properties
===================================
//Property file with Tokens to be excluded from our filter
//Lets Call this file as sql-xss-exclusion-filter.properties, PUT IN YOUR CLASSPATH
//The reason was when JSF was storing the client state it had some encrypted string with quotes
//and then filter would think that it was sql attack and would replace the quote which in trun would make
//jsf encoding fail with "StreamCorruptedException". As a result JSF couldnt restore client state in some cases.

# List of all fields that needs to be excluded from SqlInjectionAndXSSFilter
# Any fields thats not listed here will be inspected by the filter and if
# filter finds any thing suspicious (XSS or SQL attacks code) it will replace
# the contents.
# There are few fields like the jsf state and jsf tree which the filter should never edit
# else jsf cant restore the state and will throw streamcorrupted exceptions.
# Let the key and value be same so that we can load into map for easy operations. (I love maps for the ease of searching)

jsf_tree_64=jsf_tree_64

jsf_state_64=jsf_state_64




package com.test.common;

import java.util.HashMap;
import java.util.Map;
import java.util.regex.Pattern;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletRequestWrapper;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

/**
 * @author reddy
 * Used by the filter to remove any potential code sent as input parameter which has
 * XSS (Cross site scripting) and
 * More at :
 * http://ha.ckers.org/xss.html
 * http://www.symantec.com/connect/articles/detection-sql-injection-and-cross-site-scripting-attacks  
 *
 */

public class SqlInjectionAndXSSRequestWrapper extends HttpServletRequestWrapper {

 private static Logger logger = LogManager.getLogger(SqlInjectionAndXSSRequestWrapper.class);


 //The filter will set this map

 public static  Map<String,String>  excludeFieldsMap = new HashMap<String,String>();

 

 public SqlInjectionAndXSSRequestWrapper(HttpServletRequest servletRequest) {

  super(servletRequest);

 }



 public String[] getParameterValues(String parameter) {

  String[] values = super.getParameterValues(parameter);

  if (values == null) {

   return null;

  }

  int count = values.length;

  String[] encodedValues = new String[count];

  for (int i = 0; i < count; i++) {

   encodedValues[i] = replaceXSSAndSqlInjection(values[i],parameter);

  }

  return encodedValues;

  /*

  for (int i = 0; i < count; i++) {

   checkXSSAndSqlInjectionPresence(parameter,values[i]);

  }

  return values;

  */

 }



 public String getParameter(String parameter) {

  String value = super.getParameter(parameter);

  if (value == null) {

   return null;

  }

  return replaceXSSAndSqlInjection(value,parameter);

  //checkXSSAndSqlInjectionPresence(parameter,value);

  //return value;

 }



 public String getHeader(String name) {

  String value = super.getHeader(name);

  if (value == null){

   return null;

  }

  return replaceXSSAndSqlInjection(value,name);

 }

 

 /**

  * If its finds any XSS injection it will replace the values with html equivalent and
  * and for SQL injection it will replace few sql key words (insert, delete...etc)
  * @param value
  * @return
  */

 public static String replaceXSSAndSqlInjection(String value, String fieldName) {

  //The key and value will be same

  //Eg:jsf_tree_64=jsf_tree_64  - See sql-xss-exclusion-filter.properties

  //SO here we ignore any fields that we feel should not be inspeacted by this filter..

  String fieldToExclude = excludeFieldsMap.get(fieldName);

  if((fieldToExclude != null) &&(fieldToExclude.equalsIgnoreCase(fieldName))){

   logger.debug("The field name:"+ fieldName +" should not be inspected by SqlInjectionAndXSSFilter.");

   return value;

  }

  

  String orgValue = new String(value);

  //No < and > as it could be for some sql.

  value = value.replaceAll("<", "&lt;").replaceAll(">", "&gt;");

  //No () brackets as part of data....

  value = value.replaceAll("\\(", "&#40;").replaceAll("\\)", "&#41;");

  //Handle any apostrophe.  Can a name have this ??

  value = value.replaceAll("'", "&#39;");

  //Any java script stuff.

  value = value.replaceAll("eval\\((.*)\\)", "");

  value = value.replaceAll("[\\\"\\\'][\\s]*javascript:(.*)[\\\"\\\']","\"\"");

  value = value.replaceAll("script","");



  /*

   * This signature first looks out for the = sign or its hex equivalent (%3D).
   * It then allows for zero or more non-newline characters,
   * and then it checks for the single-quote, the double-dash or the semi-colon.
   * Detect either the hex equivalent of the single-quote, the single-quote itself or
   * the presence of the double-dash. These are SQL characters for MS SQL Server and Oracle,
   * which denote the beginning of a comment, and everything that follows is ignored.
   * See more info at
   * http://www.symantec.com/connect/articles/detection-sql-injection-and-cross-site-scripting-attacks
   * Regex for detecting SQL Injection meta-characters
   */

  value = value.replaceAll("/((\\%3D)|(=))[^\\n]*((\\%27)|(\')|(\\-\\-)|(\\%3B)|(;))/i","");

  

  /*

   * Regex for detecting SQL Injection with the UNION keyword
   *
   * (\%27)|(\') - the single-quote and its hex equivalent
   union - the keyword union
   */

  value = value.replaceAll("/((\\%27)|(\'))union/ix","");

  /*

   *

   * A typical SQL injection attempt of course revolves around the use of the single quote
   * to manipulate the original query so that it always results in a true value.
   *  Most of the examples that discuss this attack use the string 1'or'1'='1.
   *  However, detection of this string can be easily evaded by supplying a value
   *  such as 1'or2>1--. Thus the only part that is constant in this is the initial
   *  alphanumeric value, followed by a single-quote, and then followed by the word 'or'.
   *  The Boolean logic that comes after this may be varied to an extent where a generic pattern
   *  is either very complex or does not cover all the variants. Thus these attacks can be detected to a fair
   *  degree of accuracy by using the next regular expression
   * 
   *  Regex for typical SQL Injection attack

   *  /\w*((\%27)|(\'))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix

   */

  value = value.replaceAll("/\\w*((\\%27)|(\\'))((\\%6F)|o|(\\%4F))((\\%72)|r|(\\%52))/ix",""); 

  

  //checking for the keywords and a combination of quotes with conjunctions and quotes with double pipe (||)

  value = value.replaceAll("insert|update|delete|having|drop|(\'|%27).(and|or).(\'|%27)|(\'|%27).%7C{0,2}|%7C{2}","");  

  

  //Regex for "<img src" CSS attack

  value = value.replaceAll("/((\\%3C)|<)((\\%69)|i|(\\%49))((\\%6D)|m|(\\%4D))((\\%67)|g|(\\%47))[^\n]+((\\%3E)|>)/I","");

  

  if(logger.isDebugEnabled()){

   //Lets print only if debug is enable and if the values got changed.

   //Add as safety net

   if((orgValue != null) &&(value!= null) &&(!orgValue.equalsIgnoreCase(value))){

    logger.debug(" Value was changed by Filter from :"+ orgValue);

    logger.debug("          TO:"+ value);

   }

  }

  return value;

 }
}
web.xml:
=======
In your web.xml now configure the filter
<!--  Filter for preventing cross site attacks and Sql injection attacks. -->

 <filter>
  <filter-name>SqlInjectionAndXSSFilter</filter-name>
  <filter-class>com.test.common.SqlInjectionAndXSSFilter</filter-class>
  <init-param>
   <param-name>properties_file</param-name>
   <param-value>sql-xss-exclusion-filter.properties</param-value>
  </init-param>
 </filter>
<


!-- 
 Filter for preventing cross site attacks and Sql injection attacks.
 Include all file extensions. JSF, JSP
-->
  <filter-mapping>
   <filter-name>SqlInjectionAndXSSFilter</filter-name>
   <url-pattern>*.jsp</url-pattern>   
   <dispatcher>REQUEST</dispatcher>
  </filter-mapping>
  <filter-mapping>
   <filter-name>SqlInjectionAndXSSFilter</filter-name>
   <url-pattern>*.jsf</url-pattern>   
   <dispatcher>REQUEST</dispatcher>
  </filter-mapping>


You now try to enter some value in text fields on the UI in your application. The filter will intercept and
replace the html equivalent.So if you dont want a field to be intercepted you can add those fields
in sql-xss-exclusion-filter.properties

Hope this helps. If you have better idea or approach  or if you like this article
please leave your suggestions or comments below.


Wednesday, May 30, 2012

Cross Domain JSONP ( Json with padding ) with Jquery and Servlet or JAX-WS

  • Solving Cross Domain problem using JSONP ( Json with padding ) with Jquery and Servlet JAX-WS
  • or Cross-domain communications with JSONP
  • or Cross domain jquery or cross domain Ajax
  • or java - Sending JSONP vs. JSON data
  • or JSONP javascript or Java JSONP

    Well there are several techniques to address cross domain problem. Here are few.

1. Using CORS ( Cross-Origin Resource Sharing ) where in we modify the repsonse header
Access-Control-Allow-Origin: *
Access-Control-Allow-Origin: http://test.com:8080 http://foobar.com
The asterisk permits scripts hosted on any site to load your resources; the space-delimited lists limits access to scripts hosted on the listed servers.
Problem is CORS may not work in all browsers ( See: http://en.wikipedia.org/wiki/Cross-origin_resource_sharing)

2. Other option is to use some server side re-direct of the request to a servlet or a php script on your  own domain which in trun calls third party web site.
But at times we may not have this option to implment a servlet or PHP script which we can call on our domain.

3.If the third party url that you are invoking supports JSONP response then all browsers work without complaining


What is JSONP ?

- JSONP - JSon with padding. It means to your JSON response we will append the callback method name.
eg: Let say your JSON response is  {"totalInterestPmtAmt":5092.79,"totalPmtAmt":15092.79}
and lets assume the callback method name that was sent in request was  getPayment JSONP Response will be :getPayment( {"totalInterestPmtAmt":5092.79,"totalPmtAmt":15092.79} )
(However if you dont give call back method name Jquery dynamically generates a method name sends in request and when response comes back  it will use that method name to call it ...Read further everything will make sense...)

Since all browsers allow injecting java scripts from third party websites without complaining (that they are cross domain) in JSONP the response we need, is wrapped as a Java script method there by fooling the browser to think its as java script.
<script> tag is less restrictive hence in JSONP you got the same JSON response with a call back method added to it. So browser was happy that it was just injecting some java script function and did not complain. Sweet !!!

Enabling JSONP Server side using Servlet as wrapper for JAX-WS webservice
--------------------------------------------------------------------------------------------------
I had an exisitng JAX-WS webservice which returns  JSON response. Most time this webservice is invoked by some other server component. But we had a client who had to call using Ajax and browser started complaining that we are accessing cross domain url.

Let say my webservice url was:
http://abcd.com/Calculation/CalculationWebServiceImpl.wsdl

And the caller is on
http://xyz.com/displayRate.html ---> this page has ajax call. (will show later )

So all we did was added a servlet called CalulationServlet which had the URL
http://abcd.com/Calculation/CalculationServlet

//SERVLET:
package com.rama.test.jsonp;
public class CalculationServlet extends HttpServlet{
   // NOTE: JSONP works only iwth GET request
    protected void doGet(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        processRequest(request, response);
    }

//JSONP cannot work for POST.....So dont implement. 
  protected void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {

 }
  private void processRequest(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
     String amount = request.getParameter("amount");
     String rate= request.getParameter("interestRate");
     BigDecimal  principal = new BigDecimal( amount);
     BigDecimal  interestRate = new BigDecimal(rate);        

//If are using Jquery each time the call back method name will be auto generated.// but Parameter name can be controlled
// most often all exmaples I have seen use "callback" as request parameter
//eg: your call method name can be like.  jsonp1337622713385
String callBackJavaScripMethodName = request.getParameter("callback");


//Here called my webservice as if like any other java client.      
//callWebservice -- Takes two input param and returns response as JSON    

 String jsonResponseData =   callWebservice(principal,interestRate);
// so it will look like  
//jsonp1337622713385 ( {"totalInterestPmtAmt":5092.79,"totalPmtAmt":15092.79} );
String jsonPoutput = callBackJavaScripMethodName + "("+ jsonResponseData + ");";

//Write it to Response  

   response.setContentType("text/javascript");
   PrintWriter out = response.getWriter();
   out.println(jsonPoutput);
 }    
}
NOTE: Its very important that the response content type is set to   text/javascript
Now I edited the web.xml that was already there for my webservice I added the following entry

  <servlet>
   <description>This Servlet  was added to support JSONP protocol to resolve cross domain scripting problem
    </description>
        <display-name>CalculationServlet</display-name>
       <servlet-name>CalculationServlet</servlet-name>
        <servlet-class>com.rama.test.jsonp.CalculationServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>CalculationServlet</servlet-name>
       <url-pattern>/CalculationServlet</url-pattern>
    </servlet-mapping>
Now my servlet can be invoked using the url
http://abcd.com/Calculation/CalculationServlet?amount=10000  (as the web app name is Calculation)

JQuery to invoke:
Inside the any html page assuming you have Jquery included
<SCRIPT src="scripts/jquery.js"></SCRIPT>

<SCRIPT>

function callWebService() {
///the callback=? the questsion mark will be replace by JQuery with 
//some method name like jsonp1337622713385
//So when response comes back the response is packed inside this method.
//Thats all we did in server side. The callback method name is dynamically 
//generated by JQUERY.


var calcServcURLValue = 'http://abcd.com/Calculation/CalculationServlet?amount=10000&callback=?';
    $.ajax({ 
            url: calcServcURLValue , 
            type: 'get',  /* Dont use post it JSONP doesnt support */
            dataType: 'jsonp',
            success: function(res) {
             alert('Yahoo!!!! We got the Response back')
             processResponse(res);
          }
          , 
            error: function(e , msg){ 
                processError(e,msg);
            }
    }); 
 }


function processError(e , msg){
    alert('Call to Service Failed');
}


//The res object you get is a JSON object 
// Since the JSON response is 
// {"totalInterestPmtAmt":5092.79,"totalPmtAmt":15092.79}
//yes the call back method name will 
//be removed by Jquery isn that neat 

function processResponse(res){
    alert('totalInterestPmtAmt='+ res.totalInterestPmtAmt);
    alert('totalPmtAmt='+ res.totalPmtAmt);
}

</script>

//Add some html and a button to call function callWebService( ) you should be all set.
so this work around will save you from CROSS Domain Issues.

Thursday, March 15, 2012

Calling Stored procedure using hibernate.

Invoking a ORACLE Stored Procedure using hibernate:

There was requirement where we had to select and update a given column or row in one atomic operation and return the results of the updated row. So Oracle has support for this with the RETURNING INTO CLAUSE.
Eg: Example I wanted to update check number and here is the statement to select and update at once.
UPDATE MY_BANK_ACCOUNT  SET   LAST_CHECK_NO = LAST_CHECK_NO+1,
UPDATED_DT = SYSDATE   WHERE INSTITUTION_BANK_ID= 101
RETURNING LAST_CHECK_NO INTO v_next_check_number;
 where v_next_check_number is variable.

Table : MY_BANK_ACCOUNT used in this example
-------------------------------------------
INSTITUTION_BANK_ID            NOT NULL NUMBER INSTITUTION_ID                 NOT NULL NUMBER ROUTING_NUMBER                 NOT NULL VARCHAR2(50) ACCOUNT_NUMBER                 NOT NULL VARCHAR2(50) LAST_CHECK_NO                  NOT NULL NUMBER FRACTION_CODE                  NOT NULL VARCHAR2(50) ACTIVE_STATUS_IND              NOT NULL VARCHAR2(1) CREATED_BY                     NOT NULL VARCHAR2(50) CREATED_DT                     NOT NULL DATE UPDATED_BY                              VARCHAR2(50) UPDATED_DT                              DATE 
So here is the Stored procedure to update and return the Row:

CREATE OR REPLACE PROCEDURE  PROC_GET_NEW_CHECK_NUMBER(
p_cursor out sys_refcursor,p_bankid  IN NUMBER, p_application_name IN VARCHAR ) AS
v_next_check_number      NUMBER;
BEGIN    
    UPDATE  
MY_BANK_ACCOUNT  
        SET   LAST_CHECK_NO = LAST_CHECK_NO+1,
              UPDATED_DT = SYSDATE,
              UPDATED_BY = p_application_name
        WHERE INSTITUTION_BANK_ID= p_bankid 
        RETURNING LAST_CHECK_NO INTO v_next_check_number;
    --COMMIT;  Dont commit here as this stored proc is used in Global Txn so 
    -- we get  error which says COMMIT is not allowed in a subordinate session

    -- In case you are not using Global Txn (Like I do in J2ee) use commit here. 
    OPEN p_cursor for
     SELECT  INSTITUTION_BANK_ID, INSTITUTION_ID, ROUTING_NUMBER, ACCOUNT_NUMBER,

        /* copy  the value from local variable that we stored during update  */
         v_next_check_number AS LAST_CHECK_NO,
         FRACTION_CODE,  ACTIVE_STATUS_IND, CREATED_BY, CREATED_DT,

         UPDATED_BY,  UPDATED_DT    FROM 
MY_BANK_ACCOUNT  
    WHERE INSTITUTION_BANK_ID=p_bankid; 
END 
PROC_GET_NEW_CHECK_NUMBER ;
/

NOTE: Hibernate expects the first argument always to be the Cursor that you will be returning. IT HAS TO BE THE FIRST ARGUMENT. I uses SYS Ref cursor you can use re cursor as well.

In case you want to test your stored procedure in SQLPLUS use the below code.

SET SERVEROUTPUT ON;
DECLARE
   v_cur SYS_REFCURSOR;  
   v_inst_bank_id        NUMBER;    
   v_institute_id        NUMBER;    
   v_routing_number         VARCHAR2(50);
   v_account_number         VARCHAR2(50);
   v_next_check_number      NUMBER;     
   v_fraction_code          VARCHAR2(50);
   v_active_status_ind      VARCHAR2(1);
   v_created_by        VARCHAR2(50);
   v_created_dt        DATE;    
   v_updated_by        VARCHAR2(50);
   v_updated_dt        DATE;
BEGIN
   -- HERE THE BANK ID WE ARE USING IS 1. CHANGE IT ANY VALUE YOU WANT.    
  PROC_GET_NEW_CHECK_NUMBER(v_cur,1,'MytestAPP');  
  COMMIT; -- Caller must commit else you will keep locking that row. 

--  (Since my stored doesn't commit as I am using it in Global TXN. If you have commited in side stored proc ignore this commit )
  LOOP
    FETCH v_cur INTO v_inst_bank_id,v_institute_id,v_routing_number,v_account_number,
             v_next_check_number,v_fraction_code,v_active_status_ind,
             v_created_by,v_created_dt,v_updated_by,v_updated_dt;
    EXIT WHEN v_cur%NOTFOUND;
     dbms_output.put_line( 'New Check Number =' || v_next_check_number  ||' For Bank Id='|| v_inst_bank_id);
     dbms_output.put_line( 'Routing number='|| v_routing_number ||' Account num='|| v_account_number);
  END LOOP;
  CLOSE v_cur;
END;


Now lets Map this Stored procedure to Java class in HBM file.
MyBankAccount.hbm.xml:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="com.test.reddy.dao.model.MyBankAccount" table="MY_BANK_ACCOUNT">
        <id name="institutionBankId" type="java.lang.Long">
            <column name="INSTITUTION_BANK_ID" />
        </id>
        <property name="institutionId" type="java.lang.Long">
            <column name="INSTITUTION_ID" />
        </property>
        <property name="routingNumber" type="string">
            <column name="ROUTING_NUMBER" />
        </property>
        <property name="accountNumber" type="string">
            <column name="ACCOUNT_NUMBER" />
        </property>
        <property name="lastCheckNumber" type="java.lang.Long">
            <column name="LAST_CHECK_NO" />
        </property>
        <property name="fractionCode" type="string">
            <column name="FRACTION_CODE" />
        </property>
        <property name="activeStatusInd" type="string">
            <column name="ACTIVE_STATUS_IND" />
        </property>
        <property name="createdBy" type="string">
            <column name="CREATED_BY" />
        </property>
        <property name="createdDT" type="java.util.Date">
            <column name="CREATED_DT" />
        </property>
        <property name="updatedBy" type="string">
            <column name="UPDATED_BY" />
        </property>
        <property name="updatedDT" type="java.util.Date">
            <column name="UPDATED_DT" />
        </property>
    </class>



    <!--
        The first argument of the stored procedure is actually an OUT parameter from Oracle. 

        So map how the cursor will be interpreted by hibernate. Dont mess here.
    -->
    <sql-query name="MyBankAccount.getNextCheckNumber"    callable="true">
        <return class="com.test.reddy.dao.model.MyBankAccount">
            <return-property name="institutionBankId" column="INSTITUTION_BANK_ID" />
            <return-property name="institutionId" column="INSTITUTION_ID" />
            <return-property name="routingNumber" column="ROUTING_NUMBER" />
            <return-property name="accountNumber" column="ACCOUNT_NUMBER" />
            <return-property name="lastCheckNumber" column="LAST_CHECK_NO" />
            <return-property name="fractionCode" column="FRACTION_CODE" />
            <return-property name="activeStatusInd" column="ACTIVE_STATUS_IND" />
            <return-property name="createdBy" column="CREATED_BY" />
            <return-property name="createdDT" column="CREATED_DT" />
            <return-property name="updatedBy" column="UPDATED_BY" />
            <return-property name="updatedDT" column="UPDATED_DT" />
        </return>
        { call PROC_GET_NEW_CHECK_NUMBER(?,:bankId,:appName) }
    </sql-query>

</hibernate-mapping> 


Please create a MyBankAccount.java file with above properties
package  com.test.reddy.dao.model;
public class MyBankAccount implements Serializable {
   private Long institutionBankId;
    private Long institutionId;
    private String routingNumber;
    private String accountNumber;
    private Long lastCheckNumber;
    private String fractionCode;
    private String activeStatusInd;
    private String createdBy;
    private Date createdDT;
    private String updatedBy;
    private Date updatedDT;

//PLEASE GENERATE GETTERS and SETTERS - Left it intentionally though you need add getter and setters.

}



Calling your Stored procedure now using Java code:
public static void main(String[] args)throws Exception
        System.out.println("Calling test cursor");
        // Please get hibernate session 
        Session session = HibernateUtil.getSession();
        //Clear any previously loaded Stuff always Reload fresh from DB.
        session.clear();
        //Its just another SQL.
        Query qry = session.getNamedQuery("MyBankAccount.getNextCheckNumber");
        qry.setLong("bankId",1);
        qry.setString("appName","MyTestApp");
        Object result = qry.uniqueResult();
        System.out.println(result);
        System.out.println(((MyBankAccount)result).getLastCheckNumber());
    }


PROBLEMS I FACED:
We upgraded from Oracle9 to Oracle 11.2.0.1.0 . The OracleTypes class in 11g has been moved (I guess after Oracle 9) to a new package from "oracle.jdbc.driver.OracleTypes"  to "oracle.jdbc.OracleTypes" .
So I was getting the below error
  • Caused by: org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value.
org.springframework.orm.hibernate3.SessionFactoryUtils.convertHibernateAccessException(SessionFactoryUtils.java:679)
org.springframework.orm.hibernate3.HibernateAccessor.convertHibernateAccessException(HibernateAccessor.java:412)

So I had to upgrade from  Hibernate 3.1.jar to  hibernate-core-3.3.2.GA.jar. This may also require
- javassist-3.9.0.GA.jar , slf4j-api-1.6.4.jar and slf4j-log4j12-1.6.4.jar

Inside hibernate-core-3.3.2.GA.jar source code they check first for
ORACLE_TYPES_CLASS_NAME = "oracle.jdbc.OracleTypes";
if  not found then they look for the older version.
DEPRECATED_ORACLE_TYPES_CLASS_NAME = "oracle.jdbc.driver.OracleTypes";
(You don't need to worry about this. We can trust Gavin King the Guru of Hibernate , just use hibernate-core-3.3.2.GA.jar)

Also note that hibernate Dialect Should be set to
hibernate.dialect  ===>  org.hibernate.dialect.Oracle10gDialect 
or org.hibernate.dialect.Oracle9iDialect  or org.hibernate.dialect.Oracle8iDialect in hibernate.cfg.xml or corresponding spring xml file which ever approach you are using.

Please do not use org.hibernate.dialect.OracleDialect and  org.hibernate.dialect.Oracle9Dialect (no i ) - its deprecated.


  • Then on server start up the application started throwing XML Parsing exceptions.
 caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'ORADataSource' defined in class path resource [spring-config/sample-dao-application-context.xml]: Invocation of init method failed; nested exception is org.springframework.beans.factory.support.BeanDefinitionValidationException: Couldn't find an init method named 'JndiObjectFactoryBean' on bean with name 'ORADataSource'
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1420)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:519)
org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:456)


After several hours of removing code and adding as few hbm files to the application I found that a common hbm file that had few properties defined in another file was causing this.
My hibernate File had some thing like
 <!ENTITY CommonProperties SYSTEM "file://hibernate-mapping/CommonProperties.hbm.xml">
With hibernate-core-3.3.2.GA.jar this doesn't work. So change the file to classpath as shown below.
<!ENTITY CommonProperties SYSTEM "classpath://hibernate-mapping/CommonProperties.hbm.xml">

  • I was using a older JDBC driver so after fixing the above error I was getting
 org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [{ call PROC_GET_NEW_CHECK_NUMBER(?,?,?) }]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
  at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:44)
        at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:100)

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

    To fix this I upgraded my JDBC driver to the correct version that matches  the Oracle installation. So in my case I used ojdbc6_11g_11_2_0_1.jar since my Oracle instance :Oracle 11.2.0.1.0

  •   Finally when I added the above as part of an EJB bean which is container  managed it started throwing errors 
Exception: org.hibernate.TransactionException: Could not find UserTransaction in JNDI [java:comp/UserTransaction]
org.hibernate.transaction.JTATransactionFactory.getUserTransaction(JTATransactionFactory.java:173)
org.hibernate.transaction.JTATransactionFactory.createTransaction(JTATransactionFactory.java:149)Thanks to this Article
http://www.ibm.com/developerworks/websphere/techjournal/0609_alcott/0609_alcott.html
If its  EJB and is using Container manager transaction then use
<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.CMTTransactionFactory</prop>
DO NOT USE
<prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JTATransactionFactory</prop>
its for BMT - Bean manager Transaction . Also when you use BMT Hiberenate expects this in hibernat configuration file.
<prop key="jta.UserTransaction">java:comp/UserTransaction</prop>
And finally the stored procedure worked.
Well I added all the errors that I got and a fix to it as there were not too many threads on few topics and also I was trying to upgrade hibernate and oracle at same time so thought it would help some one out there.