|
The right way
Variables embedded in a <SQL>
statement become parameters
to the SQL statement. SQL parameters are allowed wherever a string
literal (in single quotes) would be used in "traditional" SQL. In
our patent example, we
embed the parameters $query
and $when
, which we
obtained from the user via the form:
<SQL ROW SKIP=$skip MAX=10
"select pcountry, pnumber, pdate, id
from patent
where pabstract like $query and pdate >= $when
order by pdate asc">
|
It is important to note that embedded parameters are not merely
concatenated into the statement string, but are atomically passed as
data. This means that it is safe to pass any values into a
parameter, without having to quote or escape quotes. So we could
even pass binary data - an entire GIF image for example - directly
into a table via <SQL>
.
The wrong way
If vanilla string concatenation were used, as some other scripting
environments require, the query would have to be a single-quoted SQL
literal. But a security hole would exist. For example, the
following is the wrong way to do the above statement:
<SQL ROW SKIP=$skip MAX=10
"select pcountry, pnumber, pdate, id
from patent
where pabstract like '" $query "' and pdate >= '" $when "'
order by pdate asc">
|
Here the SQL statement is concatenated from 5 arguments.
(Multiple <SQL>
arguments are concatenated to form the
statement.) Watch the quotes carefully: $query
and $when
are not inside a double-quoted <SQL>
argument here. This means they're part of the statement syntax, not
parameters, so we need to single-quote them in SQL (hence the
single-quote chars).
But what if a user enters a query with a single quote? Our
wrong-way statement would have a syntax error. What's worse, a
malicious user could try to alter the syntax of our SQL, by entering
a "date" for $when
such as:
today'; drop table patent;
|
Sure, we could manually escape the single-quotes, but why bother,
when the correct (first) statement at the top is easier, faster, and
safer: No matter what's entered for $query
and $when
, our statement syntax is unaffected.
When concatenation is needed
Because of the safety of embedded SQL parameters, it is
strongly encouraged that they be used instead of the
wrong-way "cut and paste" method. No data from the user should ever
be directly part of a SQL statement. The concatenation shown in the
second example is only used when the SQL statement must be altered
on the fly, but when a parameter is not allowed in standard SQL
syntax. For example, let's say we needed to change the table name
on the fly from the form:
<SWITCH $table>
<CASE "patent">
<CASE "papers">
<DEFAULT>
Bad table name!
<exit>
</SWITCH>
<SQL ROW SKIP=$skip MAX=10
"select pcountry, pnumber, pdate, id
from " $table "
where pabstract like $query and pdate >= $when
order by pdate asc">
|
Here the table name is taken from the variable $table
.
Since table names cannot be string literals and therefore
parameters, it is concatenated straight into the statement. Thus,
first it is checked for safety: anything other than the two proper
table names is unsafe and an error (<exit>
exits the
Vortex script: a quick way to bail on error). Since $query
and $when
are still parameters - embedded in the string -
we don't have to check them for safety.
|