MySQL-15-SQL注入和PrepareStatement对象

MySQL-15-SQL注入和PrepareStatement对象

SQL注入

sql存在漏洞,会被攻击导致数据泄露(sql会被拼接主要由于sql语句中or运算符的存在)

本例工具类,接上篇文章

package com.cmy.lesson2;

import com.cmy.lesson2.utils.JdbcUtils;

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

/**
 * @Author Void
 * @Description //TODO 测试sql注入
 * @Date 12:04 2021/3/10
 * @Param
 * @return
 **/
public class SqlInjection {
    public static void main(String[] args) {
        login("'or '1=1","'or '1=1");
    }

    public static void login(String username,String password) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            //select * from `users` where `NAME` = ''or '1=1' AND `PASSWORD` = ''or '1=1'(拼接后查出全部)
            String sql =
                    "select * from `users` where `NAME` = '" +username+ "' AND `PASSWORD` = '"+password+"'";

            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println("name = "+rs.getString("NAME"));
                System.out.println("password = "+rs.getObject("PASSWORD"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}   

Statement并不安全,由此引入PrepareStatement

PrepareStatement对象

可以防止sql注入,且效率更高,写sql语句更加友好

模板

import com.cmy.lesson2.utils.JdbcUtils;

import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Test {

    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;//查询一般放回结果集,
        try {
            conn = JdbcUtils.getConnection();
            //与statement 的区别
            //先搭建sql框架(预编译sql),并使用?充当占位符,
            String sql  = "";
            pst  = conn.prepareStatement(sql);//预编译sql
            //利用占位符传递参数 (第几个参数,值)
            pst.setInt(1,value);
            //...
            //执行 这里不再传参(预编译过了)
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("xx成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}

插入,删除,修改

......
        try {
            conn = JdbcUtils.getConnection();
       
            //与statement 的区别
            //先搭建sql框架(预编译sql),并使用?充当占位符,
            String sql  = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
            pst  = conn.prepareStatement(sql);//预编译sql

           //利用占位符传递参数 (第几个参数,值)
            pst.setInt(1,4);
            pst.setString(2,"Void");
            pst.setString(3,"123456");
            pst.setString(4,"2603135842@qq.com");
            // 注意 java.sql.Date 数据库用
            //     util.Date     Java  new Date().getTime()时间戳
            pst.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行 这里不在传参(预编译过了)
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        }
......

......			
		try {
            conn = JdbcUtils.getConnection();
            //与statement 的区别
            //先搭建sql框架(预编译sql),并使用?充当占位符,
            String sql  = "delete from `users` where id = ?";
            pst  = conn.prepareStatement(sql);//预编译sql
           //利用占位符传递参数 (第几个参数,值)
            pst.setInt(1,4);
            //执行 这里不在传参(预编译过了)
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }
        }
......

......
		try {
            conn = JdbcUtils.getConnection();
            //与statement 的区别
            //先搭建sql框架(预编译sql),并使用?充当占位符,
            String sql  = "UPDATE `users` SET `NAME` = ? WHERE `NAME` = ?";
            pst  = conn.prepareStatement(sql);//预编译sql
            //利用占位符传递参数 (第几个参数,值)
            pst.setString(1,"王五");
            pst.setString(2,"wangwu");

            //执行 这里不再传参(预编译过了)
            int i = pst.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }
        }
......

查询

...... 
		try {
            conn = JdbcUtils.getConnection();
            //与statement 的区别
            //先搭建sql框架(预编译sql),并使用?充当占位符,
            String sql  = "select * from `users` where id = ?";
            pst  = conn.prepareStatement(sql);//预编译sql
            //利用占位符传递参数 (第几个参数,值)
            pst.setInt(1,1);

            //执行 这里不再传参(预编译过了)
            rs = pst.executeQuery();
            while(rs.next()){
                System.out.println("name = "+rs.getString("NAME"));
                System.out.println("password = "+rs.getString("PASSWORD"));
            }

        }
......

防SQL注入原理

package com.cmy.lesson03;

import com.cmy.lesson2.utils.JdbcUtils;

import java.sql.*;

/**
 * @Author Void
 * @Description //TODO 测试prepareStatement sql注入
 * @Date 13:50 2021/3/10
 * @Param 
 * @return 
 **/
public class SqlInjection {
    public static void main(String[] args) {
//        login("zhansan","123456");
        login("'' or 1=1 ","'' or 1=1 ");//无效
        //原因是,prepareStatement将所有传入的参数转为字符,若其中存在转义字符,直接忽略:比如`会被直接转义

    }

    public static void login(String username,String password) {
        Connection conn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //select * from `users` where `NAME` = ''or '1=1' AND `PASSWORD` = ''or '1=1'(拼接后查出全部)
            String sql = "select * from `users` where `NAME` = ? AND `PASSWORD` = ?";
            pst = conn.prepareStatement(sql);
            pst.setString(1,username);
            pst.setString(2,password);

            rs = pst.executeQuery();
            while(rs.next()){
                System.out.println("name = "+rs.getString("NAME"));
                System.out.println("password = "+rs.getString("PASSWORD"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,pst,rs);
        }
    }
}