JDBC 02: 登录校验

1. Version 1

容易引发sql注入的问题，造成非法访问

package com.Jasper2003.jdbc01;

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

public class JDBCDemo01 {
    public static void main(String[] args) {
        System.out.println(selectByUsernamePassword("siki","123"));
    }

public static boolean selectByUsernamePassword(String username,String password) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            String url = "jdbc:mysql://localhost:3306/web01?useUnicode=true&CharacterEncoding=UTF8&useSSL=false";
            con = DriverManager.getConnection(url,"root","root");
            stmt = con.createStatement();
            
            String sql = "select * from user where username = '"+username+"' and password = '"+password+"'";
            rs = stmt.executeQuery(sql);
            
            if(rs.next()) {
                return true;
            }else {
                return false;
            }                
        }catch (Exception e) {        
            e.printStackTrace();
        } finally {
        
            try {
                if(rs!=null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        
            try {
                if(stmt!=null)
                    stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        
            try {
                if(con!=null)
                    con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return false;
    } 
}

引发sql注入问题后的sql语句和其结果:

2.  Modified Version: Use prepared statement 

public static boolean selectByUsernamePassword(String username,String password) {
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            String url = "jdbc:mysql://localhost:3306/web01?useUnicode=true&CharacterEncoding=UTF8&useSSL=false";
            con = DriverManager.getConnection(url,"root","root");
            stmt = con.createStatement();             

　　　　　　　　　　String sql = "select * from user where username = ? and password = ?";
　　　　　　　　　　PreparedStatement pstmt = con.prepareStatement(sql);

　　　　　　　　　　pstmt.setString(1, username);
　　　　　　　　　　pstmt.setString(2, password);

　　　　　　　　　　rs = pstmt.executeQuery();

            if(rs.next()) {
                return true;
            }else {
                return false;
            }                
        }catch{
             // Omitted
        } finally{
             // Omitted
        }  
　　　　　　　return false;　 
}