| 提交 | 用户 | 时间 | ||
| 722af2 | 1 | package com.dl.common.utils.sql; |
| X | 2 | |
| 3 | import com.dl.common.exception.UtilException; | |
| 4 | import com.dl.common.utils.StringUtils; | |
| 5 | import lombok.AccessLevel; | |
| 6 | import lombok.NoArgsConstructor; | |
| 7 | ||
| 8 | /** | |
| 9 | * sql操作工具类 | |
| 10 | * | |
| 11 | * @author dl | |
| 12 | */ | |
| 13 | @NoArgsConstructor(access = AccessLevel.PRIVATE) | |
| 14 | public class SqlUtil { | |
| 15 | ||
| 16 | /** | |
| 17 | * 定义常用的 sql关键字 | |
| 18 | */ | |
| 19 | public static final String SQL_REGEX = "select |insert |delete |update |drop |count |exec |chr |mid |master |truncate |char |and |declare "; | |
| 20 | ||
| 21 | /** | |
| 22 | * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序） | |
| 23 | */ | |
| 24 | public static final String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+"; | |
| 25 | ||
| 26 | /** | |
| 27 | * 检查字符，防止注入绕过 | |
| 28 | */ | |
| 29 | public static String escapeOrderBySql(String value) { | |
| 30 | if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) { | |
| 31 | throw new UtilException("参数不符合规范，不能进行查询"); | |
| 32 | } | |
| 33 | return value; | |
| 34 | } | |
| 35 | ||
| 36 | /** | |
| 37 | * 验证 order by 语法是否符合规范 | |
| 38 | */ | |
| 39 | public static boolean isValidOrderBySql(String value) { | |
| 40 | return value.matches(SQL_PATTERN); | |
| 41 | } | |
| 42 | ||
| 43 | /** | |
| 44 | * SQL关键字检查 | |
| 45 | */ | |
| 46 | public static void filterKeyword(String value) { | |
| 47 | if (StringUtils.isEmpty(value)) { | |
| 48 | return; | |
| 49 | } | |
| 50 | String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|"); | |
| 51 | for (String sqlKeyword : sqlKeywords) { | |
| 52 | if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1) { | |
| 53 | throw new UtilException("参数存在SQL注入风险"); | |
| 54 | } | |
| 55 | } | |
| 56 | } | |
| 57 | } | |
