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.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)
try:
self.cur.execute(iq1)
con1.commit()
self.components.TextArea2.appendText('Update ok\n')
except:
self.components.TextArea2.appendText('Problem during update command\n')
self.components.TextArea2.appendText(iq1)


(hope the intendation did not get too messed up )

Thanks

 

[Duncan Booth]

iq1="update MSGTALK set msgdate='NOW',subject='%s',talktext='%s' where
msgno= %d " % (mysubject,mytalktext,mymsgno)
try:
self.cur.execute(iq1)

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

iq1="update MSGTALK set msgdate='NOW',subject=%s,talktext=%s where
msgno= %d "
try:
self.cur.execute(iq1, (mysubject,mytalktext,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]

DarkBlue> Following is a code snippet from a pythoncard app
DarkBlue> the problem is with the sql string called iq1
DarkBlue> If either mysubject or mytalktext contains an
DarkBlue> apostrophe the update fails :
DarkBlue> Example: mysubject="Let's Eat" this fails
DarkBlue> mysubject="Lets Eat" this works fine

DarkBlue> What options do I have to avoid this issue but still can use
DarkBlue> apostrophes 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.execute("update MSGTALK"
" set msgdate='NOW', subject=%s, talktext=%s"
" where msgno=%s",
(self.components.TextField1.text,
self.components.TextArea1.text,
self.myamsgno))

Skip

 

[Paul Boddie]

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',subject='%s',talktext='%s' where
msgno= %d " % (mysubject,mytalktext,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',subject=%s,talktext=%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.execute(iq1, [mysubject,mytalktext,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.paramstyle). 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]

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

 

[DarkBlue]

...

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

 

[Dennis Lee Bieber]

Thank you all for prompt suggestions

I am using firebird 1.5.3 with kinterbasdb

If you haven't found it yet...
'qmark'
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/