java基础漏洞学习----SQL注入漏洞

java基础漏洞学习----SQL注入漏洞

前置基础知识

https://www.cnblogs.com/thebeastofwar/p/17759805.html

执行SQL语句的几种方式

1.Statement执行SQL语句
java.sql.Statement是Java JDBC下执行SQL语句的一种原生方式,执行语句时需要通过拼接来执行
若拼接的语句没有经过过滤,将出现SQL注入漏洞
事例代码:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StatementSQL {
    public static void main(String[] args) throws ClassNotFoundException , SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/security?&useSSL=false&serverTimezone=UTC", "root", "root");
        String id = "2";
        String sql = "select * from users where id = " + id;
        Statement ps = conn.createStatement();
        ResultSet rs = ps.executeQuery(sql);
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id") + " username: " + rs.getString("username") + " password: " + rs.getString("password"));
        }
    }
}

SQL注入利用方法1:
万能密码(or 1=1)

SQL注入利用方法2:
利用盲注
2.PreparedStatement执行SQL语句
PreparedStatement是继承statement的子接口,包含已编译的SQL语句,PreparedStatement会预处理SQL语句,SQL语句可具有一个或多个IN参数。
IN参数的值在SQL语句创建时未被指定,而是为每一个IN参数保留一个问号(?)作为占位符。每个问号的值,必须在该语句执行之前通过适当的setXXX方法来退供。
如果是int型则用setInt方法,如果是string型则用setString方法。
PreparedStatement预编译的特性使得其执行SQL语句要比Statement块,SQL语句会编译在数据库系统中,执行计划会被缓存起来,使用预处理语句比普通语句更快。
PreparedStatement预编译还有另一个优势,可以有效地防止SQL注入攻击,其相当于Statement的升级版
事例代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class PreparedStatementSQL {
    public static void main(String[] args) throws ClassNotFoundException , SQLException{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/security?&useSSL=false&serverTimezone=UTC","root","root");
        //实例化 PraparedStatement对象
        String sql = "SELECT * FROM users WHERE id = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);

        int id = 2; // 假设你要查询id为2a的用户
        //设置占位符为id变量
        preparedStatement.setInt(1, id);

        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id") + " username: " + rs.getString("username") + " password: " + rs.getString("password"));
        }
    }
}

3.MyBatis执行SQL语句
导入依赖(参考文章 https://blog.csdn.net/weixin_45991687/article/details/129351818 )

MyBatis存储SQL语句(UserMapper.java)

public interface UserMapper {
    User getUser(int id);
}

MyBatis映射存储SQL语句(UserMapper.xml)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="UserMapper">
    <select id="getUser" resultType="User">
        SELECT * FROM users WHERE id = #{id}
    </select>
</mapper>

定义MyBatis配置文件(mybatis-config.xml)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/security"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="UserMapper.xml"/>
    </mappers>
</configuration>

定义主体代码(MyBatisSQL.java)

import java.io.IOException;
import java.io.InputStream;
import java.util.Scanner;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisSQL {
    public static void main(String[] args) {
        // 加载 MyBatis 配置文件
        String resource = "mybatis-config.xml";
        InputStream inputStream;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }

        // 创建 SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        // 创建 SqlSession
        try (SqlSession session = sqlSessionFactory.openSession()) {
            // 从控制台输入要查询的用户 ID
            Scanner scanner = new Scanner(System.in);
            System.out.print("请输入要查询的用户 ID：");
            int userId = scanner.nextInt();
            scanner.close();

            // 执行 SQL 查询语句
            User user = session.selectOne("UserMapper.getUser", userId);

            // 输出结果
            if (user != null) {
                System.out.println("用户信息：");
                System.out.println("ID: " + user.getId());
                System.out.println("用户名: " + user.getUsername());
                System.out.println("密码: " + user.getPassword());
            } else {
                System.out.println("未找到该用户");
            }
        }
    }
}

4.Hibernate
下载并导入jar包 (lib\required里所有的jar包)
https://hibernate.org/orm/releases/5.2/
hibernate.cfg.xml 其中org.hibernate.dialect.MySQL5Dialect中的数字为mysql版本,请根据自己的mysql版本进行调整

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/security</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">root</property>
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.hbm2ddl.auto">update</property>
        <mapping class="User"/>
    </session-factory>
</hibernate-configuration>

User.java

public class User {
    private int id;
    private String username;
    private String password;

    public User() {
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

HibernateSQL.java

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.query.Query;
import java.util.logging.Logger;
import java.util.logging.Level;

import java.util.List;

public class HibernateSQL {

    public static void main(String[] args) {

        // 关闭 Hibernate 日志输出
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        // 关闭 MySQL 驱动程序的日志输出
        Logger.getLogger("com.mysql").setLevel(Level.OFF);
        // 创建Hibernate配置对象
        Configuration configuration = new Configuration().configure("hibernate.cfg.xml");
        // 创建SessionFactory
        SessionFactory sessionFactory = configuration.buildSessionFactory();
        // 创建Session
        Session session = sessionFactory.openSession();

        try {
            // 开启事务
            session.beginTransaction();

            // 执行查询语句
            String queryString = "SELECT * FROM Users u WHERE u.id = :id";
            Query query = session.createNativeQuery(queryString);
            query.setParameter("id", 2); // 设置参数名称和参数值
            List<Object[]> users = query.list();

            // 打印查询结果
            for (Object[] userData : users) {
                int id = (int) userData[0];
                String username = (String) userData[1];
                String password =(String) userData[2];
                System.out.println("ID: " + id);
                System.out.println("Username: "+username);
                System.out.println("Password: " + password);
            }

            // 提交事务
            session.getTransaction().commit();
        } catch (Exception e) {
            // 回滚事务
            session.getTransaction().rollback();
            e.printStackTrace();
        } finally {
            // 关闭Session
            session.close();
        }
    }
}

常见SQL注入

1.SQL语句参数直接动态拼接

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StatementSQL {
    public static void main(String[] args) throws ClassNotFoundException , SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/security?&useSSL=false&serverTimezone=UTC", "root", "root");
        String id = "2";
        String sql = "select * from users where id = " + id;
        Statement ps = conn.createStatement();
        ResultSet rs = ps.executeQuery(sql);
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id") + " username: " + rs.getString("username") + " password: " + rs.getString("password"));
        }
    }
}

如果输入"2 or 1=1"的话会返回全部内容

2.预编译有误
上面上述了实验Statement执行SQL语句存在动态拼接的SQL注入漏洞,那么如果使用PrepareStatement执行SQL语句就一定能防止SQL注入吗
答案是否定的,在预编译语句中使用错误的编程方式,也可能会产生SQL注入漏洞

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class PreparedStatementSQL2 {
    public static void main(String[] args) throws ClassNotFoundException , SQLException{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/security?&useSSL=false&serverTimezone=UTC","root","root");

        String username = "user%' or '1'='1'#";
        int id = 2;

        String sql = "SELECT * FROM users WHERE id = ?";
        sql+= " and username like '%" + username + "%'";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setInt(1, id);
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id") + " username: " + rs.getString("username") + " password: " + rs.getString("password"));
        }
    }
}

虽然前面使用了预编译,但是后面使用了变量拼接的方式
3.order by 注入
是否在预编译语句中按规范编程就能方法SQL注入?答案也是否定的,因为在某些特殊情况下不能使用PrepareStatement,比如order by字句排序
这是因为order by字句后面需要加字段名或者字段位置,二字段名是不能带引号的,否则就会被认为是一个字符串而不是字段名
PrepareStatement是使用占位符传入参数的,传递的参数就会有单引号包裹,这样会导致order by语句失效,所以order by语句只能使用拼接

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class StatementSQL {
    public static void main(String[] args) throws ClassNotFoundException , SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/security?&useSSL=false&serverTimezone=UTC", "root", "root");
        String id = "2 or 1=1";
        String sql = "select * from users order by " + id;
        Statement ps = conn.createStatement();
        ResultSet rs = ps.executeQuery(sql);
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id") + " username: " + rs.getString("username") + " password: " + rs.getString("password"));
        }
    }
}

4.%和_模糊查询
在java预编译中不会对%和_进行转义处理,而%和_刚好是like查询的通配符,因此可能会造成恶意查询

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.PreparedStatement;

public class PreparedStatementSQL2 {
    public static void main(String[] args) throws ClassNotFoundException , SQLException{
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/security?&useSSL=false&serverTimezone=UTC","root","root");
        String username = "%%";
        String sql = "SELECT * FROM users WHERE username like ?";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setString(1, username);
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            System.out.println("id: " + rs.getInt("id") + " username: " + rs.getString("username") + " password: " + rs.getString("password"));
        }
    }
}

5.MyBatis中#{}和${}的区别
#{}在底层实现上使用'?'作为占位符来生成PreparedStatement,也就是预编译
${}则相当于字符串拼接
此时将UserMapper.xml改为这样的话

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="UserMapper">
    <select id="getUser" resultType="User">
        SELECT * FROM users WHERE id = ${id}
    </select>
</mapper>

User.java内容不变

public class User {
    private int id;
    private String username;
    private String password;

    public User() {
    }

    public User(int id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

然后MyBatisSQL.java

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisSQL {
    public static void main(String[] args) throws ClassNotFoundException {
        // 加载 MyBatis 配置文件
        String resource = "mybatis-config.xml";
        InputStream inputStream;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
            return;
        }

        // 创建 SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        // 创建 SqlSession
        try (SqlSession session = sqlSessionFactory.openSession()) {

            String userId = "2 or 1=1";

            // 执行 SQL 查询语句
            List<User> userList = session.selectList("UserMapper.getUser", userId);

            // 输出结果
            if (!userList.isEmpty()) {
                System.out.println("用户信息：");
                for (User user : userList) {
                    System.out.println("ID: " + user.getId());
                    System.out.println("用户名: " + user.getUsername());
                    System.out.println("密码: " + user.getPassword());
                    System.out.println();
                }
            } else {
                System.out.println("未找到该用户");
            }
        }
    }
}

如果把${}改回#{}的话,看到虽然还是2 or 1=1,但是仍然只查询了2

在order by查询的时候只能用${}

SELECT * FROM users order by ${id}

在like查询的时使用#{}或报错

SELECT * FROM users where username like '%#{username}%'

然后主代码稍微改一下

String username = "A";
List<User> userList = session.selectList("UserMapper.getUser", username);

报错

此时把#{}改为${}
成功查询,但有很大的SQL注入威胁

在in查询的时候

SELECT * FROM users where username in (#{username})

修改一下主代码

import java.util.HashMap;
import java.util.Map;

...

String usernames = "'Angelina','superman'";
// 构建参数对象
Map<String, Object> parameter = new HashMap<>();
parameter.put("usernames", usernames);
// 执行 SQL 查询语句
List<User> userList = session.selectList("UserMapper.getUser", parameter);

因为在使用#{}时候,会把usernames数组当成一个整体,也就是说查询了in "Angelinasuperman",显然偏离了原先的逻辑
但是如果#{}是这种情况的话就没问题了

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="UserMapper">
    <!-- SQL语句定义 -->
    <select id="getUser" parameterType="java.util.Map" resultType="User">
        SELECT * FROM users WHERE username IN
        <foreach collection="usernames" item="username" open="(" close=")" separator=",">
            #{username}
        </foreach>
    </select>
</mapper>

但如果是这样的话可能会存在SQL注入漏洞

<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="UserMapper">
    <!-- SQL语句定义 -->
    <select id="getUser" parameterType="java.util.Map" resultType="User">
        SELECT * FROM users WHERE username IN (${usernames})
    </select>
</mapper>

代码审计技巧

1.搜索'${'
2.搜索Statement,createStatement,PrepareStatement
3.搜索order by,in,like或者in (${,like '% ${