JDBC预编译sql语句处理
时间：20180311
使用Statement与PreparedStatement执行DDL与DML的区别？
/**
* jdbc通用方法
* @author mengjie
*
*/
public class JdbcUtil {
//url
private static String url = "jdbc:mysql://localhost:3306/day16";
//user
private static String user = "root";
//password
private static String password = "root";
/**
*z只注册一次驱动，静态代码块
*/
static {
//注册驱动程序
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 获取连接的方法
* @throws SQLException
*/
public static Connection getConnection() {
try {
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 释放资源的方法
*/
public static void close(Statement stmt, Connection conn) {
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
/**
* 释放资源的方法
*/
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}
}
}
/**
* 使用PreparedStatement执行CRUD操作
* 执行预编译的sql语句
* @author mengjie
*
*/
public class Demo1 {
public static void main(String[] args) {
//insert();
//update();
query();
}
private static void query() {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet res = null;
try{
//1）建立连接
conn = JdbcUtil.getConnection();
//2) 创建sql
String sql = "select * from student where id = ?";
//3) 创建PreparedStatement,预编译sql语句
stmt = conn.prepareStatement(sql);
//4) 给参数赋值
/**
* 注意参数的位置，一定要对应字段的位置
*/
stmt.setInt(1, 5);
//5) 执行sql
res = stmt.executeQuery();
while(res.next()) {
int id = res.getInt("id");
String name = res.getString("name");
int age = res.getInt("age");
System.out.println(id + "\t" +name+"\t"+age);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.close(stmt, conn);
}
}
private static void update() {
/**
* 修改
*/
Connection conn = null;
PreparedStatement stmt = null;
try{
//1）建立连接
conn = JdbcUtil.getConnection();
//2) 创建sql
String sql = "update student set name = ?, age = ? where id = ?";
//3) 创建PreparedStatement,预编译sql语句
stmt = conn.prepareStatement(sql);
//4) 给参数赋值
/**
* 注意参数的位置，一定要对应字段的位置
*/
stmt.setInt(3, 4);
stmt.setString(1, "wanzhe");
stmt.setInt(2, 100);
//5) 执行sql
int n = stmt.executeUpdate();
System.out.println("影响了"+ n + "行");
}catch(Exception e) {
e.printStackTrace();
}finally {
JdbcUtil.close(stmt, conn);
}
}
private static void insert() {
/**
* 插入
*/
Connection conn = null;
PreparedStatement stmt = null;
try {
//1)获取连接
conn = JdbcUtil.getConnection();
//2)准备sql//?号是参数的占位符，一个问好代表是一个参数。
String sql = "insert into student(id,name,age) values(?,?,?)";//
//3)创建PreparedStatement,预编译sql语句
stmt = conn.prepareStatement(sql);//预编译sql语句（sql语法和权限的检查），
//4)给参数赋值
/**
* 参数一： 参数的位置。从1开始
* 参数二：参数值
*/
stmt.setInt(1, 5);
stmt.setString(2, "里斯");
stmt.setInt(3, 40);
//5)发送参数到数据库，执行sql，
//executeUpdate方法中并没有参数，是将参数和sql组装起来，并执行
int n = stmt.executeUpdate();
System.out.println("影响了"+ n + "行");
}catch(Exception e){
e.printStackTrace( );
}finally {
//关闭资源，而且不用更改close方法（此时传递的stmt是preparedStatement对象），不用重载
//实现参数为preparedStatement对象的close方法
//原因是statement是preparedstatement类的父类
JdbcUtil.close(stmt, conn);
}
}
}
为什么要使用PreparedStatement?
1.PreparedStatement接口继承了Statement，PreparedStatement实例包含已编译的SQL语句，所以其执行速度要快于Statement对象。
2.作为Statement的子类，PreparedStatment继承了Statement的所有功能。三种方法execute，executeQuery和executeUpdate已被更改以使之不再需要参数。
3.在JDBC应用中，在任何时候都不要是使用Statement，原因如下：
1)、代码的可读性和可维护性。Statement需要不断地拼接，而PreparedStatement不会。
2)、PreparedStatement尽最大可能提高性能。DB有缓存机制，相同的预编译语句再次被调用不会再次需要编译。
3)、最重要的一点是极大的提高了安全性。Statement容易被SQL注入，而PreparedStatement传入的内容和参数不会和sql语句发生任何匹配关系。
关于statement和PreparedStatement的安全性做具体分析如下：
例如用户登陆的案例
在数据库中操作：
--创建用户表
CREATE TABLE user_list(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
PASSWORD VARCHAR(20)
)
--插入数据
INSERT INTO user_list(NAME,PASSWORD) VALUES('eric','123456');
INSERT INTO user_list(NAME,PASSWORD) VALUES('jacky','654321');
SELECT * FROM user_list;
如何判断登陆是否成功呢？根据下列sql：
--登陆成功
SELECT * FROM user_list WHERE NAME='eric' AND PASSWORD='123456'
能成功登陆的情况
但是在数据库sql中存在如下情况：
SELECT * FROM user_list WHERE 1=1; --1=1; 恒成立
sql恒成立的情况
登陆失败的情况
SELECT * FROM user_list WHERE NAME='rose' AND PASSWORD='123456'
可将上述sql更改为恒成立情况，注意-- 前后的空格
SELECT * FROM user_list WHERE NAME ='rose' OR 1=1 -- ' AND PASSWORD='123456';
sql注入的情况
以上都是在客户端中执行的sql语句进行的测试对比
分割线
以下都是在代码中执行的sql语句进行的测试对比
分别在Statement和PreparedStatement中执行带有sql注入的sql，验证Statement和PreparedStatement的安全性。
- Statement情况
public class Demo2 {
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet res = null;
public static void main(String[] args) throws Exception {
String username = "rose";
String password = "123456";
conn = JdbcUtil.getConnection();
stmt = conn.createStatement();
String sql = "SELECT * FROM user_list WHERE NAME =' "+username+" ' AND PASSWORD=' "+password+" '";
res = stmt.executeQuery(sql);
if(res.next()) {
System.out.println("登陆成功");
}else {
System.out.println("登陆失败");
}
}
}
结果：
登陆失败
- Statement SQL注入的情况
public class Demo2 {
private static Connection conn = null;
private static Statement stmt = null;
private static ResultSet res = null;
public static void main(String[] args) throws Exception {
String username = "rose' OR 1=1 -- ";
String password = "123456";
conn = JdbcUtil.getConnection();
stmt = conn.createStatement();
String sql = "SELECT * FROM user_list WHERE NAME =' "+username+" ' AND PASSWORD=' "+password+" '";
res = stmt.executeQuery(sql);
if(res.next()) {
System.out.println("登陆成功");
}else {
System.out.println("登陆失败");
}
}
}
结果：
登陆成功
- PreparedStatement情况
public class Demo2 {
private static Connection conn = null;
private static PreparedStatement stmt = null;
private static ResultSet res = null;
public static void main(String[] args){
conn = JdbcUtil.getConnection();
String sql = "SELECT * FROM user_list WHERE NAME = ? AND PASSWORD= ?";
try {
stmt = conn.prepareStatement(sql);
stmt.setString(1, "rose' OR 1=1 -- ");
stmt.setString(2, "123456");
res = stmt.executeQuery();
if(res.next()) {
System.out.println("登陆成功");
}else {
System.out.println("登陆失败");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
JdbcUtil.close(res,stmt,conn);
}
}
}
结果：
登陆失败
解析PreparedStatement是如何防止SQL注入的？
- 执行stmt = conn.prepareStatement(sql);（此句话叫预编译sql）1.先将sql发送至数据库。2.发送之后验证sql的语法。3.验证sql中语法发现sql中有两个参数NAME、PSASSWORD（该sql执行时必须要有NAME、PSASSWORD两个个参数）。
- 执行stmt.setString(1, "rose' OR 1=1 -- ");
stmt.setString(2, "123456");参数赋值。 - 执行stmt.executeQuery();即执行sql，就将赋值的两个参数塞进验证sql语法验证的两个参数中去。此时"rose' OR 1=1 -- "是作为NAME的参数，因此回去数据库中查找NAME名为"rose' OR 1=1 -- "的数据，显然是查询不到的，说明PreparedStatement传入的内容不会和sql语句发生任何关系
- Statement中并不进行sql语法验证（参数验证）
Statement的情况
说明PreparedStatement传入的内容不会和sql语句发生任何关系，这也是statement中的sql（静态sql）能被注入sql的原因。
