Passing Ampersand and Dot to PostGreSQL - MySQL

[Web Admin Himalayan Academy]

Andre showed the way: use LiveCode's native databinding methods instead 
of my old fashioned replacement strings and the values are automatically 
escaped.

On 11/2/11 11:44 AM, Pete wrote:
> Interesting.  I don't have Postgres but I tried an insert into an SQLite
> table with text containing an ampersand and it worked just fine.  Same with
> mySQL.  This was on my desktop machine though, not over the web.
>
> The docs for SQLite,mySQL, and Postgres only mention the need to escape the
> single-quote character within a string, not any other special characters.
>
> What's the error you get back?
>
> Pete
> Molly's Revenge<http://www.mollysrevenge.com>
>
>
>
>
> On Wed, Nov 2, 2011 at 1:25 PM, Sivakatirswami<katir at hindu.org>  wrote:
>
>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>