SQL注入(Statement和PreparedStatement)
- 参考https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html
- 参考https://docs.oracle.com/javase/8/docs/api/java/sql/Statement.html
- 参考https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html
- 参考https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html
- 参考https://github.com/apache/commons-dbcp
- 参考https://commons.apache.org/proper/commons-dbcp/
- maven依赖
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.6</version>
</dependency>
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-dbcp2</artifactId>
<version>2.6.0</version>
</dependency>
- 使用
package com.injection;
import org.apache.commons.dbcp2.BasicDataSource;
import java.sql.*;
public class SqlInjection {
// 创建数据库使用
final static String JDBC_URL_INIT = "jdbc:mysql://192.168.1.5:3306/?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
// 操作数据表使用
final static String JDBC_URL = "jdbc:mysql://192.168.1.5:3306/jdbc?useSSL=false&useUnicode=true&characterEncoding=UTF-8";
public static BasicDataSource basicDataSource = new BasicDataSource();
//创建数据库
public static void createDB() throws SQLException {
BasicDataSource basicDataSource = new BasicDataSource();
basicDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
basicDataSource.setUrl(JDBC_URL_INIT);
basicDataSource.setUsername("root");
basicDataSource.setPassword("root");
Connection connection = null;
Statement statement = null;
// 创建名为JDBC的数据库
try {
// 连接数据库
connection = basicDataSource.getConnection();
statement = connection.createStatement();
boolean bool = statement.execute("CREATE DATABASE IF NOT EXISTS jdbc DEFAULT CHARSET utf8 COLLATE utf8_general_ci");
System.out.println(bool);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(connection != null)connection.close();
if(statement != null)statement.close();
}
}
//创建数据表
public static void createTable() throws SQLException {
Connection connection = null;
Statement statement = null;
// 创建user表
try {
// 连接数据库
connection = basicDataSource.getConnection();
statement = connection.createStatement();
boolean bool = statement.execute("CREATE TABLE `user` (\n" +
" `user_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
" `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,\n" +
" `age` int(11) NULL DEFAULT NULL,\n" +
" `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
" PRIMARY KEY (`id`) USING BTREE\n" +
") ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic");
System.out.println(bool);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(connection != null)connection.close();
if(statement != null)statement.close();
}
}
//插入(更新\删除)数据
public static void insertTable() throws SQLException {
Connection connection = null;
Statement statement = null;
try {
// 连接数据库
connection = basicDataSource.getConnection();
statement = connection.createStatement();
statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES ('雷OK', 'XM', 200 )",Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES (\"李What's\", 'BD', 300 )",Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES ('马氪金', 'TX', 100 )",Statement.RETURN_GENERATED_KEYS);
statement.executeUpdate("INSERT INTO user(user_name, password, age ) VALUES ('马买买', 'ALBB', 500 )",Statement.RETURN_GENERATED_KEYS);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(connection != null)connection.close();
if(statement != null)statement.close();
}
}
//SQL注入查询数据登陆 Statement接口
public static User queryTable(String user_name, String password) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
User user = new User();
try {
// 连接数据库
connection = basicDataSource.getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery("select id,user_name,password,age from user where user_name = '" +
user_name + "' and password = '" + password + "'");
//4.输出查询结果
while (resultSet.next()){
user.setAge(resultSet.getInt("age"));
user.setUser_name(resultSet.getString("user_name"));
user.setPassword(resultSet.getString("password"));
user.setId(resultSet.getInt("id"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(connection != null)connection.close();
if(statement != null)statement.close();
if(resultSet != null)resultSet.close();
}
return user;
}
//SQL注入查询数据登陆 PreparedStatement
public static User queryTableSql(String user_name, String password) throws SQLException {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
User user = new User();
try {
// 连接数据库
connection = basicDataSource.getConnection();
preparedStatement = connection.prepareStatement("select id,user_name,password,age from user where user_name = ? and password = ?");
preparedStatement.setString(1,user_name);
preparedStatement.setString(2,password);
resultSet = preparedStatement.executeQuery();
//4.输出查询结果
while (resultSet.next()){
user.setAge(resultSet.getInt("age"));
user.setUser_name(resultSet.getString("user_name"));
user.setPassword(resultSet.getString("password"));
user.setId(resultSet.getInt("id"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if(connection != null)connection.close();
if(preparedStatement != null)preparedStatement.close();
if(resultSet != null)resultSet.close();
}
return user;
}
public static void main(String[] args) throws SQLException {
basicDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
basicDataSource.setUrl(JDBC_URL);
basicDataSource.setUsername("root");
basicDataSource.setPassword("root");
// createDB();
// createTable();
// insertTable();
System.out.println(queryTable("雷OK","XM"));
System.out.println(queryTable("雷OK","123"));
System.out.println(queryTable("雷OK'; -- ","123")); //注入
System.out.println(queryTable("雷OK'; # ","123")); //注入
System.out.println("===========================");
System.out.println(queryTableSql("雷OK","XM"));
System.out.println(queryTableSql("雷OK","123"));
System.out.println(queryTableSql("雷OK'; -- ","123")); //注入
System.out.println(queryTableSql("雷OK'; # ","123")); //注入
}
}
class User{
private String user_name;
private Integer age;
private String password;
private Integer id;
public User(){}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
User(String user_name, Integer age, String password) {
this.user_name = user_name;
this.age = age;
this.password = password;
}
@Override
public String toString() {
return "User{" +
"user_name='" + user_name + '\'' +
", age=" + age +
", password='" + password + '\'' +
", id=" + id +
'}';
}
}
开发工具
