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 thefile.

Note that the SQL subcommands, including the letters"sql"are case sensitive, unlike HTML which is not case sensitive.

Unless specified otherwise, the % substitutionsare performed on the contents of the SQL subcommand before the contentsare 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. Whitespace is significant throughout the line - so a space before the'=' will be part of the name, and a space at the end of theline will be part of the value. The names are also case sensitive.

Different DBMSs may recognise different names in each of the SQLsubcommands. If a name is used in a subcommand which is not recognisedby 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 at1, and formvar is the name of a form variablereceived. Note that form variables must be received by the POST method.

The valarg subcommand tests the validity of the listedargument, and the valform subcommand tests the validityof the listed form variable.

The most significant use of these subcommands is to ensure that malicioususers don't put in unexpected SQL syntax to cause the SQL to divulgesensitive information or to change the wrong information. They can be usedanywhere in a dbCGI file, and they cause the remainder of the file to beignored if the validation fails.

Formatting escapes are not interpreted within the validation subcommands,except within the FORMAT name, where they are interpreted when the messageis 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 valuevalue 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 requiredby the DBMS without connecting to any actual data. The valid namesavailable and their meanings vary depending on the DBMS in use, howeverMAXBLOB is always valid and specifies how much memoryshould be allocated for each long text or BLOB field.

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

Connecting to a database - <sql connect conn_id>

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

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

Formatting Error Output - <sql error>

The error subcommand allows you to specify the formatfor error output. All error messages generated after this subcommand isprocessed will be displayed in the new format. If this subcommand is notused, a default format will be used.

The % substitutions are not performed on the contentsof the error subcommand until the error is produced.Typically you will use %e, %c and%n to include the error text, the SQL command andthe error number causing the error.

The error subcommand does not have names - its contentsare 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, andthe 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 outputafter the execution of a successful query, but beforeany data has been output. This allows you to have text that is onlyoutput if the query is successful, and to include the column names inthat output.

The % substitutions are not performed on the contentsof the headings subcommand until the headings areproduced.

The headings subcommand affects all querysubcommands until the next headings subcommand.

Example: Display the heading 'The results of the query are asfollows', followed by the names of the first, second and thirdcolumns 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 foreach output record of a query

The % substitutions are not performed on the contentsof the format subcommand until the output records areproduced.

The format subcommand affects all querysubcommands until the next format subcommand.

Example: Display the first three columns from a query, with the first columnbeing presented as a heading, but only if it has changed since the previousrecord, and display a colon between the second and third column if the valueof 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 connectsubcommand.

The execute subcommand is used to submit an SQL commandwhich is not expected to return any records, such as SET, UPDATE, INSERT,DELETE, or CREATE TABLE - in fact almost anything except SELECT and somestored procedures.

If you use any arguments or form values you should be sure to validate thembefore executing the SQL. For example, in the SQL below, you would need toensure that argument 3 was in the numeric class, as the implications ofargument 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 connectsubcommand.

The query subcommand is used to submit an SQL commandwhich is expected to return records, such as SELECT and some storedprocedures. If the query is successfully executed, the headingswill be output once, followed by the format for eachrecord resulting from the query.

If you use any arguments or form values you should be sure to validate thembefore executing the SQL. For example, in the SQL below, you would need toensure that argument 1 was in the numeric class, or the user could see detailsfor 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 connectsubcommand.

The disconnect subcommand is used to disconnect from adatabase which was connected by a previous connectsubcommand. After the disconnect subcommand you can no longerissue execute and query subcommands againstthe named connection ID.

Only Progress recognises a name in the disconnectsubcommand - DATABASE, which specifies the logicaldatabase 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 ofcommunications 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>