Re: Passing Ampersand and Dot to PostGreSQL - MySQL

Wed, 02 Nov 2011 21:32:32 -0700 

Hello Sivakatirswami,

I think it's an unicode problem if your database is UTF8.

I use this function in that case to execute the SQL:

function exeSQL_UTF8 pSQL  -- pSQL is your SQL string
    global gDB
    local tRS
    local tSQL
   put unidecode(uniencode(pSQL),"UTF8") into tSQL
   put revdb_execute(gDB,tSQL) into tRS
   return tRS
end exeSQL_UTF8

Regards
Wolfgang



Am 02.11.2011 21:25, schrieb 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.w...@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












 *
 * Englisch
 * Deutsch

 * Englisch
 * Deutsch

<javascript:void(0);>
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Reply via email to