Parameter Substitution

Variables can be placed in the SQL command as parameters. These variables are not merely substituted as strings, but become distinct arguments to the Texis SQL command. Thus, no escapement of special SQL characters like ``''' or ``;'' needs to be done for parameters, and binary data such as images may be passed safely for byte fields. There is also no danger of the SQL command being modified by a rogue argument, eg. a variable argument value like ``; drop table customer'' won't end the SQL command and get executed (but see notes under SQL Command Construction, p. ).

To simplify construction of complex where clauses, variables can be automatically dropped from a SQL select query. Normally, all variables embedded in the SQL command become parameters; if a variable is unset (has no values) it's treated as a single empty string (``'') parameter. If the special variable $null is set, however, any single-value parameter that matches any value of $null is dropped from the query, and its part of the where clause collapses.

For example, in the following query $null is set to ``any'':

<$xval = "any">
<$yval =
  "This is a test."
  "So is this."
>
<$null = "any">
<SQL "select result
      from Text
      where X = $xval and Y = $yval">
  $result
</SQL>

Since the SQL parameter $xval has one value that matches $null, it is dropped from the SQL query and the where clause becomes equivalent to ``where Y = $yval''. Unset parameter variables are treated as empty strings (``'') when comparing against $null.

This feature allows complicated queries to degenerate into simple ones when the extra variables are not needed, without cumbersome checking of all the parameters. A common use is in HTML forms, where there may be several search fields that are optional (eg. an option checkbox, or subject and author text fields). By setting $null to the empty string (<$null = "">), any unset checkboxes or empty (unfilled) text fields from the form can be implicitly dropped from the SQL query.

Note that this feature only applies to select clauses. This is to help prevent inadvertent deletion of too many rows. For example, a delete statement whose where clause parameters are accidentally left empty by the user might otherwise delete the entire table, if $null is ``''. Thus, any unwanted parameters must explicitly be left out in non-select statements. For security, the $null variable can only be set explicitly by a script; it is always initialized to no-values at script start regardless of form/environment/etc. variables.