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