MyBatis fuzzy query to prevent SQL injection

　　SQL injection , everyone is not unfamiliar, is a common way of attack. An attacker could enter some strange SQL fragment (such as "or ' 1 ' = ' 1 ') on the form information or URL of the interface, potentially invading an application with insufficient parameters . Therefore, we need to do some work in our application to guard against such attack mode. In some security-demanding applications, such as banking software, it is often used to prevent SQL injection by replacing all SQL statements with stored procedures . This is certainly a safe way , but we may not need this kind of rigid way in our usual development.

1.${} There are examples of SQL injection in fuzzy queries: (${} not compiled, just stitching it, equivalent to statement)

Sql:

    <SelectId="GetInfo2" Resulttype="Cn.xm.exam.bean.haul.Haulinfo" ParameterType="HashMap">        SELECT *  fromHaulinfo<where>            <ifTest="Name!= NULL">                 andBigname like '%${name}%'            </if>            <ifTest=The status!= NULL">                 andBigstatus=#{status}</if>        </where>    </Select>

Java testing:

It was originally a fuzzy query name, and the result added a filter to the description.

    @Test     Public void Test2 () throws SQLException {        =  new HashMap ();        Condition.put ("name", "% ", " Yangcheng");        Condition.put ("status", "not Started");        Testmapper.getinfo2 (condition);    }

Preparing:select * from Haulinfo WHERE bigname like ' percent ' and bigdescription like ' Yangcheng% ' and Bigstatus =?

Parameters: Not Started (String)

Total:2

2. Bind + #{} fuzzy query prevents SQL injection (#{} from being precompiled, passed parameters are not compiled, only as parameters, equivalent to PreparedStatement)

The bind element can create a variable from the OGNL expression and bind it to the context. Like what:

<SelectId="Selectblogslike" Resulttype="Blog">  <Bind name="Pattern" value="'%' +_parameter.gettitle ()+ '%'"/>  SELECT *  fromBLOGWHERETitle like#{pattern}</Select>

Sql:

    <SelectId="GetInfo" Resulttype="Cn.xm.exam.bean.haul.Haulinfo" ParameterType="HashMap">        SELECT *  fromHaulinfo<where>            <ifTest="Name!= NULL">                <Bind name="Names" value="'%'+Name+'%'"/>                 andBigname like#{names}</if>            <ifTest=The status!= NULL">                 andBigstatus=#{status}</if>        </where>    </Select>

Java testing:

    @Test     Public void Test1 () throws SQLException {        =  new HashMap ();        Condition.put ("name", "% ", " Yangcheng");        Condition.put ("status", "not Started");        Testmapper.getinfo (condition);    }

Preparing:select * from Haulinfo WHERE is bigname like? and Bigstatus =?

Parameters: Percent ' and bigdescription like ' Yangcheng% (string), not started (string)

total:0

"Conclusion" when writing MyBatis mapping statements, try to use the format "#{xxx}". If you have to use parameters such as "${XXX}", you should do the filtering work manually to prevent SQL injection attacks.

#{}: Equivalent to PreparedStatement in JDBC

${}: Is the value of the output variable

Simply put,#{} is pre-compiled , is secure ,${} is not precompiled, just takes the value of the variable, is non-secure, there is SQL injection.

If we use ${} after the order BY statement, there is a risk of SQL injection when we do not do any processing. You say how to prevent, then I can only sad to tell you, you have to manually deal with filtering the input content. If you determine whether the length of the input parameters is normal (the injection statement is generally very long), more accurate filtering can be queried whether the input parameters in the expected set of parameters.

MyBatis fuzzy query to prevent SQL injection