Using PHP with MySQL

byExPloIT -3:14 AM 0

Using PHP with MySQL

PHP Database Mysql Mysqli Sql Injection
PHP and MySQL is one of the most common stacks for web development, even in 2014. With it such a common combination powering so many websites, it is quite shocking that the most common online examples of using it are completely horrible and outdated by at least a decade. Try searching for "php mysql tutorial" and see what you find - it ain't pretty.

In this tutorial I will try and show a simple, yet forward thinking and secure way to use MySQL in PHP. I will explain the reasoning behind each step and compare it with the less desired alternative. This tutorial does not cover installing MySQL on your web server - it focuses on using it in PHP. Perhaps we will cover that in a future tutorial, but that part is usually pretty straightforward.
If it works, don't fix it

If they're so horrible, how come those examples are still so common?because they work. Yes, the code shown on sites like W3Schools and others of its ilk, does work. It's insecure, uses outdated methods, does not protect from change and promotes bad habits, but it does work. Let's look at an example:
1. <?php
2. $username="username";
3. $password="password";
4. $database="your_database";
5. $field1-name=$_POST['Value1'];
6. $field2-name=$_POST['Value2'];
7. $field3-name=$_POST['Value3'];
8. $field4-name=$_POST['Value4'];
9. $field5-name=$_POST['Value5'];
10. mysql_connect(localhost,$username,$password);
11. @mysql_select_db($database) or die( "Unable to select database");
12. $query = "INSERT INTO tablename VALUES('','$field1-name','$field2-name',
13. '$field3-name','$field4-name','$field5-name')";
14. mysql_query($query);
15. mysql_close();
16. ?>
What are the problems here?
- User input is fed directly into query without escaping, making this code vulnerable to SQL injection attacks (or even accidental). We always want to escape user input in database queries, or better yet - use prepared statements (more on that below), so we don't end up with a Bobby Tables situation.
- mysql_query() function used directly in the code. While this might seem fine for a simple tutorial, those tutorials target inexperienced developers and instill bad habits. You want to abstract database access in case you want to change your database or adapter (PHP extension used to communicate with the database).mysql_query() specifically has been deprecated as of PHP 5.5. It's interesting to note that W3Schools updated their tutorial to use the MySQLi extension, but most tutorials still use the mysql_functions to demonstrate usage.
- The error suppression operator is used (@), and a failure on selecting the database kills the script with a generic message (which many of you probably seen in the past on actual production websites). No proper error handling is introduced. How do you debug this script if you're a complete newbie to PHP and MySQL?
- A database connection is created manually with the credentials in the same script running the query. We would want to abstract creating the connection, so we have a central location to manage it, and likely also keep the credentials in a separate file - especially important if you serve your files directly from the document root directory (web visible). You shouldn't do that anyway, but that is outside the scope of this tutorial (perhaps in a follow up?) - but at least you should protect your database credentials. As a side note, there is no error handling on connecting to the database - which is a much more common error than not being able to select one after connecting.
Doing it the right way

Let's break the process step-by-step, and see how we can improve on the example above.
Choosing an extension

Much of the functionality of PHP is delivered by extensions - plug-in files that are loaded when you start your web server (or PHP process on some configuration). Database access is delivered through extensions, and there are multiple options to choose from.

To keep a long story short, you can go with either the MySQLi or PDO extension and do well. The MySQLi extension supports slightly more use-cases than PDO, while PDO has a uniform API for other databases (such as PostgreSQL and MsSQL), which is nice if you ever need to switch. You can view a more detailed comparison of the various extensions over at the official PHP site.

For the purposes of this tutorial we use the MySQLi extension.

A note about MySQLi:

PHP presents two ways to use MySQLi - procedural (functions) or object-oriented (using the MySQLi set of classes). Both are identical syntax and usage wise, except for the way you call the functions (as standalone or on a MySQLi object). To make things simpler for people who have not yet ventured into object oriented programming (OOP), I use the procedural version. If you do use OOP, it should not be a problem to use this tutorial as a guideline and apply it on the OOP style.
Connecting to the database

The first order of business is connecting to the database. With MySQLi, the function that does that ismysqli_connect():
1. // Try and connect to the database
2. $connection = mysqli_connect('localhost',$username,$password,$dbname);
4. // If connection was not successful, handle the error
5. if($connection === false) {
6. // Handle error - notify administrator, log to a file, show an error screen, etc.
7. }
Explanation:
- First we connect to the databasewith the mysqli_connect()which needs the host name, database username and password as a minimum to create a connection. We also pass the database name as the 4th parameter to select that database for the queries we will run. You canpass additional parameters - port and socket, if your database requires that level of configuration (the defaults work on most installations. You can also change it in your PHP configuration).
- Then we check if the connection was successful. There are many reasons why the connection could fail - common ones include wrong credentials ( / misconfigured database) and high database usage load which causes the database to refuse new connections. How you handle this error is up to you - common approaches include showing a custom error page to the user, and notifying the administrator (via Email or otherwise) about the error.
Something is missing here though - where do the database credentials come from? As I mentioned previously, it's not good practice to keep those in the database connection script, especially if the PHP files are inside the document root (which means they will be accessible directly from the web). A server error (malfunction or intentional) could cause those files to be displayed as text, revealing your database credentials to everyone viewing it at the time.

If you do not put your PHP scripts inside the document root, but rather include them from outside of it (commonly used with bootstrapping), then this is not a must. I still like to keep my configuration settings in a separate file, which I can refer to any time I need to make overall changes to my PHP application.

Let's create a simple configuration file, which looks like this:
1. [database]
2. username = root
3. password = 1234
4. dbname = mydb
This is the ini configuration format, for which PHP has very good support out of the box. Save it in a file calledconfig.ini and place it outside of the document root, so it is not accessible from the web. We will now modify our code to load the configuration and use it to create the database connection:
1. // Load configuration as an array. Use the actual location of your configuration file
2. $config = parse_ini_file('../config.ini');
4. // Try and connect to the database
5. $connection = mysqli_connect('localhost',$config['username'],$config['password'],$config['dbname']);
7. // If connection was not successful, handle the error
8. if($connection === false) {
9. // Handle error - notify administrator, log to a file, show an error screen, etc.
10. }
We've abstracted the credentials from the connection. But now we're going to go one step higher, and put the connection process inside a function, which we can later use any time we want to access the database. Note that unless you close the connection to the database (as the initial example showed), you only need to connect to the database once during the lifetime of the script.
1. function db_connect() {
3. // Define connection as a static variable, to avoid connecting more than once
4. static $connection;
6. // Try and connect to the database, if a connection has not been established yet
7. if(!isset($connection)) {
8. // Load configuration as an array. Use the actual location of your configuration file
9. $config = parse_ini_file('../config.ini');
10. $connection = mysqli_connect('localhost',$config['username'],$config['password'],$config['dbname']);
11. }
13. // If connection was not successful, handle the error
14. if($connection === false) {
15. // Handle error - notify administrator, log to a file, show an error screen, etc.
16. return mysqli_connect_error();
17. }
18. return $connection;
19. }
This our database connection function. I prefixed it with db_, which I will do as well for the rest of the database functions later. If we were using OOP, we could have an even cleaner looking interface by creating a database class which has the operations as the method names (connect(),query(), etc.). An OOP version of this tutorial has been included at the end of the tutorial.

I added one small wrinkle in there - defining $connection as a static variable, which means its state will be kept between function calls todb_connect(). This allows to connect just once to the database, and then just return the connection on future function calls. In the oldmysql_connect(), the connection would be kept globally by MySQL. We emulate that behavior here, to avoid establishing a connection every time we need to query the database.
Querying the database

Now that we have a connection, we can start querying the database. The simplest way to go about it is to use themysqli_query() function.
1. // Connect to the database
2. $connection = db_connect();
4. // Query the database
5. $result = mysqli_query($connection,$query);
Pretty straightforward. The value of$result will be false if the query failed, otherwise its value depends on the type of query we ran. For SELECT, SHOW, DESCRIBE or EXPLAIN queries, it will return amysqli_result object, and for all other queries it will return true. As before, let's wrap it up in a function:
1. function db_query($query) {
2. // Connect to the database
3. $connection = db_connect();
5. // Query the database
6. $result = mysqli_query($connection,$query);
8. return $result;
9. }
Now let's take a look at an example query:
1. // An insertion query. $result will be `true` if successful
2. $result = db_query("INSERT INTO `users` (`name`,`email`) VALUES ('John Doe','john.doe@gmail.com')");
3. if($result === false) {
4. // Handle failure - log the error, notify administrator, etc.
5. } else {
6. // We successfully inserted a row into the database
7. }
If $result is false, we would like to have the error message returned by the database. For that purpose we'll create a function that gets it for us:
1. function db_error() {
2. $connection = db_connect();
3. return mysqli_error($connection);
4. }
Then we could use it in the code above:
1. if($result === false) {
2. $error = db_error();
3. // Send the error to an administrator, log to a file, etc.
4. }
Selecting rows

As mentioned above, running a SELECT query (and a couple of other similar types) will return amysqli_result object. To get the row data itself from the database, we need an additional step that fetches that data. We'll usemysqli_fetch_assoc which retrieves rows from amysqli_result object as an associated array - the array indexes will be the column names from the database.
1. // A select query. $result will be a `mysqli_result` object if successful
2. $result = db_query("SELECT `name`,`email` FROM `users` WHERE id=5");
4. if($result === false) {
5. // Handle failure - log the error, notify administrator, etc.
6. } else {
7. // Fetch all the rows in an array
8. $rows = array();
9. while ($row = mysqli_fetch_assoc($result)) {
10. $rows[] = $row;
11. }
12. }
Now we have an array containing the rows returned from the database. If no rows were returned - the array would be empty. Since fetching rows is a very common use case, we'll wrap it up in its own function:
1. function db_select($query) {
2. $rows = array();
3. $result = db_query($query);
5. // If query failed, return `false`
6. if($result === false) {
7. return false;
8. }
10. // If query was successful, retrieve all the rows into an array
11. while ($row = mysqli_fetch_assoc($result)) {
12. $rows[] = $row;
13. }
14. return $rows;
15. }
Querying the database now is dead simple:
1. $rows = db_select("SELECT `name`,`email` FROM `users` WHERE id=5");
2. if($rows === false) {
3. $error = db_error();
4. // Handle error - inform administrator, log to file, show error page, etc.
5. }
If db_select() returned false, we need to handle the error. Otherwise we can use the returned database data in our application.
Escaping dynamic values and user input

In the previous two example queries, I manually placed the values I passed to the database in the query - the user name and email in the INSERT query, and the user id in the SELECT query. If we replace those values with variables that contain unknown values, and especially user input, we need to escape it properly. There are a few characters - notably quotation marks and carriage returns - that will break our query, and in the case of user input allow an attacker to compromise our database (an attack referred to as SQL injection).

For that purpose, we'll use themysqli_real_escape_string()function. Since it needs a database connection, we'll go ahead and wrap it in its own function. In addition, since we only need to escape strings, we might as well quote the value at the same time:
1. function db_quote($value) {
2. $connection = db_connect();
3. return "'" . mysqli_real_escape_string($connection,$value) . "'";
4. }
If we are not sure of the type of value we pass to the database, it's always best to treat it as a string, escape and quote it. Let's look at a common example - form submission. We'll use our previous INSERT query with user input:
1. // Quote and escape form submitted values
2. $name = db_quote($_POST['username']);
3. $email = db_quote($_POST['email']);
5. // Insert the values into the database
6. $result = db_query("INSERT INTO `users` (`name`,`email`) VALUES (" . $name . "," . $email . ")");
And there you have it - a safe way to use user input in your queries. As mentioned earlier in the tutorial, an alternative to escaping / quoting values is using prepared statements. MySQLi use of prepared is somewhat verbose and requires more effort to abstract properly to functions like I did here. If you are interested, check out this nice introduction to prepared statements in MySQLi.
Object oriented version

I personally use OOP exclusively when I program in PHP. Since this tutorial is aimed at beginners, I did not want the requirement of OOP proficiency to be able to understand it. In case you come from a background with a different language and have experience with OOP, here is how the above procedural code would look:
1. class Db {
2. // The database connection
3. protected static $connection;
5. /**
6. * Connect to the database
7. *
8. * @return bool false on failure / mysqli MySQLi object instance on success
9. */
10. public function connect() {
11. // Try and connect to the database
12. if(!isset(self::$connection)) {
13. // Load configuration as an array. Use the actual location of your configuration file
14. $config = parse_ini_file('./config.ini');
15. self::$connection = new mysqli('localhost',$config['username'],$config['password'],$config['dbname']);
16. }
18. // If connection was not successful, handle the error
19. if(self::$connection === false) {
20. // Handle error - notify administrator, log to a file, show an error screen, etc.
21. return false;
22. }
23. return self::$connection;
24. }
26. /**
27. * Query the database
28. *
29. * @param $query The query string
30. * @return mixed The result of the mysqli::query() function
31. */
32. public function query($query) {
33. // Connect to the database
34. $connection = $this -> connect();
36. // Query the database
37. $result = $connection -> query($query);
39. return $result;
40. }
42. /**
43. * Fetch rows from the database (SELECT query)
44. *
45. * @param $query The query string
46. * @return bool False on failure / array Database rows on success
47. */
48. public function select($query) {
49. $rows = array();
50. $result = $this -> query($query);
51. if($result === false) {
52. return false;
53. }
54. while ($row = $result -> fetch_assoc()) {
55. $rows[] = $row;
56. }
57. return $rows;
58. }
60. /**
61. * Fetch the last error from the database
62. *
63. * @return string Database error message
64. */
65. public function error() {
66. $connection = $this -> connect();
67. return $connection -> error;
68. }
70. /**
71. * Quote and escape value for use in a database query
72. *
73. * @param string $value The value to be quoted and escaped
74. * @return string The quoted and escaped string
75. */
76. public function quote($value) {
77. $connection = $this -> connect();
78. return "'" . $connection -> real_escape_string($value) . "'";
79. }
80. }
Usage is identical to before, with OOP syntax instead of function calls. Our previous example queries would look like this:
1. // Our database object
2. $db = new Db();
4. // Quote and escape form submitted values
5. $name = $db -> quote($_POST['username']);
6. $email = $db -> quote($_POST['email']);
8. // Insert the values into the database
9. $result = $db -> query("INSERT INTO `users` (`name`,`email`) VALUES (" . $name . "," . $email . ")");
A select query:
1. $db = new Db();
2. $rows = $db -> select("SELECT `name`,`email` FROM `users` WHERE id=5");
And there you have it.
Source files and contributing

All the code found in this tutorial ishosted on GitHub under an MIT license for your convenience. If you have suggestions for improvement or other feedback, use the comments below or submit a pull request. Hope you found it useful!
Was this tutorial useful? Check out ZF Database Model / Mapper:

Providing the missing link in the Zend Framework MVC structure, Lionite_Db_Model provide a strong base structure for database-based domain models

0/Post a Comment/Comments