SQL拼接、注入、处理blob类型问题

SQL拼接、注入、处理blob类型问题

SQL拼接

需求：键盘输入teacher信息，并添加到数据库

public class TestProblem1 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 连接MySQL
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String passworld = "123456";
        Connection connection = DriverManager.getConnection(url,user,passworld);
        //键盘输入教师信息
        Scanner input = new Scanner(System.in);
        System.out.println("请输入id");
        String tid = input.nextLine();
        System.out.println("请输入姓名");
        String tname = input.nextLine();
        // 编写SQL语句
        String sql = "insert into teacher values(" + "'" + tid +"'" + "," + "'" + tname +"');" ;
        // 创建Statement对象
        Statement statement = connection.createStatement();
        int i = statement.executeUpdate(sql);
        System.out.println(i>0? "输入成功":"输入失败");
        // 关闭连接
        statement.close();
        connection.close();
        input.close();
    }
}

总体来看，麻烦，尤其是拼接处

SQL注入

需求：输入教师编号，查询对应教师姓名

import java.sql.*;
import java.util.Scanner;

/*需求：输入id，查询到该id的教师信息*/
public class TestProblem2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 连接MySQL
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url,user,password);
        // SQL语句
        Scanner input = new Scanner(System.in);
        System.out.println("请输入tid");
        String s = input.next();
        String sql = "select * from teacher where tid = '" + s +"';";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()){
            String string = resultSet.getString(2);
            System.out.println(string);
        }
        statement.close();
        connection.close();
        input.close();
    }
}

但是，很容易出现这种i情况：

SELECT tname FROM `teacher` WHERE tid = '01' OR 1=1

这样的话，就会将所有的列都显示出来，数据安全性很低

SQL处理blob类型

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TestProblem3 {
    public static void main(String[] args) throws FileNotFoundException, ClassNotFoundException, SQLException {
        FileInputStream fileInputStream = new FileInputStream("C:\\Users\\dell\\Pictures\\Feedback\\我家七七\\8.jpg");
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 连接MySQL
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String passworld = "123456";
        Connection connection = DriverManager.getConnection(url,user,passworld);
        // 编写SQL
        String sql = "UPDATE teacher SET photo = " + fileInputStream + "WHERE tname = '段丽琪'; ";
        // 创建Statement对象
        Statement statement = connection.createStatement();
        int i = statement.executeUpdate(sql);
        System.out.println(i>0?"添加成功":"添加失败");
    }
	statement.close();
    connection.close();
    fileInputStream.close();
}

会报错
综上，我们选用preparestatement方法

PreparedStatement解决SQL拼接问题

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;


public class TestPreparedStatement {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.登录MySQL
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, user, password);
        // 编写SQL语句
        String sql = "insert into teacher(tid,tname) values(?,?)";
        // 创建PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        // 对？具体值传入
        preparedStatement.setString(1,"09");
        preparedStatement.setString(2,"村上春树");

        int i = preparedStatement.executeUpdate();
        System.out.println(i>0?"添加成功":"添加失败");
    }
    	connection.close();
        preparedStatement.close();
}

PreparedStatement解决SQL注入问题

import java.sql.*;
import java.util.Scanner;

public class TestPreparedStatement2 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 登录SQL
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        // 连接数据库
        Connection connection = DriverManager.getConnection(url, user, password);
        // 编写SQL语句（根据tid输出教师名）
        Scanner input = new Scanner(System.in);
        String tid = input.next();
        String sql = "select * from teacher where tid = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,tid);
        // 获取结果
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            for (int i = 1; i <= 2; i++) {
                System.out.println(resultSet.getObject(i));
            }
        }
        resultSet.close();
        preparedStatement.close();
        input.close();
    }
}

PreparedStatement解决blob问题

MySQL默认不能传输过大的文件，需要先停止MySQL服务后修改my.ini文件
max_allowed_packet=16M
修改数据类型成mediumblob（blob太小）

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;

public class TestPreparedStatement3 {
    public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
        // 读取照片
        FileInputStream fileInputStream = new FileInputStream("C:\\Users\\dell\\Pictures\\Feedback\\我家七七\\8.jpg");
        // 注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 连接MySQL
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String passworld = "123456";
        Connection connection = DriverManager.getConnection(url,user,passworld);
        // 编写SQL
        String sql = "UPDATE teacher SET photo =? WHERE tname = '段丽琪'; ";
        // 创建PreparedStatement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,fileInputStream);
        int i = preparedStatement.executeUpdate();
        System.out.println(i>0?"添加成功":"添加失败");
        // 关闭
        preparedStatement.close();
        connection.close();
        fileInputStream.close();
    }
}