sql注入问题--PreparedStatement增删查改CRUD

sql注入问题

用户登录，用statement存在sql注入问题，如下

 //String sql = "select id,name,password,email,birthday from t_user where name='"
        //                + name +"' and password='" + password+ "'";
        //String sql = "select id,name,password,email,birthday from t_user where name='xxx' and password='xxx' or'1'=1";

判断条件被改成了 or’1’=1
所以必然select成功

解决方法PreparedStatement

1. sql 用？表示参数

String sql = “select id,name,password,email,birthday from t_user where name=? and password=?”;

2. 用PreparedStatement，这里写入sql变量

PreparedStatement stmt = conn.prepareStatement(sql);//这里写入sql变量

3. 写入参数

stmt.setString(1, name);
stmt.setString(2, password);

4. Query这里，没有sql变量了

       ResultSet rs = stmt.executeQuery();
   

package com.king.service;

import com.king.User;
import com.king.util.DBUtil;

import java.sql.*;

//sql注入问题解决，使用PreparedStatement，修改代码，
public class LoginServiceNew {

    public User findUserByUsernameAndPsaaword(String name, String password) {
        User u = null;
        Connection conn = null;
        //sql里 参数用？ 代替
        String sql = "select id,name,password,email,birthday from t_user where name=? and password=?";
        //String sql = "select id,name,password,email,birthday from t_user where name='"
        //                + name +"' and password='" + password+ "'";
        //String sql = "select id,name,password,email,birthday from t_user where name='xxx' and password='xxx' or'1'=1";

        System.out.println(sql);

        try {
            conn = DBUtil.getConnection();

            PreparedStatement stmt = conn.prepareStatement(sql);//这里写入sql变量

            stmt.setString(1, name);
            stmt.setString(2, password);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                //这while里面的 user新对象，必不可少
                u = new User();
                u.setId(rs.getInt("id"));
                u.setName(rs.getString("name"));
                u.setEmail(rs.getString("email"));
                u.setPassword(rs.getString("password"));
                u.setBirthday(rs.getDate("birthday"));
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }

        return u;
    }
}

PreparedStatement增删查改CRUD

package com.king;


import com.king.util.DBUtil;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * PreparedStatement 使用
 */

public class CRUD_Test04 {


    @Test
    public void testSelect()  {
        String sql = "select id,name,password,email,birthday from t_user";

       try(
               //获取连接Connection
               Connection conn = DBUtil.getConnection();

               //得到 执行sql语句的对象statement
               PreparedStatement stmt = conn.prepareStatement(sql);

               //执行sql语句，并得到返回结果
               ResultSet rs = stmt.executeQuery();
               ){


           //处理结果
           List<User> userList = new ArrayList<>();
           while (rs.next()){
               User u = new User();
               //创建User对象的语句，一定要写在里面，否则出现问题，所有返回值都写到一个数组的元素里了
               u.setId(rs.getInt("id"));
               u.setName(rs.getString("name"));
               u.setPassword(rs.getString("password"));
               u.setEmail(rs.getString("email"));
               u.setBirthday(rs.getDate("birthday"));
               userList.add(u);
           }
           System.out.println(userList);
       } catch (SQLException e) {
           e.printStackTrace();
       }


    }

    @Test
    public void testInsert(){

    String sql = "insert into t_user(name,password,email,birthday) values (?,?,?,?)";
    try (
            //获取连接Connection
            Connection conn = DBUtil.getConnection();

            //得到 执行sql语句的对象statement
            PreparedStatement stmt = conn.prepareStatement(sql)
    ){
        stmt.setString(1,"dog");
        stmt.setString(2,"123456");
        stmt.setString(3,"dog@163.com");
        stmt.setString(4,"1997-01-01");

        int flag = stmt.executeUpdate();
        if(flag>0){
            System.out.println("succeeded");
        }else {
            System.out.println("failed");
        }


    } catch (SQLException e) {
        e.printStackTrace();
    }


    }



    @Test
    public void testUpdate() {

        String sql = "update t_user set name='horse' where id=1";
        try (
                //获取连接Connection
                Connection conn = DBUtil.getConnection();
                //得到 执行sql语句的对象statement
                PreparedStatement stmt = conn.prepareStatement(sql)
                ){
            //执行sql语句，并得到返回结果 (update，返回一个flag）
            int flag = stmt.executeUpdate();
            if(flag>0){
                System.out.println("update succeeded");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {

        String sql = "delete from t_user where id=5";
        try(
                //获取连接Connection
                Connection conn = DBUtil.getConnection();

                //得到 执行sql语句的对象
                PreparedStatement stmt = conn.prepareStatement(sql)

                ){
            int flag = stmt.executeUpdate();
            if(flag>0){
                System.out.println("delete succeeded");
            }else{
                System.out.println("failed");
            }
        }



    }
}

用户对象已经有对应创建

user 用户对象属性

package com.king;
/**
 * 用户表
 * 属性，getset，tostring方法
 */

import java.util.Date;

public class User {
    private int id;
    private String name;
    private String password;
    private String email;
    private Date birthday;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", password='" + password + '\'' +
                ", email='" + email + '\'' +
                ", birthday=" + birthday +
                '}';
    }
}

DBUtil 注册驱动，db参数写入

package com.king.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ResourceBundle;

public class DBUtil {
   //统一定好参数，通过 ResourceBundle调取 配置文件db.properties内参数属性
    private static String driverClass;
    private static String url;
    private static String username;
    private static String password;
//ResourceBundle.getBundle("db"),要求在classpath中找到db.properties,要移动到main/resources目录
    static{
        ResourceBundle rb = ResourceBundle.getBundle("db");
        driverClass = rb.getString("driverClass");
        url = rb.getString("url");
        username = rb.getString("username");
        password = rb.getString("password");

        //1.注册驱动   参数写入
        try {
            Class.forName(driverClass);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }


    //2.获得连接   参数写入
    public static Connection getConnection() throws SQLException {

        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }

}

db.properties 数据库参数配置文本

//配置文件不能有多余空格
driverClass=com.mysql.jdbc.Driver  
//错误：driverClass，必须和调用的地方名字写对，写错了找了半天bug
url=jdbc:mysql://localhost:3306/testcopy
username=root
password=root