如果在MySQL中使用如下查询,将检索出全部数据
select * from t_user where name like '%%%';
select * from t_user where name like '%_%';
select * from t_user where name like concat('%','%','%');
select * from t_user where name like concat('%','_','%');
所以我们要对其做一个转义处理
拦截目标为 like 查询语句
import com.github.pagehelper.util.MetaObjectUtil;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Intercepts({@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}), @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}),})
public class LikeStringEscapeInterceptor implements Interceptor {
private static final Pattern LIKE_PARAM_PATTERN = Pattern.compile("like\\s+['\"%_]*\\?", Pattern.CASE_INSENSITIVE);
private static final Pattern LIKE_CONCAT_PARAM_PATTERN = Pattern.compile("like\\s+concat\\s*\\(\\s*'%'\\s*,\\s*\\?,\\s*'%'\\s*\\)", Pattern.CASE_INSENSITIVE);
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object parameter = args[1];
RowBounds rowBounds = (RowBounds) args[2];
ResultHandler resultHandler = (ResultHandler) args[3];
Executor executor = (Executor) invocation.getTarget();
CacheKey cacheKey;
BoundSql boundSql;
if (args.length == 4) {
boundSql = ms.getBoundSql(parameter);
cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
} else {
cacheKey = (CacheKey) args[4];
boundSql = (BoundSql) args[5];
}
SqlCommandType sqlCommandType = ms.getSqlCommandType();
StatementType statementType = ms.getStatementType();
if (sqlCommandType == SqlCommandType.SELECT && statementType == StatementType.PREPARED) {
escapeParameterIfContainingLike(boundSql);
return executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
}
return invocation.proceed();
}
void escapeParameterIfContainingLike(BoundSql boundSql) {
if (boundSql == null) {
return;
}
String prepareSql = boundSql.getSql();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
Map<Integer, Boolean> position = findLikeParam(prepareSql);
if (position == null || position.size() == 0) {
return;
}
Map<ParameterMapping, Boolean> likeParameterMappings = new LinkedHashMap<>();
MetaObject metaObject = MetaObjectUtil.forObject(boundSql.getParameterObject());
for (int i = 0; i < parameterMappings.size(); i++) {
ParameterMapping pm = parameterMappings.get(i);
String property = pm.getProperty();
if (metaObject.hasGetter(property)) {
boundSql.setAdditionalParameter(property, metaObject.getValue(property));
if (position.containsKey(i)) {
likeParameterMappings.put(pm, position.get(i));
}
}
}
delegateMetaParameterForEscape(boundSql, likeParameterMappings);
}
void delegateMetaParameterForEscape(BoundSql boundSql, Map<ParameterMapping, Boolean> likeParameterMappings) {
for (ParameterMapping mapping : likeParameterMappings.keySet()) {
String property = mapping.getProperty();
MetaObject metaObject = MetaObjectUtil.forObject(boundSql.getParameterObject());
Object value = metaObject.getValue(property);
if (value instanceof String) {
boundSql.setAdditionalParameter(property, escapeLike((String) value, likeParameterMappings.get(mapping)));
}
}
}
String escapeLike(String value, Boolean hasConcat) {
if (value != null) {
if (hasConcat) {
return value.replaceAll("%", "\\\\%");
}
//去除首尾%如果有的话
if (value.startsWith("%")) {
value = value.substring(1);
}
if (value.endsWith("%")) {
value = value.substring(0, value.length() - 1);
}
if (StringUtils.isEmpty(value)) {
return null;
}
return "%" + value.replaceAll("%", "\\\\%") + "%";
}
return null;
}
Map<Integer, Boolean> findLikeParam(String prepareSql) {
Matcher matcher = LIKE_PARAM_PATTERN.matcher(prepareSql);
Map<Integer, Boolean> indexes = new LinkedHashMap<>();
while (matcher.find()) {
int start = matcher.start();
int index = StringUtils.countMatches(prepareSql.substring(0, start), "?");
indexes.put(index, false);
}
matcher = LIKE_CONCAT_PARAM_PATTERN.matcher(prepareSql);
while (matcher.find()) {
int start = matcher.start();
int index = StringUtils.countMatches(prepareSql.substring(0, start), "?");
indexes.put(index, true);
}
return indexes;
}
}
@Bean
public LikeStringEscapeInterceptor likeStringEscapeInterceptor() {
return new LikeStringEscapeInterceptor();
}