【14-20】用户登录业务
2022-02-18 17:25:00
# JDBC
用户登录业务
version 01
- 没有处理SQL注入
- 例如：username = abc, password = abc’ or ‘1’=’1
- 用户输入的信息中有SQL语句的关键字，关键字参与SQL语句的编译过程，达到SQL注入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105package _14_用户登录业务;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;
public class Version01 {
public static void main(String[] args) {
// 初始化界面
Map<String, String> userLoginInfo = initUI();
// 验证用户名和密码
boolean loginSuccess = login(userLoginInfo);
// 输出登录结果
System.out.println(loginSuccess == true? "登录成功！" : "登录失败");
}
/**
* 用户登录验证
* @param userLoginInfo
* @return false失败，true成功
*/
private static boolean login(Map<String, String> userLoginInfo) {
boolean loginSuccess = false;
Connection conn = null;
Statement stmt = null;
ResultSet res = null;
try {
// 1. register driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. get connection
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/sunlie",
"root",
"root"
);
// 3. get database operation object
stmt = conn.createStatement();
// 4. execute sql statement
String sql = "select * from t_user where username = '" +
userLoginInfo.get("username") + "' and password = '" +
userLoginInfo.get("password") + "'";
res = stmt.executeQuery(sql);
// 5. process result set
if (res.next()){
loginSuccess = true;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6. close resource
if (res != null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return loginSuccess;
}
/**
* 初始化界面
* @return 用户输入的用户名和密码等信息
*/
private static Map<String,String> initUI() {
Scanner scanner = new Scanner(System.in);
System.out.printf("用户名：");
String username = scanner.nextLine();
System.out.printf("密码：");
String password = scanner.nextLine();
Map<String, String> userLoginInfo = new HashMap<>();
userLoginInfo.put("username",username);
userLoginInfo.put("password",password);
return userLoginInfo;
}
}
version 02
- 解决SQL注入问题
- 只要用户的输入的信息不参与SQL语句的编译过程就可行
- 使用java.sql.PreparedStatement，该接口继承java.sql.Statement接口，属于预编译的数据库操作对象
- PreparedStatement原理是：预先对SQL语句的框架进行编译，然后再给SQL语句传值
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106package _14_用户登录业务;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;
public class Version02 {
public static void main(String[] args) {
// 初始化界面
Map<String, String> userLoginInfo = initUI();
// 验证用户名和密码
boolean loginSuccess = login(userLoginInfo);
// 输出登录结果
System.out.println(loginSuccess == true? "登录成功！" : "登录失败");
}
/**
* 用户登录验证
* @param userLoginInfo
* @return false失败，true成功
*/
private static boolean login(Map<String, String> userLoginInfo) {
boolean loginSuccess = false;
Connection conn = null;
PreparedStatement ps = null; // 更改的位置
ResultSet res = null;
try {
// 1. register driver
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. get connection
conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/sunlie",
"root",
"root"
);
// 3. get precompiled database operation object
// 其中一个 '?' 代表一个占位符，并且不能用 单引号 括起来
String sql = "select * from t_user where username = ? and password = ?";
ps = conn.prepareStatement(sql);
// 给占位符 '?' 传值，第一个 ? 下标是 1, 第二个 ? 是 2
ps.setString(1, userLoginInfo.get("username"));
ps.setString(2, userLoginInfo.get("password"));
// 4. execute sql statement
res = ps.executeQuery();
// 5. process result set
if (res.next()){
loginSuccess = true;
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 6. close resource
if (res != null) {
try {
res.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return loginSuccess;
}
/**
* 初始化界面
* @return 用户输入的用户名和密码等信息
*/
private static Map<String,String> initUI() {
Scanner scanner = new Scanner(System.in);
System.out.printf("用户名：");
String username = scanner.nextLine();
System.out.printf("密码：");
String password = scanner.nextLine();
Map<String, String> userLoginInfo = new HashMap<>();
userLoginInfo.put("username",username);
userLoginInfo.put("password",password);
return userLoginInfo;
}
}