如何使用JdbcTemplate限制仅执行SELECT查询

要实现仅允许外部用户执行SELECT查询的限制，需要从语句校验、API调用、数据库权限三个层面配合实现，以下是具体方案：

1. 前置SQL语句合法性校验 #

在执行SQL前先对用户输入的语句做校验，确保是纯SELECT查询：

• 校验语句是否以SELECT开头（忽略大小写和前置空格）
• 过滤包含INSERT、UPDATE、DELETE、DROP等DML/DDL关键字的语句
• 禁止包含多语句分隔符;，避免用户注入额外非法语句

示例校验工具类：

public class SqlSecurityValidator {
    // 匹配以SELECT开头的语句（忽略大小写和前置空格）
    private static final Pattern SELECT_START_PATTERN = 
        Pattern.compile("^\\s*(SELECT|select)\\s+", Pattern.CASE_INSENSITIVE);
    // 匹配禁用的关键字
    private static final Pattern FORBIDDEN_KEYWORDS_PATTERN = 
        Pattern.compile("\\b(INSERT|UPDATE|DELETE|DROP|ALTER|TRUNCATE|CREATE|REPLACE)\\b", Pattern.CASE_INSENSITIVE);

    public static boolean isValidSelectQuery(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return false;
        }
        // 检查是否以SELECT开头
        boolean isSelectStart = SELECT_START_PATTERN.matcher(sql).find();
        // 检查是否包含禁用关键字
        boolean hasForbiddenKeywords = FORBIDDEN_KEYWORDS_PATTERN.matcher(sql).find();
        // 检查是否包含多语句分隔符
        boolean hasMultiStatement = sql.contains(";");

        return isSelectStart && !hasForbiddenKeywords && !hasMultiStatement;
    }
}

2. 限制使用JdbcTemplate的查询类方法 #

JdbcTemplate提供了不同的方法对应不同的SQL操作：

• 仅使用query()、queryForList()、queryForObject()等查询类方法，这些方法仅支持执行返回结果集的SQL，无法执行修改类语句
• 禁止使用update()、execute()等可以执行DML/DDL的方法

示例调用代码：

@Autowired
private JdbcTemplate jdbcTemplate;

public List<Map<String, Object>> runUserQuery(String userSql) {
    // 先校验SQL合法性
    if (!SqlSecurityValidator.isValidSelectQuery(userSql)) {
        throw new IllegalArgumentException("仅允许执行SELECT查询");
    }
    // 仅使用查询类方法执行
    return jdbcTemplate.queryForList(userSql);
}

3. 数据库层面的权限兜底控制 #

这是最可靠的底层限制：

• 为系统连接数据库的账号仅授予SELECT权限，例如：

  GRANT SELECT ON your_database.* TO 'portal_user'@'%' IDENTIFIED BY 'your_password';
  

• 即使前端校验被绕过，数据库会直接抛出权限不足的异常，阻止非法操作

注意事项 #

• 校验逻辑要考虑SQL注释的情况，比如用户可能用/* ... */或--隐藏禁用关键字，需要额外处理注释内容
• 对于复杂的SELECT语句（如包含子查询、WITH子句），校验逻辑要兼容，避免误判

内容的提问来源于stack exchange，提问作者Jagrut Dalwadi