Sql注入问题和PreparedStatement预编译示例

1.SQL注入问题
由于sql语句的合法性没有判断或者过滤不严，攻击者可以用事先构造好的查询语句，在管理员不知情的情况下实现非法操作，欺骗服务器，导致数据泄露

示例代码如下：

package utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

/**
数据库工具类
*/
public class JdbcUtils {
    // jdbc驱动
    private static String driver = null;

    // 数据库url
    private static String url = null;

    // 数据库用户
    private static String username = null;

    // 数据库密码
    private static String password = null;

    static {
        try {
            InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // jdbc驱动加载
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnect() throws Exception {
        return DriverManager.getConnection(url, username, password);
    }

    public static void releaseConnection(Connection connection, Statement statement, ResultSet set) throws Exception {
        if (set != null) {
            set.close();
        }
        if (statement != null) {
            statement.close();
        }
        if (connection != null) {
            connection.close();
        }
    }
}

其中db.properties为数据库配置文件，需放在工程resources文件下：

内容如：

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

构造数据库实体类：

import utils.JdbcUtils;

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

public class JdbcSecondDemo {
    public static void main(String[] args) throws Exception {
        Connection connection = null;

        Statement statement = null;

        ResultSet set = null;

        try {
            connection = JdbcUtils.getConnect();
            statement = connection.createStatement();

            String name = "小明";
            int id = 15;

            String sql = "select * from workdb.t_students where name = '" + name + "' and id = " + id;
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getObject("id"));
                System.out.println(resultSet.getObject("name"));
                System.out.println(resultSet.getObject("gender"));
                System.out.println(resultSet.getObject("grade"));
                System.out.println(resultSet.getObject("score"));
                System.out.println("====================================");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(connection, statement, set);
        }
    }
}

能正常查询结果：

通过修改以下代码进行sql构造、注入：

String name = "xxx' or '1=1";
String id = "'11111' or '1=1'";

则执行sql后会查询到所有的数据：

2.PreparedStatement预编译

• 可以防止SQL注入，效率更高
• 本质是对于传入的参数当作字符处理，即最终传入的值为'name'->''' or 1=1','id'->''11111' or 1=1',如果字符中带有特殊字符，如：''，则会进行转意

import utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class JdbcThirdDemo {
    public static void main(String[] args) throws Exception {
        Connection connection = null;

        PreparedStatement preparedStatement = null;

        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnect();

            // 预编译sql，先写sql，但是不执行
            String name = "'' or 1=1";
            String id = "'11111' or 1=1";
            String sql = "select * from workdb.t_students where name = ? and id = ?";

            preparedStatement = connection.prepareStatement(sql);

            // 手动给参数赋值
            preparedStatement.setObject(1, name);
            preparedStatement.setObject(2, id);

            // 执行sql
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getObject("id"));
                System.out.println(resultSet.getObject("name"));
                System.out.println(resultSet.getObject("gender"));
                System.out.println(resultSet.getObject("grade"));
                System.out.println(resultSet.getObject("score"));
                System.out.println("====================================");
            }


        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(connection, preparedStatement, resultSet);
        }
    }
}

执行截图：查询不到结果