Prepared SQL statements are supported by a lot of database abstraction drivers. Prepared statements are great. If you aren’t using prepared statements, you should seriously look into it!
Prepared statements are immune to SQL injection attacks. That’s right, immune. When you use prepared statements, you don’t have to worry about properly escaping inputs, it is handled for you.
Aside from the security, prepared statements when you want to perform a query multiple times with different parameters. The structure of prepared statements is intended just for that. In fact, its the idea behind prepared statements. Prepare the statement, run it several times with new sets of parameters.
The downside of prepared statements is the execution speed. Before the query can be executed, it is “prepared” and parameters must be bound. In a high-load setting, the increased execution time might be noticeable, but for average instances, its negligible.
When digging through old, crappy code, it is pretty common-place to see developers incorrectly using prepared statements. How is this possible? Is it a vulnerability issue? Well, one of the main ideas behind a prepared statement, is that the statement may need to be executed several times, but the statement only needs to be “prepared” once. If you have a loop which executes a query, prepare the statement before entering the loop. Inside the loop, you bind the parameters and execute. Don’t prepare the statement inside the loop.
Preparing a statement is a string manipulation, doing it multiple times is extra load on precious CPU time. I created a simple MySQL schema and PHP script to test this scenario, to get an idea of the extra execution time resulting in this improper usage of prepared statements. The table I used simply had 3 fields:
id int unsigned not null auto_increment primary key,
hash varchar(255) not null,
hashType tinyint unsigned not null
The simple table and the fact that my laptop has virtually no load meant that the queries ran FAST. 20,000 executions took place in ~3 seconds. I upped the number to 300,000 queries. Preparing the statement 300,000 times resulted in a script execution time of 35 seconds (average over 3 trials), and preparing the statement once and then executing 300,000 times resulted in a script execution time of 33 seconds (average over 3 trials).
2 seconds isn’t significant, no, but this was on a dual-core laptop which saw the load peak at 0.61 during the trials. Imagine this running on your shared hosting database. On mine, I saw a 3 second difference when only running 5,000 queries.
