UltraMega Blog
28Jul/0929

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
  • Limited to SELECT, INSERT, REPLACE, UPDATE, DELETE, and CREATE TABLE 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.