JDBC中使用preparedStatement解决SQL注入的问题

JDBC中使用preparedStatement解决SQL注入的问题

今天学习JDBC的时候老师讲到了使用Statement会产生sql注入的问题&＃xff0c;并且讲了解决方案&＃xff0c;所以写在这里和大家一起学习
下面我记录得东西都是比较基础的&＃xff0c;如果是大佬&＃xff0c;不喜勿喷[/抱拳]&＃xff01;如有错误&＃xff0c;也欢迎指正&＃xff01;
话不多说&＃xff0c;开整&＃xff1a;

先看张图

账号和密码我是随便输入的&＃xff0c;但是他却显示登陆成功了&＃xff0c;这是怎么回事呢&＃xff1f;
主要问题出在以下这行代码中

//获取数据操作对象
statement &＃61; connection.createStatement();
//执行SQL
String sql &＃61; "select * from t_user where loginName &＃61; &＃39;"&＃43;initializeTheInterface.get("logginName")&＃43;"&＃39; and loginPwd &＃61; &＃39;"&＃43;initializeTheInterface.get("logginPwd")&＃43;"&＃39;";
resultSet &＃61; statement.executeQuery(sql);


当我们在动态获取账户和密码的时候&＃xff0c;如果我们输入的是可以干扰到查询语句的字符&＃xff0c;那么在上面这行语句的执行的时候&＃xff0c;查询就会出现故障&＃xff0c;这时即使输入错误的密码也能登录成功&＃xff1b;举个例子&＃xff1a;

当我们输入以上的这串密码时候&＃xff0c;其实也就相当于把这句sql语句变成了&＃xff1a;“select * from t_user where loginName &＃61; fdsa and loginPwd &＃61; fdsa or “1”&＃61;1“,那么这时这个语句必然成立&＃xff0c;查询成功。
这也就是sql注入的根本原因所在&＃xff01;

下面是使用Statement连接数据库的代码&＃xff1a;

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;/** &＃64;Auther:sunny_wwu* &＃64;Data:2022-04-15 8:39* &＃64;Description:JDBC* &＃64;Version&＃xff1a;1.0* &＃64;Detail&＃xff1a;* */
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 &＃61;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 &＃61; 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* &＃64;param initializeTheInterface Username and password entered by the user* &＃64;return Compare the results, return true for success, false for failures*/private static boolean login(Map<String, String> initializeTheInterface) {//打标记boolean logginSuccess &＃61; false;//JDBC 代码Connection connection &＃61; null;Statement statement &＃61; null;ResultSet resultSet &＃61; null;try {//注册驱动Class.forName("com.mysql.jdbc.Driver");//获取连接connection &＃61; DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bgpowernode","root","123456");//获取数据操作对象statement &＃61; connection.createStatement();//执行SQLString sql &＃61; "select * from t_user where loginName &＃61; &＃39;"&＃43;initializeTheInterface.get("logginName")&＃43;"&＃39; and loginPwd &＃61; &＃39;"&＃43;initializeTheInterface.get("logginPwd")&＃43;"&＃39;";resultSet &＃61; statement.executeQuery(sql);//处理结果集if (resultSet.next()){//登录成功logginSuccess &＃61; true;}} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();}finally {//关闭资源if (resultSet !&＃61; null) {try {resultSet.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (statement !&＃61; null) {try {statement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (connection !&＃61; null) {try {connection.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}return logginSuccess;}/*** Initialize the login interface* &＃64;return entered user&＃39;s username and password*/private static Map<String, String> initializeTheInterface() {Scanner scanner &＃61; new Scanner(System.in);System.out.print("用户名:");String logginName &＃61; scanner.nextLine();System.out.print("密码:");String logginPwd &＃61; scanner.nextLine();Map<String,String> userLogginInfo &＃61; new HashMap<>();userLogginInfo.put("logginName",logginName);userLogginInfo.put("logginPwd",logginPwd);return userLogginInfo;}
}


针对本程序的sql注入解决方案

这里我们是使用preparedStatement&＃xff08;预编译Statement&＃xff09;来解决的。
preparedStatement&＃xff08;预编译Statement&＃xff09;接口它是继承了java。sql.Statement&＃xff0c;属于预编译的数据库操作对象&＃xff0c;其原理是预先编译sql语句的框架&＃xff0c;然后再给sql语句传“值”&＃xff0c;那么由此一来&＃xff0c;即使传进去带有sql敏感的词汇&＃xff0c;也不会扭曲语句的意思。

其中最大的改变就是下面的几行代码&＃xff1a;

String sql &＃61; "select * from t_user where loginName &＃61; ? and loginPwd &＃61; ?";
preparedStatement &＃61; connection.prepareStatement(sql);
//给占位符传值&＃xff0c;第一个问号下标是1&＃xff0c;第二个问号下标是2.......
preparedStatement.setString(1,initializeTheInterface.get("logginName"));
preparedStatement.setString(2,initializeTheInterface.get("logginPwd"));
//执行SQL
resultSet&＃61; preparedStatement.executeQuery();


在这里我们不难看出&＃xff0c;使用占位符先构成查询完成之后&＃xff0c;再由preparedStatement的setString方法把值传进去&＃xff0c;这样bug就解决了。这里需要解释以下“&＃xff1f;”作为占位符&＃xff0c;他只是一个代表&＃xff0c;不参加预编译&＃xff0c;只是后来查询的时候通过传进来的值进行查询的。另外&＃xff0c;setString方法的第一个参数是int类型的数子&＃xff0c;代表的是第几个占位符&＃xff0c;第二个是要传进去的String值。

下面是改进之后的完全代码&＃xff0c;其他地方也有些许改动&＃xff0c;请仔细看完。

import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;
/** &＃64;Auther:sunny_wwu* &＃64;Data:2022-04-16 9:48* &＃64;Description:JDBC-com* &＃64;Version&＃xff1a;1.1* &＃64;Detail&＃xff1a;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 &＃61;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 &＃61; 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* &＃64;param initializeTheInterface Username and password entered by the user* &＃64;return Compare the results, return true for success, false for failures*/private static boolean login(Map<String, String> initializeTheInterface) {//打标记boolean logginSuccess &＃61; false;//JDBC 代码Connection connection &＃61; null;PreparedStatement preparedStatement &＃61; null;ResultSet resultSet &＃61; null;try {//注册驱动Class.forName("com.mysql.jdbc.Driver");//获取连接connection &＃61; DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/bgpowernode","root","123456");//获取数据操作对象//statement &＃61; connection.createStatement();String sql &＃61; "select * from t_user where loginName &＃61; ? and loginPwd &＃61; ?";preparedStatement &＃61; connection.prepareStatement(sql);//给占位符传值&＃xff0c;第一个问号下标是1&＃xff0c;第二个问号下标是2.......preparedStatement.setString(1,initializeTheInterface.get("logginName"));preparedStatement.setString(2,initializeTheInterface.get("logginPwd"));//执行SQLresultSet&＃61; preparedStatement.executeQuery();//处理结果集if (resultSet.next()){//登录成功logginSuccess &＃61; true;}} catch (ClassNotFoundException | SQLException e) {e.printStackTrace();}finally {//关闭资源if (resultSet !&＃61; null) {try {resultSet.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (preparedStatement !&＃61; null) {try {preparedStatement.close();} catch (SQLException throwables) {throwables.printStackTrace();}}if (connection !&＃61; null) {try {connection.close();} catch (SQLException throwables) {throwables.printStackTrace();}}}return logginSuccess;}/*** Initialize the login interface* &＃64;return entered user&＃39;s username and password*/private static Map<String, String> initializeTheInterface() {Scanner scanner &＃61; new Scanner(System.in);System.out.print("用户名:");String logginName &＃61; scanner.nextLine();System.out.print("密码:");String logginPwd &＃61; scanner.nextLine();Map<String,String> userLogginInfo &＃61; new HashMap<>();userLogginInfo.put("logginName",logginName);userLogginInfo.put("logginPwd",logginPwd);return userLogginInfo;}}


总结

面对上面的修改&＃xff0c;那是不是说Statement就完全不需要了呢&＃xff1f;

其实不是&＃xff0c;有的项目也可能会要求允许sql注入&＃xff0c;比如项目中有说明要求sql语句拼接的要求&＃xff0c;这时Statement就起到作用了
总之&＃xff0c;针对这边文章&＃xff0c;我们总结为以下几点

对比一下statement和preparedStatement&＃xff1f;

• 后者存在sql注入的问题&＃xff0c;前者解决了sql注入的问题&＃xff1b;
• 前者是编译一次执行一次&＃xff0c;后者是编译一次&＃xff0c;可执行N次&＃xff0c;这样效率会高一点&＃xff1b;
• 后者会在编译阶段做类型的安全检查&＃xff1b;
• 前者使用较多&＃xff0c;但是在极少数的情况下也是需要用到后者的&＃xff1b;
• 业务方面要求需要进行sql语句拼接的&＃xff0c;可使用Statement&＃xff1b;

最后&＃xff0c;感谢您能看到这里&＃xff0c;如果您觉得文章对您有些帮助的话&＃xff0c;还请帮忙点点赞&＃xff0c;如果您有不同意见也可以在下面评论区一起讨论。