JDBC【5】-- JDBC预编译和拼接Sql对比

秦怀杂货店

发布于 2022-02-15 14:21:40

1.1K0

发布于 2022-02-15 14:21:40

文章被收录于专栏：技术杂货店技术杂货店

在jdbc中，有三种方式执行sql，分别是使用Statement（sql拼接），PreparedStatement（预编译），还有一种CallableStatement（存储过程），在这里我就不介绍CallableStatement了，我们来看看Statement与PreparedStatement的区别。

1. 创建数据库，数据表

数据库名字是test,数据表的名字是student，里面有四个字段，一个是id，也就是主键（自动递增），还有名字，年龄，成绩。最后先使用sql语句插入六个测试记录。

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `student` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(20) NOT NULL , 
`age` INT NOT NULL , `score` DOUBLE NOT NULL , PRIMARY KEY (`id`)) ENGINE = MyISAM; 
INSERT INTO `student` VALUES (1, '小红', 26, 83);
INSERT INTO `student` VALUES (2, '小白', 23, 93);
INSERT INTO `student` VALUES (3, '小明', 34, 45);
INSERT INTO `student` VALUES (4, '张三', 12, 78);
INSERT INTO `student` VALUES (5, '李四', 33, 96);
INSERT INTO `student` VALUES (6, '魏红', 23, 46);

建立对应的学生类：

/**
 * student类，字段包括id,name,age,score
 * 实现无参构造，带参构造，toString方法，以及get，set方法
 * @author 秦怀
 */
public class Student {
 private int id;
 private String name;
 private int age;
 private double score;
 
 public Student() {
  super();
  // TODO Auto-generated constructor stub
 }
 public Student(String name, int age, double score) {
  super();
  this.name = name;
  this.age = age;
  this.score = score;
 }
 public int getId() {
  return id;
 }
 public void setId(int id) {
  this.id = id;
 }
 public String getName() {
  return name;
 }
 public void setName(String name) {
  this.name = name;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
 public double getScore() {
  return score;
 }
 public void setScore(double score) {
  this.score = score;
 }
 @Override
 public String toString() {
  return "Student [id=" + id + ", name=" + name + ", age=" + age
    + ", score=" + score + "]";
 }
 
}

2.测试代码

先来看代码,下面是获取数据库连接的工具类 DBUtil.class：

public class DBUtil {
 private static String URL="jdbc:mysql://127.0.0.1:3306/test";
 private static String USER="root";
 private static String PASSWROD ="123456";
 private static Connection connection=null;
 static{
  try {
   Class.forName("com.mysql.jdbc.Driver");
   // 获取数据库连接
   connection=DriverManager.getConnection(URL,USER,PASSWROD);
   System.out.println("连接成功");
  } catch (ClassNotFoundException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  } catch (SQLException e) {
   // TODO Auto-generated catch block
   e.printStackTrace();
  }
 }
 // 返回数据库连接
 public static Connection getConnection(){
  return connection;
 }
}

下面是根据id查询学生信息的代码片段,返回student对象就能输出了：

 public Student selectStudentByStatement(int id){
     // 拼接sql语句
  String sql ="select * from student where id = "+id;
  try {
      // 获取statement对象
   Statement statement = DBUtil.getConnection().createStatement();
   // 执行sql语句，返回 ResultSet
   ResultSet resultSet = statement.executeQuery(sql);
   Student student = new Student();
   // 一条也只能使用resultset来接收
   while(resultSet.next()){
    student.setId(resultSet.getInt("id"));
    student.setName(resultSet.getString("name"));
    student.setAge(resultSet.getInt("age"));
    student.setScore(resultSet.getDouble("score"));
   }
   return student;
  } catch (SQLException e) {
   // TODO: handle exception
  }
  return null;
 }

我们可以看到整个流程是:

先获取到数据库的连接Class.forName("com.mysql.jdbc.Driver"); connection=DriverManager.getConnection(URL,USER,PASSWROD);
获取到连接之后通过连接获取statement对象,通过statement来执行sql语句，返回resultset这个结果集，Statement statement = DBUtil.getConnection().createStatement();ResultSet resultSet = statement.executeQuery(sql);
值得注意的是，上面的sql是已经拼接好，写固定了的sql，所以很容易被注入，比如这句：

sql = "select * from user where name= '" + name + "' and password= '" + password+"'";

如果有人的name故意传入很奇怪的字符串：

name = "name' or '1'= '1"
password = "password' or '1'='1"，那么整个语句就会变成：

sql = "select * from user where name= 'name' or '1'='1' and password= 'password' or '1'='1'";

那么就会返回所有的信息，所以这是很危险的。还有更加危险的，是在后面加上删除表数据的操作，不过一般我们都不会把这些权限开放的。

// 如果password = " ';drop table user;select * from user where '1'= '1"
// 后面一句不会执行，但是这已经可以删除表格了
sql = "select * from user where name= 'name' or '1'='1' and password= '' ;drop table user;select * from user where '1'= '1'";

所以预编译显得尤为重要了。

3.PreparedStatement预编译

我们先来看看预编译的代码：

 // 根据id查询学生
 public Student selectStudent(int id){
  String sql ="select * from student where id =?";
  try {
   PreparedStatement preparedStatement = DBUtil.getConnection()..prepareStatement(sql);
   preparedStatement.setInt(1, id);
   ResultSet resultSet = preparedStatement.executeQuery();
   Student student = new Student();
   // 一条也只能使用resultset来接收
   while(resultSet.next()){
    student.setId(resultSet.getInt("id"));
    student.setName(resultSet.getString("name"));
    student.setAge(resultSet.getInt("age"));
    student.setScore(resultSet.getDouble("score"));
   }
   return student;
  } catch (SQLException e) {
   // TODO: handle exception
  }
  return null;
 }

预编译也是同样需要获取到数据库连接对象connection，但是sql语句拼接的时候使用了占位符 ？，将含有占位符的sql当参数传进去，获取到PreparedStatement预编译的对象，最后是通过set来绑定参数，然后再去使用execute执行预编译过的代码。这样就避免了sql注入的问题，同时，由于sql已经编译过缓存q起来，所以执行起来不用再编译，速度就会比较快。

4.为什么预编译可以防止sql注入

在使用占位符，或者说参数的时候，数据库已经将sql指令编译过，那么查询的格式已经订好了，也就是我们说的我已经明白你要做什么了，你要是将不合法的参数传进去，会有合法性检查，用户只需要提供参数给我，参数不会当成指令部分来执行，也就是预编译已经把指令以及参数部分区分开，参数部分不允许传指令进来。这样的好处查询速度提高，因为有了预编译缓存，方便维护，可读性增强，不会有很多单引号双引号，容易出错，防止大部分的sql注入，因为参数和sql指令部分数据库系统已经区分开。百度文库里面提到:传递给PreparedStatement对象的参数可以被强制进行类型转换，使开发人员可以确保在插入或查询数据时与底层的数据库格式匹配。要是理解不透彻可以这么来理解：

select * from student where name= ?

预编译的时候是先把这句话编译了，生成sql模板，相当于生成了一个我知道你要查名字了，你把名字传给我，你现在想耍点小聪明，把字符串'Jame' or '1=1'传进去，你以为他会变成下面这样么：

select * from student where name= 'Jame' or '1=1'

放心吧,不可能的，这辈子都不可能的啦，数据库都知道你要干嘛了，我不是有sql模板了么，数据库的心里想的是我叫你传名字给我，行，这名字有点长，想害我，可以，我帮你找，那么数据库去名字这一字段帮你找一个叫'Jame' or '1=1'的人，他心里想这人真逗，没有这个人，没有！！！而不会将你的语句执行，预编译大概就是提前知道了你大概是需要干什么？最后你只需要将参数传递过来，参数的地方是个占位符，而不会把参数解析成为具有语义的语句。

我理解的，这也就是为什么预编译可以防止sql注入的解释了，它是经过了解释器解释过的，解释的过程我就不啰嗦了，只要是对参数做转义，转义之后让它在拼接时只能表示字符串，不能变成查询语句。

此文章仅代表自己（本菜鸟）学习积累记录，或者学习笔记，如有侵权，请联系作者删除。人无完人，文章也一样，文笔稚嫩，在下不才，勿喷，如果有错误之处，还望指出，感激不尽~

技术之路不在一时，山高水长，纵使缓慢，驰而不息。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2020-12-01，如有侵权请联系 cloudcommunity@tencent.com 删除

sql