I had someone point out to me that some of the sample code I have used might be vulnerable to an insertion attack. So I feel it is time to address this subject.
First when using examples I try to just focus on the subject of the current blog and often strip a lot of things that are irrelevant to the point I am trying to make out. But now to the subject of keeping your code save.
If you write and SQL statement looking something like
lc_sql = “SELECT * from customer WHERE custno = ‘” + lc_custno + “‘”
Someone could enter “‘; TRUNCATE TABLE customer;’” resulting in a SQL statement looking like
SELECT * from customer WHERE custno = ”; TRUNCATE TABLE customer; ”
Which could be quite disasterous. So to avoid this we could use parameterized queries which I cannot as I am using a seperate function to wrap around the SQLEXEC() function to do all kinds of things like connection maintenance, Error handling, Logging and even in some cases sending an email to the IT when certain conditions arise. So the above mentioned problem needs to be addressed when creating the sql statement.
The first line of protection is to properly escape any string I send. The table below is copied from the MYSQL manual showing what needs escaping
So I wrote a vfp function called addslashes()
LPARAMETERS lc_str, ll_notrim
lc_str = STRTRAN(lc_str,”\”, “\\”)
lc_str = STRTRAN(lc_str,”‘”, “\’”)
lc_str = STRTRAN(lc_str,’”‘, ‘\”‘)
lc_str = STRTRAN(lc_str,’%', ‘\%’)
lc_str = STRTRAN(lc_str,’_', ‘\_’)
lc_str = STRTRAN(lc_str,CHR(0), ‘\0′)
lc_str = STRTRAN(lc_str,CHR(10), ‘\n’)
lc_str = STRTRAN(lc_str,CHR(13), ‘\r’)
lc_str = STRTRAN(lc_str,CHR(26), ‘\z’)
lc_str = STRTRAN(lc_str,CHR(9), ‘\t’)
lc_str = STRTRAN(lc_str,CHR(8), ‘\b’)
IF !ll_notrim THEN
lc_str = RTRIM(lc_Str)
I could Have used a FOR … NEXT Loop with a CASE statement and loop through the string but found that that was slower than calling the STRTRAN() function multiple times. So now we run every string through this routine and the above SQL statement becomes
lc_sql = “SELECT * FROM customer WHERE custno = ‘” + ADDSLASHES(lc_custno) + “‘”
And the above attempt to mess with the system will produce an sql now looking like
SELECT * from customer WHERE custno = ‘\’; TRUNCATE TABLE customer; \”
Which would just return an empty record set.
This will also allow me now to insert and retrieve string like “John’s Barber Shop” or multi line entries. Furthermore I made it a rule to emulate strong typing when it comes to SQL statements. Simply to to prevent a “Garbage in”/”Garbage out” Scenario. So Each textget has its validation routine and is bound to a local cursor. VFP allows you to change the type of a variable by simple assigning some different type of data to it but fields in a cursor are of a fixed type. This also allows us to use functions to build the SQL statements based on the underlying cursor. So every string and Memofield gets treated with ADDSLASHES() each numeric with STR(), Each date field WITH DTOS() to make a MYSQL valid date string etc.
At this point some might say it looks like a lot of work and some of this is replicating what VFP does through remote views. So why use SQL Pass through and not remote views? The single aswer is performance. This way I can control what to send when and how and avoid data being sent unnecessarily or at inoportune times.