Skip navigation
2012

Why IBatis?

  • iBatis provides framework to use XML files to map JavaBeans to SQL statements.
  • SQL mapping statements are externalized. You don’t have to code SQL statements in your java code and you can have different mapping files for different databases.

    

iBatis Wildcard Search

 

  • iBatis allows you to use “$” dollar sign to perform wildcard search. However you may expose your application to SQL injection if you do that.

 

Solution

 

  • Use “||” sign instead of dollar sign “$”  to perform wildcard search

     

Demonstration Example

 

Application Description

 

We will build a sample application which allows users to browse SESS table.

 

  • The SESS table has three columns: SESS_LNK, NM and OWNER.
  • Users can search existing sessions by session names
  • The application should only display sessions owned by “TEST2”
  • The application should not display sessions owned by other owners.

    

 

Session.xml map file:

 

<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd" >

<sqlMap namespace="session">

  

    <resultMap id="sessionResultMap" class="org.example.domain.impl.Session">

        <result column="SESS_LNK" property="id" />

        <result column="NM" property="name" />

        <result column="OWNER" property="owner"/>

    </resultMap>

   

    <parameterMap id="searchParamMap" class="org.example.domain.impl.SessionSearch">

        <parameter property="name" />  

    </parameterMap>

 

     <select id="dangerousSearchMethod" parameterMap="searchParamMap" resultMap="sessionResultMap">        

                                SELECT                session.SESS_LNK,

                                                session.NM,

                                                session.OWNER

                FROM ${jdbc.schema}.SESS session

                                where session.OWNER = 'TEST2'

                                <dynamic >

                                <isNotEmpty prepend="and" property="name">

                                                session.NM like '%$name$%'

                                </isNotEmpty>                                                                   

                                </dynamic>         

    </select>

   

     <select id="rightSearchMethod" parameterMap="searchParamMap" resultMap="sessionResultMap">                   

                                SELECT                session.SESS_LNK,

                                                session.NM,

                                                session.OWNER

                FROM ${jdbc.schema}.SESS session

                                where session.OWNER = 'TEST2'

                                <dynamic>

                                <isNotEmpty prepend="and" property="name">

                                                session.NM like '%' || #name# || '%'

                                </isNotEmpty>                                                                   

                                </dynamic>

    </select>

</sqlMap>

 

  • The system provides two search methods: dangerousSearchMethod and rightSearchMethod
  • The dangerousSearchMethod  uses “$” dollar sign
  • The rightSearchMethod  uses “||” sign
  • The application is supposed to display sessions owned by “TEST2

    

 

 

SQL Injection

 

SQL Statements Generated for the dangerousSearchMethod

 

iBatis generate the following SQL Statements  for the dangerousSearchMethod when a user enters ' OR 'a%'='.

 

<<EXECUTING>>

 

SELECT session.SESS_LNK,

 

session.NM,

 

session.OWNER

 

FROM WEBAPPS.ACEF_SESS session

 

where session.OWNER = 'TEST2'

 

and session.NM like '%name'

 

OR 'a%'='a%';

 

 

Big Problem

 

  • The user has altered the SQL statements and is able to see sessions he or she is not supposed to see.

 

 

 

SQL Statements Generated for the rightSearchMethod

 

iBatis generated the following SQL  Statements for rightSearchMethod  when a user enters ' OR 'a%'='.

 

 

<<EXECUTING>>

 

SELECT session.SESS_LNK,

 

session.NM,

 

session.OWNER

 

FROM WEBAPPS.ACEF_SESS session

 

where session.OWNER = 'TEST2'

and session.NM like '%' || 'NAME' OR 'A%'='A' || '%';

 

No Problem at all

 

  • This search method allows wildcard search and it is SQL injection safe.