baffling sql string

[John Machin]

DarkBlue wrote:
> Following is a code snippet from a pythoncard app
> the problem is with the sql string called iq1
> If either mysubject or mytalktext contains an
> apostrophe the update fails :
> Example: mysubject="Let's Eat"  this fails
>          mysubject="Lets Eat"   this works fine
>
> What options do I have to avoid this issue but still
> can use apostrophes in my input data ?
>
> mysubject=self.components.TextField1.text
> mytalktext=self.components.TextArea1.text
> mymsgno=self.myamsgno
> iq1="update MSGTALK set msgdate='NOW',subject='%s',talktext='%s' where
> msgno= %d " % (mysubject,mytalktext,mymsgno)

Your SQL after the % formatting will read something like this:
    update ... set ... subject='Let's Eat',talktext=....
which is illegal SQL syntax -- if a string constant contains an
apostrophe, it must be doubled:
     ... subject='Let''s Eat', ...
which would require you to do data.replace("'", "''") on each text
column. In general, % formatting is *not* a good idea, for this reason
and also because it leaves you wide open to an SQL injection attack. It
is much better to use the placeholder system, and let the called
software worry about inserting apostrophes, converting date formats,
etc etc. So:

iq1="""\
update MSGTALK
set msgdate='NOW', subject=?, talktext=?
where msgno= ?
"""

self.cur.execute(iq1, (mysubject,mytalktext,mymsgno))

Your [unspecified] DBMS adaptor may use some other placeholdet than
"?"; this will be documented in its manual .....

HTH,
John