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
NULL option is set, however,
any single-value parameter that matches any value of that option is
dropped from the query, and its part of the
For example, in the following query
NULL is set to
<$xval = "any">
"This is a test."
"So is this."
<SQL NULL="any" "SELECT result
WHERE X = $xval AND Y = $yval">
Since the SQL parameter xval has one value that matches the
NULL option, 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 the
Parameter-dropping allows complicated queries to collapse 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
clauses. This is to help prevent inadvertent deletion/modification 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