Administration and Programming Guide for OS/400

Macro Development Techniques

Net.Data provides several mechanisms that allow users to assign values to input variables. To ensure that macros execute in the manner intended, these input variables should be validated by the macro. Your database and application should also be designed to limit a user's access to the data that the user is authorized to see.

Use the following development techniques when writing your Net.Data macros. These techniques will help you ensure that your applications execute as intended and that access to data is limited to properly authorized users.

Ensure that Net.Data variables cannot be overridden in a URL

The setting of Net.Data variables by a user within a URL overrides the effect of DEFINE statements used to initialize variables in a macro. This might alter the manner in which your macro executes. To safeguard against this possibility, initialize your Net.Data variables using the DTW_ASSIGN() function.

Example: Instead of using %DEFINE SHOWSQL="NO" to set the Net.Data SHOWSQL variable, use @DTW_ASSIGN(SHOWSQL, "NO"). Then, a query string assignment such as SHOWSQL=YES does not override the macro setting.

You can disable the display of SQL statements in production environments using one of the following methods:

• When using versions of Net.Data that support the DTW_SHOWSQL configuration variable, use this variable in the Net.Data initialization file to override the effect of setting SHOWSQL within your Net.Data macros. See DTW_SHOWSQL: Enable or Disable SHOWSQL Configuration Variable for syntax and additional information.
• Use the DTW_ASSIGN() function as described in the above example, to assign the value of SHOWSQL to prevent it from being overridden.

See SHOWSQL in the variables chapter of Net.Data Reference for syntax and examples for the SHOWSQL Net.Data variable.

You can also use DTW_ASSIGN to ensure that other Net.Data variables, such as RPT_MAX_ROWS or START_ROW_NUM, are not overridden. See the variables chapter of Net.Data Reference for more information about these variables.

Validate that your SQL statements cannot be modified in ways that alter the intended behavior of your application

Adding a Net.Data variable to an SQL statement within a macro allows users to dynamically alter the SQL statement before executing it. It is the responsibility of the macro writer to validate user-provided input values and ensure that an SQL statement containing a variable reference is not being modified in an unexpected manner. Your Net.Data application should validate user-provided input values from the URL so the Net.Data application can reject invalid input. Your validation design process should include for the following steps:

1. Identify the syntax of valid input; for example, a customer ID must start with a letter and can contain only alphanumeric characters.
2. Determine what potential harm can be caused by allowing incorrect input, intentionally harmful input, or input entered to gain access to internal assets of the Net.Data application.
3. Include input verification statements in the macro that meet the needs of the application. Such verification depends on the syntax of the input and how it is used. In simpler cases it can be enough to check for invalid content in the input or to invoke Net.Data to verify the type of the input. If the syntax of the input is more complex, the macro developer might have to parse the input partially or completely to verify whether it is valid.

Example 1: Using the DTW_POS() string function to verify SQL statements

%FUNCTION(DTW_SQL) query1() {  		
   select * from shopper where shlogid = '$(shlogid)'              
%}

The value of the shlogid variable is intended to be a shopper ID. Its purpose is to limit the rows returned by the SELECT statement to rows that contain information about the shopper identified by the shopper ID. However, if the string "smith' or shlogid<>'smith" is passed as the value of the variable shlogid, the query becomes:

select * from shopper where shlogid = 'smith' or shlogid<>'smith'

This user-modified version of the original SQL SELECT statement returns the entire shopper table.

The Net.Data string functions can be used to verify that the SQL statement is not modified by the user in inappropriate ways. For example, the following logic can be used to ensure that the input value associated with the shlogid variable consists of a single shopper ID:

@DTW_POS(" ", $(shlogid), result)
%IF (result == "0")
  @query1()
%ELSE
  %{ perform some sort of error processing %}
%ENDIF

Example 2: Using DTW_TRANSLATE()

Suppose that your application needs to validate that the value provided in the input variable number_of_orders is an integer. One way of accomplishing this is to create a translation table input_translation_table that contains all keyboard characters except the numeric characters 0-9 and to use the DTW_TRANSLATE and DTW_POS string functions to validate the input:

@DTW_TRANSLATE(number_of_orders, "x", input_translation_table, "x", string_out) 
      
 @DTW_POS("x", string_out, result)
 
     %IF (result = "0")
 
       %{ continue with normal processing %}
 
     %ELSE
 
       %{ perform some sort of error processing %}
 
     %ENDIF

Note that SQL statements within stored procedures cannot be modified by users at Web browsers and that user-provided input parameter values are constrained by the SQL data types associated with the input parameters. In situations where it is impractical to validate user input values using the Net.Data string functions, you can use stored procedures.

Ensure that a file name in an INCLUDE statement is not modified in ways that alter the intended behavior of your application

If you specify the value for the file name with an INCLUDE statement using a Net.Data variable, then the file to be included is not determined until the INCLUDE file is executed. If your intent is to set the value of this variable within your macro, but to not allow a user at the browser to override the macro-provided value, then you should set the value of the variable using DTW_ASSIGN instead of DEFINE. If you do intend to permit the user at a browser to provide a value for the file name, then your macro should validate the value provided.

Example: A query string assignment such as filename="../../x" can result in the inclusion of a file from a directory not normally specified in the INCLUDE_PATH configuration statement. Suppose that your Net.Data initialization file contains the following path configuration statement:

INCLUDE_PATH /usr/lpp/netdata/include

and that your Net.Data macro contains the following INCLUDE statement:

%INCLUDE "$(filename)"

A query string assignment of filename="../../x" would include the file /usr/lpp/x , which was not intended by the INCLUDE_PATH configuration statement specification.

The Net.Data string functions can be used to verify that the file name provided is appropriate for the application. For example, the following logic can be used to ensure that the input value associated with the file name variable does not contain the string "..":

@DTW_POS("..", $(filename), result)
%IF (result > "0")
  %{ perform some sort of error processing %}
%ELSE
%{ continue with normal processing %}
%ENDIF 

Design your database and queries so that user requests do not have access to sensitive data about other users

Some database designs collect sensitive user data in a single table. Unless SQL SELECT requests are qualified in some fashion, this approach may make all of the sensitive data available to any user at a web browser.

Example: The following SQL statement returns order information for an order identified by the variable order_rn:

select setsstatcode, setsfailtype, mestname
	from   merchant, setstatus
	where  merfnbr   = setsmenbr
	and    setsornbr = $(order_rn)

This method permits users at a browser to specify random order numbers and possibly obtain sensitive information about the orders of other customers. One way to safeguard against this type of exposure is to make the following changes:

• Add a column to the order information table that identifies the customer associated with the order information within a specific row.
• Modify the SQL SELECT statement to ensure that the SELECT is qualified by an authenticated customer ID provided by the user at the browser.

For example, if shlogid is the column containing the customer ID associated with the order, and SESSION_ID is a Net.Data variable that contains the authenticated ID of the user at the browser, then you can replace the previous SELECT statement with the following statement:

select setsstatcode, setsfailtype, mestname
  from   merchant, setstatus
  where  merfnbr   = setsmenbr
  and    setsornbr = $(order_rn)
  and    shlogid   = $(SESSION_ID)

Use Net.Data hidden variables

You can use Net.Data hidden variables to conceal various characteristics of your Net.Data macro from users that view your HTML source with their Web browser. For example, you can hide the internal structure of your database. See Hidden Variables for more information about hidden variables.

Request validation information from a user

You can create your own protection scheme based on user-provided input. For example, you can request validation information from a user through an HTML form and validate it using data that your Net.Data macro retrieves from a database or by calling an external program from a function defined in your Net.Data macro.

For more information on protecting your assets, see the Internet security list of frequently asked questions (FAQ) at this Web site:

http://www.w3.org/Security/Faq