PreparedStatement防止SQL注入

添加数据：

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestInsert {
    
    public static void main(String[] args) {
    
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
    
            //3、获取数据库连接
            connection = JDBCUtils.getConnection();

            //使用？占位符替代参数
            String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) " +
                    "VALUES (?,?,?,?,?);";
            //4、获取执行sql的对象
            //预编译sql，先写sql，然后不执行
            preparedStatement = connection.prepareStatement(sql);
            //手动给参数赋值
            preparedStatement.setInt(1, 4);
            preparedStatement.setString(2, "hyc");
            preparedStatement.setString(3, "123456");
            preparedStatement.setString(4, "[email protected]");
            //java.sql.Date 是数据库的Date ，只包含年月日信息 ，它是java.util.Date（包含年月日和时分秒信息）的子类
            preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));

            //5、执行sql语句
            //6、返回执行结果集
            int num = preparedStatement.executeUpdate();
            if (num > 0) {
    
                System.out.println("插入数据成功！");
            }

        } catch (SQLException throwables) {
    
            throwables.printStackTrace();
        } finally {
    
            //7、释放连接
            JDBCUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

删除数据：

package com.hyc.study03;

import com.hyc.study02.utils.JDBCUtils;

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

public class TestDelete {
    
    public static void main(String[] args) {
    
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
    
            //3、获取数据库连接
            connection = JDBCUtils.getConnection();

            //使用？占位符替代参数
            String sql = "DELETE FROM `users` WHERE id=?";
            //4、获取执行sql的对象
            //预编译
            preparedStatement = connection.prepareStatement(sql);
            //手动给参数赋值
            preparedStatement.setInt(1, 4);

            //5、执行sql语句
            //6、返回执行结果集
            int num = preparedStatement.executeUpdate();
            if (num > 0) {
    
                System.out.println("删除数据成功！");
            }
        } catch (SQLException throwables) {
    
            throwables.printStackTrace();
        } finally {
    
            //7、释放连接
            JDBCUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

修改数据：

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestUpdate {
    
    public static void main(String[] args) {
    
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
    
            //3、获取数据库连接
            connection = JDBCUtils.getConnection();

            //使用？占位符替代参数
            String sql = "UPDATE `users` SET `NAME`=?,`email`=? WHERE `id`=?";
            //4、获取执行sql的对象
            //预编译
            preparedStatement = connection.prepareStatement(sql);
            //手动给参数赋值
            preparedStatement.setString(1, "zhangsan");
            preparedStatement.setString(2, "[email protected]");
            preparedStatement.setInt(3, 1);

            //5、执行sql语句
            //6、返回执行结果集
            int num = preparedStatement.executeUpdate();
            if (num > 0) {
    
                System.out.println("更新数据成功！");
            }
        } catch (SQLException throwables) {
    
            throwables.printStackTrace();
        } finally {
    
            //7、释放连接
            JDBCUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

查询数据：

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestSelect {
    
    public static void main(String[] args) {
    
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
    
            //3、获取数据库连接
            connection = JDBCUtils.getConnection();

            //使用？占位符替代参数
            String sql = "SELECT * FROM `users` WHERE id=?";
            //4、获取执行sql的对象
            //预编译
            preparedStatement = connection.prepareStatement(sql);
            //手动给参数赋值
            preparedStatement.setInt(1, 1);

            //5、执行sql语句
            //6、返回执行结果集
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
    
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException throwables) {
    
            throwables.printStackTrace();
        } finally {
    
            //7、释放连接
            JDBCUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

PreparedStatement防止SQL注入：
PreparedStatement防止SQL注入的本质是将传递进来的参数当做字符（想当于给传进来的参数包装成一个新的字符串），如果其中存在转义字符，例如 ‘ 会被直接转义。这样能够避免字符串拼接成非法的sql语句，造成数据泄露。

package com.hyc.study03;
import com.hyc.study02.utils.JDBCUtils;
import java.sql.*;

public class PourIntoSql {
    
    public static void main(String[] args) {
    
        login("'' or '1=1", "123456");
    }

    public static void login(String username, String psw) {
    
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
    
            connection = JDBCUtils.getConnection();

            String sql = "SELECT * FROM `users` WHERE `NAME`=? AND `PASSWORD`=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, psw);

            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
    
                System.out.println(resultSet.getString("NAME"));
                System.out.println("===========================================");
            }
        } catch (SQLException throwables) {
    
            throwables.printStackTrace();
        } finally {
    
            JDBCUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

结果：

在PreparedStatement的作用下，再尝试通过字符串拼接达到SQL注入的目的无法实现。