0x01 Prepared Statements
When writing SQL through JDBC in Java, we can use prepared statements. Their major advantages are faster execution—especially when the database is accessed repeatedly—and protection against most SQL injection attacks.
The following sample shows a JDBC prepared statement in Java:
String sql = "select * from t_student where name = ? and content = ?"
try {
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1,name);
ps.setString(2,content);
ps.executeUpdate(sql_update);
}catch(Exception e){
e.printStackTrace();
}
How, then, does this preparation mechanism prevent SQL injection?
0x02 How Prepared Statements Work
The reason is that the SQL statement has already been prepared before the code runs. Before the program first accesses the database, the database analyzes, compiles, and optimizes the SQL, then caches the resulting execution plan so the query can be executed in parameterized form.
At runtime, JDBC dynamically passes the parameters toPreparedStatement, even when the parameter contains sensitive characters such as' or ' 1' = '1、updatexml(2,concat(0x7e,(version())),0), and so on.preparedStatement escapes keywords and special characters in the input, such as converting a single quote to\', following roughly this process:
In short, JDBC prepares SQL statements before execution. A prepared statement compiles a fixed SQL structure and stores it in an in-memory JDBC cache. When weWhen the same SQL statement is executed again, it does not need to be prepared a second time. Even if an injected value contains special SQL syntax, it is passed only as a parameter and is never executed as an instruction.。