JDBC

参考文章：

https://www.freebuf.com/articles/web/431498.html

参考靶场：JavaSecLab

采用Statement方法拼接SQL语句

// 原生sql语句动态拼接 参数未进行任何处理
public R vul1(String type,String id,String username,String password) {
    //注册数据库驱动类
    Class.forName("com.mysql.cj.jdbc.Driver");

    //调用DriverManager.getConnection()方法创建Connection连接到数据库
    Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);

    //调用Connection的createStatement()或prepareStatement()方法 创建Statement对象
    Statement stmt = conn.createStatement();
    switch (type) {
        case "add":
            //这里没有标识id id自增长
            sql = "INSERT INTO sqli (username, password) VALUES ('" + username + "', '" + password + "')";
            //通过Statement对象执行SQL语句，得到ResultSet对象-查询结果集
            // 这里注意一下 insert、update、delete 语句应使用executeUpdate()
            rowsAffected = stmt.executeUpdate(sql);
            //关闭ResultSet结果集 Statement对象 以及数据库Connection对象 释放资源
            stmt.close();
            conn.close();
            return R.ok(message);
        case "delete":
            sql = "DELETE FROM users WHERE id = '" + id + "'";
            rowsAffected = stmt.executeUpdate(sql);
            ...
        case "update":
            sql = "UPDATE sqli SET password = '" + password + "', username = '" + username + "' WHERE id = '" + id + "'";
            rowsAffected = stmt.executeUpdate(sql);
            ...
        case "select":
            sql = "SELECT * FROM users WHERE id  = " + id;
            ResultSet rs = stmt.executeQuery(sql);
            ...
    }
}

prepareStatement采用方法拼接SQL语句

PreparedStatement是 JDBC 中用于执行预编译 SQL 语句的接口（继承自Statement）

它是Statement的 “安全升级版”，底层依然基于 JDBC 实现；

核心逻辑：先把 SQL 模板（带?占位符）发给数据库预编译，再传入参数执行，而非每次拼接完整 SQL。

// 虽然使用了conn.prepareStatement(sql)创建了一个PreparedStatement对象，但在执行 stmt.executeUpdate(sql)时，却是传递了完整的SQL语句作为参数，而不是使用了预编译的功能
public R vul2(String type,String id,String username,String password) {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
    PreparedStatement stmt;
    switch (type) {
        case "add":
            sql = "INSERT INTO sqli (username, password) VALUES ('" + username + "', '" + password + "')";
            stmt = conn.prepareStatement(sql);
            rowsAffected = stmt.executeUpdate(sql);
            ...
        case "delete":
            sql = "DELETE FROM users WHERE id = '" + id + "'";
            stmt = conn.prepareStatement(sql);
            rowsAffected = stmt.executeUpdate(sql);
            ...
        case "update":
            sql = "UPDATE sqli SET username = '" + username + "', password = '" + password + "' WHERE id = '" + id + "'";
            stmt = conn.prepareStatement(sql);
            rowsAffected = stmt.executeUpdate(sql);
            ...
        case "select":
            sql = "SELECT * FROM users WHERE id  = " + id;
            stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery(sql);
            ...
    }
}

安全写法

public R vul2(String type,String id,String username,String password) throws ClassNotFoundException, SQLException {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
    PreparedStatement stmt = null;
    int rowsAffected = 0;
    String sql = null; 
    switch (type) {
        case "add":
            sql = "INSERT INTO sqli (username, password) VALUES (?, ?)";
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, password);
            rowsAffected = stmt.executeUpdate();
            break; 
    }
    return null; 
}

JdbcTemplate-SQL语句拼接

JdbcTemplate 是 Spring 框架对原生 JDBC的封装工具类（属于 spring-jdbc 模块）

它不是替代 JDBC，而是 “省掉原生 JDBC 中所有繁琐的样板代码”；

底层依然基于 JDBC 的PreparedStatement实现，天生具备防 SQL 注入能力；

核心目标：用一行代码完成数据库操作，无需手动管理连接、关闭资源、处理异常。

// JDBCTemplate是Spring对JDBC的封装，底层实现实际上还是JDBC
public R vul3(String type,String id,String username,String password) {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl(dbUrl);
    dataSource.setUsername(dbUser);
    dataSource.setPassword(dbPass);
    JdbcTemplate jdbctemplate = new JdbcTemplate(dataSource);
    switch (type) {
        case "add":
            sql = "INSERT INTO sqli (username, password) VALUES ('" + username + "', '" + password + "')";
            //Spring的JdbcTemplate会自动管理连接的获取和释放，不需要手动关闭连接
            rowsAffected = jdbctemplate.update(sql);
            ...
        case "delete":
            sql = "DELETE FROM users WHERE id = '" + id + "'";
            rowsAffected = jdbctemplate.update(sql);
            ...
        case "update":
            sql = "UPDATE sqli SET username = '" + username + "', password = '" + password + "' WHERE id = '" + id + "'";
            rowsAffected = jdbctemplate.update(sql);
            ...
        case "select":
            sql = "SELECT * FROM users WHERE id  = " + id;
            stringObjectMap = jdbctemplate.queryForMap(sql);
            ...
    }
}

安全写法-预编译

// JDBCTemplate预编译 此时在常规DML场景有效的防止了SQL注入攻击的发生
public R safe2(String type,String id,String username,String password) {
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
    dataSource.setUrl(dbUrl);
    dataSource.setUsername(dbUser);
    dataSource.setPassword(dbPass);
    JdbcTemplate jdbctemplate = new JdbcTemplate(dataSource);
    switch (type) {
        case "add":
            sql = "INSERT INTO sqli (username, password) VALUES (?,?)";
            rowsAffected = jdbctemplate.update(sql, username, password);
            ...
        case "delete":
            sql = "DELETE FROM users WHERE id = ?";
            rowsAffected = jdbctemplate.update(sql, id);
            ...
        case "update":
            sql = "UPDATE sqli SET username = ?, password = ? WHERE id = ?";
            rowsAffected = jdbctemplate.update(sql, username, id);
            ...
        case "select":
            sql = "SELECT * FROM users WHERE id  = ?";
            stringObjectMap = jdbctemplate.queryForMap(sql, id);
            ...
    }
}

其他加固方法

黑名单过滤

// 检测用户输入是否存在敏感字符：'、;、--、+、,、%、=、>、<、*、(、)、and、or、exeinsert、select、delete、update、count、drop、chr、midmaster、truncate、char、declare
public R safe3(String type,String id,String username,String password) {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
    Statement stmt = conn.createStatement();
    switch (type) {
        case "add":
            if (checkUserInput.checkSqlBlackList(username) || checkUserInput.checkSqlBlackList(password)) {
                return R.error("黑名单检测到非法SQL注入!");
            } else {
                sql = "INSERT INTO users (username, password) VALUES ('" + username + "', '" + password + "')";
                rowsAffected = stmt.executeUpdate(sql);
                ...
        case "delete":
            if (checkUserInput.checkSqlBlackList(id)) {
                return R.error("黑名单检测到非法SQL注入!");
            } else {
                sql = "DELETE FROM users WHERE id = '" + id + "'";
                rowsAffected = stmt.executeUpdate(sql);
                ...
        case "update":
            if (checkUserInput.checkSqlBlackList(id) || checkUserInput.checkSqlBlackList(username) || checkUserInput.checkSqlBlackList(password)) {
                return R.error("黑名单检测到非法SQL注入!");
            } else {
                sql = "UPDATE users SET password = '" + password + "', username = '" + username + "' WHERE id = '" + id + "'";
                rowsAffected = stmt.executeUpdate(sql);
                ...
        case "select":
            if (checkUserInput.checkSqlBlackList(id)) {
                return R.error("黑名单检测到非法SQL注入!");
            } else {
                sql = "SELECT * FROM users WHERE id  = " + id;
                ResultSet rs = stmt.executeQuery(sql);
                ...
    }
}

对于数字型查询，强制类型转换

// 强制类型转换 对用户请求参数进行校验
public R safe4(Integer id) {
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);
    Statement stmt = conn.createStatement();
    message = checkUserInput.checkUser(id);
    if (!message.isEmpty()) return R.error(message);
    sql = "SELECT * FROM users WHERE id  = " + id;
    ResultSet rs = stmt.executeQuery(sql);
    ...
}

采用ESAPI过滤

ESAPI（Enterprise Security API）是 OWASP（开放式 Web 应用程序安全项目）推出的开源安全工具库

它是一套 “安全通用方法集”，封装了 Web 应用开发中常见的安全防护逻辑（如输入验证、XSS 过滤、SQL 注入防护、加密等）；

支持 Java、.NET、Python 等多语言，其中 Java 版本最成熟，是企业级应用解决安全漏洞的常用工具。

// ESAPI提供了多种输入验证API，提供对XSS攻击和SQL注入攻击等的防护
public R safe4(String id) {
    Codec<Character> oracleCodec = new OracleCodec();
    Class.forName("com.mysql.cj.jdbc.Driver");
    Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPass);

    Statement stmt = conn.createStatement();
    // 使用了 Oracle 的编解码器 OracleCodec 和 ESAPI 库来对 ID 进行编码，以防止 SQL 注入攻击。
    String sql = "select * from sqli where id = '" + ESAPI.encoder().encodeForSQL(oracleCodec, id) + "'";
    // String sql = "select * from sqli where id = '" + id + "'";
    String sql = "select * from users where id = '" + id + "'";
    ResultSet rs = stmt.executeQuery(sql);
}

Mybatis

MyBatis 是一款半自动 ORM（对象关系映射）框架，也是 Java 生态中主流的持久层框架：

核心定位：封装原生 JDBC，解决 “SQL 与代码耦合、结果手动映射” 的痛点；

核心特点：SQL 语句与 Java 代码分离（写在 XML / 注解里），自动完成 “SQL 结果→Java 对象” 的映射，底层仍基于 JDBC 的 PreparedStatement 实现，天生支持防 SQL 注入。

安全代码-原生写法

// 这里以增加功能为例
// Controller层
public R safe1(
switch (type) {
    case "add":
        rowsAffected = sqliService.nativeInsert(new Sqli(id, username, password));
        message = (rowsAffected > 0) ? "数据插入成功 username:" + username + " password:" + password : "数据插入失败";
        return R.ok(message);
        ...
}
// Service层
@Override
public int nativeInsert(Sqli user) {
    return sqliMapper.insert(user);
}

// Mapper层
int insert(T entity); 

安全代码-自定义方法

// 这里以增加功能为例
// Controller层
public R safe2( 
switch (type) {
    case "add":
        //这里插入数据使用MyBatiX插件生成的方法
        rowsAffected = sqliService.customInsert(new Sqli(id, username, password));
        message = (rowsAffected > 0) ? "数据插入成功 username:" + username + " password:" + password : "数据插入失败";
        return R.ok(message);
        ...
}
// Service层
//自定义SQL-使用#{}
@Override
public int customInsert(Sqli user) {
    return sqliMapper.customInsert(user);
}

// Mapper层
<insert id="customInsert">
    insert into sqli (id,username,password) values (#{id,jdbcType=INTEGER},#{username,jdbcType=VARCHAR},#{password,jdbcType=VARCHAR})
</insert>

Order By下的注入

由于使用#{}会将对象转成字符串，比如select user order by aa会被#{}转换为select user order by “aa”，数据库不支持这种查询方法会报错，因此很多研发会采用${}来解决，从而造成注入。

// Controller层
public R special1OrderBy() {
  List<Sqli> sqlis = new ArrayList<>();
  switch (type) {
      case "raw":
          sqlis = sqliService.orderByVul(field);
          break;
      case "prepareStatement":
          sqlis = sqliService.orderByPrepareStatement(field);
          break;
      case "writeList":
          sqlis = sqliService.orderByWriteList(field);
      ...
// Service层
//自定义SQL-使用#{}
@Override
public List<Sqli> orderByVul(String field) {
    return sqliMapper.orderByVul(field);
}
@Override
public List<Sqli> orderByPrepareStatement(String field) {
    return sqliMapper.orderByPrepareStatement(field);
}
@Override
public List<Sqli> orderByWriteList(String field) {
    return sqliMapper.orderByWriteList(field);
}
// Mapper层
<!--    Order by下的${}拼接注入问题-->
<select id="orderByVul" resultType="top.whgojp.modules.sqli.entity.Sqli">
    SELECT * FROM sqli
    <if test="field != null and field != ''">
        ORDER BY ${field}
    </if>
</select>
<!--    Order by下的#{}写法 排序不生效-->
<select id="orderByPrepareStatement" resultType="top.whgojp.modules.sqli.entity.Sqli">
    SELECT * FROM sqli
    <if test="field != null and field != ''">
        ORDER BY #{field}
    </if>
</select>
<!--    Order by下的安全写法 白名单-->
<select id="orderByWriteList" resultType="top.whgojp.modules.sqli.entity.Sqli">
    SELECT * FROM sqli
    <if test="field != null and field != ''">
        <choose>
            <!-- 排序列名白名单 -->
            <when test="field == 'id' or field == 'username' or field == 'password'">
                ORDER BY ${field}
            </when>
            <otherwise>
                <!-- 默认使用id进行排序 -->
                ORDER BY id
            </otherwise>
        </choose>
    </if>
</select>

in之后多个id查询时使用#同样会报错，从而造成注入。

假设要查询<font style="color:rgb(0, 0, 0);background-color:rgba(0, 0, 0, 0);">id IN (1,2,3)</font>，若直接用<font style="color:rgb(0, 0, 0);background-color:rgba(0, 0, 0, 0);">#{ids}</font>接收多个 ID（比如传入字符串<font style="color:rgb(0, 0, 0);background-color:rgba(0, 0, 0, 0);">"1,2,3"</font>），MyBatis 会把参数当成**单个字符串**处理，自动加引号，最终生成错误SQL

// Controller层
public R special3In(String type,String scope) {
  switch (type) {
      case "raw":
          sqlis = sqliService.inVul(scope);
          break;
      case "prepareStatement":
          sqlis = sqliService.inPrepareStatement(scope);
          break;
      case "Foreach":

          sqlis = sqliService.inSafeForeach(parseInputToList(scope));
          break;
  ...
// Service层
@Override
public List<Sqli> inVul(String scope) {
    return sqliMapper.inVul(scope);
}
@Override
public List<Sqli> inPrepareStatement(String scope) {
    return sqliMapper.inPrepareStatement(scope);
}
@Override
public List<Sqli> inSafeForeach(List<Integer> scope) {
    return sqliMapper.inSafeForeach(scope);
}
// Mapper层
<select id="inVul" resultType="top.whgojp.modules.sqli.entity.Sqli">
    select * from sqli where id in (${id})
</select>

<select id="inPrepareStatement" resultType="top.whgojp.modules.sqli.entity.Sqli">
    select * from sqli where id in (#{id})
</select>
<select id="inSafeForeach" resultType="top.whgojp.modules.sqli.entity.Sqli">
    SELECT * FROM sqli WHERE id IN
    <foreach collection="scope" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

使用%和模糊查询-like

模糊查询需要拼接%（如%张三%），若直接在#{}中传入带%的参数，或直接写#{keyword，会因参数被加引号导致语法 / 逻辑错误，

select id from user where name like %"zhangsan"%;

// Controller层
public R special1OrderBy() {
@PostMapping("/special2-Like")
public R special2Like(String type,String keyword) {
    List<Sqli> sqlis = new ArrayList<>();
    switch (type) {
        case "raw":
            sqlis = sqliService.likeVul(keyword);
            break;
        case "prepareStatement":
            sqlis = sqliService.likePrepareStatement(keyword);
            break;
    ...
// Service层
@Override
public List<Sqli> orderByWriteList(String field) {
    return sqliMapper.orderByWriteList(field);
}
@Override
public List<Sqli> likeVul(String keyword) {
    return sqliMapper.likeVul(keyword);
}
// Mapper层
<!--  模糊查询-->
<select id="likeVul" resultType="top.whgojp.modules.sqli.entity.Sqli">
    SELECT * FROM sqli WHERE username LIKE '%${keyword}%'
</select>
<select id="likePrepareStatement" resultType="top.whgojp.modules.sqli.entity.Sqli">
    SELECT * FROM sqli WHERE username LIKE CONCAT('%', #{keyword}, '%')
</select>

Hibernate

使用拼接写法导致SQL注入

public R vul1(@RequestParam String username) {
    try {
        String sql = "SELECT * FROM sqli WHERE username = '" + username + "'";
        Object[] result = (Object[]) hibernateTemplate.execute(session ->
                session.createNativeQuery(sql).uniqueResult()
        );
        message = "查询成功，用户名：" + result[1] + " 密码：" +result[2];
        return R.ok(message);
    } catch (Exception e) {
        log.error("查询失败", e);
        return R.error(e.getMessage());
    }
}

HQL查询拼接写法导致SQL注入

HQL（Hibernate Query Language）是 Hibernate 框架提供的面向对象的查询语言

核心特点：操作的是Java 实体类和属性（而非数据库表 / 字段），由 Hibernate 自动解析为原生 SQL 执行；

底层仍基于 JDBC 的PreparedStatement，支持参数绑定，可防注入。

public R vul2(@RequestParam String username) {
    try {
        String hql = "FROM Sqli WHERE username = '" + username + "'";
        Sqli result = (Sqli) hibernateTemplate.execute(session ->
                session.createQuery(hql).uniqueResult()
        );
        message = "查询成功，用户名：" +result.getUsername()+ " 密码：" +result.getPassword();
        return R.ok(message);
    } catch (Exception e) {
        log.error("查询失败", e);
        return R.error(e.getMessage());
    }
}

HQL查询参数化查询

public R safe(@RequestParam String username) {
    try {
        String hql = "FROM Sqli WHERE username = :username";
        Sqli result = hibernateTemplate.execute(session ->
                (Sqli) session.createQuery(hql)
                        .setParameter("username", username)
                        .uniqueResult()
        );
        message = "查询成功，用户名：" +result.getUsername()+ " 密码：" +result.getPassword();
        return R.ok(message);
    } catch (Exception e) {
        log.error("查询失败", e);
        return R.error(e.getMessage());
    }
}

JPA

JPA（Java Persistence API）是 Java 官方定义的ORM（对象关系映射）规范（不是具体框架）：

核心定位：为 Java 对象与数据库表之间的映射提供统一标准，解决不同 ORM 框架（如 Hibernate）API 不统一的问题；

常见实现：Hibernate 是 JPA 最主流的实现（JPA = 规范，Hibernate = 实现），此外还有 EclipseLink、OpenJPA 等；

底层仍基于 JDBC 的PreparedStatement，天生支持参数绑定防注入。

JPA-JPQL注入

也是使用了拼接的方法来构造SQL查询语句

public R vul1(@RequestParam String username) {
    try {
        String jpql = "SELECT s FROM Sqli s WHERE s.username = '" + username + "'";
        Query query = entityManager.createQuery(jpql);
        List<Sqli> results = query.getResultList();
        if (results == null || results.isEmpty()) {
            return R.error("未找到记录");
        }
        StringBuilder sb = new StringBuilder();
        sb.append("查询成功，找到 ").append(results.size()).append(" 条记录\n");
        message = sb.toString();
        log.info(message);
        return R.ok(message);
    } catch (Exception e) {
        String errorMsg = e.getMessage();
        log.error("查询失败: {}", errorMsg, e);
        return R.error(errorMsg);
    }
}

JPA-参数化查询

public R safe(@RequestParam String username) {
    try {
        String jpql = "SELECT s FROM Sqli s WHERE s.username = :username";
        Query query = entityManager.createQuery(jpql)
                .setParameter("username", username);
        List<Sqli> results = query.getResultList();
        if (results == null || results.isEmpty()) {
            return R.error("未找到记录");
        }
        StringBuilder sb = new StringBuilder();
        sb.append("查询成功，找到 ").append(results.size()).append(" 条记录\n");
        message = sb.toString();
        log.info(message);
        return R.ok(message);
    } catch (Exception e) {
        String errorMsg = e.getMessage();
        log.error("查询失败: {}", errorMsg, e);
        return R.error(errorMsg);
    }
}

总结

针对于JAVA程序的SQL

黑盒：
FUZZ测试
数据交互点测试
正常发现和利用即可
白盒：
1、确定数据库通讯技术
2、确定类型后找调用写法
3、确定写法是否安全（预编译）