Monthly Archives: August 2011

Using mysqli Extension to Query MySQL from PHP Instead of the mysql Extension

I was answering a very basic question on stackoverflow today that boiled down to “How do I query mysql from PHP using a user input” and was horrified to see the first two answers.

The first answer went along the lines of:
$result = mysql_query(“SELECT * FROM table WHERE field = ‘”. $_POST[‘input’] .”‘”);
while($row = mysql_fetch_array($result)){
//do stuff

This is bad. Very bad. Very, very bad.
This is the kind of thing that allowed (maybe a bit of hyperbole, but stick with me) Sony to be brought to its knees. This is how all your user data gets stolen. This is how Anonymous makes you look like a fascist who wants to eat babies. It’s bad. It did come from someone with a pretty low score on SO, but I’m living proof that a low score on SO doesn’t mean you don’t know what you’re doing.


The first MAJOR problem is: You are taking direct, unsanitized user input. You have not validated that the input is correct, even relates to what you are having them input, or is safe.
By taking direct input straight from the user (by directly using the $_POST variable nonetheless) they could attack via sql injection at worst, and the $_POST field may not exist, giving you an error, at best.

The second problem is the use of the mysql extension in PHP. According to the manual, the mysql extension should only be used in 4.1.3 and lower, anything newer should be using mysqli for improved performance and security.

I have heard there are some issues with mysqli in certain cases that essentially require the use of the mysql_ extension to get around them. As far as I know, these are few in number and getting better by the day. I personally, haven’t ever had a problem with mysqli.

On to a more correct example:
$db = new mysqli(“localhost”,”user”,”password”,”database”);

printf(“Connection failed:%s \n”,mysqli_connect_error());

$input = mysqli_real_escape_string($db, $_POST[‘search’]);

* Validate that $input is correct after escaping
* Implement your validation below!

if($result = $db->query(“SELECT * FROM table WHERE field = $input”, MYSQLI_ASSOC))
while($row = $result->fetch_object())
// $row is an associative array
// Do something here

What’s better about this code?

First, it’s not perfect. You could pick it apart, but it’s a good example of where to start and I wrote it in about 45 seconds.
It shows:

  • Connecting to the mysql database
  • Ensuring a connection occured and handling the mysql connection error
  • Querying the database
  • Escaping user input AND THEN validating
  • Iterating through the mysql results
  • Closing the database connection (people never remind beginners to do this)!

The basics shouldn’t be so complicated that it takes two months to make heads or tails of what your code really does (I’m looking at you Zend Framework), but it should start with a good explanation of the big picture.