Passing Ampersand and Dot to PostGreSQL - MySQL

[Pete]

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
>
>
>
>
>
>
>
>
>
>
> --
> Om Shanti
> Sivakatirswami
>
> Kauai Aadheenam
>
> ______________________________**_________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/**mailman/listinfo/use-livecode<http://lists.runrev.com/mailman/listinfo/use-livecode>
>
>