Issue
Some databases let you send multiple SQL statements in a single query. Therefore, there are potential security risks when you pass parameters in a query string to a dynamically generated database query. Hackers could 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.
Solution
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
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 needs 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 verifies 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; } } ?>
-
$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"));
Insert, Update, and Delete Record server behaviors and Record Insertion Form wizard
The Insert, Update, and Delete Record server behaviors and Record Insertion Form wizard already use the GetSQLValueString() function, so they need to be refined to prevent SQL injections. The only changes needed are in the GetSQLValueString() function.
Here is the original code. The line to change 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; } }
Update the definition of the variable $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);
Log In User server behavior
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"));
Check New User server behavior
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. Toward 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"));
Master Detail Page Set
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"));
Record Update Form wizard
-
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.
Additional Information
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.