Month: June 2010

This is what I do for every field that I read from a form or from the URL if I am going to use it in a database query.
The mysql_real_escape_string looks for things that could be used to subvert a query and escapes them.
When I create my SQL query statement they can’t be treated as part of the command.

This is a part of a file that reads and validates a form

  // Look to see if the checkbox has a value, then update the database
  if ($id != '') {
    $NickName = mysql_real_escape_string($_POST["NickName$i"]);
    $Address = mysql_real_escape_string($_POST["Address$i"]);
    $Method = mysql_real_escape_string($_POST["Method$i"]);
    
    $Name = mysql_real_escape_string($_POST["Name$i"]);      
    $Code = mysql_real_escape_string($_POST["Code$i"]);
    $TimeZone = mysql_real_escape_string($_POST["TimeZone$i"]);
  }

I have Javascript that validates the data in user forms but I always validate again on my end before I put it into the database.

    $numToUpdate++;  
    $data_is_valid = TRUE;
    if (!$DeleteRecipients) {
      include('address_book_validation.php');
    }

This is what the escape string looks like for URLs e.g.
http://wellgolly.com/index.php?p=Reminder&m=Pricing

  
  $page  = mysql_real_escape_string($_GET['p']);
  if ($page == '') { $page = 'Main'; }

  $menuChoice = mysql_real_escape_string($_GET['m']);
  if ($menuChoice == '') { $menuChoice = 'New'; }

Important note:
mysql_real_escape_string() calls MySQL’s library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ‘, ” and \x1a.

Since mysql_real_escape_string is a mysql command, not a php command it requires an active database in order to run. If a database session is not running, it will create one. From the manual, The MySQL connection. If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments. If no connection is found or established, an E_WARNING level error is generated.

If you escape every get command, you can actually cause your server to be inaccessible because the number of database connections will be exceeded. This happened to us when being scanned by Bing recently. The bot made so many requests to a file with multiple escape strings, that it blocked all other connections.

Update: This post uses mysql_real_escape_string but they now recommend using mysqli_real_escape_string. I haven’t looked into what is required to make the update.