重拾JDBC（四）使用PreparedStatement

使用PreparedStatement

为何要使用PreapredStatement?

1. 使用statement需要拼接sql语句，麻烦；
2. 可以有效防止sql注入；
3. 提高性能。

表示预编译的 SQL 语句的对象，可以使用占位符的sql语句。

//是Statement的子接口
public interface PreparedStatementextends Statement

语法示例：

//con 表示一个活动连接
PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES
                                 SET SALARY = ? WHERE ID = ?");
//从1开始
pstmt.setBigDecimal(1, 153833.00);
pstmt.setInt(2, 110592);

示例：

/**
 * 使用 PreparedStatement 将有效的解决 SQL 注入问题.
 */
@Test
public void testSQLInjection2() {
    String username = "a' OR PASSWORD = ";
    String password = " OR '1'='1";

    String sql = "SELECT * FROM users WHERE username = ? "
            + "AND password = ?";

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    try {
        connection = JDBCTools.getConnection();
        preparedStatement = connection.prepareStatement(sql);

        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);

        resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {
            System.out.println("登录成功!");
        } else {
            System.out.println("用户名和密码不匹配或用户名不存在. ");
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.releaseDB(resultSet, preparedStatement, connection);
    }
}

/**
 * SQL 注入.
 */
@Test
public void testSQLInjection() {
    String username = "a' OR PASSWORD = ";
    String password = " OR '1'='1";

    String sql = "SELECT * FROM users WHERE username = '" + username
            + "' AND " + "password = '" + password + "'";

    System.out.println(sql);

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;

    try {
        connection = JDBCTools.getConnection();
        statement = connection.createStatement();
        resultSet = statement.executeQuery(sql);

        if (resultSet.next()) {
            System.out.println("登录成功!");
        } else {
            System.out.println("用户名和密码不匹配或用户名不存在. ");
        }

    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        JDBCTools.releaseDB(resultSet, statement, connection);
    }
}