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, preserving their type. 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 (aka "SQL injection"),
e.g. a variable argument value like "
; DROP TABLE customer"
won't end the SQL command and get executed (but see notes under SQL
Command Construction, here).
Parameters that are multi-value variables may be converted into
another type (e.g. an array of
varchar values converted into a
strlst), depending on the current <sqlcp arrayconvert>
To simplify construction of complex
WHERE clauses, parameter
variables can be automatically dropped from a SQL
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
<$xval = "any">
"This is a test."
"So is this."
<$null = "any">
<SQL "SELECT result
WHERE X = $xval AND Y = $yval">
Since the SQL parameter
$xval has one value that matches
$null, it is dropped from the SQL query and the
clause becomes equivalent to "
WHERE Y = $yval". Unset
parameter variables are treated as empty strings ("") when comparing
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 (e.g. 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
This is to help prevent inadvertent deletion of too many rows. For
DELETE statement whose
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-
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.