What are the advantages of prepared statements over statements?
Prepared statements offer better performance, as they are pre-compiled. They reuse the same execution plan for different arguments rather than creating a new execution plan every time.
Prepared statements use bind arguments, which are sent to the database engine. This allows mapping different requests with same prepared statement but different arguments to execute the same execution plan.
Prepared statements are more secure because they use bind variables, which can prevent SQL injection attack.
The most common type of SQL injection attack is SQL manipulation. The attacker attempts to modify the SQL statement by adding elements to the WHERE clause or extending the SQL with the set operators like UNION, INTERSECT etc.
Let us look at the following SQL
SELECT * FROM employee where ename=’john’ AND password=’xyfdsw’;
The attacker can manipulate the SQL as follows
SELECT * FROM employee where ename=’john’ AND password=’xyfdsw’ or 1 = 1;
The above “WHERE” clause is always true because of the operator precedence. The PreparedStatement can prevent this by using bind variables:
String strSQL = SELECT * FROM employee where ename=? AND password=?);
PreparedStatement pstmt = myConnection.prepareStatement(strSQL);