Приглашаем посетить
Чернышевский (chernyshevskiy.lit-info.ru)

Executing Queries

Previous
Table of Contents
Next

Executing Queries

Once we are connected to the database server and have a database selected, we will likely want to start retrieving data from those tables. To do this, we can again use the query method on the mysqli class. This method takes as its parameter the query to execute (without the trailing semicolon) and returns one of the following:

  • FALSE, to indicate that an error has occurred

  • trUE to indicate success for queries other than SELECT, SHOW, DESCRIBE, or EXPLAIN

  • A mysqli_result object, which can be used to fetch the results one row at a time for those queries that return result sets.

Retrieving Data

To retrieve a list of all users in our message board system, we might execute a SQL query, such as the following:

SELECT * FROM Users;

Let us look at how we would list all of the users in our message board system within PHP. The main workhorse function we will use is the fetch_assoc method on the mysqli_result class returned by the query method on mysqli objects. This method returns the values from the next row in the result set as an array, with the keys set to the names of the columns in the result set. When there are no more rows, NULL is returned. If there is more than one column with the same name (for instance, from a JOIN), only the last is included in the array (since arrays do not allow duplicate keys). For example, calling this on a row in our Users table might return an array as follows:

array(5) { ["user_id"]=> string(1) "1"
           ["user_name"]=> string(5) "lijin"
           ["full_name"]=> string(9) "Li Jin La"
           ["user_email"]=> string(20) lijin@somedomain.com
           ["birthdate"]=> string(10) "0000-00-00" }

To get the values of the row back with numeric indices instead of text-column key names in the array, you can use the fetch_array (and not have to worry about duplicate column names). Similarly, you can call the fetch_object method, which returns an object where the public member variables are the field names from the database and the values are the data. We will likely work the most with the fetch_assoc and fetch_array methods in this book.

Here is our code to print all of the users:

<?php

  $conn = @new mysqli('localhost', 'mbuser', 'password',
                      'MessageBoard');
  if (mysqli_connect_errno() != 0)
  {
    $errno = mysqli_connect_errno();
    $errmsg = mysqli_connect_error();
    echo "Connect Failed with: ($errno) $errmsg<br/>\n";
    exit;
  }

  // don't forget to set up character set
  $conn->query("SET NAMES 'utf8'");

  // prepare the query for execution
  $query_str = "SELECT * FROM Users";
  $result = @$conn->query($query_str);
  if ($result === FALSE)
  {
    $errno = $conn->errno;
    $errmsg = $conn->error;
    echo "Connect Failed with: ($errno) $errmsg<br/>\n";
    $conn->close();
    exit;
  }
  else
  {
    echo <<<EOM
  <table>
  <tr>
    <td>User ID</td>
    <td>Username</td>
    <td>Full Name</td>
    <td>Email Address</td>
    <td>Birthdate</td>
  </tr>
EOM;

    // fetch the data one row at a time.
    while (($row_data = @$result->fetch_assoc()) !== NULL)
    {
      echo <<<EOM
  <tr>
    <td>{$row_data['user_id']}</td>
    <td>{$row_data['user_name']}</td>
    <td>{$row_data['full_name']}</td>
    <td>{$row_data['user_email']}</td>
    <td>{$row_data['birthdate']}</td>
  </tr>

EOM;
    }

    echo <<<EOTABLE
  </table>

EOTABLE;

    //
    // clean up result sets when we're done with them!
    //
    $result->close();
  }

  //
  // finally, clean up the connection.
  //
  $conn->close();

?>

The output of this can be seen in Figure 12-2.

Figure 12-2. Printing the contents of our Users table.

Executing Queries


There are a few important things to note about the query. First, we have not coded the most helpful error messages to save space and not confuse the example too much. Second, once you have successfully made a connection and have a working mysqli object instantiated, you can use the errno and error properties on this object to get the error status and message of the last operation, which we do in the previous script. Successful operations set the errno property to 0.

After we have fetched all of the row data, we make a point of cleaning up the results of the query operation by calling the close method on the mysqli_result class. Theoretically, this is unnecessary since PHP closes and cleans up these objects for us when the script exits. However, for scripts where we will be executing large numbers of queries and analyzing their results, we might run into problems if PHP starts to run low on resources. Therefore, we help it by cleaning up objects (using the close method) as soon as we are done with them.

Finally, we close the connection when we are done with it to release any resources it might hold open.

Validating User Input

There will be times when you want to take user input and use it as part of a query. For example, if we were to have a login page that took a user's username and password, we might consider executing code along the following lines:

$q_str = "SELECT * FROM Users WHERE user_name='"
         . trim($_POST[user_name]) . "'";

$result = @$conn->query($q_str);

The problem arises if the user enters as his username

a'; DELETE FROM Users

If you were to execute this query, you would find yourself with a lot fewer users. The complete query we just sent to the database server was

SELECT * FROM Users WHERE username='a'; DELETE FROM Users;

This is known as a SQL injection attack (the user is maliciously injecting extra SQL code into his user input), and we must be extra careful to protect ourselves against these.

There are a few ways we can make user input safer:

  • Escape or exclude dangerous characters from user input, especially quote characters and semicolons.

  • Validate user input before we put it into query strings. Items such as dates, numbers, and currency values are perfect examples of this.

  • In extreme cases, we may wish to look through the user input and remove items we perceive as particularly dangerous.

The first and most important action is to make sure that there are no unescaped quotes in the user's input that are not escaped with a preceding backslash character (\). To do this, we use the member function mysqli::real_escape_string, which looks through a string and makes sure that a certain number of characters is properly escaped. These characters include single quotes, double quotes, backslashes, carriage returns, linefeeds (part of new lines), and a few other nonprintable sequences.

To modify our previous code to be safer, we can simply do the following:

$user_name = trim($user_name);
$user_name = @conn->real_escape_string($user_name);
$q_str = "SELECT * FROM Users WHERE user_name='"
         . $user_name . "'";

$result = @$conn->query($q_str);

Note that we use the trim function to remove any leading or trailing whitespace characters. As we mentioned in Chapter 6, this function is theoretically not multi-byte character enabled, but it never breaks UTF-8 strings, so we cautiously use it. As long as we always wrap arguments to queries in single quotes, we can be guaranteed that any semicolons are treated only as part of those strings, and know that we have given ourselves additional safety in dealing with user input.

Unfortunately, the real_escape_string method does not escape percent sign characters (%), which can be a potentially dangerous character in our input. Imagine if we search for names using a LIKE clause and the user enters the name "%". Thus, we write an improved version of real_escape_string, to include in appropriate classes and call whenever we wish to be sure that a string is safe enough to be sent to the database. Web application authors who are not using MySQL databases should be sure to see what is escaped by the database functions they are using. Our new function looks as follows:

<?php

  function super_escape_string
  (
    $in_string, 
    $in_conn, 
    $in_removePct = FALSE
  )
  {
    $str = $in_conn->real_escape_string($in_string);
    if ($in_removePct)
      $str = ereg_replace('(%)', '\\\1', $str);
    return $str;
  }

?>

The function accepts an optional third parameter controlling whether or not it should escape percent signs (%) for use in LIKE clauses. (Note that escaping percent signs in other places is entirely unnecessary and leads to extra backslash characters in your tables.)

To do its work, this function uses ereg_replace. (This function will be discussed more in Chapter 22, "Data Validation with Regular Expresssions"). This new function behaves similarly to str_replace and other functions that manipulate strings, but it does so in a slightly more powerful andmost importantlysafer multi-byte character set fashion.

For a second strategy, we will spend time in our PHP scripts validating the user input. If we know that usernames must be one word and cannot contain anything other than Latin ASCII letters, numbers, and underscores, we can reject any string with

  • Whitespace.

  • NULL characters.

  • Punctuation and other control characters.

  • Non-ASCII letters (Æ, Executing Queries, and Executing Queries are perfect examples).

Similarly, we could validate other non-string types for sanity. If somebody wanted to withdraw money from an account, entering -100000$ would be confusing and potentially problematic. Also, we want to make sure this person was not born on February 31 and his gender was of a known/accepted value (male, female, unknown) as opposed to "lemur."

Finally, for extreme cases where we are paranoid about the input coming from the user, it cannot hurt to write some extra code to pick through the input and remove anything about which we are particularly twitchy or nervous. Some people might decide to make sure that the keywords DELETE, UPDATE, DROP, ALTER, GRANT, REVOKE, and CREATE are never included in user input except where it makes perfect sense (for instance, in a message body).

Like all things with security (see Chapter 16, "Securing Your Web Applications: Planning and Code Security"), there are no perfect solutions, but with varying degrees of effort, we can reduce the risk to our applications.

Inserting, Deleting, and Updating Data

Even though they do not return result sets like the various SELECT and SHOW queries do, we can still use the query method on the mysqli class to execute INSERT, DELETE, and UDPATE queries.

$q_str = <<<EOQ

 INSERT INTO Users(user_name,full_name,user_email,birthdate)
   VALUES ($user_name, $full_name, $user_email, $u_birthdate)
EOQ;

  $result = @$conn->query($q_str);
  if ($result === FALSE)
  {
    echo "Connect Failed with: $conn->error<br/>\n";
    $conn->close();
    exit;
  }
  else
  {
    echo "Created new user with user_id: {$conn->insert_id}";
  }

Many of the tables in our databases contain auto-incrementing integer primary keys. One of the things we would like to find out after executing an INSERT query is the value of the newly created primary key. Instead of having to execute a whole new SELECT statement, we can instead query the insert_id property on the mysqli class, which gives us the value for the last row inserted.

Similarly, after we have executed an UPDATE or DELETE query, we might wish to find out how many rows were affected (updated or removed) as a result of the query. The affected_rows property on the mysqli class is used precisely for this purpose.

$q_str = <<<EOQ

UPDATE Users
  SET full_name = 'Petunia Wallace'
  WHERE user_name = 'pdurbin'

EOQ;

  $result = @$conn->query($q_str);
  if ($result === FALSE)
  {
    echo "Connect Failed with: $conn->error<br/>\n";
    $conn->close();
    exit;
  }
  else
  {
    echo "I modified '{$conn->affected_rows}' rows.<br/>";
  }

The number of affected rows from a query can be 0, which is not a SQL or database server error, but merely an indication that no rows were changed. However, it might indicate an error in the logic of our web application.

Transactions

You can use transactions with the mysqli extension through three methods on the mysqli class:

  • autocommit You pass FALSE to this to turn off autocommitting, meaning that nothing happens to your data until you call commit or rollback. You pass TRUE to this function to resume normal nontransacted activity.

  • commit This is the equivalent of calling COMMIT.

  • rollback This is the equivalent of calling ROLLBACK.

Thus, code used to execute transactions looks something like the following:

  $conn->autocommit(FALSE);
  $num_in_stock -= 1;
  $query1 = <<<EOQ1
UPDATE Products SET number_in_stock = $num_in_stock
   WHERE pid = $pid
EOQ1;

  $results = @$conn->query($query1);
  if ($results === FALSE)
  {
    // abort and rollback any changes
    $conn->rollback();
    echo "Aieee: " .$conn->error;
    exit;
  }

  $query2 = <<<EOQ2
INSERT INTO Orders (order_date, user_id, product, num_units)
     VALUES (NOW(), $userid, $pid, 1)
EOQ2;
  $results = @$conn->query($query2);
  if ($results === FALSE)
  {
    // abort and rollback any changes
    $conn->rollback();
    echo "Aieee: " .$conn->error;
    exit;
  }
// commit the transaction!
$result = $conn->commit();
if ($result === FALSE)
{
  echo "Aieee: " .$conn->error;
  exit;
}    
$conn->close();

Errors from mysqli

Although we have briefly shown the code for this, it is worth taking a closer look at the error reporting and handling functionality in the myslqi extension. There are two key places we might see an error when working with our database:

  • Connecting to the database

  • Executing a query or statement

For the former, we use the mysqli_connect_errno and mysqli_connect_error functions. This returns 0 on success and an error number when there is a failure connecting to the database. It is the function we should call in code to detect errors on connection. The second function returns the text of the error message from the database in the language of the database software (typically US English).

Thus, our creation code almost always looks as follows:

<?php

  $conn = @new mysqli($host, $user, $pwd, $db);
  if (mysqli_connect_errno() !== 0)
  {
    $msg = mysqli_connect_error();
    // take appropriate action on error ...
  }

  // otherwise continue as normal...

?>

Once we have one of the various mysqli objects, most of the other errors with which we deal are exposed through the errno and error properties on the object we are using. After we execute a query, we can check the errno property on the mysqli object:

<?php

  $query = 'SELECT * FROM Users';
  $results = @$conn->query($query);
  if ($conn->errno !== 0)
  {
    $msg = $conn->error;
    // take appropriate action here for the failure ...
  }

  // continue with data retrieval.

?>

One case where we might be interested in the value of the return code is when we use foreign keys to enforce data integrity in our database. As mentioned in the section "Foreign Keys and Cascading Deletes" (in Chapter 9), the database can ensure that foreign key references are valid whenever we add a row into a table by using the FOREIGN KEY syntax in SQL.

We used the following syntax to create our Replies table:

CREATE TABLE Replies
(
  reply_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  author_id INTEGER NOT NULL,
  message_id INTEGER NOT NULL,
  date_posted DATETIME,
  title VARCHAR(150),
  body TEXT,
  FOREIGN KEY (author_id) REFERENCES Users (user_id),
  FOREIGN KEY (message_id) REFERENCES Messages (message_id)
);

For cases in our web applications when we wish to detect foreign key violations and report these differently to the user, we can look for a specific value for errno after an INSERT INTO statement. For example, we could do this when we receive a request to create a reply to message 54835 and no such message exists in our database. Instead of showing the user a frightening error message that says something went wrong with the database, we could use the fact that we know it was a foreign key violation to give the user a better message.

In MySQL, a foreign key violation causes the database server to return error code 1219:

<?php

  define('FOREIGN_KEY_VIOLATION', 1219);

  $query = <<<EOQ
INSERT INTO Replies (author_id, message_id, date_posted,
                     title, body)
     VALUES($logged_in_user, intval({$_GET['msgid']}), NOW()
            $title, $body)
EOQ;
  $result = @$conn->query($query);
  if ($conn->errno === FOREIGN_KEY_VIOLATION)
  {
    echo 'Sorry, the specified message does not exist';
  }
  else if ($conn->errno !== 0)
  {
    // deal with serious db failure here.
  }
  else
  {
    // all is well!
  }

?>

The Replies table we have created has two foreign keys that could cause this error situation. However, it is probable that we already verified that the author_id is valid when we logged the user into our message board system, so we can be sure it will not generate a foreign key violation.


Previous
Table of Contents
Next