JDBC的使用和SQL註入問題

基本的JDBC使用：

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

/**
 * 測試查詢所有用戶的類
 *
 */
public class QueryAll {
    
    @Test
    public void testQueryAll(){
        Connection conn= null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            //1.註冊驅動
            Class.forName("com.mysql.jdbc.Driver");
            //2.獲取連接
            String url ="jdbc:mysql://localhost:3306/mybase";
            String username="root";
            String password="xuyiqing";
            conn = DriverManager.getConnection(url,username,password);
            //3.獲取執行sql語句對象
            stmt = conn.createStatement();
            //4.編寫sql語句
            String sql = "select * from user";
            //5.執行sql語句
            rs = stmt.executeQuery(sql);
            //6.處理結果集
            while(rs.next()){
                System.out.println("用戶名："+rs.getString(2)+" 密碼："+rs.getString("upassword"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(rs!=null)
                try {
                    rs.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();
                }
        }
    }
}

接下來分析SQL註入問題：

資料庫準備：

CREATE DATABASE mybase;
USE mybase;
CREATE TABLE users(
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(64),
upassword VARCHAR(64)
);
INSERT INTO users (username,upassword) VALUES("zhangsan","123"),("lisi","456"),("wangwu","789");
SELECT * FROM users;

JDBC代碼：

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import org.junit.Test;

public class TestLogin {

    @Test
    public void testLogin() {
        try {
            login("zhangsan", "123");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void login(String username, String password) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mybase";
        String usern = "root";
        String pwd = "xuyiqing";
        Connection conn = DriverManager.getConnection(url, usern, pwd);
        Statement stmt = conn.createStatement();
        String sql = "select * from users where " + "username='" + username + "'and upassword='" + password + "'";
        ResultSet rs = stmt.executeQuery(sql);
        if (rs.next()) {
            System.out.println("登錄成功");
            System.out.println(sql);
        } else {
            System.out.println("賬號或密碼錯誤！");
        }
        if (rs != null) {
            rs.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

這時候運行，輸出：

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

如果這樣：

            login("zhangsan", "1234");

輸出：

賬號或密碼錯誤！

但是，如果這樣做：

    @Test
    public void testLogin() {
        try {
            login("zhangsan' or 'zhangsan", "666");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

輸出卻是登錄成功！：

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

明顯資料庫中沒有這個用戶和相應的密碼，但是登錄成功，這裡就是簡單的SQL註入攻擊

解決辦法：

1.分開驗證，先判斷用戶存在否

2.預處理對象，使用如下的方法：

package demo;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.junit.Test;

public class TestLogin {

    @Test
    public void testLogin() {
        try {
            login("zhangsan' or 'zhangsan", "666");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    public void login(String username, String password) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/mybase";
        String usern = "root";
        String pwd = "xuyiqing";
        Connection conn = DriverManager.getConnection(url, usern, pwd);
        String sql = "select * from users where username=? and upassword=?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        pstmt.setString(1, username);
        pstmt.setString(2, password);
        ResultSet rs = pstmt.executeQuery();
        if (rs.next()) {
            System.out.println("登錄成功");
            System.out.println(sql);
        } else {
            System.out.println("賬號或密碼錯誤！");
        }
        if (rs != null) {
            rs.close();
        }
        if (pstmt != null) {
            pstmt.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

這時候輸出的賬號或密碼錯誤，解決了上邊的SQL註入問題