jdbc中的sql注入问题

jdbc连接数据库

创建的db.properties工具类获取配置信息

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
user=root
password=123456

创建util公共类

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String user = 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");
            user = properties.getProperty("user");
            password = properties.getProperty("password");

            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
            return DriverManager.getConnection(url, user, password);
    }
    public static void release(Connection conn, Statement st,ResultSet rs){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st != null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

通过jdbc操作数据库(使用Statement进行操作)

public class Test {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //建立连接
            conn = JdbcUtils.getConnection();
            //执行sql的对象
            st = conn.createStatement();
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)" +
                    "VALUES('4','zhaoliu','123456','zl@sina.com','2020-02-01')";
            long i = st.executeLargeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

当时用以上方法进行操作时，在封装工具类的时候，进行字符串拼接操作可能会造成sql注入，导致程序极不安全，因此可以改用PreparedStatement进行操作，提前对sql预编译，然后赋值，而不是直接执行sql，从而加大了程序的安全性。

public class Test {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();
            //预编译
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);
            //设置参数
            st.setInt(1,4);
            st.setString(2,"zhaoliu");
            st.setString(3,"123456");
            st.setString(4,"zl@sina.com");
            //获取数据库可执行时间
            st.setDate(5, new java.sql.Date(new Date().getTime()));

            int i = st.executeUpdate();

            if(i>0){
                System.out.println("插入成功");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }

    }
}