Note that the SQL subcommands, including the letters "sql" are case sensitive, unlike HTML which is not case sensitive.
Unless specified otherwise, the % substitutions are performed on the contents of the SQL subcommand before the contents are examined, and SQL subcommands are of the form:
Different DBMSs may recognise different names in each of the SQL subcommands. If a name is used in a subcommand which is not recognised by the DBMS in use, the name will be silently ignored.
The valarg subcommand tests the validity of the listed argument, and the valform subcommand tests the validity of the listed form variable.
The most significant use of these subcommands is to ensure that malicious users don't put in unexpected SQL syntax to cause the SQL to divulge sensitive information or to change the wrong information. They can be used anywhere in a dbCGI file, and they cause the remainder of the file to be ignored if the validation fails.
Formatting escapes are not interpreted within the validation subcommands, except within the FORMAT name, where they are interpreted when the message is displayed.
The argument validation names are:
The CLASS name specifies a basic datatype that the value being validated must adhere to. Class names are case sensitive. Unrecognised class names are ignored. There are three classes:
The numeric class may have a leading "+" or "-", a series of digits, a ".", and a second series of digits. Any part may be missing, so all of the following are valid:
Tabbedtext is like plaintext, but the horizontal tab character is permitted.
The MAXCHARS name specifies the maximum number of characters permitted in the value.
Example:
The MINCHARS name specifies the minimum number of characters required in the value.
Example:
The FORBIDDEN name presents a list of characters which should cause the value to be rejected if any one of the characters appears in the value. You can use normal text or the C escapes '\n' (the new line character), '\r' (the carriage return character), '\b' (The backspace character), '\t' (the tab character), '\\' (The backslash character), and '\nnn' where nnn is a three digit octal number representing the value of the character. The following example rejects the letters a, b, c, the new line character and the escape character (character 27, or 033 in octal).
The RANGE name is the reverse of the FORBIDDEN name. Thus "RANGE=abc\n\033" would cause anything containing characters other than the letters a, b, c, the new line character and the escape character to be rejected.
The FORMAT name specifies how the error message that should be displayed if an value fails validation. If the string "%v" appears in this value, it will be replaced with the actual value that failed validation. If this name is omitted, a default is used.
You must use the init subcommand before you can use the connect subcommand.
All names are taken to be the name of an environment variable to set. It is not possible to give a complete list of valid environment variables here, because different versions of Informix can require different environment variables, but the following example is for Informix Online version 4:
All names are taken to be the name of an environment variable to set.
Example:
ODBC does not recognise any names in the init section.
The names ORACLE_HOME, ORACLE_SID and TWO_TASK are valid, and correspond the the environment variables of the same names:
The following names are valid for Progress:
Example:
The only name recognised for Sybase is SYBASE, which specifies the location of the Sybase directory (the directory containing the interfaces file.
Example:
You must use init before you can use connect. Like init, the names valid for connect vary depending on the database.
The only name recognised for Informix is DATABASE, which gives the name of the database to connect to. Note that informix can only have one active connection at a time - you must use disconnect before a second use of connect.
Example:
The only name recognised for Ingres is DATABASE, which gives the name of the database to connect to. Note that Ingres can only have one active connection at a time - you must use disconnect before a second use of connect.
Example:
ODBC recognises the following names:
Example:
Oracle recognises the following names:
Non SQL*NET example:
Progress recognises only one name - CONNSTR, which specifies the connection string (arguments to the Progress CONNECT command). You must assign a logical database name in the connection string. In the example below, the logical database name is demodb.
Example:
Sybase recognises the following names:
Example:
The error subcommand allows you to specify the format for error output. All error messages generated after this subcommand is processed will be displayed in the new format. If this subcommand is not used, a default format will be used.
The % substitutions are not performed on the contents of the error subcommand until the error is produced. Typically you will use %e, %c and %n to include the error text, the SQL command and the error number causing the error.
The error subcommand does not have names - its contents are used "as is" for the formatting of errors.
Example: display the errors under the heading 'SQL Error' with the SQL command in strong text, followed by the error text, and the error number.
The % substitutions are not performed on the contents of the headings subcommand until the headings are produced.
The headings subcommand affects all query subcommands until the next headings subcommand.
Example: Display the heading 'The results of the query are as follows', followed by the names of the first, second and third columns separated by commas.
The % substitutions are not performed on the contents of the format subcommand until the output records are produced.
The format subcommand affects all query subcommands until the next format subcommand.
Example: Display the first three columns from a query, with the first column being presented as a heading, but only if it has changed since the previous record, and display a colon between the second and third column if the value of the third column is not null.
The execute subcommand is used to submit an SQL command which is not expected to return any records, such as SET, UPDATE, INSERT, DELETE, or CREATE TABLE - in fact almost anything except SELECT and some stored procedures.
If you use any arguments or form values you should be sure to validate them before executing the SQL. For example, in the SQL below, you would need to ensure that argument 3 was in the numeric class, as the implications of argument 3 being, say '1000, bank_account = 1234567, amount_overpaid = 10000000' may be dangerous.
Example:
The query subcommand is used to submit an SQL command which is expected to return records, such as SELECT and some stored procedures. If the query is successfully executed, the headings will be output once, followed by the format for each record resulting from the query.
If you use any arguments or form values you should be sure to validate them before executing the SQL. For example, in the SQL below, you would need to ensure that argument 1 was in the numeric class, or the user could see details for all customers by setting argument 1 to '1 OR 2<>1'
Example:
The disconnect subcommand is used to disconnect from a database which was connected by a previous connect subcommand. After the disconnect subcommand you can no longer issue execute and query subcommands against the named connection ID.
Only Progress recognises a name in the disconnect subcommand - DATABASE, which specifies the logical database name to disconnect.
Example for everything but Progress:
Example: