6. Use JDBC to complete login cases and SQL injection issues

6. Use JDBC to complete login cases and SQL injection issues

Preface

In the previous chapters, we have learned to use statement to perform database add, delete, check and modify operations, and encapsulate a JDBC tool class to achieve database connection acquisition and resource closing methods.

Let's use the statement to implement a simple user login case and bring up a SQL injection problem.

Case-Login Case

1. Demand

Enter the user name and password in the console to query the database. If there is a current user in the database, the login is successful!

If the current user does not exist in the database, login failure is displayed!

img

2 analysis

2.1 What does login do

To put it bluntly, login is to query the database based on the user name and password. If it can be queried, the login is successful, and if the query fails, the login failed

2.2 Thinking analysis

image-20191202144713867

3. Code implementation

3.1 Then first we need to prepare the mysql data generated in the previous chapter, as follows:

mysql> select * from t_user;+----+-------+------+------+| id | uname | age  | sex  |+----+-------+------+------+|  1 | zs    |   18 |    1 ||  2 | ls    |   20 |    0 ||  3 | ww    |   23 |    1 ||  4 | zl    |   24 |    1 ||  5 | lq    |   15 |    0 ||  6 | hh    |   12 |    0 ||  7 | wzx   |   60 | NULL ||  8 | lb    | NULL | NULL |+----+-------+------+------+8 rows in set (0.00 sec) mysql> 

3.2 Prepare the JdbcUtil tool class packaged in the previous chapter

Create a configuration file, the configuration file is in the resources directory, and the resources directory should be marked as the root path of the resource file, the extension is properties

1537261759376

Configuration file:

jdbc.username=rootjdbc.password=Li********密码**********0jdbc.url=jdbc:mysql://localhost:3306/testdb?rewriteBatchedStatements=true&serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=falsejdbc.driverClass=com.mysql.jdbc.Driver

The tools are as follows:

image-20210121000203107

package com.utils; import java.io.InputStream;import java.sql.*;import java.util.Properties;import java.util.ResourceBundle; /** * * @Description:JdbcUtils 工具类 * 1.抽取: 复用重复的代码 *      方式: 把重复代码抽取到父类, 把公共代码抽取成方法, 把公共代码抽取到工具类 *      原则: 抽取重复的 * 2.选择把公共代码抽取到工具类 *      找重复的: (1,2 -- 获得连接的方法), (5-- 释放资源的方法) *   思路: *      定义一个类 *      在这个类里面定义两个方法 * 3. 工具类优化 *      把四个参数(驱动,路径,用户名,密码)抽取成 jdbc.properties *      在static{}里面读取jdbc.properties, 给变量赋值 * * @author Aron.li * @date 2021/1/20 7:59 */public class JdbcUtils {     private static String driverClass;    private static String url;    private static String username;    private static String password;     //驱动只需要注册一次,把注册驱动的代码放在静态代码块    static {        //静态代码块就会在类加载的时候执行        try {            //读取jdbc.properties配置文件中的内容            //1. 将配置文件转换成字节输入流,使用类加载器            InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");            //2. 创建一个Properties对象            Properties properties = new Properties();            //3. properties对象加载配置文件            properties.load(is);             //4.调用properties的方法,根据key获取值            username = properties.getProperty("jdbc.username");            password = properties.getProperty("jdbc.password");            url = properties.getProperty("jdbc.url");            driverClass = properties.getProperty("jdbc.driverClass");             //5.注册驱动            Class.forName(driverClass);        } catch (Exception e) {            e.printStackTrace();        }     }     /**     * 获得连接     *     */    public static Connection getConnection() {        //2. 获得连接        Connection connection = null;        try {            connection = DriverManager.getConnection(url, username, password);        } catch (SQLException e) {            e.printStackTrace();            throw new RuntimeException(e.getMessage()); // 抛出运行时异常        }        return connection;    }     /**     * 释放资源     *     * @param resultSet     * @param statement     * @param connection     */    public static void closeAll(ResultSet resultSet, Statement statement, Connection connection) {        try {            if (resultSet != null) {                resultSet.close();            }             if (statement != null) {                statement.close();            }            if (connection != null) {                connection.close();            }        } catch (SQLException e) {            e.printStackTrace();            throw new RuntimeException(e.getMessage()); // 抛出运行时异常        }     }     public static void closeAll(Statement statement, Connection connection) throws SQLException {        closeAll(null, statement, connection);    } }

3.3 Create a login test class LoginClient

image-20210121001514776

public class LoginClient {     /**     * 使用 JDBC 实现登录案例     */    public static void main(String[] args) throws SQLException {         //1. 获取用户输入的 用户名        System.out.println("请输入的用户名: ");        Scanner scanner = new Scanner(System.in);        String username = scanner.nextLine();        System.out.println("输入的用户名: " + username);         //2. 获取数据库连接        Connection connection = JdbcUtils.getConnection();         //3. 根据用户名执行数据查询        String sql = "select * from t_user where uname = '" + username + "'"; // 设置SQL        Statement statement = connection.createStatement(); // 获取statement        ResultSet resultSet = statement.executeQuery(sql); // 执行查询SQL         //4. 如果查询存在用户名的数据,那么则登录成功;反之,登录失败        if (resultSet.next()) {            System.out.println("登录成功");        } else {            System.out.println("登录失败");        }    } }

3.4 Perform test login as follows:

4. Summary

4.1 login ideas

Log in to put it bluntly is to query the database based on the user name and password

Login Ideas

Log in successfully print'login successful'

Login failed print login failed'

Get the user name and password entered by the user

Use Jdbc to query the database based on the user name and password and package it into a User object

Determine whether the login is successful (judge whether User is null)

5. Use statement to execute SQL, there will be SQL injection problems

5.1 SQL injection problems appear

When the password is entered   ' or '' = ', it is found that the login is always successful.

Below we first demonstrate the situation of inquiring a non-existent user name and login failure, as follows:

Then stitching ' or '' = 'string will be successful login

5.2 SQL injection problem analysis

The entered password 'or'' =', the statement is as follows

-- 当查询 hello 的用户名,则会查询无数据mysql> select * from t_user where uname = 'hello';Empty set (0.00 sec)-- 当在查询条件后面加上 or '' = '' ,那么则可以查询出所有数据mysql> select * from t_user where uname = 'hello' or '' = '';+----+-------+------+------+| id | uname | age  | sex  |+----+-------+------+------+|  1 | zs    |   18 |    1 ||  2 | ls    |   20 |    0 ||  3 | ww    |   23 |    1 ||  4 | zl    |   24 |    1 ||  5 | lq    |   15 |    0 ||  6 | hh    |   12 |    0 ||  7 | wzx   |   60 | NULL ||  8 | lb    | NULL | NULL |+----+-------+------+------+8 rows in set (0.00 sec) mysql> 

Similar SQL that can cause problems is as follows:

select * from t_user where uname = 'hello' or '' = '';select * from t_user where uname = 'hello' or true;select * from t_user where  true;select * from t_user;

Found that the statement has SQL injection problems

This is because when the statement uses the splicing sql string, the or entered by the user is used as a keyword and injected into the sql statement.

So how to solve this problem? We will use preparedStatement to solve the problem of SQL injection in the next chapter.