Rev CGI and SQL injection attacks

[Dave Cragg]

Tinfoil hat time. :-)

I'm putting together some rev CGIs that will take input from web  
broswer forms, and then populate/retrieve data from a MySQL database.  
I'm just a little worried about the ability for smart and nasty  
people to inject some SQL in the web form that will produce  
undesirable results. In this case, I won't eventually have  
responsibility for the web forms themselves, so I'd like the CGI  
scripts to be reasonably secure. Bank level security is not required.  
It's an online quiz site, and the worst someone can do is probably  
put themselves in the number 1 position in the score rankings.  
However, some personal data is involved so there are minor privacy  
issues too. Any advice on best practices would be appreciated.

My current thinking is that two steps are needed in the script to  
avoid problems.

1.  Use "variable substitution" when constructing the SQL queries.  
For example, if you parse out the passed data from the request into  
an array named sCGIData, you can do something like this:

   put word 1 of sCGIData["id"] into tValues[1]
   put word 1 of sCGIData["whatever"] into tValues[2]

   put "SELECT * from users WHERE id = :1 AND otherfield = :2" into  
tQuery
   put revdb_querylist(,,sDBID,tQuery,"tValues") into tData

I'm assuming that Rev's db handling will quote the values that get  
sent to MySQL. (I'm not sure how important this is. But it would be  
reassuring to know that Rev does things as you might expect.)

(I tend to use "word 1 of sCGIData["id"]" to eliminate any spaces  
from data. No good, of course, where spaces are valid in the input.)

2.  Escape any single quotes in the data.

So the above would now read:

   put esq(word 1 of sCGIData["id"]) into tValues[1]
   put esq(word 1 of sCGIData["whatever"]) into tValues[2]

  function esq pString
    replace "'" with "\'" in pString
    return pString
  function

Am I being naive in thinking this is enough? Any comments or  
suggestions welcome.

Cheers
Dave