前言
有时业务需求,需要做动态的DDL拼接,此时在代码层级做SQL注入校验
之前有做过字段动态拼接,自己配了两个数据库字典做了一次转换,将页面传过来的值,拿到字典里进行匹配,获取到真实的数据库字段后,再执行SQL
这里翻了几篇帖子,转载记录一下,以下是转载内容:
1. 注解方式,校验入参
注解
import javax.validation.Constraint;
import javax.validation.Payload;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Constraint(validatedBy = SqlInjectionConstraintValidator.class)
@Target(value = { ElementType.FIELD, ElementType.METHOD, ElementType.PARAMETER })
public @interface SqlInjection {
/**
* 错误提示.
*/
String message() default "Contains illegal characters";
/**
* groups.
*/
Class<?>[] groups() default {};
/**
* payload.
*/
Class<? extends Payload>[] payload() default {};
}
验证器
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.StringUtils;
import javax.validation.ConstraintValidator;
import javax.validation.ConstraintValidatorContext;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@Slf4j
public class SqlInjectionConstraintValidator implements ConstraintValidator<SqlInjection, String> {
/**
* 预编译SQL过滤正则表达式
*/
private Pattern sqlPattern = Pattern.compile(
"(?:')|(?:--)|(/\\*(?:.|[\\n\\r])*?\\*/)|(\\b(select|update|and|or|delete"
+ "|insert|trancate|char|substr|ascii|declare|exec|count|master|into|drop|execute)\\b|(\\*|;|\\+|'|%))");
@Override
public void initialize(SqlInjection constraintAnnotation) {
log.info("initialize constraint");
}
@Override
public boolean isValid(String source, ConstraintValidatorContext constraintValidatorContext) {
if (StringUtils.hasText(source)) {
Matcher matcher = sqlPattern.matcher(source);
if (matcher.find()) {
log.error("包含非法字符|{}", source);
return false;
}
}
return true;
}
}
测试:
2. 代码显式校验
/**
* 是否含有sql注入,返回true表示含有
* @param obj 校验内容
* @return 如果含SQL敏感词汇,则返回true
*/
public static boolean containsSqlInjection(Object obj){
Pattern pattern= Pattern.compile("\\b(and|exec|insert|select|drop|grant|alter|delete|update|count|chr|mid|master|truncate|char|declare|or)\\b|(\\*|;|\\+|'|%)");
Matcher matcher=pattern.matcher(obj.toString());
return matcher.find();
}