Using MySQL Prepared Statements in PHP

Prepared statements in MySQL are an alternative to writing raw SQL code to execute. Instead, you write a statement with placeholders (?) where you want variable to go, then attach variables to those placeholders.

A prepared statement is basically a template that can be reused with different variables. There are some benefits and drawbacks to prepared statements that should be considered:

Pros:

• Prevents SQL injection without needing to escape data
• Allows you to repeat the same statement without the overhead of parsing the SQL
• Allows you to send raw binary data in packets
• Creates code that is easier to read by separating SQL logic from data

Cons:

• Slower for one time queries since it requires two requests from the MySQL server
• Does not work for ALL queries (only data manipulation queries)
• Placeholders can only be used for values and not table/column names

Conclusion: I'd say prepared statements win due to security benefits alone

PHP supports MySQL prepared statements using the Mysqli (MySQL Improved) extension in PHP 5 via the MySQLi_STMT class. They are fairly easy to use once you get used to the differences from writing raw SQL statements. This tutorial will explain how to use prepared statements.