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