JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

Security Considerations

Database security with respect to PHP comes down to two broad issues:

  • Protecting the database access information

  • Inspecting the information being stored in the database

You can accomplish the first objective by securing the MySQL connection script. I discussed some of the options for safeguarding the mysqli_connect.inc.php file earlier in this chapter. The best, although not always possible, method is to store the file outside of the Web document root (Figures 7.1 and Figure 7.2) so that it is never viewable from a Web browser.

For the second objective, there are numerous options. One, as I've been doing in this chapter, is to use the $_POST array (or $_GET) instead of global variables. Second, and this is beyond the scope of this book, is to use regular expressions to make sure that submitted data matches what you would expect it to be. I also recommend that numeric values be type cast (forcibly converted) before use in a query.

With strings, you need to protect against problematic characters. Thus far I've done so using the addslashes() function on submitted data to escape problematic characters. (The same benefit could be achieved by using PHP's Magic Quotes feature, as mentioned in the sidebar.) Alternatively, you could use the specific mysqli_real_escape_string() function:

$str = mysqli_real_escape_string ($dbc, ' $str);

This function acts like addslashes()and should be used with any textbut is more database-specific. It also takes into account the character set being used, which is a great advantage in multilingual and non-English applications.

To demonstrate this function, and also to show one last PHP/MySQL technique, I will write a script that allows a user to edit an expense record.

To use mysqli_real_escape_string():

1.
Create a new PHP document in your text editor, beginning with the HTML (Script 7.6).

<!DOCTYPE html PUBLIC "-//W3C//DTD  XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/ xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/ xhtml" xml:lang="en" lang="en">
<head>
  <meta http-equiv="content-type" content="text/html; charset= 'iso-8859-1" />
  <title>Edit An Expense</title>
</head>
<body>

Script 7.6. The final script in this chapter incorporates better security by way of the mysqli_real_escape_string() function. It also demonstrates how to run an UPDATE query from a PHP script.

1   <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
2           "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
3   <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
4   <head>
5       <meta http-equiv="content-type" content="text/html; charset=utf-8" />
6       <title>Edit An Expense</title>
7   </head>
8   <body>
9   <?php
10
11  // ***** edit_expense.php *****
12  // ***** Script 7.6 *****
13  // This page displays and handles a form for
14  // editing records in the expenses table.
15  // This page requires receipt of an eid (expense_id).
16
17  // Include the MySQL information:
18  require_once ('../mysqli_connect2.inc.php');
19
20  if (isset($_POST['submitted'])) { // If the form has been submitted, handle it.
21
22      // Check the required form fields:
23      if ( (isset($_POST['expense_category_id']) OR !empty($_POST['expense_category']))
 AND !empty($_POST['expense_amount']) AND !empty($_POST['expense_description']) ) {
24
25          // Start the query:
26          $q = "UPDATE expenses SET ";
27
28          // Determine if a new expense category was entered:
29          if (!empty($_POST['expense_category'])) {
30
31              // Create a second query:
32              $q2 = "INSERT INTO expense_categories VALUES (NULL, '" .
 mysqli_real_escape_string ($dbc, $_POST['expense_category']) . "')";
33
34              // Execute the second query and react accordingly:
35              $r = @mysqli_query ($dbc, $q2);
36              if (@mysqli_affected_rows($r) == 1) {
37                  echo '<b><font color="green">The new expense category has been added!<
/font></b><br />';
38                  $q .= "expense_category_id=" . mysqli_insert_id($dbc) . ", ";
39               } else {
40                   echo '<b><font color="red">The new expense category was not entered 
into the table!</font></b><br />';
41                   echo '<b><font color="red">MySQL reported: '. mysqli_error($dbc) .'</
font></b><br />';
42                   $problem = TRUE;
43               }
44
45           } else { // Finish the expense_category conditional.
46               $q .= "expense_category_id=" . (int) $_POST['expense_category_id'] . ', ';
47           }
48
49           // Finish the query:
50           $q .= "expense_amount=" . (float) $_POST['expense_amount'] . ",
 expense_description='" .
             mysqli_real_escape_string($dbc, $_POST['expense_description']) . "',
 expense_date='" .
             mysqli_real_escape_string($dbc, $_POST['expense_date']) . "' WHERE
 expense_id=" . (int)
             $_POST['expense_id'];
51
52           // Check to see if there was a problem:
53           if (!$problem) {
54
55               // Execute the query:
56               $r = @mysqli_query ($dbc, $q);
57
58             // Print a message indicating success or not:
59             if (@mysqli_affected_rows($dbc) == 1) {
60                 echo '<b><font color="green">The expense has been edited!</font></b>';
61             } else {
62                 echo '<b><font color="red">The expense was not edited!</font></b>';
63                 echo '<b><font color="red">MySQL reported: '. mysqli_error($dbc) .'</
font></b><br />';
64             }
65         } else { // If there was a problem:
66             echo '<b><font color="red">The expense was not edited because the new 
expense category could not be added!</font></b>';
67         }
68
69     } else { // Print a message if they failed to enter a required field:
70         echo '<b><font color="red">You missed a required field!</font></b>';
71     }
72
73 } else { // If the form has not been submitted, display it.
74
75     // Create the query:
76     $q = 'SELECT * FROM expenses WHERE expense_id = ' . (int) $_GET['eid'] . ' LIMIT 1';
77
78     // Execute the query:
79     $r = @mysqli_query ($dbc, $q);
80
81     // See if an expense was returned:
82     if (@mysqli_num_rows($r) == 1) {
83
84         // Retrieve and print the results:
85         $row = mysqli_fetch_array ($r, MYSQLI_ASSOC);
86         mysqli_free_result($r);
87
88         echo 'Edit this expense:<br />
89         <form action="edit_expense.php" method="post">
90         <ul>
91         <li>Expense Category: <select name="expense_category_id">';
92
93         // Display the expense categories:
94         $r2 = mysqli_query ($dbc, 'SELECT * FROM expense_categories ORDER BY
 expense_category');
95         while ($row2 = mysqli_fetch_array ($r2, MYSQLI_NUM)) {
96             if ($row2[0] == $row['expense_category_id']) {
97                 echo "<option value=\"$row2[0]\" selected=\"selected\">$row2[1]</option>\n";
98             } else {
99                 echo "<option value=\"$row2[0]\">$row2[1]</option>\n";
100            }
101        }
102        
103        mysqli_free_result($r2);
104
105        // Finish the form:
106        echo '</select></li>
107        or<br />
108        <li>Enter a new expense category: <input type="text" name="expense_category" 
size="30" maxlength="30" /></li>
109        </ul>
110        <p>Expense Amount: <input type="text" name="expense_amount" value="' .
 $row['expense_amount'] . '" size="10" maxlength="10" /></p>
111        <p>Expense Date: <input type="text" name="expense_date" value="' .
 $row['expense_date'] . '" size="10" maxlength="10" /></p>
112        <p>Expense Description: <textarea name="expense_description" rows="5"
 cols="40">' . $row['expense_description'] . '</textarea></p>
113        <input type="hidden" name="submitted" value="true" />
114        <input type="submit" name="submit" value="Submit!" />
115        <input type="hidden" name="expense_id" value="' . $_GET['eid'] . '" />
116        </form>';
117
118    } else { // No record returned!
119        echo '<b><font color="red">This page must receive a valid expense ID!</font></b>';
120    }
121
122 } // Finish the main "submit" conditional.
123
124 // Close the connection (not required):
125 mysqli_close($dbc);
126
127 ?>
128 </body>
129 </html>

2.
Start the PHP section, including the MySQL information.

<?php
require_once (`../mysqli_connect2. inc.php');

If you didn't rename the mysqli_connect.inc.php script in the previous section, be sure to use the correct (original) script name here.
3.
Create the conditional for displaying or handling the form.

if (isset($_POST[`submitted'])) {

4.
Check the required form fields.

if ( (isset($_POST[`expense_ category_id']) OR !empty($_POST [`expense_category'])) AND
 !empty($_POST[`expense_amount']) AND !empty($_POST[`expense_ description']) ) {

This code is similar to that in add_expense2.php. The form will allow the user to select from the list of current expense categories or to enter a new one.
5.
Begin the main query.

$q = "UPDATE expenses SET ";

Since this form will be for editing existing records, the query will be an UPDATE rather than an INSERT. Otherwise, this section (and the next) of the script is familiar territory.
6.
Check for a new expense category and handle it accordingly.

if (!empty($_POST[`expense_ category'])) {
  $q2 = "INSERT INTO expense_ categories VALUES (NULL, `" .  mysqli_real_escape_string 
($dbc,  $_POST[`expense_category']) .  "`)";
  $r = @mysqli_query ($dbc, $q2);
  if (@mysqli_affected_rows($r) ==  1) {
     echo `<b><font color="green"> The new expense category has  been added!</font></b><br
 />';
     $q .= "expense_category_id=" .  mysqli_insert_id($dbc) . ", ";

} else {

     echo `<b><font color="red">The new expense category was not entered into the table! <
/font></b><br/>';

     echo `<b><font color="red"> MySQL reported: `.  mysqli_error($dbc)
 .'</font></b><br
/>';

     $problem = TRUE;

  }

One change you'll need to make when using an UPDATE is that instead of a query being of the form INSERT INTO tablename VALUES (`value', `value2'...), it will now be UPDATE tablename SET column='value', column2='value2', .... In this part of the script, if the expense_category_id is changed, it will be updated by the query.

Note that for security purposes, I send the new expense category through the mysqli_real_escape_string() function. For debugging purposes, I invoke the mysqli_error() function, in case the query doesn't work for some reason (Figure 7.19). I might also want to print the query that was executed if I need more help debugging the problem.

Figure 7.19. If the new expense category could not be added, the MySQL error is reported (for debugging purposes).


7.
Complete the main query.

} else {
   $q .= "expense_category_id=" . (int)  ' $_POST[`expense_category_id'] .  ' `, `; 
}
$q .= "expense_amount=" . (float)  $_POST[`expense_amount'] . ", expense_description='" .
 mysqli_ real_escape_string($dbc, $_POST [`expense_description']) . "`,  expense_date='" .
 mysqli_real_ escape_string($dbc, $_POST [`expense_date']) . "` WHERE  expense_id=" . (int)
 $_POST [`expense_id'];

The first part of this code completes the expense category conditional. If a current expense category is used, that value is type cast to an integer and added to the query. Two other numbersthe expense amount and the expense IDare also type cast. If you wanted, you could validate that each of these values is greater than 0 as well.

For the strings in the query, each is run through the mysqli_real_escape_string() function.

8.
Execute the query and report upon the results (Figure 7.20).

Figure 7.20. As with the other scripts in this chapter, a standard message indicates successful execution of the script.


if
(!$problem) {
  $r = @mysqli_query ($dbc, $q);
  if (@mysqli_affected_rows($dbc) ==  ' 1) {
     echo `<b><font color="green">The expense has been edited!</font></b>';
  } else {
     echo `<b><font color="red">The expense was not edited! </font></b>';
     echo `<b><font color="red"> MySQL reported: `. mysqli_error($dbc) ' .'</font></b><br />';
     }
  } else {
     echo `<b><font color="red">The expense was not edited because the expense category 
could not be added!</font></b>';
  }

In this example, I've decided to use the mysqli_affected_rows() function, which returns the number of rows affected by the previous query. It's usable whenever you run an UPDATE, ALTER, DELETE, or INSERT.
9.
Complete the conditionals.

     } else {
        echo `<b><font color="red">
          The expense was not edited
          because the new expense
          category could not be
          added!</font></b>;
     }
  } else {
     echo `<b><font color="red">You
       missed a required field!
       </font></b>';
  }else {

This completes the "handling" section of the form, which updates the record. The rest of the form will display the record, within an HTML form, for editing purposes.
10.
Select the current record from the database.

$q = `SELECT * FROM expenses WHERE expense_id = ` . (int) $_GET[`eid'] . ` LIMIT 1';
$r = @mysqli_query ($dbc, $q);
if (@mysqli_num_rows($r) == 1) {
   $row = mysqli_fetch_array ($r,
   MYSQLI_ASSOC);
   mysqli_free_result($r);

To edit a record, I'll need to retrieve it from the database. The best way of doing so is to refer to the record's primary key (expense_id). This script assumes that it will receive the expense_id as a variable in the URL called eid. This value is type cast for sake of security.

To confirm that the expense ID is valid, I use mysqli_num_rows(). Then I fetch that one row (since I am retrieving only one record, there's no need for a while loop). At the end of this section of code, the $row associative array will contain all of the information related to one expense.

11.
Start the HTML form.

echo `Edit this expense:<br />
<form action="edit_expense.php"
          method="post">
 <ul>
<li>Expense Category: <select
      name="expense_category_id">';

The form will be very much like the add_expense.php form, although with preset values.
12.
Create the pull-down menu.

$r2 = mysqli_query ($dbc, `SELECT * FROM expense_categories ORDER BY expense_category');
while ($row2 = mysqli_fetch_array ($r2, MYSQLI_NUM)) {
  if ($row2[0] == $row [`expense_category_id']) {
     echo "<option value=\"$row2[0]\" selected=\"selected\">$row2 [1]</option>\n";
  } else {
     echo "<option value=\ "$row2[0]\">$row2[1]
     </option>\n";
  }
}
mysqli_free_result($r2);

This pull-down menu is more involved than the similar one used in add_expense.php because I want to match up the current expense_category_id value with that in the pull-down menu so that the current category is automatically displayed. This can be accomplished with a basic if-else conditional and the selected="selected" HTML code.
13.
Finish the rest of the HTML form.

echo `</select></li>
or<br />
<li>Enter a new expense category: <input type="text" name= "expense_category" size="30" 
maxlength="30" /></li>
</ul>
<p>Expense Amount: <input type="text" name="expense_amount" value="' .
 $row[`expense_amount'] . `" size="10" maxlength="10" /></p>
<p>Expense Date: <input type="text" name="expense_date" value="' . $row[`expense_date'] . 
`" size="10" maxlength="10" /></p>
<p>Expense Description: <textarea name="expense_description" rows="5" cols="40">' . 
$row[`expense_description'] . `</textarea></p>
<input type="hidden" name= "submitted" value="true" />
<input type="submit" name="submit" value="Submit!" />
<input type="hidden" name= "expense_id" value="' . $_GET[`eid'] . `" />
</form>';

For standard text inputs and text areas, you can preset a value based upon stored information by using the value attribute for text boxes or simply entering the stored information between the text area tags. The record's primary key must be stored as a hidden variable so that the script knows on which record to run the UPDATE query.

Magic Quotes

Magic QuotesPHP's ability to automatically escape problem charactershas changed significantly over PHP's development. As a convenience, Magic Quotes handles single and double quotation marks submitted by an HTML form, retrieved from a database, and so forth. But, to encourage more security-conscious programming, PHP now comes with Magic Quotes disabled. If this is the case with your server, you must use either the addslashes() or mysqli_real_escape_string() function instead.

You can easily determine what you need to do by running some of the scripts in this chapter using strings with single quotation marks in them as examples. If the queries are not being entered, Magic Quotes is turned off and you must escape these characters. If data is being entered with multiple backslashes, this means that you have Magic Quotes turned on and are also using addslashes() mysqli_real_escape_string() and you should therefore eliminate one of these features.


14.
Complete the PHP script and the HTML code.

  } else {
     echo `<b><font color="red">This
 ' page must receive a valid  expense ID!</font></b>';
  }
} mysqli_close($dbc);
?>
</body>
</html>

15.
Save the file as edit_expense.php, upload it to your server, and test in your Web browser (Figures 7.21 and 7.22 ). As it stands, to test this script, you'll need to append the URL with code like

?eid=x,
where x
refers to an expense_id
of a record in the database.

Figure 7.21. Since this record was entered without a date value, I can update that item using this form.


Figure 7.22. The edit_expense.php script, like add_expense.php, also allows you to enter a new category for an item.


Tips

  • The information discussed in this and the previous section can easily be applied to the earlier scripts as well to improve their security and error management.

  • On the book's Web site, you can download a browse_expenses.php page that links to this edit_expense.php script.



Previous Page
Next Page


JavaScript Editor Js editor     Website development


©