Rev CGI and SQL injection attacks
Dave Cragg
dcragg at lacscentre.co.uk
Thu Dec 14 07:09:03 EST 2006
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
More information about the use-livecode
mailing list