Why MySQLi prepared statements?

Question

What are the advantages of using prepared statements with MySQLi?

If the only purpose is to secure the query, isn't it better to clean the query using something like mysqli_real_escape_string instead of writing so many lines of code for each query (like prepare, bind_param, execute, close, etc.)?

Answer 1

Preparing statements is not just for code security. It helps the SQL server parse your statement and generate an execution plan for your query.

If you run a SELECT 1000 times then the SQL server will have to parse, prepare, and generate a plan on how to get your data 1000 times.

If you prepare a statement then run the prepared statement 1,000 times each with different bound values then the statement is parsed only once and the same query plan is used each time.

This helps not only when you run 1,000 queries inside the script but also if you only have 1 statement and run the script 1,000 times. The server can remember the plan server side. The next time your script runs it will use the same plan again.

Sure it may seem trivial for one or two queries but when you start executing large numbers of queries or the same query repeatedly you will save a lot of processing time.

Answer 2

There are several advantages:

• Security - you don't need to escape anything, you just need to bind the parameters.
• Correctness - If you write WHERE $x = 4 you will get a syntax error if $x is null, but WHERE ? = 4 will work.
• Performance - prepared queries can be reused with different parameters, saving rebuilding the string, and also reparsing on the server.
• Maintainability - the code is much easier to read. When concatenating strings to create the SQL it's easy to end up with lots of small pieces of SQL and PHP code intermingled. Using prepared statements encourages you to separate the SQL from determining the values of the variables.