baffling sql string

[DarkBlue]

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.Text Field1.text
mytalktext=self .components.Tex tArea1.text
mymsgno=self.my amsgno
iq1="update MSGTALK set msgdate='NOW',s ubject='%s',tal ktext='%s' where
msgno= %d " % (mysubject,myta lktext,mymsgno)
try:
self.cur.execut e(iq1)
con1.commit()
self.components .TextArea2.appe ndText('Update ok\n')
except:
self.components .TextArea2.appe ndText('Problem during update command\n')
self.components .TextArea2.appe ndText(iq1)
(hope the intendation did not get too messed up )

Thanks

[Duncan Booth]

DarkBlue <no****@nixmail .comwrote:

iq1="update MSGTALK set msgdate='NOW',s ubject='%s',tal ktext='%s' where
msgno= %d " % (mysubject,myta lktext,mymsgno)
try:
self.cur.execut e(iq1)

Use parameterised queries and get rid of the quotes in the SQL:

iq1="update MSGTALK set msgdate='NOW',s ubject=%s,talkt ext=%s where
msgno= %d "
try:
self.cur.execut e(iq1, (mysubject,myta lktext,mymsgno) )
....

depending on your actual database you might need to use something other
than %s to specify the parameters. Check out 'paramstyle' for your database
connection.

[skip]

DarkBlueFollowi ng is a code snippet from a pythoncard app
DarkBluethe problem is with the sql string called iq1
DarkBlueIf either mysubject or mytalktext contains an
DarkBlueapostro phe the update fails :
DarkBlueExample : mysubject="Let' s Eat" this fails
DarkBlue mysubject="Lets Eat" this works fine

DarkBlueWhat options do I have to avoid this issue but still can use
DarkBlueapostro phes in my input data ?

You don't mention what database adapter you're communicating with, but they
all have argument quoting facilities. You should be using them instead of
doing the string interpolation yourself.

For example, if I was using MySQL, my code might look something like this:

self.cur.execut e("update MSGTALK"
" set msgdate='NOW', subject=%s, talktext=%s"
" where msgno=%s",
(self.component s.TextField1.te xt,
self.components .TextArea1.text ,
self.myamsgno))

Skip

[Paul Boddie]

DarkBlue wrote:

>
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 ?

Use proper "bind parameters" or "bind variables" when executing the
statement, rather than using string substitution/interpolation.

[...]

iq1="update MSGTALK set msgdate='NOW',s ubject='%s',tal ktext='%s' where
msgno= %d " % (mysubject,myta lktext,mymsgno)

This merely "edits" a statement, stuffing values directly into the
text. As you've noticed, merely substituting values for placeholders
isn't always going to work. Moreover, it's something that could cause
errors (in the nicest circumstances such as in a local application) or
security holes (in nasty circumstances such as in networked or Web
applications).

Instead try this:

iq1="update MSGTALK set msgdate='NOW',s ubject=%s,talkt ext=%s" \
" where msgno=%d" # note that the parameters are not substituted here

Then execute the statement with the parameters as a separate argument:

self.cur.execut e(iq1, [mysubject,mytal ktext,mymsgno])

The database module will then ensure that the values you've supplied
really will get used properly when executing the statement, and things
like apostrophes won't cause any problems at all.

Paul

P.S. The above assumes that the "parameter style" of the database
module is "format", as found by looking at the paramstyle attribute of
the module (eg. MySQLdb.paramst yle). Other styles include "qmark" where
you use "?" instead of "%s" (or other format codes) to indicate where
your values will be used in a statement.

P.P.S. See also the DB-API 2.0 specification:
http://www.python.org/dev/peps/pep-0249/

[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.Text Field1.text
mytalktext=self .components.Tex tArea1.text
mymsgno=self.my amsgno
iq1="update MSGTALK set msgdate='NOW',s ubject='%s',tal ktext='%s' where
msgno= %d " % (mysubject,myta lktext,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.execut e(iq1, (mysubject,myta lktext,mymsgno) )

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

HTH,
John

[DarkBlue]

Duncan Booth wrote:

...

depending on your actual database you might need to use something other
than %s to specify the parameters. Check out 'paramstyle' for your
database connection.

Thank you all for prompt suggestions

I am using firebird 1.5.3 with kinterbasdb

Db