12.SQL注入、PreparedStatement对象解决SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

1.代码：

public class JdbcUtils {
    private static final String DRIVER="com.mysql.jdbc.Driver";
    private static final String URL="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8";
    private static final String USERNAME="root";
    private static final String PASSWORD="root";
    public static Connection getConn(){
        Connection conn=null;
        try {
            Class.forName(DRIVER);
            conn= DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }
    public static void closeAll(Connection conn, Statement st, ResultSet rs) throws SQLException {
        if (rs!=null){
            rs.close();
        }
        if (st!=null){
            st.close();
        }
        if (conn!=null){
            conn.close();
        }
    }
}

public class SqlInject {
    public static void main(String[] args) throws SQLException {
        login("zhangsan","123456");
    }
    // 登录业务
    public static void login(String username,String pwd) throws SQLException {
        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConn();
            st = conn.createStatement();

            //SQL
            String sql="select * from users where `NAME`='"+username+"' AND `password`='"+pwd+"'";
            st.executeQuery(sql);       //查询
            //查询完毕返回结果集
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.closeAll(conn,st,rs);
        }
    }
}

成功：

SQL注入：

//SQL注入
login(" 'or '1=1"," 'or '1=1");

全部用户的名称和密码都输出了：
原因：
将代码完整拼接：

SELECT * FROM users WHERE `NAME` = '' or 1=1 AND `password`='' or 1=1

name，password等于空，或者1=1，因为1=1恒为true，所以name和password为true，因此SQL语句能够成功执行

2.解决SQL注入：preparedStatement对象

PreparedStatement防止SQL注入的本质：把传递进来的参数当做字符，假设其中存在转义字符，会被直接转义
(一)、新增

public class PreparedStatementInsert {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement st=null;
        try {
            conn = JdbcUtils.getConn();
            //区别:
            //1.使用占位符"?"代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES(?,?,?,?,?)";
            //2.没有createStatement，直接prepareStatement
            //预编译SQL，先写SQL，但是不执行
            st = conn.prepareStatement(sql);
            //手动给参数赋值
            st.setInt(1,4);             //st.setInt(parameterIndex,int x);
            st.setString(2,"giaogiao"); //st.setString(parameterIndex,String x);
            st.setString(3,"332786");
            st.setString(4,"823659823@qq.com");
            //sql.Date  数据库   java.sql.Date()
            //util.Date Java    new java.util.Date().getTime() 获得时间戳
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            //执行
            //3.statement：executeUpdate(sql);prepareStatement:预编译，设置参数，直接调用执行方法
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeAll(conn,st,null);
        }
    }
}

(二)、删除

public class PreparedStatementDelete {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement st=null;
        try {
            conn = JdbcUtils.getConn();
            String sql = "delete from  users where id=?";
            st = conn.prepareStatement(sql);
            st.setInt(1,4);
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeAll(conn,st,null);
        }
    }
}

(三)、更新

public class PreparedStatementUpdate {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement st=null;
        try {
            conn = JdbcUtils.getConn();
            String sql = "update users set name = ? where id = ?;";
            st = conn.prepareStatement(sql);
            st.setString(1,"giao1");
            st.setInt(2,6);
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeAll(conn,st,null);
        }
    }
}

(四)、查询

public class PreparedStatementSelect {
    public static void main(String[] args) throws SQLException {
        Connection conn=null;
        PreparedStatement st;
        st = null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConn();
            //编写sql
            String sql="select * from users where id = ?";
            //预编译
            st=conn.prepareStatement(sql);
            //传递参数
            st.setInt(1,1);
            //执行
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.closeAll(conn,st,rs);
        }
    }
}

(五)、防止SQL注入

public class KillSqlInject {
    public static void main(String[] args) throws SQLException {
        //正常登录
//        login("zhangsan","123456");
        //SQL注入
        login(" 'or '1=1"," 'or '1=1");		//不会输出
    }
    // 登录业务
    public static void login(String username,String pwd) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            //获取数据库连接
            conn= JdbcUtils.getConn();
            //获得SQL执行对象
            String sql= "select * from users where  `name`=? and `password`=?;";
            st=conn.prepareStatement(sql);
            st.setString(1,username);
            st.setString(2,pwd);

            rs=st.executeQuery();
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.closeAll(conn,st,rs);
        }
    }
}