| Database security with respect to PHP comes down to two broad issues: Protecting the database access information Being cautious when running queries, particular those involving user-submitted data
You can accomplish the first objective by securing the MySQL connection script outside of the Web directory so that it is never viewable through a Web browser. I discussed this is some detail earlier in the chapter. For the second objective, there are numerous steps you can and should take. First, as I've been doing in this chapter, be sure to use the $_POST array (or $_GET) instead of global variables. Second, validate that some data has been submitted, or that it is of the proper type (number, string, etc.). Third, use regular expressions to make sure that submitted data matches what you would expect it to be. Regular expressions will also be covered in Chapter 10. A fourth recommendation is to use the specific mysql_real_escape_string() function. This function cleans data by escaping what could be problematic characters: $data = mysql_real_escape_string  ($data, $dbc);
This function acts like addslashes()and should be used with any text fields from your formsbut is more database-specific. It was added in version 4.3 of PHP. If you are using an older version of PHP, use this function and syntax instead (notice that no database connection is required): $data = mysql_escape_string ($data);
I'll incorporate this new function into the registration script. However, since your server may have Magic Quotes enabled, the script will need to make sure that submitted data is not over-escaped. Thus, the escape_data() function will be defined, making use of a new function, ini_get(), as described in the sidebar on page 257 "Magic Quotes and mysql_real_escape_string()." To use mysql_real_escape_string() 1. | Open register.php (Script 7.3) in your text editor.
| 2. | After the submit conditional (line 7), add the following (Script 7.5).
require_once ('../mysql_connect.  php); function escape_data ($data) { global $dbc; if (ini_get('magic_quotes_gpc')) { $data = stripslashes($data); } return mysql_real_escape_string (trim($data), $dbc); }
Script 7.5. The register.php script now uses the mysql_real_escape_string() function to clean the submitted data.   
The escape_data() function will take a string, trim any extra spaces from it, apply the mysql_real_escape_string() function to it, and then return the results. If the data has already been run through Magic Quotes (if ini_get('magic_quotes_gpc') is trUE), the data is first stripped of its slashes so that the data is not over-escaped (where every appropriate character would be escaped twice).
Because this function requires a database connection, the mysql_connect.php script must be required before this function is called and the database connection itself$dbcmust be made available as a global variable.
Magic Quotes and mysql_real_escape_string() Magic Quotes, which I discussed in Chapter 2, "Programming with PHP," will automatically escape problematic characters in text entered in forms. When querying databases this is particularly useful because those quotation marks will create errors in your SQL commands. However, the mysql_real_escape_string() function is a slightly better way to achieve the same result, since it's database- and language-specific. With this in mind, I want to create a way to use mysql_real_escape_string() and not Magic Quotes (using both will over-escape the values). My solution in this script is to test the Magic Quotes status using the ini_get() function. This function will return the setting in PHP's configuration file (php.ini) for a particular option. If ini_get() returns trUEmeaning Magic Quotes is onI'll strip the existing slashes before applying mysql_real_escape_string(). In current versions of PHP, Magic Quotes is turned off by default, so ini_get() will return FALSE and there's no need to strip any existing slashes. |
| 3. | Change the validation routines to use this function, replacing each occurrence of $var = trim($_POST['var']) with $var = escape_data($_POST['var']).
$fn = escape_data($_POST ['first_name]); $ln = escape_data($_POST ['last_name]); $e = escape_data($_POST['email']); $p = escape_data($_POST ['password1]);
Instead of just assigning the submitted value to each variable ($fn, $ln, etc.), I'll run the value through the escape_data() function first.
| 4. | Delete the original require_once ('../mysql_connect.php') line (refer to Script 7.3, line 46).
Since I've moved this line near the top of the script, I should remove it here.
| 5. | Move the mysql_close() line so that it's called before the end of the main submit conditional.
To be consistent, since the database connection is opened as the first step of the main conditional, it should be closed as the last step of this same conditional.
| 6. | Save the file as register.php, upload it to your Web server, and test in your Web browser (Figures 7.10 and 7.11).
Figure 7.10. Values with apostrophes in them will be handled properly whether or not Magic Quotes is enabled (see Figure 7.11). 
Figure 7.11. Now the script worksand is more database securewith or without Magic Quotes. 
|
 Tips
The mysql_real_escape_string() function escapes a string in accordance with the language being used, which is an added advantage over addslashes() or the mysql_escape_string() function. If you see results like those in Figure 7.12 it means that the mysql_real_escape_string() function cannot access the database (because it has no connection, like $dbc). Figure 7.12. Since the mysql_real_escape_string() requires a database connection, improperly using it can lead to other errors. 
Without the use of this function and without Magic Quotes turned on, a common name like O'Toole will generate a MySQL error (Figure 7.13) because the apostrophe in the name will conflict with the apostrophes used in the query. Figure 7.13. Apostrophes in form values can be problematic in queries, if not accounted for. 
With Magic Quotes turned on, you may want to use the stripslashes() function before printing a submitted value back to the sticky form (see the Last Name input in Figure 7.14). Figure 7.14. With Magic Quotes enabled, escaped values reprinted in a form will be inaccurate unless the stripslashes() function is used. 
The get_magic_quotes_gpc() function can also be used to return the current Magic Quotes setting. I prefer to use ini_get(), as this function can be used to get the setting of many different php.ini settings, not just Magic Quotes.
|
