How To Fix SQL Injection: MyBatis

How To Fix SQL Injection:

How to Fix SQL Injection using MyBatis


The MyBatis data mapper framework makes it easier to use a relational database with object-oriented applications. Unlike traditional ORM solutions, MyBatis maps objects with SQL statements or stored procedures using a XML descriptor, rather than mapping objects to tables in a database; thus providing complete control over SQL, therefore susceptible to SQL injection if used incorrectly.

This article is intended to illustrate how certain syntax offered by MyBatis to define SQL, is better over the other, in terms defense against SQL injection attacks.

Secure Usage
<select id="getPerson" parameterType="int" resultType="org.application.vo.Person">
SELECT * FROM PERSON WHERE ID = #{id}
</select>

The getPerson statement above takes a parameter of type int and returns an object of type Person.

Note the parameter notation, #{id}. By default, using the #{} syntax will cause MyBatis to generate PreparedStatement parameters (e.g. ?) and set the values safely against them, like the following:

/* Comparable JDBC code */
String selectPerson = "SELECT * FROM PERSON WHERE ID = ?"; 
PreparedStatement ps = conn.prepareStatement(selectPerson); 
ps.setInt(1, id);

Thus, SQL Injection proof.

Here are some more examples of proper usage:

<insert id="insertPerson" parameterType="org.application.vo.Person">
insert into Person (id, name, email, phone)
values (#{id}, #{name}, #{email}, #{phone})
</insert>

<update id="updatePerson" parameterType="org.application.vo.Person">
update Person set name = #{name}, email = #{email}, phone = #{phone}
where id = #{id}
</update>


<delete id="deletePerson" parameterType="int">
delete from Person where id = #{id}
</delete>
Vulnerable Usage
<select id="getPerson" parameterType="string" resultType="org.application.vo.Person">
SELECT * FROM PERSON WHERE NAME = #{name} AND PHONE LIKE '${phone}'; 
</select>

Note the parameter notation, ${phone} in the above getPerson statement. By default, using the ${} syntax will cause MyBatis to directly inject a string, unmodified, into a SQL Statement. MyBatis does NOT modify or escape the string before substitution.

Assuming phone is a user entered value, that has not been validated or escaped appropriately, an attacker can enter a string(payload) like, "1%' OR '1'='1" for phone; the query now becomes the following, returning all rows from the PERSON table.

SELECT * FROM PERSON WHERE NAME = ? and PHONE LIKE '1%' OR '1' = '1'

On the other hand, a payload like "A%'; DELETE FROM PERSON; --" for phone, results in the following query, deleting all data in the PERSON table.

SELECT * FROM PERSON WHERE NAME = ? and PHONE LIKE 'A%'; DELETE FROM PERSON; --'

It is not safe to accept input from a user and use directly in a statement unmodified as illustrated above. This leads to potential SQL Injection attacks. Therefore, when using the ${} syntax, user input should either be disallowed or appropriate validation and escaping should be performed.

Here are some more examples of vulnerable usage:

<insert id="insertPerson" parameterType="org.application.vo.Person">
insert into Person (id, name, email, phone)
values (#{id}, #{name}, #{email}, ${phone})
</insert>

<update id="updatePerson" parameterType="org.application.vo.Person">
update Person set phone = ${phone}
where id = #{id}
</update>


<delete id="deletePerson" parameterType="int">
delete from Person where id = ${id}
</delete>

Authors

Priya Gnanasundar