数据库(DataBase)-SQL注入问题代码举例

SQL注入问题

sql存在漏洞，容易导致数据泄露
本文数据库：

CREATE DATABASE JdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE JdbcStudy;

CREATE TABLE `users`(
	`id` INT PRIMARY KEY,
	`NAME` VARCHAR(40),
	`PASSWORD` VARCHAR(40),
	`email` VARCHAR(60),
	`birthday` DATE
);

INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES
(1,'ALita','123456','Alita@qq.com','2022-12-04'),
(2,'Edwin','456798','Edwin@qq.com','2000-12-04'),
(3,'Jarvis','789123','Jarvis@qq.com','2000-12-04');

select * from user;

配置文件：

driver = com.mysql.jdbc.Driver

url = jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true&characterEncoding = utf8&useSSL = true

username = root

password = 1234

工具类：

package com.Edwin.lession02.utils;

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

/**
 * @author Edwin D
 * @date 2020.5.21 上午 9:22
 */
public class JdbcUtils {

    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

//            驱动只需加载一次即可。
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

//        获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

//    释放资源
    public static void release(Connection con, Statement sta, ResultSet res){
        if (res != null) {
            try {
                res.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (sta != null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (con != null) {
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

操作代码：

package com.Edwin.lession02;
import com.Edwin.lession02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
/**
 * @author Edwin D
 * @date 2020.5.21 下午 1:01
 */
public class Sql注入 {

    public static void main(String[] args) {
//        普通登录
//        login("Edwin","456798");
//        攻击漏洞型登录
        login(" ' or '1=1","789123 ' or ' 1=1");
    }
    //    登录业务
    public static void login(String username, String password) {
        Connection con = null;
        Statement sta = null;
        ResultSet res = null;

        try {
            con = JdbcUtils.getConnection(); // 获取数据库连接。
            sta = con.createStatement(); // 获取Sql的执行对象。

//        Sql:select * from `users` where `name` = 'Edwin' and `password` = '123456';
            String sql = "select * from `users` where `name` = '" + username + "' and `password` = '" + password + "'";

            res = sta.executeQuery(sql);//查询完毕返回一个结果集
            while (res.next()) {
                System.out.println("id = " + res.getObject("id"));
                System.out.println("name = " + res.getObject("name"));
                System.out.println("password = " + res.getObject("password"));
                System.out.println("email = " + res.getObject("email"));
                System.out.println("birthday = " + res.getObject("birthday"));
                System.out.println("+_+-+_+-+_+-+_+-+_+-+_+-");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,sta,res);
        }
    }
}

普通登录查询结果：

利用漏洞的查询结果：

整个数据库里面的数据都被查了出来，存在着极大的安全隐患。

5.防范Sql注入问题

package com.Edwin.lession03;
import com.Edwin.lession02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
/**
 * @author Edwin D
 * @date 2020.5.21 下午 5:39
 */
public class Sql注入2 {
    public static void main(String[] args) {
//        普通登录
//        login("Edwin","456798");
//        攻击漏洞型登录
        login("'' or 1=1","'' or 1=1");
    }

    //    登录业务
    public static void login(String username, String password) {
        Connection con = null;
        PreparedStatement pres = null;
        ResultSet res = null;

        try {
            con = JdbcUtils.getConnection(); // 获取数据库连接。

//        PreparedStatement 防止Sql注入的本质：把传递进来的参数当做字符.
//        传进来的东西会自带一个 '' 包裹起来，再使用之前的 ' 来通过漏洞来进行操作，会被转义。
            String sql = "select * from `users` where `name`=? and `password`=?";

            pres = con.prepareStatement(sql);
            pres.setString(1, username);
            pres.setString(2, password);

            res = pres.executeQuery();//查询完毕返回一个结果集
            while (res.next()) {
                System.out.println("id = " + res.getObject("id"));
                System.out.println("name = " + res.getObject("name"));
                System.out.println("password = " + res.getObject("password"));
                System.out.println("email = " + res.getObject("email"));
                System.out.println("birthday = " + res.getObject("birthday"));
                System.out.println("+_+-+_+-+_+-+_+-+_+-+_+-");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,pres,res);
        }
    }
}

login(“Edwin”,“456798”);执行效果：

login("’’ or 1=1","’’ or 1=1");执行效果

参考文献

《【狂神说Java】MySQL最新教程通俗易懂》
视频连接：https://www.bilibili.com/video/BV1NJ411J79W

2020.05.24