如何在Java中使用AWS Athena预准备语句防范SQL注入风险

你之前通过字符串拼接构造Athena查询的SQL注入风险，通过以下两种预准备语句集成方式即可完全规避，符合OWASP安全规范要求：

前置依赖说明 #

• 推荐使用官方AWS Java SDK 2.x版本，兼容性更好
• 若采用JDBC方式对接，需引入对应版本Athena JDBC驱动包

两种实现方式 #

方式1：使用AWS Java SDK 2.x原生实现 #

Athena预准备语句分为两步执行：先通过PREPARE定义带占位符的参数化查询，再通过EXECUTE传入实际参数执行，底层自动处理参数转义：

import software.amazon.awssdk.regions.Region;
import software.amazon.awssdk.services.athena.AthenaClient;
import software.amazon.awssdk.services.athena.model.*;
import java.util.List;

public class AthenaPreparedStatementDemo {
    private static final String ATHENA_OUTPUT_BUCKET = "s3://你的Athena结果存储桶路径/";
    private static final String WORKGROUP = "primary";

    public static void main(String[] args) {
        AthenaClient athenaClient = AthenaClient.builder()
                .region(Region.US_EAST_1)
                .build();

        // 1. 定义预准备语句，?为参数占位符
        String prepareQuery = "PREPARE user_query FROM " +
                "'SELECT user_id, user_name FROM user_db.user_info WHERE country = ? AND register_time > ?'";

        QueryExecutionContext context = QueryExecutionContext.builder().database("user_db").build();
        ResultConfiguration resultConfig = ResultConfiguration.builder().outputLocation(ATHENA_OUTPUT_BUCKET).build();
        StartQueryExecutionRequest prepareRequest = StartQueryExecutionRequest.builder()
                .queryString(prepareQuery)
                .queryExecutionContext(context)
                .resultConfiguration(resultConfig)
                .workGroup(WORKGROUP)
                .build();
        String prepareExecutionId = athenaClient.startQueryExecution(prepareRequest).queryExecutionId();
        // 等待PREPARE执行完成，可自行封装轮询判断逻辑
        waitForQueryToComplete(athenaClient, prepareExecutionId);

        // 2. 执行EXECUTE传入实际参数，无需手动转义
        String executeQuery = "EXECUTE user_query USING '中国', DATE '2023-01-01'";
        StartQueryExecutionRequest executeRequest = StartQueryExecutionRequest.builder()
                .queryString(executeQuery)
                .queryExecutionContext(context)
                .resultConfiguration(resultConfig)
                .workGroup(WORKGROUP)
                .build();
        String executeExecutionId = athenaClient.startQueryExecution(executeRequest).queryExecutionId();
        waitForQueryToComplete(athenaClient, executeExecutionId);

        // 3. 获取查询结果
        GetQueryResultsResponse results = athenaClient.getQueryResults(GetQueryResultsRequest.builder()
                .queryExecutionId(executeExecutionId)
                .build());
        processResult(results.resultSet().rows());
    }
    // 省略等待查询完成、结果解析工具方法实现
}

方式2：使用JDBC PreparedStatement实现（适配传统JDBC开发习惯） #

如果使用Athena JDBC驱动，可直接使用标准PreparedStatement接口，驱动会自动转换为Athena预准备语句逻辑，无需手动编写PREPARE/EXECUTE语句：

import java.sql.*;
import java.util.Properties;

public class AthenaJDBCPreparedDemo {
    public static void main(String[] args) throws Exception {
        // JDBC连接URL格式
        String url = "jdbc:awsathena://athena.us-east-1.amazonaws.com:443/";
        Properties props = new Properties();
        props.setProperty("User", "你的AWS访问密钥AK");
        props.setProperty("Password", "你的AWS访问密钥SK");
        props.setProperty("S3OutputLocation", "s3://你的Athena结果存储桶路径/");
        props.setProperty("Workgroup", "primary");

        Connection conn = DriverManager.getConnection(url, props);
        // 直接写参数化SQL，用?做占位符
        String sql = "SELECT user_id, user_name FROM user_db.user_info WHERE country = ? AND register_time > ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        // 传入参数，底层自动处理转义，完全规避SQL注入
        pstmt.setString(1, "中国");
        pstmt.setDate(2, Date.valueOf("2023-01-01"));

        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println("用户ID：" + rs.getString("user_id") + " 用户名：" + rs.getString("user_name"));
        }
        rs.close();
        pstmt.close();
        conn.close();
    }
}

注意事项 #

• 预准备语句的生命周期和当前Session绑定，Session失效后需要重新执行PREPARE定义
• 单个预准备语句最多支持256个参数占位符，超出需要调整查询逻辑
• 字符串类型参数传入时无需手动加单引号，底层会自动处理转义
• 若使用JDBC方式，需确保驱动版本不低于2.0.2，对预准备语句的支持更完善

内容的提问来源于stack exchange，提问作者Sandesh Dumbre