Statement与PreparedStatement

PreparedStatement是用来执行SQL查询语句的API之一,Java提供了 Statement、PreparedStatement 和 CallableStatement三种方式来执行查询语句,其中 Statement 用于通用查询, PreparedStatement 用于执行参数化查询,而 CallableStatement则是用于存储过程。同时PreparedStatement还经常会在Java面试被提及,譬如:Statement与PreparedStatement的区别以及如何避免SQL注入式攻击?

Statement详解

Statement是与数据库打交道最关键的一个接口,该接口包括了我们常用的 CRUD操作,我们查看API文档发现这么一段说明:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment’s current ResultSet object if an open one exists.

在默认情况下,同一时间每个Statement只能打开一个ResultSet对象。因此,如果读取一个ResultSet对象与读取另一个交叉,则这两个对象必须是由不同的Statement对象生成。如果使用相同的Statement打开了新的ResultSet对象,则Statement接口中的所有执行方法都会隐式关闭之前的ResultSet对象。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Test
public void test() {
Connection conn = null;
Statement statement = null;
ResultSet resultSet = null;

try {
conn = JdbcUtils.getConn();
statement = conn.createStatement();
String sql = "select count(*) from db_user";
resultSet = statement.executeQuery(sql);

String _sql = "select user_name,password,email,birthday from db_user where id = 1";
//会隐式关闭之前的ResultSet对象
ResultSet _resultSet = statement.executeQuery(_sql);

//As it seems the junit.framework.Assert class has been moved to org.junit.Assert in JUnit 4.0 - you can use that instead, it's not deprecated.
Assert.assertEquals(true, resultSet.isClosed());
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(resultSet,statement,conn);
}
}

CRUD操作

1.新增数据的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Override
public int insert(User user) {
int result = -1;
if (user == null) return result;
Connection connection = null;
Statement statement = null;

try {
String sql = "insert into db_jdbc.db_user(user_name,password,email,birthday) values('%s','%s','%s','%s')";
sql = String.format(sql, user.getUserName(), user.getPassword(), user.getEmail(), user.getBirthday());
connection = JdbcUtils.getConn();

statement = connection.createStatement();

statement.executeUpdate(sql);

} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(null, statement, connection);
}

return result;
}

2.读取数据的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
@Override
public User selectById(Integer id) {
User record = null;
if (id == null || id <= 0) return record;
String sql = "select * from db_jdbc.db_user where id = %s";
sql = String.format(sql, id);
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
connection = JdbcUtils.getConn();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
String userName = resultSet.getString(2);
String password = resultSet.getString(3);
String email = resultSet.getString(4);
Timestamp birthday = resultSet.getTimestamp(5);

record = new User();
record.setId(id);
record.setUserName(userName);
record.setEmail(email);
record.setBirthday(birthday);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(resultSet, statement, connection);
}
return record;
}

3.修改数据的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
@Override
public int update(User user) {
int result = -1;
if (user == null) return result;
User record = selectById(user.getId());

String sql = "update db_jdbc.db_user set user_name='%s',password='%s',email='%s',birthday='%s' where id = %s";
String userName = (user.getUserName() == null) ? record.getUserName() : user.getUserName();
String password = (user.getPassword() == null) ? record.getPassword() : user.getPassword();
String email = (user.getEmail() == null) ? record.getEmail() : user.getEmail();
Timestamp birthday = (user.getBirthday() == null) ? record.getBirthday() : user.getBirthday();
sql = String.format(sql, userName, password, email, birthday,user.getId());
Connection connection = null;
Statement statement = null;

try {
connection = JdbcUtils.getConn();
statement = connection.createStatement();
result = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(null, statement, connection);
}

return result;
}

4.删除数据的方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Override
public int delete(Integer id) {
Integer result = -1;
if (id == null || id <= 0) return result;
String sql = "delete from db_jdbc.db_user where id = %s";
sql = String.format(sql, id);
Connection connection = null;
Statement statement = null;
try {
connection = JdbcUtils.getConn();
statement = connection.createStatement();
result = statement.executeUpdate(sql);
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(null, statement, connection);
}
return result;
}

Statement的SQL注入问题

如某个网站的登录验证SQL查询代码为:

1
strSQL = "SELECT * FROM db_user WHERE name = '" + userName + "' and pw = '"+ passWord +"';"

如果我们恶意输入如下的参数:

userNamepassWord
any_value’ or ‘1’ = ‘1’;show tables --flwcyany_value

那么最终SQL语句就变成了:

1
strSQL = "SELECT * FROM db_user WHERE name = 'any_value' or '1' = '1';show tables -- rooike and pw = 'any_value';"

在MySQL中,最后连续的两个减号表示忽略此SQL减号后面的语句.因为WHERE条件恒为真,这就相当于执行:

1
strSQL = "SELECT * FROM db_user;SHOW TABLES"

注意:在MySQL中,最后连续的两个减号表示忽略此SQL减号后面的语句,我本机的MySQL版本号为5.6.12,目前几乎所有SQL注入实例都是直接采用两个减号结尾,但是实际测试,这个版本号的MySQL要求两个减号后面必须要有空格才能正常注入,而浏览器是会自动删除掉URL尾部空格的,所以我们的注入会在两个减号后面统一添加任意一个字符或单词,本篇文章的SQL注入实例统一以 – flwcy 结尾。

因此可以达到无账号密码亦可登录网站且数据库表的名字db_user也被打印在了页面上。如果恶意用户要是更坏一点,用户输入如下参数:

userNamepassWord
any_value‘; DROP TABLE db_user where ‘1’ = ‘1

那么最终SQL语句变成了:

1
strSQL = "SELECT * FROM db_user WHERE name = 'any_value' and pw = ''; DROP TABLE db_user where '1' = '1';"

这样一来,虽然没有登录,但是数据表都被删除了。通过以上的描述,我们简单的总结一下Statement的缺点

  • 执行时发送 sql,影响效率.
  • 同样的 sql,每次都要发送,不能进行有效的缓存,是一种资源的浪费.
  • 为了防止恶意数据我们还需要编写附加的程序(过滤器)带来不必要的开支.
  • 拼接 sql 字符串很容易出现错误.

预处理语句

为了解决之前所说的问题,我们需要学习一个新的接口PreparedStatement,比起凌乱的字符串追加似的查询,PreparedStatement查询可读性更好、更安全,另外,PreparedStatement比Statement对象生成的查询速度更快。如下两个SELECT语句,第二个查询就是正确使用PreparedStatement的查询,它比SQL1能获得更好的性能:

SQL Query 1:字符串追加形式的PreparedStatement

1
2
String userName = getUserName();
PreparedStatement prestmt = conn.prepareStatement("select user_name,password,email,birthday from db_user where user_name=" + userName);

SQL Query 2:使用参数化查询的PreparedStatement

1
2
3
String userName = getUserName();
PreparedStatement prestmt = conn.prepareStatement("select user_name,password,email,birthday from db_user where user_name=?");
prestmt.setString(1,userName);

这里的”?”就是参数的占位符(占位符的索引位置从1开始而不是0,如果填入0会导致java.sql.SQLException invalid column index异常。所以如果PreparedStatement有两个占位符,那么第一个参数的索引时1,第二个参数的索引是2.)。

PreparedStatement是java.sql包下面的一个驱动,PreparedStatement继承于Statement,用来执行SQL语句查询。通过调用connection.preparedStatement(sql)方法可以获得PreparedStatment对象。PreparedStatement与java.sql.Connection对象是关联的,一旦你关闭了connection,PreparedStatement也没法使用了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
private String url = "jdbc:mysql://localhost:3306/db_jdbc";

private String userName = "root";

private String password = "123456";

@Override
public int insert(User user) {
int result = -1;
if (user == null) return result;
Connection connection = null;
PreparedStatement preparedStatement = null;

try {
String sql = "insert into db_jdbc.db_user(user_name,password,email,birthday) values(?,?,?,?)";
sql = String.format(sql, user.getUserName(), user.getPassword(), user.getEmail(), user.getBirthday());
connection = JdbcUtils.getConn();

preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1,user.getUserName());
preparedStatement.setString(2,user.getPassword());
preparedStatement.setString(3,user.getEmail());
preparedStatement.setTimestamp(4, user.getBirthday());

preparedStatement.executeUpdate();

} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.close(null, preparedStatement, connection);
}

return result;
}

应用程序在创建好PreparedStatement后,DBMS就已经对SQL进行了预编译(数据库对SQL语句的分析,编译,优化已经在第一次查询前完成了),同时DBMS会把SQL保存到高速缓存中,调用executeupdate()后,DBMS就直接执行SQL语句了,第二次执行时DBMS会直接查询高速缓存,只要高速缓存中还能找到相同的SQL就无需重新对SQL进行语法语义分析了,这样一来, 这种预处理语句查询比普通的查询运行速度更快。另外,传递给PreparedStatement对象的参数可以被强制进行类型转换,使开发人员可以确保在插入或查询数据时与底层的数据库格式相匹配。

在使用参数化查询的情况下,数据库系统(eg:MySQL)不会将参数的内容视为SQL指令的一部分来处理,而是在数据库完成SQL指令的编译后,才套用参数运行,因此,即使参数里有敏感字符如 or ‘1=1’数据库也会将其作为一个参数来处理,而不会作为一个SQL指令。因此使用PreparedStatement的参数化的查询可以阻止大部分的SQL注入。为了减少数据库的负载,生产环境中的JDBC代码你应该尽量使用PreparedStatement 。

PreparedStatement的优缺点

为了防止SQL注入攻击,PreparedStatement不允许一个占位符(?)有多个值,在执行有IN子句查询的时候这个问题变得棘手起来。下面这个SQL查询使用PreparedStatement就不会返回任何结果

1
2
String sql = "SELECT * FROM db_user WHERE user_name IN (?)";
preparedSatement.setString(1, "'jjr123', 'rooike', 'js123'");

简单总结一下PreparedStatement的优点:

  • PreparedStatement可以阻止常见的SQL注入式攻击,因为它会自动对特殊字符转义。
  • PreparedStatement可以写参数化查询,比起凌乱的字符串追加似的查询,PreparedStatement查询可读性更好、更安全。
  • 对于PreparedStatement来说, 数据库系统会对sql语句进行预编译处理(如果JDBC驱动支持的话),所以其执行速度要快于 Statement 对象。 特别是如果带有不同参数的同一SQL语句被多次执行的时候效率更高。 (Statement总是在执行时发送sql,影响效率.同样的sql,每次都要发送,不能进行有效的缓存,是一种资源的浪费)。

More Read

文章目录
  1. 1. Statement详解
  2. 2. CRUD操作
    1. 2.1. 1.新增数据的方法
    2. 2.2. 2.读取数据的方法
    3. 2.3. 3.修改数据的方法
    4. 2.4. 4.删除数据的方法
  3. 3. Statement的SQL注入问题
  4. 4. 预处理语句
  5. 5. PreparedStatement的优缺点
  6. 6. More Read
|