Passing Ampersand and Dot to PostGreSQL - MySQL
Web Admin Himalayan Academy
katir at hindu.org
Fri Nov 4 21:38:20 EDT 2011
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
>>
>>
>>
>>
>>
>>
>>
>>
>>
More information about the use-livecode
mailing list