Prepared Statements in PHP and MySQL
I'm working on a web security presentation, and I was curious to know if PHP supported prepared statements. It looks like as of PHP 5 they do support it with the new
mysqli object (mysqli replaces the mysql class with support for mysql 4.x features)
Here's how you do a prepared statement with php 5 and mysql (error checking is omitted):
$db_connection = new mysqli("localhost", "user", "pass", "db"); $statement = $db_connection->prepare("SELECT thing FROM stuff WHERE id = ?"); $statement->bind_param("i", $id); $statement->execute(); ...
The first argument of
bind_param is the type, in this case I used
i for integer - you can also use
s for string,
d for double, and
b for blob.
The variables in your query are represented with the
? question marks, just like with JDBC. This makes maintenance kind of a pain, it makes you appreciate CFML's prepared statement implementation with
You can also use
PEAR:DB to run prepared statements in PHP, since it is a database abstraction layer, it is probably a good way to go.
MySQL supports prepared statements in version 4.1 and above.
- Multiple Statements with MySQL and JDBC - May 16, 2005
- Cheat Sheet Roundup - Over 30 Cheatsheets for developers - September 1, 2005
- Upcoming CFML Conferences in April 2017
- CFSummit 2016 Slides
- Securing Legacy CFML - dev.Objective() 2016 Slides
- My CFSummit 2015 Slide Decks
- Adding Chrome Custom Search for CFDocs
- Disable Flash Remoting on ColdFusion Servers
- HackMyCF Adds SSL/TLS Scanner
- IncompatibleClassChangeError after ColdFusion 11 Update 5