MySQLi, what’s the difference between standard queries and prepared statements?

You may have heard of prepared statements, but don’t quite understand the differences. In this post I’m going to try and help you with that. Well, with your standard query, you would get your information, escape it for safety, build your query and execute.

<?php
/* instantiate our class, and select our database automatically */
$sql = mysqli('localhost','user','password','database');
 
/*
let's assume we've just received a form submission.
so we'll receive the information, and we'll escape it
*/
$name  = $sql->real_escape_string($_POST['name']);
$age   = $sql->real_escape_string($_POST['age']);
$email = $sql->real_escape_string($_POST['email']);
 
/* build the query, we'll use an insert this time */
$query = "INSERT INTO `tablename` VALUES ('$name','$age','$email');";
 
/* execute the query, nice and simple */
$sql->query($query) or die($query.'<br />'.$sql->error);
?>

So, what is an advantage to doing this? An advantages is you can view the full query prior to running it, simply by echoing it out. How does this help? It lets you make sure that your query is building properly before you start executing it.
What is a disadvantage? Well, you have to escape each string for security purposes, that takes a little more time than some people may like to spend.

And how about prepared statements?

<?php
/* instantiate our class, and select our database automatically */
$sql = mysqli('localhost','user','password','database');
 
/*
let's assume we've just received a form submission.
so we'll receive the information, and we'll escape it
this step is not necessary, but why not.
*/
$name  = $_POST['name'];
$age   = $_POST['age'];
$email = $_POST['email'];
 
/* build the query, we'll use an insert this time */
$query = $sql->prepare("INSERT INTO `tablename` VALUES ('?','?','?');");
 
/*
bind your parameters to your query
in our case, string integer string
*/
$query->bind_param("sis",$name,$age,$email);
/* execute the query, nice and simple */
$query->execute();
?>

The beauty of these prepared statements is there are fewer steps, and you need not escape the parameters because the bind_param() will do that for you.

So prepared statements are best suited for queries that take user input, such as an INSERT or UPDATE query, or a DELETE or SELECT with a WHERE clause.

Hope that helps.

Author: Mike on April 16, 2011
Category: MySQLi, PHP
Tags: ,

One thought on “MySQLi, what’s the difference between standard queries and prepared statements?”

  1. Lele says:

    Ppl like you get all the brains. I just get to say thanks for the answer.

Comments are closed.

Last articles