How to use prepared statements with mysqli

Prepared statements is basically a way to avoid sql injection on your script. If you want to learn more about prepared statements here’s a link to a Wikipedia article and stackoverflow question on best practices on using prepared statements.

To summarize the contents of the two links above it basically says that you need to use prepared statements in order to prevent sql injection attacks and if you need to insert, modify, or remove lots of data from you database. Prepared statements are basically faster than your average query since they are compiled before execution.

 

Methods

Here are a few methods that we need to use when using prepared statements.

  • prepare() – takes up a query as an argument. You need to mask the values that you want to supply to the query using the question mark symbol(?).
  • bind_param() used to bind parameters to a prepared statement. You specify how many arguments it would take by using a string which has the same length as the values that you want to pass in. For example if you want to supply a user id, password, department and role you need to input a string with a length which is equal to 4, and then the following arguments would be the actual values that you want to use on your query.
  • execute() as the name suggests this is used to execute the prepared statement along with the arguments that has been bind to it.
  • bind_result() – use this method to bind the results of a prepared statement into their respective variables.
  • fetch() – use this method to fetch the results that has been returned by the prepared statement. It doesn’t take up any arguments, just call it immediately after binding the results to their specific variables.

 

Inserting records

Next, let’s try to insert records. On line’s 1 to 4 we assigned the data that we want to insert to each of their variables. Next we try to check if the query is a valid query using an if statement. Note that we are using question marks(?) as a mask for the actual values that we want to insert in the query. And then we call up the bind_param() method. As you can see, we used “ssss” as our first argument this specifies that we are submitting for string arguments to the prepared statement. The next arguments are the actual values that you want to pass in. Make sure that they are of the same order with the fields that you used in your query. This means that if you have first set the value of UserID in your query then you also use it as first value that you bind. Just examine the example below if you’re confused with my messy explanation. Lastly, you execute it using the execute() method.

$user_id = "lily's"; $hashed_pw = md5("1234"); $department = 1; $role = "ROL3"; if($query = $db->prepare("INSERT INTO sys_users

SET UserID=?, strPassword=?, intDepartment=?, intRole=?")){

$query->bind_param("ssss", $user_id, $hashed_pw,

$department, $role); $query->execute(); }

 

Updating records

Updating records is almost the same with the inserting of records the only difference is the query that you are performing. Always remember that when calling bind_param, you use the actual values in the order that they appear on the query. In the case of an update query, the field that you use in the where clause will always be the last argument that you supply on the bind_param() method.

$user_id = 'yohasakura'; $hashed_pw = md5('9877'); $role_id = 'ROL9'; $dept_id = 3; if($query = $db->prepare("UPDATE sys_users SET strPassword=?, intRole=?, intDepartment=? WHERE UserID=?")){ $query->bind_param('ssss', $hashed_pw, $role_id,

$dept_id, $user_id); $query->execute(); }

 

Deleting records

Same story, different query. In a delete query often times we only need to supply one argument to the query that’s why I use just “s” as the first argument in the bind_param() method. And the second is the user id, the field the you used in your where clause.

$user_id = 'yohasakura';
if($query = $db->prepare("DELETE FROM sys_users WHERE UserID=?")){
	$query->bind_param("s", $user_id);
	$query->execute();
}

 

Fetching a single record

Same story, just used two additional methods namely bind_result() and fetch(). Note that when using bind_result() the order of the fields that you have selected in your query doesn’t actually matter, it is how you output the values which gives the meaning to it.

$user_id = 'tcollector'; if($query = $db->prepare("SELECT UserID, intRole, intDepartment FROM sys_users WHERE UserID=?")){ $query->bind_param('s', $user_id); $query->execute(); $query->bind_result($userID, $department, $role); $query->fetch(); printf("User: %s <br/> Role:%s <br/> Department: %s<br/>",

$userID, $role, $department); }

Also note that the %s is being substituted with the 2nd to the last arguments that you specify in the print_f() function, this is where the order of how you output things matters. But if you don’t want to take care of the order, its perfectly fine to do it this way

$user_id = 'tcollector';
if($query = $db->prepare("SELECT UserID, intRole, intDepartment 
FROM sys_users WHERE UserID=?")){

	$query->bind_param('s', $user_id);
	$query->execute();
	$query->bind_result($userID, $department, $role);
	$query->fetch();
	echo 'User: '. $userID.'<br/>';
	echo 'Role: '. $role.'<br/>';
	echo 'Department: '. $department.'<br/>';
}

 

Fetching multiple records

Same story, just added a while loop to loop through all the records that has been returned. This basically says that while a result is still being returned you need to output something. And yes, you can use the variables that was bind to output things out. Just make sure that when binding, you follow the same order indicated in your query. If you use SELECT * you might want to follow the order that is in your table, for example if UserID is the first field from the left then you also supply it as the first argument when using bind_result().

$department_id = 2;
if($query = $db->prepare("SELECT UserID, intRole, intDepartment 
FROM sys_users WHERE intDepartment=?")){

	$query->bind_param('s', $department_id);
	$query->execute();
	$query->bind_result($user, $role, $department);
	
	while($query->fetch()){
		echo $user.'<br/>';
	}
}

 

Conclusion

Okay I guess that’s it for this short introduction on how to use prepared statements in mysqli. Prepared statements is a great way to prevent sql injection on your queries, you won’t actually need to sanitize strings that you supply on your queries if you use prepared statements and you don’t have to worry about sql injection.

One thought on “How to use prepared statements with mysqli

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s