Chris Dale recently posted a horrifying article on his blog.  It is called “Why it’s easy being a hacker – A SQL injection case study“.  The most horrifying part of the post was that when you type in the Google search “How to use PHP with MySQL” a significant number of the results come back with some VERY poor examples.  Most of them were vulnerable to SQL injection.  That means that people new to PHP are getting really, really bad advice on how to connect to the database.

With that in mind I want to give a very brief tutorial on how you really should be connecting to MySQL.  It will use only native PHP code and will contain no application architecture examples.  It will also not expound upon larger security issues.  It will simply give you a good starting point for using PHP with MySQL.

My hope in writing this blog post is that enough people will link to this page so-as to increase its page rank so that when people search for “how to use php with mysql” that it (or something like it) will be pre-eminent.  In some ways, my purpose in writing this page is simply Google-bait.

With that in mind, please, please promote this page on your blog, Twitter, Google+, etc.  There are a lot of really bad examples of PHP example code which contains SQL injection vulnerabilities and the only way to negate that is to educate people on the proper way to do it.

What to use

There are three different extensions you can use.  mysql, mysqli and pdo_mysql.  The vanilla mysql extension is deprecated and will be removed.  So if you are using function calls that start with mysql_ then you are using the wrong library.  You should use either the mysqli API or the pdo_mysql API.  PDO places a database abstract layer in between your code and the database driver which makes your code more portable.  It is not perfect in that it does not take care of vendor SQL differences but it does remove the API as a consideration if you need to migrate your database, for the most part.

So for the purpose of this article I will focus only on the PDO driver.  Additionally, there are several different sources for how to connect to MySQL, create tables and execute queries.  For that reason I will focus solely on PHP as an interface and not worry too much about actual SQL.

Setting up PHP

It is recommended that you use the new mysqlnd driver for providing MySQL access.  It adds a lot of new features like lazy connections and query caching.  To compile your PHP with mysqlnd make sure that you configure your source distribution like this

1

./configure --with-mysqli=mysqlnd --with-pdo-mysql=mysqlnd --with-mysql=mysqlnd

Connecting to the database

To connect to the database you will need to create a new PDO object.  This is done with the following code.

1
2

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');

The first argument is called the DSN.  It is a connection string that defines various options for PDO, including which driver to use, in this case mysql.  The second argument is the username to connect to and the third is the password.

You should generally not hard code these values into your application.  They should be retrieved from some configuration source so you don’t need to redeploy your application or edit live code to make a DSN change.

Inserting and updating data

A database is useless without data. In many articles inserting data is done with code similar to this

1
2
3

<?php
 
mysql_query("INSERT INTO `data` VALUES ('$name', '$email', '$location')");

This is wrong.  Horribly wrong.  This code is vulnerable to SQL injection.  Me, as an attacker, can use this code to execute arbitrary SQL statements on your system.

There are two ways to do this right.  One is to use the function mysqli_real_escape_string.  The other is to use prepared statements.  In my opinion prepared statements are much preferred for the following reasons

1. Statements can be re-used without re-parsing SQL (escaping properly is better, but not best, IMHO)
2. Prepared statements send data outside of the SQL statement via a binary protocol

Using a prepared statement is very easy.  You pass the driver an SQL string but you omit the data, providing placeholders instead.  Because there is no user data being provided an attacker cannot manipulate your SQL statement.

1
2
3
4
5
6
7
8
9
10

<?php
 
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('INSERT INTO customers (name, address) VALUES (?, ?)');
$statement->execute(
  array(
    $_POST['name'],
    $_POST['address']
  )
);

When prepare() is called the statement is not actually executed.  It has no data to work with.  The data is only inserted into the database when the execute() method is called.

If you like, you can also provide named parameters.  In the previous example, the values inserted will correspond to the position in the array.  Using named parameters you can simply specify the placeholder names and not have to worry about the order.

1
2
3
4
5
6
7
8
9

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('INSERT INTO customers (name, address) VALUES (:name, :address)');
$statement->execute(
  array(
    'address' => $_POST['address'],
    'name' => $_POST['name']
  )
);

Updating is done in virtually the exact same way, except with an UPDATE query.

1
2
3
4
5
6
7
8
9

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('UPDATE customers SET name = :name WHERE name = :oldname');
$statement->execute(
  array(
    'oldname' => $_POST['oldname'],
    'name' => $_POST['name']
  )
);

Retrieving data

Retrieving data is done via a SELECT call.  To start, do the same thing that you did before by creating a prepared statement.  However, this time we are going to iterate over the result set using the fetch() method.  You have four different options when retrieving data.  You can use fetch() to fetch a row, fetchAll() to fetch all rows in a multi-dimentional array, fetchColumn() to only get data for an individual column or fetchObject() to retrieve the row as an object of a specific type, or of stdClass if no type is provided.

I will only show fetch().

1
2
3
4
5
6
7
8
9
10
11
12

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('SELECT address FROM customers WHERE name = ?');
$statement->execute(
  array(
    'Kevin Schroeder'
  ) 
);
 
while(($data = $statement->fetch()) !== false) {
  echo htmlspecialchars($data['address']) . '<br />';
}

However, there is a bit of a problem.  MySQL does not allow you to pass certain things in as a prepared statement parameter, such as a LIMIT clause.   The variables that you can provide are generally limited to data.  Because of that you may need to take some care in constructing prepared SQL statements that do require “inline” user-submitted data, like a LIMIT clause.  Thankfully, that can easily be taken care of with casting.

1
2
3

<?php
$limit = (int)$_POST['limit'];
$statement = $pdo->prepare('SELECT address FROM customers WHERE name = ? LIMIT ' . $limit);

You will note that I put the LIMIT value in a separate variable.  I do this because it makes for easier debugging.  It is tough to use a debugger if you put data transformations in a method calls since it will not show up in your variable list.

Deleting data

Deleting data is pretty much the same as selecting data, but you don’t have a record set to iterate over.

1
2
3
4
5
6
7
8

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '');
$statement = $pdo->prepare('DELETE FROM customers WHERE name = :name');
$statement->execute(
  array(
    'name' => $_POST['name']
  )
);

Related Links

• PDO
• Choosing a PHP/MySQL library
• Why it’s easy being a hacker – A SQL injection case study

Conclusion

This blog post is not meant to give a full introduction to SQL operations on PHP, nor is it intended to showcase secure PHP programming practices.  Rather, it is intended to showcase basic PHP/SQL usage in a manner that is consistent with security best practices… unlike the other billion “PHP and MySQL” pages out there.  If there is something I’ve missed, or said incorrectly, please feel free to leave a comment.

Also, please share this page as much as possible so that its page rank is increased.  Also, if you have good links to highlight how to properly work with SQL please leave them in the comments and I will put them in the related links section.  If people really are getting lots of bad PHP/MySQL info let’s see if we can raise the tide of the good examples over the bad ones.