Use Dynamic SQL parameters inside standard Recordsets in Dreamweaver 8.0.2 and CS3

• Address database security vulenability
• SQL Injections and prevention methods
• Use prepared statements
• Allow for dynamic parameters in standard Recordsets

Address database security vulnerability

To increase security in Dreamweaver 8.0.2 and CS3 we removed the ability to use dynamic SQL parameters inside standard Recordsets. The original code exposed databases to SQL injections. Using prepared statements for ASP_VBS and ColdFusion server models addressed the security vulnerability. It was necessary to add a function to the page when the first Recordset is applied and then call that function from all Recordsets on the page for PHP_MySQL. This technique is similar to the prepared statements of ASP_VBS and ColdFusion.

In the effort to close the security vulnerability, we have traded some flexibility for security. We want to give developers who already have or plan to develop extensions that will need to handle dynamic SQL queries;as well as end users who want to customize their recordsets to accept dynamic parameters;the ability to edit these custom Recordsets from within Dreamweaver.

SQL Injections and prevention methods

A SQL Injection is a technique that exploits a security vulnerability occurring in the database layer of an application. The vulnerability is present when user input is either incorrectly filtered for string literal escape characters embedded in SQL statements or user input is not strongly typed and thereby unexpectedly executed. It is in fact an instance of a more general class of vulnerabilities that can occur whenever one programming or scripting language is embedded inside another. Please visit Wikipedia for additional information.

Standard SQL Query example:

SELECT * FROM company_com WHERE name_com = '$companyName'

In the example above, the $companyName variable is read from a input form field, so the user have the total control of what value will be submitted. In the best case scenario, when the user enters "Adobe" (without the quotes) in the input corresponding to the company name, the given SQL query will look like this:

SELECT * FROM company_com WHERE name_com = 'Adobe'

But there are cases when users may want to break the web site and steal sensitive information and/or destroy the database. Considering the example above, a SQL injection will be easy achievable by entering the following string "blabla' OR '1'='1"(without the surrounding quotes). By giving this sequence of characters, the user will have access to all companies instead of a single company. The actual SQL that gets executed is:

SELECT * FROM company_com WHERE name_com = 'blabla' OR '1'='1'

Moreover, if the user wants to delete the entire company_com table, he/she may do this by simply passing the following string "x'; DROP TABLE company_com; --"(without the surrounding quotes). here is the complete SQL query:

SELECT * FROM company_com WHERE name_com = 'x'; DROP TABLE company_com; --'

Use one of the following measures to protect your website from SQL injections:

• Restrict input to a minimum set of allowed characters by automatically removing all other characters that falls outside of the given range.
• Escape all characters that can cause issues when used inside a SQL query (for example, single quotes).
• Use prepared statements.
• Use database access rights.
• Use stored procedures.

We recommend prepared statements which are described in more detail in Using prepared statements. Dreamweaver 8.0.2 and CS3 use the prepared statements approach for ASP_VBS, ASP_JavaScript, Cold Fusion and JSP server models, and the escape the user's input approach for the PHP_MySQL server model. Please refer to SQL Injection Attacks by Example for additional information on SQ injection attacks.

Use prepared statements

Dreamweaver 8.0.2 and CS use prepared statements since this solution guarantees us that the replacement of the actual SQL parameters with given values (possibly sent via URL) will be done on the server and not on the page. Prepared statements also guarantee that the value passed to the SQL itself has the appropriate data type and uses the appropriate escaping (if a parameter is said to be of type int, then the user will not be able to submit a letter, or if a parameter is of type text, then the entire passed string will be properly escaped and the user will not have the chance to perform a SQL injection).

Prepared statements for the ASP_VBS and ASP_JS server models

For ASP_VBS and ASP_JS, we rely on the ADODB database layer. The following example shows a simple ASP_VBS Recordset that uses a dynamic SQL parameter to filter results:

Prepared statements for the ColdFusion server model

ColdFusion offers built-in support for prepared statements. The following example shows a simple ColdFusion Recordset that uses a dynamic SQL parameter to filter results:

Simulated prepared statements for the PHP_MySQL server model

Prepared statements for PHP became available with MySQL 4.1, but because we wanted to support previous versions of MySQL as well, we decided to implement a custom function that does virtually the same thing. The following example shows a simple PHP Recordset that uses a dynamic SQL parameter to filter results:

Allow for dynamic parameters in standard Recordsets

So how can we protect against SQL injections while still allowing for dynamic parameters and keeping the resulting code editable via the Server Behaviors panel in Dreamweaver? The solution is to insert the dynamic parameters into the SQL query itself, rather than passing them as classic SQL parameters (which would either be escaped or appended to the SQL via prepared statements).

Important: Users cannot generate such code using the Objects and Server Behaviors that ship with Dreamweaver 8.0.2 or CS3 (Example: Inserting a Recordset via the Server Behaviors panel or Insert bar). This solution requires the user to edit the code manually, or to install a third-party extension that creates such code. The responsibility of writing secure code falls on the user or third party software developer.

The purpose of this method is to let experienced developers create dynamic SQL queries. Misuse of thise solution gives a hacker the possibility to get unwanted privileges and/or break the web pages and database. Please refer to SQL Injections and prevention methods to minimize the risk of SQL injections.

The main cases we identified this solution would be used are:

• Manually creating/updating a SQL query to accept dynamic parameters.
• Create/upgrade existing extensions to generate dynamic parameters.

Both use cases will be detailed in the following paragraphs.

Manually creating/updating a SQL query to accept dynamic parameters while keeping the Recordset still editable from within Dreamweaver

In the following example the user wants to add sorting functionality to a dynamic table. He decides to reload the page while passing the files and the sort direction to be used (ascending or descending) in the URL. A initial URL example is:

Hand-coding is required to accomplish this task in Dreamweaver using the Server Behaviors. The following code samples show the changes that must be made for each server model.

Important: Please note that none of the examples below have validation against SQL injections. Their role is pure educational and should be not used in production as they are. The examples are not bullet-proof to keep them as simple as possible. You must protect against SQL injections. For additional information please refer to SQL Injections and prevention methods.

ASP_VBS

Before:

After:

ColdFusion

Before:

After:

PHP_MySQL

Before:

After:

Create/upgrade existing extensions to generate dynamic parameters while keeping the Recordset still editable from within Dreamweaver

Now consider the same scenario as above, except that the user wants to create an extension that will generate the appropriate code rather than relying on a combination of built-in functionality and hand-editing. The new extension should generate the correct code for the ASP_VBS, ColdFusion, and PHP_MySQL server models.

Important: The following sections we will only focus on generating code that will use the values from the URL parameters to create a dynamic SQL query. The main focus is to generate the code so that it will be still recognized by Dreamweaver and will be editable from within standard Dreamweaver interfaces. This tutorial will not handle the validation of input data (via URL parameters) or protect the final SQL against any attempt of SQL injections because the complexity of such code falls outside of the scope of this tutorial. Developer is solely responsible to protect the final SQL against SQL injections.

We provide a demo extension which takes a connection and a table as input and generates a dynamic table displaying all records. It also updates the generated SQL to contain the orderBy variable as shown in the examples above. The extension was designed for Dreamweaver 8.0.2 and CS3. The generated code is not bullet proof, as the goal is to create Recordsets containing dynamic parameters that remain editable using standard Dreamweaver interfaces. The extension source code is located in User's Configuration folder.

In all of the code examples below, the highlighted sections were added to enable the sorting functionality via the URL parameters sortCol and sortDir.

ASP_VBS

The code that generates the appropriate SQL query for the ASP_VBS server model is located within "[USER_CONFIGURATION_FOLDER]/Commands/My Dynamic Table.js" file in the User Configuration Folder. The notable change is on line 130:

The participants file for the MyDynamicTable ASP_VBS server behavior includes an extra participant which adds the "orderBy" variable definition to the page:

ColdFusion

The same "[USER_CONFIGURATION_FOLDER]/Commands/My Dynamic Table.js" in the User Configuration Folder also contains the code for ColdFusion server model; the relevant change in this case is on line 138:

The participants file for the MyDynamicTable ColdFusion server behavior includes an extra participant which adds the "orderBy" variable definition to the page:

PHP_MySQL

The last server model in the extension is PHP_MySQL. The relevant change in the "[USER_CONFIGURATION_FOLDER]/Commands/My Dynamic Table.js" file in the User Configuration Folder is on line 122:

The participants file for the MyDynamicTable PHP_MySQL server behavior includes an extra participant which adds the "orderBy" variable definition to the page:

Pros

The method described above has several advantages, including:

• The more advanced developers can take advantage of the SQL injection vulnerability to construct more complex SQLs, dynamically.
• The Recordsets created will still be editable from within Dreamweaver.
• It's not difficult to integrate into existing Dreamweaver extensions that generate Recordsets with dynamic SQL parameters.
• The default code generated by Dreamweaver remains unchanged; developers who do not require dynamic queries (and their clients) can be assured that the code written by Dreamweaver is secure.

Cons

Since Dreamweaver was not designed by default to support this method, it also has some drawbacks, including:

• The code generated using the method described in this document is vulnerable to SQL injections and therefore needs to be protected in other ways. The list of such protection methods includes but is not limited to:
  • Ensure that the end user cannot use the SQL injection method to comprise the web page and/or database;
  • Escape the arguments before they reach the actual SQL;
  • Make sure that even if the user manages to break your code, he/she will not steal sensitive information and/or will not comprise the database.
• Two known issues that cannot be fixed until the next version of Dreamweaver is released:
  • ColdFusion: Simple Recordset UI removes the hashes around variable names from the SQL query when edited and then re-applied
  • ColdFusion, ASP_VBS, ASP_JS: Test button from Advanced Recordset UI is not working when SQL query contains variable names

Locating the User's Configuration folder

• Windows XP
  
  C:\Documents and Settings\[username]\Application Data\Adobe\Dreamweaver 9\Configuration

• Windows Vista
  
  C:\Users\[username]\AppData\Roaming\Adobe\Dreamweaver 9\Configuration

• Mac OS 10.4.x
  
  /Users/[username]/Library/Application\ Support/Adobe/Dreamweaver\ 9/Configuration