SQL Command Construction

The SQL command is normally a single string literal whose embedded variables become parameters. However, in some cases it is necessary to dynamically alter the command in ways not possible with $null. For example, the table may need to be set at run time, yet SQL syntax does not permit it to be a parameter.

The command can be set at run time by passing one or more variables or literals to SQL, instead of a single string literal. In this case, the variable(s) and literal(s) are concatenated together and become the command. Any variables embedded in this concatenated string then become the command parameters. Thus, an arbitrary SQL command can be constructed at run time by the Vortex script, yet still be protected from rogue arguments sent by users:

<A NAME=main>
  <FORM METHOD=post ACTION=$url/search.html>
    Search: <INPUT NAME=title> <BR>
    What: <SELECT NAME=what>
            <OPTION>Magazines
            <OPTION>Books
          </SELECT>
          <BR>
    <INPUT TYPE=submit>
  </FORM>
</A>

<A NAME=search>
  <IF $what eq "Magazines">
    <$tbl = "magazines">
  <ELSE>
    <$tbl = "books">
  </IF>
  <SQL "select Title from " $tbl " where Title like $title">
    $Title
  </SQL>
</A>

Note: For security, the resultant SQL command should not contain any value(s) that are not explicitly generated by the Vortex script itself. Any user-supplied values should be parameters ($title in this case) or otherwise checked first ($what), so that the command is still protected from rogue SQL in a variable.

In the above example, the command is the concatenation of the arguments to SQL. The first variable in the command, $tbl, will be part of the SQL command, rather than a SQL parameter, because it is not embedded in a string literal. Thus, we must ensure it does not contain rogue SQL code: the IF statement explicitly sets it to a known string. The second variable, $title, will be a SQL parameter, because it is embedded in a literal string. Thus, no matter what its value, it cannot affect the action taken by the SQL statement: it is safe to import directly from the user.