baffling sql string

[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',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/