Statement 和 PrepareStatement安全机制

对比 Statement 和 PrepareStatement    使用    PrepareStatement 避免SQL注入

Statement 

一：查询，SQL注入将导致条件过滤无效，直接暴露所有信息。

Connection connection = JDBCUtils.getCon();   //获取连接，省略了连接的步骤String stuNo = "123456789' OR '1' = '1";//由于一等于一的原因，此条SQL语句执行后，就是永远为真的情况，会查数据库中所有的字段。String sql = "SELECT COUNT(*) FROM tb_stu WHERE stu_no ='"+stuNo+"'";//可能会造成SQL注入Statement statement = connection.createStatement();//建立连接ResultSet resultSet = statement.executeQuery(sql);//执行SQL语句System.out.println("--------statement---------");while(resultSet.next()){//此处将遍历出数据库第一列的所有信息System.out.println(resultSet.getString(1));}

二：SQL注入删除操作，将造成数据库所有数据消失。

/** * SQL注入现象造成表数据被清空 * @throws SQLException */@Testpublic void test1() throws SQLException{Connection connection = JDBCUtils.getCon();Statement statement = connection.createStatement();//SQL注入现象// 123456' or '1' = '1//select * from tb_stu where stu_no = '12345' or '1' = '1';String no = "12345' or '1' = '1";//加上转义字符可以避免SQL注入//String no = "123456789";//no.replace("'", "\\'");String sql = "delete from tb_stu where stu_no = '"+no+"'";System.out.println(sql);statement.execute(sql);}

PrepareStatement    使用，有效防止SQL注入

一：查询  

//获取preparedStatement对象String sql2 = "SELECT COUNT(*) FROM tb_stu WHERE stu_no = ?";//将SQL语句放入连接中，等待执行PreparedStatement preparedStatement = connection.prepareStatement(sql2);preparedStatement.setString(1, stuNo);//sql语句中的第一问号中填入的值ResultSet resultSet2 = preparedStatement.executeQuery();//真正执行SQL语句System.out.println("--------preparedStatement---------");while(resultSet2.next()){System.out.println(resultSet2.getString(1));}

二：查询二

public void test3() throws SQLException{Connection connection = JDBCUtils.getCon();String sql = "UPDATE tb_stu SET stu_head = ? WHERE stu_id >= ? and stu_id < ?";PreparedStatement preparedStatement = connection.prepareStatement(sql);//第一个问号处替换为    1-5的头像preparedStatement.setString(1, "1-5的头像");//第二个问号出替换为    1preparedStatement.setInt(2, 1);//第三个问号处替换为    5preparedStatement.setInt(3, 5);System.out.println("影响了："+preparedStatement.executeUpdate());}

三：批处理

/** * 测试批处理 * Batch * addBatch() * executeBatch() * @throws SQLException */@Testpublic void test4() throws SQLException{Connection connection = JDBCUtils.getCon();String sql = "UPDATE tb_stu SET stu_head = ? WHERE stu_id >= ? and stu_id < ?";System.out.println("-------------------");PreparedStatement preparedStatement = connection.prepareStatement(sql);   preparedStatement.setString(1, "1-5的头像2");preparedStatement.setInt(2, 1);preparedStatement.setInt(3, 5);preparedStatement.addBatch();System.out.println("-------------------");preparedStatement.setString(1, "5-10的头像");preparedStatement.setInt(2, 5);preparedStatement.setInt(3, 10);preparedStatement.addBatch();System.out.println("-------------------");preparedStatement.setString(1, "10-20的头像");preparedStatement.setInt(2, 10);preparedStatement.setInt(3, 20);preparedStatement.addBatch();//addBatch()方法是将查询的方法放入队列中，等待执行方法executeBatch（）的执行，//只有当方法executeBatch()执行后，更新操作才会生效System.out.println("影响了："+preparedStatement.executeBatch());}