Why precompilation prevents sql injection

The reason why precompilation can prevent sql injection: allowing the database to do parameterized queries. When using parameterized queries, the database will not regard the content of the parameters as part of the SQL execution, but as the attribute value of a field. In this way, even if the parameters contain destructive statements (or '1=1' ) will not be executed.

Why can PreparedStatement prevent SQL injection to a certain extent?

PreparedStatement will pre-compile SQL. The database will analyze, compile and optimize before executing SQL for the first time. At the same time, the execution plan will also be cached, which allows the database to make parameterized queries. . When using parameterized queries, the database will not regard the content of the parameters as part of the SQL execution, but as the attribute value of a field. In this way, even if the parameters contain destructive statements (or '1=1' ) will not be executed.

Recommended course: Java tutorial

How to use PreparedStatement? How to avoid SQL injection attacks? What is the difference between PreparedStatement and Statement, and what are their advantages?

A simple example of PreparedStatement

public class JDBCTest {
    public static void main(String[] args) {
        //表示使用Unicode字符集；字符编码设置为utf-8；不使用SSL连接
        String URL = "jdbc:mysql://127.0.0.1:3306/sampledb?useUnicode=true&" +
                "characterEncoding=utf-8&useSSL=false";
        String USER = "spring4";//用户
        String PASSWORD = "spring4";//密码
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            //1.加载驱动程序到JVM
            Class.forName("com.mysql.jdbc.Driver");
            //2.创建数据库连接
            conn = DriverManager.getConnection(URL, USER, PASSWORD);
            //3.创建Statement，实现增删改查
            String sql = "select user_id,user_name,credits from t_user where credits > ?";
            st = conn.prepareStatement(sql);//这里使用PreparedStatement
            st.setInt(1, 8);
            //4.向数据库发送SQL命令
            rs = st.executeQuery();
            //5.使用ResultSet处理数据库的返回结果
            while (rs.next()) {
                System.out.println(rs.getLong("user_id") + " "
                        + rs.getString("user_name") + " "
                        + rs.getString("credits"));
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            //6.关闭资源
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Several implementations of Statement

The Statement object is used to send SQL statements to the database.

Every time Statement executes a SQL statement, the database must execute the compilation of the SQL statement. It is best used in situations where the query is only executed once and the results are returned.

1. Execute static SQL statements. Usually implemented through Statement instances.

2. Execute dynamic SQL statements. Usually implemented through a PreparedStatement instance.

3. Execute the database stored procedure. Usually implemented through a CallableStatement instance.

The difference between '#' and '$'

sql pre-compilation refers to the database driver compiling the sql statement before sending the sql statement and parameters to the DBMS. In this way, when the DBMS executes sql, it does not need to be recompiled.

‘#{ }’: Parsed as a parameter marker of a JDBC prepared statement (prepared statement), a ‘ #{ }’ is parsed as a parameter placeholder ? .

‘${ }’ is just a pure string replacement, variable replacement will be performed during the dynamic SQL parsing phase. Already replaced by variables before precompilation

The replacement phase of the ‘${ }’ variable is in the dynamic SQL parsing phase, while the replacement of the ‘#{ }’ variable is in the DBMS.

What is the difference between PreparedStatement and Statement

1.PreparedStatement can be precompiled, and this precompiled SQL query statement can be reused in future queries. In this way , which generates queries faster than Statement objects.

2.PreparedStatement can write dynamic parameterized queries

3.PreparedStatement can prevent SQL injection attacks

4.PreparedStatement queries are more readable and can add conditions The statement is messy

5.PreparedStatement does not allow a placeholder (?) to have multiple values

The above is the detailed content of Why precompilation prevents sql injection. For more information, please follow other related articles on the PHP Chinese website!