In MYSQL, when using the LIKE keyword for fuzzy matching, special characters need to be escaped, as follows: \ is escaped into \, _ is escaped into \_, and % is escaped into \%.
In mybatis and mybatis-plus, there is no relevant method to automatically escape. In fact, this is logical. Things like escaping should be left to the developers themselves. The official framework does not invade this kind of logic code for better flexibility.
In business development environments, tests often raise such bugs: “Enter special characters to filter, but the corresponding data is not filtered out.” In fact, problems like this should not exist in essence. They should be cut off at the root. That is to say, it is forbidden to enter special characters for filtering. But often they don’t understand the technology at all when it comes to testing and products, so don’t waste your time arguing with them. So let’s take a look at how to deal with the LIKE special character escaping problem.
Option 1: Implement the plug-in interface of mybatis
We know that mybatis provides a plug-in interface to facilitate developers for secondary development. mybatis-plus enhances mybatis, and the InnerInterceptor interface needs to be implemented in mybatis-plus for interception. If you only introduce mybatis, implement the Interceptor interface to intercept. pay attention:
This solution will have certain limitations on the query methods of like xxx% and like %xxx. like:
When the user inputs = %a, the business queries according to the semantics of like xxx%, which is to query the data starting with xxx. Normally this should be escaped to like \%a%.
When the user inputs = a%, the business queries according to the semantics of like %xxx, and queries the data ending in xxx. Normally this should be escaped to like %a\%.
However, when the program converts, it will still be escaped according to the semantics of like %xxx%. This is unavoidable because this is where the ambiguity really comes in. When the program parses like %a%, it cannot completely determine which part is input by the user, so it can only parse and escape according to the standard %xxx%.
However, in general business queries, left LIKE and right LIKE are rarely used, so this situation does not need to be considered.
The following is the interception interface that implements mybatis-plus. The code is as follows:
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
/**
* @author shaohuailin
* @date 2023/10/8
*/
@Slf4j
public class LikeEscapeInnerInterceptor implements InnerInterceptor {<!-- -->
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {<!-- -->
escapeLike(boundSql);
}
/**
* If there are keywords in the like condition, escape them
*
* @param boundSql
*/
public void escapeLike(BoundSql boundSql) {<!-- -->
String sql = boundSql.getSql().toLowerCase();
if (!sql.contains(" like ") || !sql.contains("?")) {<!-- -->
return;
}
MetaObject parameterObjectMetaObject = SystemMetaObject.forObject(boundSql.getParameterObject());
String[] strList = sql.split("\?");
Set<String> keyNames = new HashSet<>();
for (int i = 0; i < strList.length; i + + ) {<!-- -->
if (strList[i].contains(" like ")) {<!-- -->
String keyName = boundSql.getParameterMappings().get(i).getProperty();
keyNames.add(keyName);
}
}
for (String keyName : keyNames) {<!-- -->
if (parameterObjectMetaObject.hasGetter(keyName)) {<!-- -->
Object value = parameterObjectMetaObject.getValue(keyName);
if (value instanceof String) {<!-- -->
String v = (String) value;
parameterObjectMetaObject.setValue(keyName, convert(v));
}
} else if (boundSql.getParameterObject() instanceof String) {<!-- -->
String v = (String) boundSql.getParameterObject();
boundSql.setAdditionalParameter(keyName, convert(v));
}
}
}
private String convert(String before) {<!-- -->
char[] chars = before.toCharArray();
int len = chars.length;
int offset = 0;
char[] result = new char[len*2];
for (int i = 0; i < len; i + + ) {<!-- -->
char c = chars[i];
// Not the first and last characters, or the first and last characters are not "%"
if ((i > 0 & amp; & amp; i < len -1) || c != '%') {<!-- -->
//Add escape for special characters
if (c == '\' || c == '_' || c == '%') {<!-- -->
result[offset + + ] = '\';
}
}
result[offset + +] = c;
}
return new String(result, 0, offset);
}
}
Then configure MybatisPlusInterceptor. It should be noted that the PaginationInnerInterceptor plug-in needs to be at the end.
/**
* @author shaohuailin
* @date 2023/10/12
*/
@Configuration
public class MybatisPlusConfig {<!-- -->
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {<!-- -->
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new LikeEscapeInnerInterceptor());
interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
return interceptor;
}
}
Option 2: Using aspects combined with annotations
Compared with the first solution, this solution is more flexible and can add special character processing for some interfaces. At the same time, this solution will not have the ambiguity problem in the first solution.
First define the special character processing annotations as follows:
import java.lang.annotation.*;
/**
* Escape like special characters
*
* @author shaohuailin
* @date 2023/10/12
*/
@Target({<!-- -->ElementType.PARAMETER, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface EscapeLike {<!-- -->
}
Configure an aspect for special character processing, as follows:
/**
* Escape like query aspects of special characters
* <p> Add the {@link EscapeLike} annotation to the attribute fields that need to be escaped.
*
* @author shaohuailin
* @date 2023/10/12
*/
@Slf4j
@Aspect
@Component
public class EscapeLikeAspect {<!-- -->
@Pointcut("@within(org.springframework.web.bind.annotation.RestController)")
public void pointcut() {<!-- -->}
@Around("pointcut()")
public Object around(ProceedingJoinPoint joinPoint) throws Throwable {<!-- -->
Object[] args = joinPoint.getArgs();
MethodSignature signature = (MethodSignature) joinPoint.getSignature();
Annotation[][] parameterAnnotations = signature.getMethod().getParameterAnnotations();
for (int i = 0; i < args.length; i + + ) {<!-- -->
Object arg = args[i];
if (arg instanceof String) {<!-- -->
// Process string parameters
boolean match = Arrays.stream(parameterAnnotations[i]).anyMatch(n -> n instanceof EscapeLike);
if (match) {<!-- -->
// start processing
args[i] = convert(((String) arg));
}
} else {<!-- -->
// Process object parameters
Field[] fields = ClassUtil.getFields(arg);
for (Field field : fields) {<!-- -->
EscapeLike annotation = field.getAnnotation(EscapeLike.class);
if (annotation == null) {<!-- -->
continue;
}
String fieldName = field.getName();
Object value = ReflectUtil.getValue(arg, fieldName);
if (value instanceof String) {<!-- -->
ReflectUtil.setValue(arg, field.getName(), convert(((String) value)));
}
}
}
}
return joinPoint.proceed(args);
}
/**
* Special character conversion
*
* @param before
* @return
*/
private String convert(String before) {<!-- -->
char[] chars = before.toCharArray();
int len = chars.length;
int offset = 0;
char[] result = new char[len*2];
for (int i = 0; i < len; i + + ) {<!-- -->
char c = chars[i];
//Add escape for special characters
if (c == '\' || c == '_' || c == '%') {<!-- -->
result[offset + + ] = '\';
}
result[offset + +] = c;
}
return new String(result, 0, offset);
}
}
Just replace the tool classes used in the code with ones you commonly use. Please note that there is a difference between the convert of this solution and the convert of the first solution! In this way, just add the annotation @EscapeLike to the field attributes that need to be escaped.
Summary
Both Option 1 and Option 2 solve the problem of handling the like special character. Each has its own pros and cons, and the decision must be made based on the actual situation.