The SQL Subcommands of dbCGI

Contents

Introduction

The SQL subcommands are used to perform actions against the database. They are presented here in the order they are typically used in the file.

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:

<sql subcommand> name1=value1 name2=value2 . . namen=valuen </sql> The name=value pairs must be delimited by new lines. White space is significant throughout the line - so a space before the '=' will be part of the name, and a space at the end of the line will be part of the value. The names are also case sensitive.

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.

Validation - <sql valarg n> and <sql valform formvar>

n is the argument number to validate, starting at 1, and formvar is the name of a form variable received. Note that form variables must be received by the POST method.

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:

Example: allow argument 1 to contain visible characters, space and tab, but reject the characters '$', '%' and '&'. Display the message "The value value is not a valid address", under the heading 'Invalid value' <sql valarg 1> CLASS=TABBEDTEXT FORBIDDEN=$%& FORMAT=<H1>Invalid Value</H1>The value %v is not a valid address </sql>

Initialising the DBMS - <sql init>

The init subcommand performs any initialisation required by the DBMS without connecting to any actual data. The valid names available and their meanings vary depending on the DBMS in use, however MAXBLOB is always valid and specifies how much memory should be allocated for each long text or BLOB field.

You must use the init subcommand before you can use the connect subcommand.

Connecting to a database - <sql connect conn_id>

conn_id is a connection ID you assign to the new connection. This connection ID is used in subsequent query, execute and disconnect subcommands to identify the connection to be used.

You must use init before you can use connect. Like init, the names valid for connect vary depending on the database.

Formatting Error Output - <sql error>

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.

<sql error> <H2>SQL Error</H2> <STRONG>%c</STRONG><BR> %e (Error number %n)<BR> </sql>

Formatting Headings - <sql headings>

The headings subcommand specifies a format to be output after the execution of a successful query, but before any data has been output. This allows you to have text that is only output if the query is successful, and to include the column names in that output.

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.

<sql headings> <H1>The results of the query are as follows</H1> %1h, %2h, %3h<BR> </sql>

Formatting Results - <sql format>

The format subcommand allows you to specify the format for each output record of a query

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.

<sql format> %[!1:<H1>%1d</H1>%] %2d %3(:%) %3d<BR> </sql>

Executing an SQL command - <sql execute conn_id>

conn_id is a connection ID used in a previous connect subcommand.

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:

<sql execute conn1> UPDATE customer SET credit_limit = %3a WHERE first_name = '%['%1a%] AND last_name = '%['%2a%] </sql>

Executing an SQL query - <sql query conn_id>

conn_id is a connection ID used in a previous connect subcommand.

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:

<sql execute conn1> SELECT first_name, last_name, address, credit_limit, phone_number FROM customer WHERE customer_no = %1a </sql>

Disconnecting from a database - <sql disconnect conn_id>

conn_id is a connection ID used in a previous connect subcommand.

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:

<sql disconnect conn1> </sql> Example for Progress: <sql disconnect conn1> DATABASE=demodb </sql>

Uninitialising the DBMS - <sql uninit>

The uninit command performs any final shutdown of communications with the DBMS. This is usually the last subcommand used. It recognises no names, so its contents should be left empty.

Example:

<sql uninit> </sql>