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注入问题