Parameterized queries decrease the likelihood of SQL injection attacks and increase the speed with which you can subsequently execute the same query. A parameterized query is just like a normal SQL query, but you use placeholders for values instead of directly referencing them.
For example, a regular query might look like this:
SELECT * FROM user WHERE username='dave'
The same query can be represented using parameters, where a question mark indicates some variable value:
SELECT * FROM user WHERE username=?
PHP has included support for parameterized MySQL queries for a while, but using them quickly becomes cumbersome. Handling a result set which contains an unknown number of fields per record is extremely frustrating. Setting up, executing, and retrieving a result set takes many lines of code. To remedy these issues, I’ve created a convenience class for handling MySQL connections.
Using this class, you can easily create a parameterized query and retrieve a complete result set by calling a single method:
$bindParams = array( 'dave' );
$types = 's'; // see types for the mysqli_stmt_bind_param method.
$mysql = new MySQL();
$results = $mysql->parameterizedQuery( 'SELECT * FROM user WHERE username=?', $types, $bindParams );
Or more succinctly:
$mysql = new MySQL();
$results = $mysql->parameterizedQuery( 'SELECT * FROM user WHERE username=?', 's', array( 'dave' ) );
The returned $results variable contains an array of records matching your query, with each record’s structure being identical to the output expected from mysqli_fetch_array.
Download MySQL.php to make use of the parameterizedQuery() convenience method.
Parts of this class were derived from comments on PHP documentation pages. Many thanks to TheJkWhoSaysNi for his contributions to the mysqli_stmt_bind_result method discussion, and to tasdildiren for his contribution to the mysqli_stmt_bind_param method discussion.
