Passing Ampersand and Dot to PostGreSQL - MySQL

stephen barncard stephenREVOLUTION2 at barncard.com
Wed Nov 2 16:30:39 EDT 2011


Trevor does. There are escaping handlers in his old MySQL Abstraction
Library, if you have that around.

On 2 November 2011 13:25, 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>
>



-- 



Stephen Barncard
San Francisco Ca. USA

more about sqb  <http://www.google.com/profiles/sbarncar>



More information about the use-livecode mailing list