Passing Ampersand and Dot to PostGreSQL - MySQL

[G. Wolfgang Gaich]

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.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
>
>
>
>
>
>
>
>
>
>

  *
  * Englisch
  * Deutsch

  * Englisch
  * Deutsch

<javascript:void(0);>