Re: Passing Ampersand and Dot to PostGreSQL - MySQL

Web Admin Himalayan Academy Fri, 04 Nov 2011 18:38:56 -0700

Andre showed the way: use LiveCode's native databinding methods insteadof my old fashioned replacement strings and the values are automaticallyescaped.


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



_______________________________________________
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

Re: Passing Ampersand and Dot to PostGreSQL - MySQL

Reply via email to