JDBC的使用和SQL注入问题

基本的JDBC使用：

1package demo;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.SQLException;
7import java.sql.Statement;
8
9import org.junit.Test;
10
11/**
12 * 测试查询所有用户的类
13 *
14 */
15public class QueryAll {
16    
17    @Test
18    public void testQueryAll(){
19        Connection conn= null;
20        Statement stmt = null;
21        ResultSet rs = null;
22        try {
23            //1.注册驱动
24            Class.forName("com.mysql.jdbc.Driver");
25            //2.获取连接
26            String url ="jdbc:mysql://localhost:3306/mybase";
27            String username="root";
28            String password="xuyiqing";
29            conn = DriverManager.getConnection(url,username,password);
30            //3.获取执行sql语句对象
31            stmt = conn.createStatement();
32            //4.编写sql语句
33            String sql = "select * from user";
34            //5.执行sql语句
35            rs = stmt.executeQuery(sql);
36            //6.处理结果集
37            while(rs.next()){
38                System.out.println("用户名："+rs.getString(2)+" 密码："+rs.getString("upassword"));
39            }
40        } catch (Exception e) {
41            e.printStackTrace();
42        }finally{
43            if(rs!=null)
44                try {
45                    rs.close();
46                } catch (SQLException e) {
47                    e.printStackTrace();
48                }
49            if(stmt!=null)
50                try {
51                    stmt.close();
52                } catch (SQLException e) {
53                    e.printStackTrace();
54                }
55            if(conn!=null)
56                try {
57                    conn.close();
58                } catch (SQLException e) {
59                    e.printStackTrace();
60                }
61        }
62    }
63}

View Code

接下来分析SQL注入问题：

数据库准备：

1CREATE DATABASE mybase;
2USE mybase;
3CREATE TABLE users(
4uid INT PRIMARY KEY AUTO_INCREMENT,
5username VARCHAR(64),
6upassword VARCHAR(64)
7);
8INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");
9SELECT * FROM users;

JDBC代码：

1package demo;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.ResultSet;
6import java.sql.Statement;
7
8import org.junit.Test;
9
10public class TestLogin {
11
12    @Test
13    public void testLogin() {
14        try {
15            login("zhangsan", "123");
16        } catch (Exception ex) {
17            ex.printStackTrace();
18        }
19    }
20
21    public void login(String username, String password) throws Exception {
22        Class.forName("com.mysql.jdbc.Driver");
23        String url = "jdbc:mysql://localhost:3306/mybase";
24        String usern = "root";
25        String pwd = "xuyiqing";
26        Connection conn = DriverManager.getConnection(url, usern, pwd);
27        Statement stmt = conn.createStatement();
28        String sql = "select * from users where " + "username='" + username + "'and upassword='" + password + "'";
29        ResultSet rs = stmt.executeQuery(sql);
30        if (rs.next()) {
31            System.out.println("登录成功");
32            System.out.println(sql);
33        } else {
34            System.out.println("账号或密码错误！");
35        }
36        if (rs != null) {
37            rs.close();
38        }
39        if (stmt != null) {
40            stmt.close();
41        }
42        if (conn != null) {
43            conn.close();
44        }
45    }
46}

View Code

这时候运行，输出：

登录成功
select * from users where username='zhangsan'and upassword='123'

如果这样：

login("zhangsan", "1234");

输出：

账号或密码错误！

但是，如果这样做：

1    @Test
2    public void testLogin() {
3        try {
4            login("zhangsan' or 'zhangsan", "666");
5        } catch (Exception ex) {
6            ex.printStackTrace();
7        }
8    }

输出却是登录成功！：

登录成功
select * from users where username='zhangsan' or 'zhangsan'and upassword='666'

明显数据库中没有这个用户和相应的密码，但是登录成功，这里就是简单的SQL注入攻击

解决办法：

1.分开验证，先判断用户存在否

2.预处理对象，使用如下的方法：

1package demo;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import org.junit.Test;
8
9public class TestLogin {
10
11    @Test
12    public void testLogin() {
13        try {
14            login("zhangsan' or 'zhangsan", "666");
15        } catch (Exception ex) {
16            ex.printStackTrace();
17        }
18    }
19
20    public void login(String username, String password) throws Exception {
21        Class.forName("com.mysql.jdbc.Driver");
22        String url = "jdbc:mysql://localhost:3306/mybase";
23        String usern = "root";
24        String pwd = "xuyiqing";
25        Connection conn = DriverManager.getConnection(url, usern, pwd);
26        String sql = "select * from users where username=? and upassword=?";
27        PreparedStatement pstmt = conn.prepareStatement(sql);
28        pstmt.setString(1, username);
29        pstmt.setString(2, password);
30        ResultSet rs = pstmt.executeQuery();
31        if (rs.next()) {
32            System.out.println("登录成功");
33            System.out.println(sql);
34        } else {
35            System.out.println("账号或密码错误！");
36        }
37        if (rs != null) {
38            rs.close();
39        }
40        if (pstmt != null) {
41            pstmt.close();
42        }
43        if (conn != null) {
44            conn.close();
45        }
46    }
47}

View Code

这时候输出的账号或密码错误，解决了上边的SQL注入问题