MySQL Header

Coding MySQL PHP calls the correct way

A vast majority of websites are coded using PHP. Chances are if you have a PHP website, it is being driven by a MySQL database back-end… and the chances are that the code you are using to get data to and from the PHP script to the MySQL includes the MySQL functions.

MySQL functions are quick and easy to implement; however they contain gaping security holes that easily allow SQL injections and cross site scripting attacks. For this reason, using the MySQL functions is being strongly advised against, by anyone in the know, including the PHP developers themselves. In short, it is essential that you remove these functions for the security of your website. At this point, a lot of developers may shy away from having to update what could be hundreds of scripts, however the changes required are actually not too much different than what you already have and in the end, is essential for the security of your website and services.

Mysql Alternatives

So what are the alternatives to MySQL?

  • PDO
  • MySQLi

A lot of developers will try and tell you to use PDO. There’s no reason not to, but there’s no good reason not to use MySQLi either. It supports connections to different types of databases through the use of drivers where as my MySQLi only supports MySQL. PDO uses procedural functions whereas MySQLi is procedural or object based.

This article will describe the use of mysqli, which is an improved version of mysql.

Prepared Statements Vs Non-Prepared Statements

So why are these functions better than the original MySQL function?

The main reason is MySQLi (and PDO) enable you to use prepared statements. In short, a prepared statement will send the SQL statement and parameters seperatly, whereas a non-prepared statement will send it all at once in the same string. Sending it all at once enables the possibility of someone intercepting the command and altering the data. Sending the data separately greatly reduces that risk.

Example Data

The examples in this post will use the following table as an example:

ID Name Country Notes
1 Andrew England Likes databases
2 Ben England
3 Chris USA Hates mysql

 How To Not Do It

The following is an example of how not to do it:

<?php
	/* connect to the db */
	$link = mysql_connect('localhost','username','password') or die('Cannot connect to the DB');
	mysql_select_db('database',$link) or die('Cannot select the DB');
 
	$query = "SELECT name FROM table WHERE id = '" . $id . "'";
	$result = mysql_query($query,$link); or die('Errant query:  '.$query);
 
	if (mysql_num_rows($result) --> 0)
	{
		//Some rows were returned
	}
 
	/* disconnect from the db */
	mysql_close($link);
?>

The code above is using the old MySQL function with no prepared statement. Although the code is valid, and if you execute that, it will work fine; it is not safe! These are the functions we want to strip out and replace with MySQLi.

 Using MySQLi

MySQLi actually allows you to do exactly the same unprepared statement as above; however I am not going to show you or explain how to do this, as using any un-prepared statements is a security risk, regardless of what function you use.

All the examples in this post will cover using MySQLi object based functions. Visit the PHP documentation pages to see how to use MySQLi procedurally.

Connecting To Your Database

Ok, the first step is always to create a connection to the database. This is achieved as such:

<?php
	/* connect to the db */
	$conn = new mysqli('localhost', 'username', 'password', 'database');
 
	if($conn ->connect_errno > 0){
	    die('ERROR: Unable to connect to database [' . $conn ->connect_error . ']');
	    return; 
	}
?>

This is very similar to the mysql way. It just creates a connection and stores it in the ‘conn’ variable. We then check the connection was successful, and if not we exit with an error message.

Prepared Statements

Whether you want to do a ‘select’, ‘update’, or ‘insert’ the procedure is the same, just you query will change. For this example we will do a ‘select’ statement. First we need to prepare the statement:

//Prepare the statement
$statement1 = $conn->prepare("SELECT id, notes FROM tablename WHERE country=? AND name=?");

Notice the ‘?’ where you would expect your parameter to be? Now we will add those parameters by binding them:

$statement1->bind_param('ss', $conn->escape_string($var_country),$conn->escape_string($var_name));

The first parameter in the ‘bind_param’ function is the variable datatype. (s=string, d=double, i=integer). You need to supply a type for each parameter.

After setting the parameter data types, we pass in each parameter we need to send. One for each question mark in the statement. These should be seperated by a comma.

You may also notice that I am passing the variables through ‘escape_string’ function. This will basically strip the variable of anything nasty that could cause problems (like html elements). Although this isn’t strictly necessary for a ‘select’ statement, it is highly advisable for parameters you are planing to insert into the database either through an ‘insert’ or ‘update’. I have included it here so you can see what it would look like.

Now the last thing to do is to execute the statement:

//execute the statement
$statement1->execute();

Error Checking

At this point we can check to see if an error occured with this method:

//Check for error
if($statement1->error)
{
	die("Failed: " . $statement1->error);
}

This will check the executed statement for any error codes that may be present, then will return the error message.

Affected Rows / Number Of Rows

If your statement was an ‘insert’, you may wish to know how many rows were effected? If so, the following function call will return the number of effected rows:

$effected_rows = $statement1->affected_rows;

However, if like the example above you have done a ‘select’ statement; you may wish to find out how many rows were returned. This can be slight more awkward. At this moment in time we have only executed the statement, we haven’t actually returned or fetched any data back. Because of this we can’t count the rows (after all you can’t count something you haven’t got yet!). So we need to fetch some data. We can use the fetch function to return the data (covered in the section below) or we can do this:

//This enables us to check number of rows
$statement->store_result();
 
//Check no of rows
//must be called after the first fetch
//otherwise no data to get
$rows = $statement1->num_rows;

The ‘store_result’ function basically stores the data returned by the query. This then enables you to check the number of rows.

Returning The Data

So now you will probably want to see that data you have returned.

First stage is to bind the result to variables:

//Bind the result columns. This binds a column to a var
//must be set before a fetch()
$statement1->bind_result($id, $notes);

This function will bind the column to a variable. In our ‘select’ statement we are returning the id number and the notes column, so we are just binding these columns to a variable, ready to iterate over. If you have more columns, just add them in to the function call, separated by columns

Now we can perform the fetch whilst iterating through all available rows:

while($statement1->fetch()){
    echo $id . ' - ' . $notes . '<br />';
}

This loops through each row and outputs the id and notes column.

What About JSON?

Now a lot of people really get in a mess over trying to return their data in JSON format. This was simple using the old MySQL way, and the misconception is that it is difficult to do using MySQLi. Well, lets see how difficult it is…

$JSONoutput=array();
while ($statement1->fetch()) {
	//Output the row to an array
    	$JSONoutput[]=array($id);
}
echo json_encode($JSONoutput);

First we create an array to store our JSON. We then loop through fetching each row. We add the fetched data to the JSON array. Then we encode it.

The result will look like this:

[[1],[2]]

I know what your saying… how do you parametrise the JSON statement? Thats just as simple:

$JSONoutput=array();
while ($statement1->fetch()) {
	//Output the row to an array
    	$JSONoutput[]=array("id"=>$id, "notes"=>$notes);
}
echo json_encode($JSONoutput);

This will return something like the following JSON:

[{"id":"1","notes":"Likes databases"}, {"id":"2","notes":""}]

Performing More Than 1 Statement

There may be scenarios where you need to perform more than one statement. For instance, you may need to do an ‘insert’ then a ‘select’. This is perfectly possible.

First let’s clean up the first statement:

//Close the statement
$statement1->free_result();
$statement1->close();

This will clean the statement object. This is not strictly needed, but is good practice and is the correct way of doing things. There is no need to close the database connection, as we are about to use it again!

At this point we can create our new statement something like this:

$statement2 = $conn->prepare("SELECT * FROM table");

You can then execute the statement as previous examples

Finishing Off

After we have finished with the database and statements, its good practice to clean up after ourselfs by releasing the statement, and closing the database connection if it is no longer needed.

Close the statements:

//Close the statement
$statement1->free_result();
$statement1->close();

Then close the database connection:

//Close the DB Connection
$conn->close();

It may not be necessary to close the database connection. You may need to use the same database for other functions later. In this case it is advisable to leave the connection open as it is more economical than closing and re-opening.

MySQLi Transactions

Although I won’t be providing any examples at this stage, it is also possible to queue up your database actions using MySQLi transactions. Transaction statements are useful if you have many statements to execute, especially if they all rely on each other. For instance if your second insert statement relies on your first insert statement, but the first insert statement fails, you can roll back the transactions so no modifications will have been made.

First you want to stop your statements from auto-commiting. This will enable us to execute all our statements without actually changing any data in the database:

$conn->autocommit(FALSE);

When all your statements have been executed and you are ready to alter the data, you need to call:

$conn->commit();

And if, for any reason you may have made a mistake somewhere, you can roll back the transactions using:

$conn->rollback();

Conclusions

As you can see from the examples, using MySQLi is just as simple as using the old MySQL functions. It is time to convert those old functions and sure up that old cold.

Leave a Reply

Your email address will not be published. Required fields are marked *
You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>