Thunderstone Support Forums

I'm trying to build a query string with the $$rank value in the select statement but I'm getting an error when trying to feed this string to as sql statement.

<sum "%s" "select id,$$rank Rank from tbl1 where desc likep 'test'" $sortstr>
<$sqlstr=$ret>

<sql $sqlstr></sql>

Here is the error:
<!-- 015 /vortex/search:384: Needed parameters not supplied in the function execntexis -->

Is there a special escape character for the '$' character?

You need to escape the dollar sign twice: once for the parameter to <sum>, and again when passed to <SQL>. You're actually passing "... $rank ..." to <SQL>, which would be the parameter $rank, which is missing. Vortex versions after July 1998 can take multiple arguments which will be concatenated automatically, so the <sum> is actually not needed. (Also, parameters should be used instead of literal strings in SQL for security and speed, eg. $query instead of 'test'.):

<$query = "test"> <!-- or from user input form -->
<SQL "select id, $$rank Rank from tbl1 where desc likep $query" $sortstr></SQL>

$$rank is escaped, $query is a parameter, and $sortstr is part of the statement. $sortstr should *only* be set by the script, not come from a user input form in any way, or your SQL may be altered.

If you were to still use the <sum>:

<$query = "test">
<sum "%s" "select id, $$$$rank Rank from tbl1 where desc likep $$query" $sortstr>
<$sqlstr = $ret>
<SQL $sqlstr></SQL>

Note the double-escapement of $rank, and the single-escapement of $query.