Protecting PHP server behaviors from SQL injection vulnerability

Some databases let you send multiple SQL statements in a single query. Because of this, there are potential security risks when you pass parameters in a query string to a dynamically generated database query. Hackers might try to modify URL or form variables in a dynamic query by appending malicious SQL statements to existing parameters. This is often referred to as a SQL injection attack. Some of the server behavior code created by Dreamweaver should be modified to reduce the risk of SQL injection attacks. For more background information on SQL injection, see this Wikipedia article. (Ref. 201713)

Note: The issue described in this TechNote has been fixed in the Dreamweaver 8.0.2 Updater.

This TechNote covers Dreamweaver's PHP server behaviors. There are separate TechNotes for the ColdFusion, ASP VBScript, ASP JavaScript and JSP server behaviors. The ASP.NET server behaviors are not affected.

When you let a query string pass a parameter, ensure that only the expected information is passed. Adobe has created a Dreamweaver 8.0.2 Updater that reduces the risk of SQL injection attacks. These fixes will be incorporated into all future releases of Dreamweaver. After updating Dreamweaver 8, you will need to reapply the server behaviors to the pages that use them, and redeploy those pages to your server.

The remainder of this TechNote documents how to manually edit Dreamweaver MX 2004 code to prevent SQL injection attacks with the PHP server model. Server behaviors that are vulnerable to these attacks are listed below, along with fixes:

Recordset with a filter
Insert, Update, and Delete Record server behaviors and Record Insertion Form Wizard
Log In User
Check New User
Master Detail Page Set
Record Update Form Wizard

Unfiltered recordsets do not need to be modified, but filtered recordsets do. In the example below, a Dreamweaver MX 2004 recordset named "rs_byDate" is filtered by a date value passed in from a URL parameter. The highlighted code below is what will need to be changed:

<?php $colname_rs_byDate = "1"; if (isset($_GET['relDate'])) {   $colname_rs_byDate = (get_magic_quotes_gpc()) ? $_GET['relDate'] : addslashes($_GET['relDate']); } mysql_select_db($database_dreamqa2, $dreamqa2); $query_rs_byDate = sprintf("SELECT * FROM albums WHERE relDate = '%s'", $colname_rs_byDate); $rs_byDate = mysql_query($query_rs_byDate, $dreamqa2) or die(mysql_error()); $row_rs_byDate = mysql_fetch_assoc($rs_byDate); $totalRows_rs_byDate = mysql_num_rows($rs_byDate); ?>

To protect the recordset from SQL injection attacks, a custom GetSQLValueString() function should be created near the top of the page. This function will verify the data type of the query parameter. Once the function has been created, then update the recordset code to use the GetSQLValueString() function.

Go to the second line in code view, right after the include file for the connection, and add the GetSQLValueString() function to the beginning of the code as follows:

<?php if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")  {   $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;   $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);   switch ($theType) {     case "text":       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";       break;      case "long":     case "int":       $theValue = ($theValue != "") ? intval($theValue) : "NULL";       break;     case "double":       $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";       break;     case "date":        $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";        break;     case "defined":       $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;       break;   }   return $theValue; } } ?>

Remove the magic quotes code so that backslashes are not incorrectly added to GET and POST data when magic quotes are turned off on the PHP server.

Original code:

$colname_rs_byDate = (get_magic_quotes_gpc()) ? $_GET['relDate'] : addslashes($_GET['relDate']);

Modified code:

$colname_rs_byDate = $_GET['relDate'];

Update the recordset code to create a SQL string using the GetSQLValueString() function created above. Update the value of the variable $query_rs_byDate:

Original code:

$query_rs_byDate = sprintf("SELECT * FROM albums WHERE relDate = '%s'", $colname_rs_byDate);

Modified secured code:

$query_rs_byDate = sprintf("SELECT * FROM albums WHERE relDate = %s", GetSQLValueString($colname_rs_byDate, "date"));

The Insert, Update and Delete Record server behaviors and Record Insertion Form Wizard already use the GetSQLValueString() function, so they just need to be refined to prevent SQL injections. The only changes needed are in the GetSQLValueString() function.

Here is the original code. The line that will be changed is highlighted in yellow:

if (!function_exists("GetSQLValueString")) { function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")    {$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;   switch ($theType) {     case "text":       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";       break;      case "long":     case "int":       $theValue = ($theValue != "") ? intval($theValue) : "NULL";       break;     case "double":       $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";       break;     case "date":       $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";       break;     case "defined":       $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;       break;   }   return $theValue; } }

Original code:

$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

Modified secured code:

$theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue; $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

Here are the steps to fix the Log In User server behavior:

Create a GetSQLValueString() function as described in Recordset with a filter above.

Modify the code that constructs the login SQL query.

Original code:

$LoginRS__query=sprintf("SELECT username, password FROM login WHERE username='%s' AND password='%s'",    get_magic_quotes_gpc() ? $loginUsername : addslashes($loginUsername), get_magic_quotes_gpc() ? $password : addslashes($password));

Modified secured code:

$LoginRS__query=sprintf("SELECT username, password FROM login WHERE username=%s AND password=%s",    GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text"));

The Check New User server behavior requires that an Insert Record server behavior be added to the page first. The GetSQLValuesString() function is included with the Insert Record server behavior. All that needs to be done is to update the GetSQLValuesString() to better handle SQL Injection and update the Check New User server behavior to use this function when a parameter is passed in.

Change the first line in the function to update the GetSQLValuesString() function as described in Update the definition of the variable $theValue.

Modify the code to construct the login SQL query to use the GetSQLValuesString() to pass in parameters. Towards the top of the page, locate the code in the section starting with // *** Redirect if username exists.

Original code:

$LoginRS__query = "SELECT username FROM login WHERE username='" . $loginUsername . "'"

Modified secured code:

$LoginRS__query = sprintf("SELECT username FROM login WHERE username=%s", GetSQLValueString($loginUsername, "text"));

For the Master Detail Page Set application object, the changes are mainly in the recordset that Dreamweaver creates for the Detail Page. If you don't have a filtered recordset in the Master Page, then no changes need to be made to the Master Page. If you have a filtered recordset, then see recordset with a filter to update the recordset code.

Dreamweaver creates a filtered recordset in the Detail Page, so that recordset code needs to be updated to use a GetSQLValueString() function to pass in parameters and sprintf() to construct the SQL string.

Create the GetSQLValueString() function as described in Recordset with a filter above.

Update the variable declaration code and construct the SQL query using the GetSQLValuesString() function.

Original code:

$recordID = $_GET['recordID']; $query_DetailRS1 = "SELECT * FROM albums WHERE ID = $recordID";

Modified secured code:

$colname_DetailRS1 = "-1"; if (isset($_GET['recordID'])) { $colname_DetailRS1 = (get_magic_quotes_gpc()) ? $_GET['recordID'] : addslashes($_GET['recordID']); } $query_DetailRS1 = sprintf("SELECT * FROM albums WHERE ID = %s", GetSQLValueString($colname_DetailRS1, "int"));

Update the definition of the variable $theValue as described in Insert, Update, and Delete Record server behaviors and Record Insertion Form Wizard.

Update the code for the Recordset which is required for the Record Update Form as described in step 2 of Recordset with a filter.

Please refer to the following article for more information about SQL injection: Wikipedia article on SQL injection.

Adobe Security Bulletin: APSB 06-07 Dreamweaver Server Behavior SQL Injection vulnerability.

Protecting PHP server behaviors from SQL injection vulnerability

Products Affected

Issue

Solution

Recordset with a filter

Insert, Update, and Delete Record server behaviors and Record Insertion Form Wizard

Update the definition of the variable $theValue:

Log In User server behavior

Check New User server behavior

Master Detail Page Set

Record Update Form Wizard

Additional Information


1	2	3	4	5
Hated it		It was ok		Loved it