JDBC中使用preparedStatement解决SQL注入的问题
今天学习JDBC的时候老师讲到了使用Statement会产生sql注入的问题,并且讲了解决方案,所以写在这里和大家一起学习
下面我记录得东西都是比较基础的,如果是大佬,不喜勿喷[/抱拳]!如有错误,也欢迎指正!
话不多说,开整:
先看张图
账号和密码我是随便输入的,但是他却显示登陆成功了,这是怎么回事呢?
主要问题出在以下这行代码中
//获取数据操作对象
statement = connection.createStatement();
//执行SQL
String sql = "select * from t_user where loginName = '"+initializeTheInterface.get("logginName")+"' and loginPwd = '"+initializeTheInterface.get("logginPwd")+"'";
resultSet = statement.executeQuery(sql);
当我们在动态获取账户和密码的时候,如果我们输入的是可以干扰到查询语句的字符,那么在上面这行语句的执行的时候,查询就会出现故障,这时即使输入错误的密码也能登录成功;举个例子:
当我们输入以上的这串密码时候,其实也就相当于把这句sql语句变成了:“select * from t_user where loginName = fdsa and loginPwd = fdsa or “1”=1“,那么这时这个语句必然成立,查询成功。
这也就是sql注入的根本原因所在!
下面是使用Statement连接数据库的代码:
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;
/*
* @Auther:sunny_wwu
* @Data:2022-04-15 8:39
* @Description:JDBC
* @Version:1.0
* @Detail:
* */
public class Log {
public static void main(String[] args) {
/*
Initialize the interface,
create a method for entering the user name and password,
and return the assignment to“initializeTheInterface”
*/
Map<String,String> initializeTheInterface =initializeTheInterface();
/*
Verify the username and password,
compare the entered username and password through the login method,
return the result as boolean type, and assign the verification result to“logginsuccess”
*/
boolean logginSuccess = login(initializeTheInterface);
/*
output login result
*/
System.out.println(logginSuccess ? "登录成功" : "登录失败");
}
/**
* Compare with the database user name and password Check
* whether the user name and password are correct
* @param initializeTheInterface Username and password entered by the user
* @return Compare the results, return true for success, false for failures
*/
private static boolean login(Map<String, String> initializeTheInterface) {
//打标记
boolean logginSuccess = false;
//JDBC 代码
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bgpowernode","root","123456");
//获取数据操作对象
statement = connection.createStatement();
//执行SQL
String sql = "select * from t_user where loginName = '"+initializeTheInterface.get("logginName")+"' and loginPwd = '"+initializeTheInterface.get("logginPwd")+"'";
resultSet = statement.executeQuery(sql);
//处理结果集
if (resultSet.next()){
//登录成功
logginSuccess = true;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
//关闭资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return logginSuccess;
}
/**
* Initialize the login interface
* @return entered user's username and password
*/
private static Map<String, String> initializeTheInterface() {
Scanner scanner = new Scanner(System.in);
System.out.print("用户名:");
String logginName = scanner.nextLine();
System.out.print("密码:");
String logginPwd = scanner.nextLine();
Map<String,String> userLogginInfo = new HashMap<>();
userLogginInfo.put("logginName",logginName);
userLogginInfo.put("logginPwd",logginPwd);
return userLogginInfo;
}
}
针对本程序的sql注入解决方案
这里我们是使用preparedStatement(预编译Statement)来解决的。
preparedStatement(预编译Statement)接口它是继承了java。sql.Statement,属于预编译的数据库操作对象,其原理是预先编译sql语句的框架,然后再给sql语句传“值”,那么由此一来,即使传进去带有sql敏感的词汇,也不会扭曲语句的意思。
其中最大的改变就是下面的几行代码:
String sql = "select * from t_user where loginName = ? and loginPwd = ?";
preparedStatement = connection.prepareStatement(sql);
//给占位符传值,第一个问号下标是1,第二个问号下标是2.......
preparedStatement.setString(1,initializeTheInterface.get("logginName"));
preparedStatement.setString(2,initializeTheInterface.get("logginPwd"));
//执行SQL
resultSet= preparedStatement.executeQuery();
在这里我们不难看出,使用占位符先构成查询完成之后,再由preparedStatement的setString方法把值传进去,这样bug就解决了。这里需要解释以下“?”作为占位符,他只是一个代表,不参加预编译,只是后来查询的时候通过传进来的值进行查询的。另外,setString方法的第一个参数是int类型的数子,代表的是第几个占位符,第二个是要传进去的String值。
下面是改进之后的完全代码,其他地方也有些许改动,请仔细看完。
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;
/*
* @Auther:sunny_wwu
* @Data:2022-04-16 9:48
* @Description:JDBC-com
* @Version:1.1
* @Detail:Solve the vulnerability that the program can be injected by SQL
* */
public class log02 {
public static void main(String[] args) {
/*
Initialize the interface,
create a method for entering the user name and password,
and return the assignment to“initializeTheInterface”
*/
Map<String,String> initializeTheInterface =initializeTheInterface();
/*
Verify the username and password,
compare the entered username and password through the login method,
return the result as boolean type, and assign the verification result to“logginsuccess”
*/
boolean logginSuccess = login(initializeTheInterface);
/*
output login result
*/
System.out.println(logginSuccess ? "登录成功" : "登录失败");
}
/**
* Compare with the database user name and password Check
* whether the user name and password are correct
* @param initializeTheInterface Username and password entered by the user
* @return Compare the results, return true for success, false for failures
*/
private static boolean login(Map<String, String> initializeTheInterface) {
//打标记
boolean logginSuccess = false;
//JDBC 代码
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//注册驱动
Class.forName("com.mysql.jdbc.Driver");
//获取连接
connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bgpowernode","root","123456");
//获取数据操作对象
//statement = connection.createStatement();
String sql = "select * from t_user where loginName = ? and loginPwd = ?";
preparedStatement = connection.prepareStatement(sql);
//给占位符传值,第一个问号下标是1,第二个问号下标是2.......
preparedStatement.setString(1,initializeTheInterface.get("logginName"));
preparedStatement.setString(2,initializeTheInterface.get("logginPwd"));
//执行SQL
resultSet= preparedStatement.executeQuery();
//处理结果集
if (resultSet.next()){
//登录成功
logginSuccess = true;
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
//关闭资源
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
return logginSuccess;
}
/**
* Initialize the login interface
* @return entered user's username and password
*/
private static Map<String, String> initializeTheInterface() {
Scanner scanner = new Scanner(System.in);
System.out.print("用户名:");
String logginName = scanner.nextLine();
System.out.print("密码:");
String logginPwd = scanner.nextLine();
Map<String,String> userLogginInfo = new HashMap<>();
userLogginInfo.put("logginName",logginName);
userLogginInfo.put("logginPwd",logginPwd);
return userLogginInfo;
}
}
总结
面对上面的修改,那是不是说Statement就完全不需要了呢?
其实不是,有的项目也可能会要求允许sql注入,比如项目中有说明要求sql语句拼接的要求,这时Statement就起到作用了
总之,针对这边文章,我们总结为以下几点
对比一下statement和preparedStatement?
- 后者存在sql注入的问题,前者解决了sql注入的问题;
- 前者是编译一次执行一次,后者是编译一次,可执行N次,这样效率会高一点;
- 后者会在编译阶段做类型的安全检查;
- 前者使用较多,但是在极少数的情况下也是需要用到后者的;
- 业务方面要求需要进行sql语句拼接的,可使用Statement;
最后,感谢您能看到这里,如果您觉得文章对您有些帮助的话,还请帮忙点点赞,如果您有不同意见也可以在下面评论区一起讨论。