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.