You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Arsalan Zaidi <ar...@arrkgroup.com> on 2007/02/20 09:34:12 UTC
Avoiding SQL injection when calling stored procedures
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
Re: Avoiding SQL injection when calling stored procedures
Posted by Jeff Butler <je...@gmail.com>.
There is no danger of SQL injection in the first example. This is standard
JDBC syntax for stored procedures.
You can use the # syntax if you want, but you can't use a parameter map.
Use a parameter class instead, and you'll need to use the advance inline
paramater syntax. But you should know that iBATIS will turn each parameter
into a question mark, so you get back to the same thing anyway.
Jeff Butler
On 2/20/07, Arsalan Zaidi <ar...@arrkgroup.com> wrote:
>
> 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
>
>
>