Avoiding SQL injection when calling stored procedures

[Arsalan Zaidi]

Hi All.

When I call a stored procedure in Oracle like below, it works just fine:

    <parameterMap id="getTitles" class="map">
        <parameter property="applicationId"
jdbcType="NUMERIC"         javaType="java.lang.Long"     mode="IN" />
        <parameter property="userId"
jdbcType="NUMERIC"         javaType="java.lang.Long"     mode="IN" />
        <parameter property="o_titles_record_set"
jdbcType="ORACLECURSOR"    javaType="java.sql.ResultSet"     mode="OUT"
resultMap="titles" />
        <parameter property="o_error_code"
jdbcType="VARCHAR"        javaType="java.lang.String"     mode="OUT" />
    </parameterMap>
    <!-- Calling the Stored procedure -->
    <procedure id="get_titles_proc" parameterMap="getTitles">
                {  call ABC$$ECOM.get_titles( ?,?,?,? ) }    
    </procedure>

However, I think using '?' does not provide any protection from SQL
injection attacks. Converting the ? to # should do the trick. However, if I
were to try the following:

    <parameterMap id="getTitles" class="map">
        <parameter property="applicationId"
jdbcType="NUMERIC"         javaType="java.lang.Long"     mode="IN" />
        <parameter property="userId"
jdbcType="NUMERIC"         javaType="java.lang.Long"     mode="IN" />
        <parameter property="o_titles_record_set"
jdbcType="ORACLECURSOR"    javaType="java.sql.ResultSet"     mode="OUT"
resultMap="titles" />
        <parameter property="o_error_code"
jdbcType="VARCHAR"        javaType="java.lang.String"     mode="OUT" />
    </parameterMap>
    <!-- Calling the Stored procedure -->
    <procedure id="get_titles_proc" parameterMap="getTitles">
                {  call ABC$$ECOM.get_titles( #applicationId#,#userId#,
#o_titles_record_set#,#o_error_code# ) }    
    </procedure>


I get an "Invalid Column Index" Exception.

What am I doing wrong?

Regards.

--Arsalan

 

Regards,

 

Arsalan Zaidi