Benefit of Prepared Statement

Question

Can someone provide some reasoning of why it is better to use a PreparedStatement rather than making a String with the SQL statement set to that variable?

Thanks

Also, the database engine will be able to cache the query plan even if the parameter value changes. — Aasmund Eldhuset, Apr 08 '14 at 21:36

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

PreparedStatement protect you from sql-injections.All details you can find below : https://stackoverflow.com/a/8265319/3444240

edited May 23 '17 at 12:05

Community

1
1

answered Apr 08 '14 at 21:36

potashin

44,205
11
83
107

score 0 · Answer 2 · answered Apr 08 '14 at 21:43

There are two reasons really. First is security the second is speed.

A prepared statement is compiled by the DBMS without the parameters. Later when the parameters are supplied the DBMS simply inputs the values and doesn't have to rely on proper quotations to tell the differences between strings and the rest of the statement. This can help mitigate SQL injection attacks a lot. Because the comment specifier has no special meaning in this context.

Second, since the statement is compiled the execution plan is already created and ready to go before execution and can be reused. In the case of SQL strings the execution plan needs to be recreated every time. This causes some slowdown (Not much but will quickly add up).

A decent overview is at http://en.m.wikipedia.org/wiki/Prepared_statement.

Benefit of Prepared Statement

2 Answers2