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.