Passing Ampersand and Dot to PostGreSQL - MySQL

[Sivakatirswami]

I'm having some difficulty with passing data from a web form to a 
PostGreSQL database. I'm using iRev on the server and gFormData has all 
the data in the array. After handling all the data, running the CC 
charges, sending notifications, everything works, but my final functions 
to insert the data into the database fail in certain cases.

i use this method to build the query:


put "INSERT INTO donations (first_name, last_name, anonymous, 
email_address, "   into  tSQLQuery
put "addr1, addr2, city, state, postal_code, country, phone_no, 
created_on, " after tSQLQuery
put " entry_point, comment, amount, monetra_user, monetra_msg, cc_no) " 
after tSQLQuery
put "VALUES ('#FIRST_NAME', '#LAST_NAME', '#ANONYMOUS', 
'#EMAIL_ADDRESS', "  after  tSQLQuery
Put "'#ADDR1', '#ADDR2', '#CITY', '#STATE', '#POSTAL_CODE', '#COUNTRY', 
'#PHONE_NO', '#CREATED_ON', " after tSQLQuery
Put "'#ENTRY_POINT', '#COMMENT', '#AMOUNT', '#MONETRA_USER', 
'#MONETRA_MSG', '#CC_NO');" after tSQLQuery

the poke it like this:

replace "#FIRST_NAME" with gFormData["first_name"]  in tSQLQuery
replace "#COMMENT" with gFormData["comment"]  in tSQLQuery
replace "#EMAIL_ADDRESS" with gFormData["email_address"]  in tSQLQuery

when the data in the array values contain certain characters, the 
insertion query fails.

I have been able to definitively diagnose at least two cases (there are 
probably more)

if the data contains an ampersand or a dot, the query fails  e.g these 
will all cause the insertion to fail:

      First Name:  Ravi & Sheela # ampersand

      Comment: All the books & audio.... [etc.]  # ampersand

      email address: gail.wood at verizon.com  # dot in the email address.

so, I'm not sure what to do.

  I need to escape all the special chars (I don't even have a list of 
what they are) in the gFormData array values that will break the SQL 
query. And we also have to block SQL injection attempts at the same time....

My "baby xTalk"  method would be tortuous: for each key/value in the 
gFormData array

put gFormData["first_name"]  in tFirstName

put fixBadChars(tFirstName) into tFirstName

replace "#FIRST_NAME" with tFirstName  in tSQLQuery

function fixBadChars pString

     replace "&" with "and" in pString
       # and more of these which I don't even know what they would be
       # and replacing the dot in the email will mean the email is wrong 
after insertion
     return pString

end fixBadChars

I'm sure this has been dealt with already... Does anyone have a "escape 
input data for SQL insertion"   library they can share?

Thanks!
Sivakatirswami










-- 
Om Shanti
Sivakatirswami

Kauai Aadheenam