Today we will look into JDBC Statement vs PreparedStatement and some SQL Injection Example. While working with JDBC for database connectivity, we can use Statement or PreparedStatement to execute queries. These queries can be CRUD operation queries or even DDL queries to create or drop tables.
今天，我们将研究JDBC语句与PreparedStatement以及一些SQL注入示例。 在使用JDBC进行数据库连接时，我们可以使用Statement或PreparedStatement来执行查询。 这些查询可以是CRUD操作查询，甚至可以是DDL查询来创建或删除表。
声明与PreparedStatement (Statement vs PreparedStatement)
Before comparing Statement vs PreparedStatement, let’s see why we should avoid JDBC Statement. JDBC Statement has some major issues and should be avoided in all cases, let’s see this with a simple example.
在比较Statement与PreparedStatement之前，让我们看看为什么我们应该避免使用JDBC Statement。 JDBC语句存在一些主要问题，在所有情况下都应避免使用JDBC语句，让我们以一个简单的示例来看一下。
I have Users table in my local MySQL database with following data.
我的本地MySQL数据库中有用户表，其中包含以下数据。
Below script will create the table and insert the data for test use.
下面的脚本将创建表并插入数据以供测试使用。
CREATE TABLE `Users` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL DEFAULT '',
`email` varchar(20) NOT NULL DEFAULT '',
`country` varchar(20) DEFAULT 'USA',
`password` varchar(20) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `Users` (`id`, `name`, `email`, `country`, `password`)
VALUES
(1, 'Pankaj', 'pankaj@apple.com', 'India', 'pankaj123'),
(4, 'David', 'david@gmail.com', 'USA', 'david123'),
(5, 'Raman', 'raman@google.com', 'UK', 'raman123');A utility class for creating JDBC Connection to our mysql database.
一个实用程序类，用于创建与我们的mysql数据库的JDBC连接。
DBConnection.java
DBConnection.java
package com.journaldev.jdbc.statements;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
public final static String DB_DRIVER_CLASS = "com.mysql.jdbc.Driver";
public final static String DB_URL = "jdbc:mysql://localhost:3306/UserDB";
public final static String DB_USERNAME = "pankaj";
public final static String DB_PASSWORD = "pankaj123";
public static Connection getConnection() throws ClassNotFoundException, SQLException {
Connection con = null;
// load the Driver Class
Class.forName(DB_DRIVER_CLASS);
// create the connection now
con = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
System.out.println("DB Connection created successfully");
return con;
}
}Now let’s say we have following class that asks user to enter the email id and password and if it matches, then prints the user details. I am using JDBC Statement for executing the query.
现在，我们说下面的类，要求用户输入电子邮件ID和密码，如果匹配，则打印用户详细信息。 我正在使用JDBC语句执行查询。
GetUserDetails.java
GetUserDetails.java
package com.journaldev.jdbc.statements;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class GetUserDetails {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//read user entered data
Scanner scanner = new Scanner(System.in);
System.out.println("Please enter email id:");
String id = scanner.nextLine();
System.out.println("User id="+id);
System.out.println("Please enter password to get details:");
String pwd = scanner.nextLine();
System.out.println("User password="+pwd);
printUserData(id,pwd);
}
private static void printUserData(String id, String pwd) throws ClassNotFoundException, SQLException {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try{
con = DBConnection.getConnection();
stmt = con.createStatement();
String query = "select name, country, password from Users where email = '"+id+"' and password='"+pwd+"'";
System.out.println(query);
rs = stmt.executeQuery(query);
while(rs.next()){
System.out.println("Name="+rs.getString("name")+",country="+rs.getString("country")+",password="+rs.getString("password"));
}
}finally{
if(rs != null) rs.close();
stmt.close();
con.close();
}
}
}Let’s see what happens when we pass different kinds of input to above program.
让我们看看将不同类型的输入传递给上述程序时会发生什么。
Valid User:
有效用户 ：
Please enter email id:
david@gmail.com
User id=david@gmail.com
Please enter password to get details:
david123
User password=david123
DB Connection created successfully
select name, country, password from Users where email = 'david@gmail.com' and password='david123'
Name=David,country=USA,password=david123So our program works fine and a valid user can enter their credentials and get his details.
因此，我们的程序运行良好，有效的用户可以输入其凭据并获取其详细信息。
Now let’s see how a hacker can get unauthorized access to a user because we are using Statement for executing queries.
现在，让我们看看黑客如何获得未经授权的用户访问权限，因为我们正在使用Statement执行查询。
SQL Injection:
SQL注入
Please enter email id:
david@gmail.com' or '1'='1
User id=david@gmail.com' or '1'='1
Please enter password to get details:
User password=
DB Connection created successfully
select name, country, password from Users where email = 'david@gmail.com' or '1'='1' and password=''
Name=David,country=USA,password=david123As you can see that we are able to get the user details even without having password. The key point to note here is that query is created through String concatenation and if we provide proper input, we can hack the system, like here we did by passing user id as david@gmail.com' or '1'='1.
如您所见，即使没有密码，我们也可以获取用户详细信息。 这里要注意的关键点是查询是通过String串联创建的，如果我们提供正确的输入，我们可以破解系统，就像在这里我们通过将用户ID传递为david@gmail.com' or '1'='1 。
This is an example of SQL Injection where poor programming is responsible for making our application vulnerable for unauthorized database access.
这是SQL注入的一个示例，其中不良的编程负责使我们的应用程序容易受到未经授权的数据库访问的攻击。
One solution is to read the user input and then escape all the special characters that are used by MySQL but that would be clumsy and error prone. That’s why JDBC API came up with PreparedStatement interface that extends Statement and automatically escape the special characters before executing the query.
一种解决方案是读取用户输入，然后转义MySQL使用的所有特殊字符，但这些字符笨拙且容易出错。 这就是JDBC API带有PreparedStatement接口的原因，该接口扩展了Statement并在执行查询之前自动转义特殊字符。
Let’s rewrite above class using PreparedStatement and try to hack the system.
让我们使用PreparedStatement重写上面的类，然后尝试修改系统。
GetUserDetailsUsingPS.java
GetUserDetailsUsingPS.java
package com.journaldev.jdbc.statements;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class GetUserDetailsUsingPS {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// read user entered data
Scanner scanner = new Scanner(System.in);
System.out.println("Please enter email id:");
String id = scanner.nextLine();
System.out.println("User id=" + id);
System.out.println("Please enter password to get details:");
String pwd = scanner.nextLine();
System.out.println("User password=" + pwd);
printUserData(id, pwd);
}
private static void printUserData(String id, String pwd) throws ClassNotFoundException,
SQLException {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
String query = "select name, country, password from Users where email = ? and password = ?";
try {
con = DBConnection.getConnection();
ps = con.prepareStatement(query);
//set the parameter
ps.setString(1, id);
ps.setString(2, pwd);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println("Name=" + rs.getString("name") + ",country="
+ rs.getString("country") + ",password="
+ rs.getString("password"));
}
} finally {
if (rs != null)
rs.close();
ps.close();
con.close();
}
}
}Now if we will try to hack the system, let’s see what happens.
现在，如果我们尝试破解系统，让我们看看会发生什么。
SQL Injection:
SQL注入
Please enter email id:
david@gmail.com' or '1'='1
User id=david@gmail.com' or '1'='1
Please enter password to get details:
User password=
DB Connection created successfullySo we are not able to hack the database, it happened because the actual query that is getting executed is:
因此，我们无法破解数据库，因为发生的实际查询是：
select name, country, password from Users where email = 'david@gmail.com\' or \'1\'=\'1\' and password=''
select name, country, password from Users where email = 'david@gmail.com\' or \'1\'=\'1\' and password=''
When we fire a query to be executed for a relational database, it goes through following steps.
当我们触发要为关系数据库执行的查询时，它将经历以下步骤。
- Parsing of SQL query SQL查询解析
- Compilation of SQL Query SQL查询编译
- Planning and optimization of data acquisition path 计划和优化数据采集路径
- Executing the optimized query and return the resulted data 执行优化的查询并返回结果数据
When we use Statement, it goes through all the four steps but with PreparedStatement first three steps are executed when we create the prepared statement. So execution of query takes less time and more quick that Statement.
当我们使用Statement ，它将经历所有四个步骤，但是使用PreparedStatement时，在创建预处理语句时将执行前三个步骤。 因此，执行该查询所需的时间更少，并且该语句所需的时间更快。
Another benefit of using PreparedStatement is that we can use Batch Processing through addBatch() and executeBatch() methods. We can create a single prepared statement and use it to execute multiple queries.
使用PreparedStatement的另一个好处是，我们可以通过addBatch()和executeBatch()方法使用批处理。 我们可以创建一个准备好的语句，然后使用它执行多个查询。
Some points to remember about JDBC PreparedStatement are:
关于JDBC PreparedStatement要记住的几点是：
- PreparedStatement helps us in preventing SQL injection attacks because it automatically escapes the special characters. PreparedStatement帮助我们防止SQL注入攻击，因为它会自动转义特殊字符。
- PreparedStatement allows us to execute dynamic queries with parameter inputs. PreparedStatement允许我们使用参数输入执行动态查询。
- PreparedStatement provides different types of setter methods to set the input parameters for the query. PreparedStatement提供了不同类型的setter方法，以设置查询的输入参数。
- PreparedStatement is faster than Statement. It becomes more visible when we reuse the PreparedStatement or use it’s batch processing methods for executing multiple queries. PreparedStatement比Statement快。 当我们重用PreparedStatement或将其批处理方法用于执行多个查询时，它会变得更加可见。
- PreparedStatement helps us in writing object Oriented code with setter methods whereas with Statement we have to use String Concatenation to create the query. If there are multiple parameters to set, writing Query using String concatenation looks very ugly and error prone. PreparedStatement帮助我们使用setter方法编写面向对象的代码，而使用Statement，则必须使用String Concatenation创建查询。 如果要设置多个参数，则使用String串联编写Query看起来非常难看且容易出错。
- PreparedStatement returns
FORWARD_ONLYResultSet, so we can only move in forward direction. PreparedStatement返回FORWARD_ONLYResultSet，因此我们只能向前移动。 - Unlike Java Arrays or List, the indexing of PreparedStatement variables starts with 1. 与Java数组或列表不同，PreparedStatement变量的索引从1开始。
- One of the limitation of PreparedStatement is that we can’t use it for SQL queries with IN clause because PreparedStatement doesn’t allow us to bind multiple values for single placeholder (?). However there are few alternative approaches to use PreparedStatement for IN clause, read more at JDBC PreparedStatement IN clause. PreparedStatement的局限性之一是我们不能将其用于带有IN子句SQL查询，因为PreparedStatement不允许我们为单个占位符（？）绑定多个值。 但是，几乎没有其他方法可以将PreparedStatement用于IN子句，有关更多信息，请参见JDBC PreparedStatement IN子句 。
That’s all for the comparison of JDBC Statement vs PreparedStatement. You should always use PreparedStatement because it’s fast, object oriented, dynamic and more reliable.
这就是JDBC语句与PreparedStatement的比较。 您应该始终使用PreparedStatement，因为它快速，面向对象，动态且更可靠。
翻译自: https://www.journaldev.com/2489/jdbc-statement-vs-preparedstatement-sql-injection-example
370
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
