JDBC MySQL ORM 实现登录功能（避免SQL注入）

忘是亡心i 2024-03-23 10:34 33阅读 0赞

**目录**

一、创建数据库表

二、创建Users类

三、登录类

四、存在的漏洞

（1）SQL注入

（2）分析

（3）补救

--------------------

## 一、创建数据库表 ##

CREATE DATABASE jdbcdatabase;
    
    
    CREATE TABLE IF NOT EXISTS `user`(
    `uid` INT PRIMARY KEY AUTO_INCREMENT,
    `username` VARCHAR(10) UNIQUE NOT NULL,
    `password` VARCHAR(10) NOT NULL,
    `phone` VARCHAR(11) 
    );
    
    INSERT INTO `user`(`uid`,`username`,`password`,`phone`) VALUES(1001,'zhangsan','123456','13112345678');
    
    INSERT INTO `user`(`uid`,`username`,`password`,`phone`) VALUES(1002,'lisi','123456','13844445555');

## 二、创建Users类 ##

public class Users {
        // 存放从数据库取出的字段
        private int uid;
        private String username;
        private String password;
        private String phone;
    
    
        public Users() {
        }
    
        public Users(int uid, String username, String password, String phone) {
            this.uid = uid;
            this.username = username;
            this.password = password;
            this.phone = phone;
        }
    
        public Users(String username,String password){
            this.username = username;
            this.password = password;
        }
    
        /**
         * 获取
         * @return uid
         */
        public int getUid() {
            return uid;
        }
    
        /**
         * 设置
         * @param uid
         */
        public void setUid(int uid) {
            this.uid = uid;
        }
    
        /**
         * 获取
         * @return username
         */
        public String getUsername() {
            return username;
        }
    
        /**
         * 设置
         * @param username
         */
        public void setUsername(String username) {
            this.username = username;
        }
    
        /**
         * 获取
         * @return password
         */
        public String getPassword() {
            return password;
        }
    
        /**
         * 设置
         * @param password
         */
        public void setPassword(String password) {
            this.password = password;
        }
    
        /**
         * 获取
         * @return phone
         */
        public String getPhone() {
            return phone;
        }
    
        /**
         * 设置
         * @param phone
         */
        public void setPhone(String phone) {
            this.phone = phone;
        }
    
        public String toString() {
            return "Users{uid = " + uid + ", username = " + username + ", password = " + password + ", phone = " + phone + "}";
        }
    }

## 三、登录类 ##

(1)代码

细节，流程都在注释里。

import java.sql.*;
    import java.util.Scanner;
    
    public class Login {
        public static void main(String[] args) {
            // 1.加载驱动
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(e);
            }
    
            // 2.准备所需数据
            String url = "jdbc:mysql://127.0.0.1:3306/jdbcdatabase";
            String user = "root";
            String password = "123456";
            // 2.1 提示用户输入
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入账号：");
            String un = scanner.nextLine();
            System.out.println("请输入密码：");
            String pw = scanner.nextLine();
            // 2.2 创建Users对象接受数据
            Users users = new Users(un, pw);
            // 2.3写入SQL语句
            String sql = "select password from user where username = '" + users.getUsername() + "'";
    
            try (
                    // 3.获取连接对象
                    Connection connection = DriverManager.getConnection(url, user, password);
                    // 4.获取发送对象
                    Statement statement = connection.createStatement()) {
                // 5.执行SQL
                ResultSet resultSet = statement.executeQuery(sql);
                // 6.判断是否存在该账号
                if (resultSet.next()){
                    if (resultSet.getString(1).equals(pw)){
                        System.out.println("登录成功！");
                    }else{
                        System.out.println("密码错误！");
                    }
                }else {
                    System.out.println("该账号不存在，请注册！");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
    
        }
    }

（2）运行结果图

![fa61b06d0adc4127882909ffe0831686.png][]

![f27c9aa7be754ee3a9eea06f06e12670.png][]

## 四、存在的漏洞 ##

### （1）SQL注入 ###

即，当输入账号时，特意将SQL关键字输入，找到其漏洞，进行非法登录，如

当账号为 任意字母' or 1=1;\# ，密码为正确时，返回登录成功。

![d67b500921e84952bd20fa48cca06a96.png][]

### （2）分析 ###

上述代码中的SQL语句为：

"select password from user where username = '" + users.getUsername() + "'";

当输入 f' or 1=1;\#时，我们做一个替换，得到

"select password from user where username = 'f' or 1=1;#'";

解读为，从表user中查询password，过滤条件是当username为'f'，或着 1=1，

紧接着分号结束，\#后面为注释则不执行，而1=1永远成立，所以查询结果返回所有密码

当输入的密码正确时，就登录成功了。

### （3）补救 ###

使用prepareStatement()方法改写代码

import java.sql.*;
    import java.util.Scanner;
    
    public class Login {
        public static void main(String[] args) {
            // 1.加载驱动
            try {
                Class.forName("com.mysql.jdbc.Driver");
            } catch (ClassNotFoundException e) {
                throw new RuntimeException(e);
            }
    
            // 2.准备所需数据
            String url = "jdbc:mysql://127.0.0.1:3306/jdbcdatabase";
            String user = "root";
            String password = "123456";
            // 2.1 提示用户输入
            Scanner scanner = new Scanner(System.in);
            System.out.println("请输入账号：");
            String un = scanner.nextLine();
            System.out.println("请输入密码：");
            String pw = scanner.nextLine();
            // 2.2 创建Users对象接受数据
            Users users = new Users(un, pw);
            // 2.3写入SQL语句 避免SQL注入 此处使用？占位符
            String sql = "select password from user where username = ?";
    
            try (
                    // 3.获取连接对象
                    Connection connection = DriverManager.getConnection(url, user, password);
                    // 4.获取发送对象
                    PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
                // 5.给占位符赋值
                preparedStatement.setString(1,users.getUsername());
                // 5.执行SQL
                ResultSet resultSet = preparedStatement.executeQuery();
                // 6.判断是否存在该账号
                if (resultSet.next()){
                    if (resultSet.getString(1).equals(pw)){
                        System.out.println("登录成功！");
                    }else{
                        System.out.println("密码错误！");
                    }
                }else {
                    System.out.println("该账号不存在，请注册！");
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
    
        }
    }

运行结果：

![f9a86f9f48f944a1b612c687bbc7e706.png][]

[fa61b06d0adc4127882909ffe0831686.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/939843f175414c48b211c6a3d5772efb.png
[f27c9aa7be754ee3a9eea06f06e12670.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/f47c2c7ec26f475985270c6a3065e5aa.png
[d67b500921e84952bd20fa48cca06a96.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/1ce287ce608548828179fc13d11ee467.png
[f9a86f9f48f944a1b612c687bbc7e706.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/2ff2a42a157049f8bde6e95cc94a5608.png