Limitations of Java Prepared Statements in Certain Scenarios

0x00 The MyBatis Framework

The Difference Between `#` and `## 0x00 The MyBatis Framework

The Difference Between `#` and

# causes the statement to be precompiled
${ } only performs string replacement; variables are substituted when the SQL is dynamically parsed

0x01 Limitations of Precompilation in Certain Scenarios

When a table name is used as a variable, concatenation is required

select * from `user`   
select * from 'user' #报错，表名不能使用单引号

Concatenation is required after order by

select * from user order by name
select * from user order by 'name' # 语义发生改变，没有排序的效果了

0x02 I Previously Heard Someone Say That Statements After `order by` Do Not Participate in Precompilation?

This statement is incorrect. Order
by does participate in precompilation. As we can see from the JDBC setString algorithm above, setString adds quotation marks around the parameter, turning it into a string. This causes order
by to lose its original meaning. It can only be said that using order
by with precompilation is unsuitable. Therefore, to defend against this type of order
by issue, it is recommended to hard-code it directly in the application. For order by injection, we can retrieve data by observing differences in the order of the returned results.

0x03 Why Does Precompilation Ensure That Input Data Can Only Be Treated as Data, and What Is the Underlying Principle?

Generally speaking, an SQL statement goes through several stages between being submitted and executed: syntax tree generation, execution plan optimization, and execution. During precompilation, the database first receives an SQL statement containing the prepared-statement placeholder ?, parses it to generate a syntax tree (Lex), and caches it. It then receives the corresponding parameter information, retrieves the syntax tree from the cache, sets the parameters, and proceeds with optimization and execution. Because the syntax tree has already been generated before the parameter information is supplied, the syntactic structure being executed cannot be changed by the parameters, thereby naturally preventing SQL injection. This profound yet simple explanation should answer our initial question.