SQL注入与PreparedStatement对象

SQL注入   

         sql存在漏洞，会被攻击导致数据泄露

例：

        登录

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


public class SQLQuestion {
    public static void main(String[] args) {

        //正常登录
//        login("张三","1234567");

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

    }

    /**
     * 登录业务
     */
    public static void login(String userName, String password) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JDBCUtils.getConnection();
            st = con.createStatement();
            String sql = "SELECT * FROM users WHERE `name`='"+userName+"' AND `password`='"+password+"'";
            // SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456'
            System.out.println(sql);
            rs = st.executeQuery(sql);
            while (rs.next()) {
                System.out.println("id="+rs.getInt("id"));
                System.out.println("name="+rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.release(con, st, rs);
        }
    }
}

 

PreparedStatement对象

        PreparedStatement可以防止SQL注入，效率更好

例：

        新增

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

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = JdbcUtils.getConnection();
            // PreparedStatement 与 Statement 的区别
            //使用 ? 占位符替代
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)";//预编译，SQL,先写SQL，然后不执行
            preparedStatement = connection.prepareStatement(sql);
            //手动给参数赋值
            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"牛六");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"niuliu@sina.com");
            preparedStatement.setDate(5, new java.sql.Date(new java.util.Date().getTime()));
            int num = preparedStatement.executeUpdate();
            if(num > 0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

        删除

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

public class TestDelete {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;

        try {
            con = JdbcUtils.getConnection();
            String sql = "DELETE FROM users WHERE `id` = ?";
            st = con.prepareStatement(sql);
            st.setInt(1,5);

            int num = st.executeUpdate();
            if(num > 0){
                System.out.println("删除成功");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,null);
        }
    }
}

        更新

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;

        try {
            con = JdbcUtils.getConnection();
            String sql = "UPDATE users SET birthday=? WHERE id=?";
            st = con.prepareStatement(sql);
            st.setDate(1, new java.sql.Date(new java.util.Date().getTime()));
            st.setInt(2,1);
            int num = st.executeUpdate();
            if(num > 0){
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,null);
        }
    }
}

        查看

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

public class TestSelect {
    public static void main(String[] args) {
        Connection con = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            con = JdbcUtils.getConnection();
            String sql = "select * from users where `id` = ?";
            st = con.prepareStatement(sql);
            st.setInt(1,1);
            rs = st.executeQuery();
            while (rs.next()){
                System.out.println("id="+ rs.getInt("id"));
                System.out.println("name="+ rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }

    }
}

防止SQL注入（登录）

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {
        //正常登录
        login("张三","123456");
        //SQL注入
        login("' or '1=1","123456");
    }

    public static void login(String userName,String password){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
            // 假设其中存在转义字符，比如说'会被直接转义
            String sql =  "SELECT * FROM users WHERE `name`=? AND `password`=?";
//          SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456'
//            System.out.println(sql);
            statement = connection.prepareStatement(sql);
            statement.setString(1,userName);
            statement.setString(2,password);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                System.out.println("id="+resultSet.getInt("id"));
                System.out.println("name="+resultSet.getString("name"));
                System.out.println("=============");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }

}

 

小结：

        PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符