Recently I ran across a prominent PHP Developer who incorrectly claimed that only PDO allows binding values to variables for prepared statements. A lot of developer use prepared statements to reduce the potential of SQL Injection and it is a good first step. But there are some features that you do no kno
What is a Prepared Statement?
The MySQL Manual states The MySQL database supports prepared statements. A prepared statement or a parameterized statement is used to execute the same statement repeatedly with high efficiency.
So far, so good. Well there is also a performance issue to consider too. From the same source The prepared statement execution consists of two stages: prepare and execute. At the prepare stage a statement template is sent to the database server. The server performs a syntax check and initializes server internal resources for later use.
So it is a two step process. Set up the query as a template and then plug in the value. If you need to reuse the query, just plug in a new value into the template.
So lets look at how it is done.
PDO
On PHP.NET, there are a lot of really great examples.
/* Prepared statement, stage 1: prepare */if (!($stmt = $mysqli–>prepare("INSERT INTO test(id) VALUES (?)"))) { echo "Prepare failed: (" . $mysqli–>errno . ") " . $mysqli–>error;
}
So this is our template with a ‘place holder’, designated as a question mark (?).
And then it is executed.
$id = 1;if (!$stmt–>bind_param("i", $id)) { echo "Binding parameters failed: (" . $stmt–>errno . ") " . $stmt–>error;} if (!$stmt–>execute()) { echo "Execute failed: (" . $stmt–>errno . ") " . $stmt–>error;}?>
So if we wanted to insert an $id with a value of 2, we would just assign that value ($id = 2)
and rerun the $stmt->bind_param/$stmt_execute duo again.
So that is the basics. But what do they look like with the other two extensions?
MySQLi
So what does the MySQLi version look like? Once again question marks are used as placeholders.
$stmt = $mysqli->prepare("INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
$stmt->bind_param(‘sssd’, $code, $language, $official, $percent);
$code = ‘DEU’;$language = ‘Bavarian’;$official = "F";$percent = 11.2;
/* execute prepared statement */$stmt->execute();
printf("%d Row inserted.\n", $stmt->affected_rows);
But what is that sssd stuff? That is where you declare the type of variable you are want to use. Use ‘s’ for string, ‘i’ for integer, ‘d’ for double, and ‘b’ for a blob (binary large object). So you get the advantage of type checking.
X DevAPI
The much newer X DevAPI is for the new X Protocol and the MySQL Document Store. Unlike the other two examples it is not Structured Query Language (SQL) based.
$res = $coll->modify(‘name like :name’)->arrayInsert(‘job[0]’, ‘Calciatore’)->bind([‘name’ => ‘ENTITY’])->execute();
$res = $table->delete()->orderby(‘age desc’)->where(‘age < 20 and age > 12 and name != :name’)->bind([‘name’ => ‘Tierney’])->limit(2)->execute();
Note that this is not an object relational mapper as it is the protocol itself and not something mapping the object to the SQL.
Wrap Up
via Planet MySQL
Prepared Statements for MySQL: PDO, MySQLi, and X DevAPI