Talk about the difference between mybatis and $and how to prevent the injection of sql
- 2020-05-12 02:34:48
- OfStack
The difference between # and $in mybatis
1. Treat all incoming data as a string, and put a double quotation mark on the automatically incoming data. For example, order by #user_id#, if the value passed in is 111, then order by "111" will be the value parsed into sql; if the value passed in is id, sql will be order by "id".
2. $generates the incoming data directly in sql. For example, order by $user_id$, if the value passed in is 111, then the value parsed into order by user_id will be order by user_id, if the value passed in is id, then the value parsed into sql will be order by id.
3. # prevents sql injection to a large extent.
4. The $method does not prevent Sql injection.
5.$1 is generally used to pass in database objects, such as table names.
6.1 if you can use #, don't use $.
Prevent Sql injection
Note: the SQL statement should not be written as select * from t_stu s_name name like '%$name$%', which makes it extremely vulnerable to injection attacks.
Parameters in the form of "${xxx}" are directly involved in sql compilation and cannot avoid injection attacks. But when it comes to dynamic table names and column names, you can only use parameter formats like "${xxx}".
When writing mybatis mapping statements, try to use a format such as "#{xxx}". If you have to use a parameter like "${xxx}", do the filtering manually to prevent an sql injection attack.
example
<sql id="condition_where">
<isNotEmpty property="companyName" prepend=" and ">
t1.company_name like #companyName#
</isNotEmpty>
</sql>
The java code is pretty much the same as your old one, but there's nothing wrong with it. Would you mind encapsulating null and '%' into one method
if (!StringUtil.isEmpty(this.companyName)) {
table.setCompanyName("%" + this.companyName + "%");
}