PreparedStatement防止SQL注入

添加数据&＃xff1a;

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class TestInsert {public static void main(String[] args) {Connection connection &＃61; null;PreparedStatement preparedStatement &＃61; null;ResultSet resultSet &＃61; null;try {//3、获取数据库连接connection &＃61; JDBCUtils.getConnection();//使用&＃xff1f;占位符替代参数String sql &＃61; "INSERT INTO &＃96;users&＃96;(&＃96;id&＃96;,&＃96;NAME&＃96;,&＃96;PASSWORD&＃96;,&＃96;email&＃96;,&＃96;birthday&＃96;) " &＃43;"VALUES (?,?,?,?,?);";//4、获取执行sql的对象//预编译sql&＃xff0c;先写sql&＃xff0c;然后不执行preparedStatement &＃61; connection.prepareStatement(sql);//手动给参数赋值preparedStatement.setInt(1, 4);preparedStatement.setString(2, "hyc");preparedStatement.setString(3, "123456");preparedStatement.setString(4, "123456789&＃64;qq.com");//java.sql.Date 是数据库的Date &＃xff0c;只包含年月日信息 &＃xff0c;它是java.util.Date&＃xff08;包含年月日和时分秒信息&＃xff09;的子类preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));//5、执行sql语句//6、返回执行结果集int num &＃61; preparedStatement.executeUpdate();if (num > 0) {System.out.println("插入数据成功&＃xff01;");}} catch (SQLException throwables) {throwables.printStackTrace();} finally {//7、释放连接JDBCUtils.release(connection, preparedStatement, resultSet);}}
}


删除数据&＃xff1a;

package com.hyc.study03;import com.hyc.study02.utils.JDBCUtils;import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestDelete {public static void main(String[] args) {Connection connection &＃61; null;PreparedStatement preparedStatement &＃61; null;ResultSet resultSet &＃61; null;try {//3、获取数据库连接connection &＃61; JDBCUtils.getConnection();//使用&＃xff1f;占位符替代参数String sql &＃61; "DELETE FROM &＃96;users&＃96; WHERE id&＃61;?";//4、获取执行sql的对象//预编译preparedStatement &＃61; connection.prepareStatement(sql);//手动给参数赋值preparedStatement.setInt(1, 4);//5、执行sql语句//6、返回执行结果集int num &＃61; preparedStatement.executeUpdate();if (num > 0) {System.out.println("删除数据成功&＃xff01;");}} catch (SQLException throwables) {throwables.printStackTrace();} finally {//7、释放连接JDBCUtils.release(connection, preparedStatement, resultSet);}}
}


修改数据&＃xff1a;

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestUpdate {public static void main(String[] args) {Connection connection &＃61; null;PreparedStatement preparedStatement &＃61; null;ResultSet resultSet &＃61; null;try {//3、获取数据库连接connection &＃61; JDBCUtils.getConnection();//使用&＃xff1f;占位符替代参数String sql &＃61; "UPDATE &＃96;users&＃96; SET &＃96;NAME&＃96;&＃61;?,&＃96;email&＃96;&＃61;? WHERE &＃96;id&＃96;&＃61;?";//4、获取执行sql的对象//预编译preparedStatement &＃61; connection.prepareStatement(sql);//手动给参数赋值preparedStatement.setString(1, "zhangsan");preparedStatement.setString(2, "zhangsan&＃64;sina.com");preparedStatement.setInt(3, 1);//5、执行sql语句//6、返回执行结果集int num &＃61; preparedStatement.executeUpdate();if (num > 0) {System.out.println("更新数据成功&＃xff01;");}} catch (SQLException throwables) {throwables.printStackTrace();} finally {//7、释放连接JDBCUtils.release(connection, preparedStatement, resultSet);}}
}


查询数据&＃xff1a;

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;public class TestSelect {public static void main(String[] args) {Connection connection &＃61; null;PreparedStatement preparedStatement &＃61; null;ResultSet resultSet &＃61; null;try {//3、获取数据库连接connection &＃61; JDBCUtils.getConnection();//使用&＃xff1f;占位符替代参数String sql &＃61; "SELECT * FROM &＃96;users&＃96; WHERE id&＃61;?";//4、获取执行sql的对象//预编译preparedStatement &＃61; connection.prepareStatement(sql);//手动给参数赋值preparedStatement.setInt(1, 1);//5、执行sql语句//6、返回执行结果集resultSet &＃61; preparedStatement.executeQuery();while (resultSet.next()) {System.out.println(resultSet.getString("NAME"));}} catch (SQLException throwables) {throwables.printStackTrace();} finally {//7、释放连接JDBCUtils.release(connection, preparedStatement, resultSet);}}
}


PreparedStatement防止SQL注入&＃xff1a;
PreparedStatement防止SQL注入的本质是将传递进来的参数当做字符&＃xff08;想当于给传进来的参数包装成一个新的字符串&＃xff09;&＃xff0c;如果其中存在转义字符&＃xff0c;例如 ‘ 会被直接转义。这样能够避免字符串拼接成非法的sql语句&＃xff0c;造成数据泄露。

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.*;public class PourIntoSql {public static void main(String[] args) {login("&＃39;&＃39; or &＃39;1&＃61;1", "123456");}public static void login(String username, String psw) {Connection connection &＃61; null;PreparedStatement preparedStatement &＃61; null;ResultSet resultSet &＃61; null;try {connection &＃61; JDBCUtils.getConnection();String sql &＃61; "SELECT * FROM &＃96;users&＃96; WHERE &＃96;NAME&＃96;&＃61;? AND &＃96;PASSWORD&＃96;&＃61;?";preparedStatement &＃61; connection.prepareStatement(sql);preparedStatement.setString(1, username);preparedStatement.setString(2, psw);resultSet &＃61; preparedStatement.executeQuery();while (resultSet.next()) {System.out.println(resultSet.getString("NAME"));System.out.println("&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;&＃61;");}} catch (SQLException throwables) {throwables.printStackTrace();} finally {JDBCUtils.release(connection, preparedStatement, resultSet);}}
}


结果&＃xff1a;

在PreparedStatement的作用下&＃xff0c;再尝试通过字符串拼接达到SQL注入的目的无法实现。