SQL注入举例及解决方法

package demo;

import domain.User;
import util.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class JdbcDemo9 {
    public static void main(String[] args) {
        //1.键盘录入
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        //2.调用方法
        boolean flag = new JdbcDemo9().login(username,password);
        //3.判断结果，并判断
        if(flag){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }
    }
    /*
    * 此方法登录验证规则，容易出现SQL注入问题。
    * */
    public boolean login(String username,String password) {
        if (username == null || password == null){
            return false;
        }
            Statement stmt = null;
            Connection conn = null;
            ResultSet rs = null;

        try{
            conn = JDBCUtils.getConnection();
            //sql 语句符号使用要注意
            String sql = "select * from user where username = '"+username+"'and password = '"+password+"'";
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            return rs.next();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,stmt,conn);
        }

        return false;
    }
}

package util;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.util.Properties;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.ResultSet;


//获取连接驱动的方法，抽取作为一个工具类
public class JDBCUtils {
    //静态变量才能被静态代码块访问，静态方法访问。
    private static String url;
    private static String user;
    private static String password;
    private static String driver;

    static {
        try {
            //读取资源文件，获取值
            //1.创建Properties集合类
            Properties pro = new Properties();
            // 获取src路径下的文件的方式 ： ClassLoader 类加载器
            ClassLoader classloader = JDBCUtils.class.getClassLoader();
            URL res = classloader.getResource("jdbc.properties");
            String path = res.getPath();
            //2.加载文件
            pro.load(new FileReader(path));
            //3.获取数据，赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        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语句为：“select * from user where username = '”+username+"‘and password = ‘"+password+"’";

结合输入：username = hjkh，password = a’ or ‘a’ = 'a ,
sql 语句为 ： select * from user where username = ‘hjkh’ and password = ‘a’ or ‘a’ = ‘a’
使得sql语句等效为：select * from user ,可以将数据表中的内容全查询出来，并显示登陆成功。
为了解决静态sql语句的不安全问题，常采用预编译sql语句进行安全校验。

package demo;

import util.JDBCUtils;

import java.sql.*;
import java.util.Scanner;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JdbcDemo10 {
    public static void main(String[] args) {
        //1.键盘录入
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String username = sc.nextLine();
        System.out.println("请输入密码");
        String password = sc.nextLine();
        //2.调用方法
        boolean flag = new JdbcDemo10().login(username,password);
        //3.判断结果，并判断
        if(flag){
            System.out.println("登录成功");
        }else {
            System.out.println("登录失败");
        }
    }
    
    public boolean login(String username,String password) {
        if (username == null || password == null){
            return false;
        }
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstmt = null;
        try{
            conn = JDBCUtils.getConnection();
            /*
             * 该方法使用PreparedStatement对象的预编译的方式避免SQL注入问题。
             * 参数使用？作为占位符
             * */
            String sql = "select * from user where username = ? and password = ?";
            pstmt = conn.prepareStatement(sql);
            //为占位符？赋值
            pstmt.setString(1,username);
            pstmt.setString(2,password);
            rs = pstmt.executeQuery();
            return rs.next();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBCUtils.close(rs,pstmt,conn);
        }

        return false;
    }
}