1、SQL注入
SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。
1.1、SQL注入问题
SQL存在漏洞,会被攻击导致数据泄漏
package com.jialidun.test;
import com.jialidun.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SqlInject {
public static void main(String[] args) {
//login("rygar", "123456"); //正常输入情况下 rygar 123456
login("'or '1=1'#",""); //SQL注入攻击
}
//登陆业务
public static void login(String username,String password){
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//创造传输器
statement = connection.createStatement();
//执行sql
String sql = "select * from users where `NAME`='"+username +"' AND `PASSWORD`= '"+ password+"'";
rs = statement.executeQuery(sql);
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//释放连接
JdbcUtils.close(connection,statement,rs);
}
}
}
2、preparedStatement对象
PreparedStatement继承Statement
preparedStatement可以防止Sql注入。效果更好
1、新增
package com.jialidun.preparedSt;
import java.sql.*;
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pst = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC%2B8";
String username = "root";
String password = "root";
// 1.获取连接
connection = DriverManager.getConnection(url, username, password);
String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
//2、获取传输器并执行SQL
pst = connection.prepareStatement(sql);
pst.setInt(1,4);
pst.setString(2,"小明");
pst.setString(3,"xiaoming123");
pst.setString(4,"xiaoming@163.com");
pst.setDate(5,new Date(new java.util.Date().getTime()));
int num = pst.executeUpdate();
if(num > 0){
System.out.println("添加用户成功!");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
//释放连接
if(pst!=null){
try {
pst.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
2、修改
package com.jialidun.preparedSt;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pst = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC%2B8";
String username = "root";
String password = "root";
//1.获取连接
connection = DriverManager.getConnection(url, username, password);
String sql = "update users set `PASSWORD`=? WHERE id = 4";
//获取Statement并执行sql
pst = connection.prepareStatement(sql);
pst.setString(1,"xiaoming111");
int num = pst.executeUpdate();
if(num>0){
System.out.println("修改成功!");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
if(pst!=null){
try {
pst.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
3、查询
package com.jialidun.preparedSt;
import java.sql.*;
public class TestRead {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pst = null;
ResultSet resultSet = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "root";
//获取连接
connection = DriverManager.getConnection(url, username, password);
String sql = "select * from users where id= ?";
pst = connection.prepareStatement(sql);
pst.setInt(1,4);
resultSet = pst.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("NAME"));
System.out.println(resultSet.getString("PASSWORD"));
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
//释放连接
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(pst!=null){
try {
pst.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
4、删除
package com.jialidun.preparedSt;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement pst = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&useSSL=true&characterEncoding=utf8&serverTimezone=UTC%2B8";
String username = "root";
String password = "root";
//获取数据库连接
connection = DriverManager.getConnection(url,username,password);
String sql = "delete from users where id = ?";
pst = connection.prepareStatement(sql);
pst.setInt(1,4);
int num = pst.executeUpdate();
if(num>0){
System.out.println("删除成功!");
}
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}finally {
//释放连接
if(pst!=null){
try {
pst.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
}
2.1、SQL注入解决
package com.jialidun.preparedSt;
import com.jialidun.utils.JdbcUtils;
import java.sql.*;
public class SqlInject {
public static void main(String[] args) {
//login("rygar", "123456"); //正常输入情况下 rygar 123456
login("'or '1=1'#",""); //SQL注入攻击
}
//登陆业务
public static void login(String username,String password){
Connection connection = null;
PreparedStatement pst = null;
ResultSet rs = null;
try {
//获取连接
connection = JdbcUtils.getConnection();
//执行sql
String sql = "select * from users where `NAME`=? and `PASSWORD`=?";
pst = connection.prepareStatement(sql);
pst.setString(1,username);
pst.setString(2,password);
rs = pst.executeQuery();
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("PASSWORD"));
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
//释放连接
JdbcUtils.close(connection,pst,rs);
}
}
}