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
- ColdFusion returning empty response with server-error: true
- Careful applying CF11u16, CF2016u8, CF2018u2
- Sessions don't work in Chrome but do in IE
- csrfVerifyToken does not invalidate the token
- The cf_sql_ is optional in cfqueryparam
- Cookie Expires / Max-Age 1969-12-31T23:59:59.000Z
- Burst Throttling on AWS API Gateway Explained
- How to Resolve Java HTTPS Exceptions