baffling sql string

Discussion in 'Python' started by DarkBlue, Sep 27, 2006.

  1. DarkBlue

    DarkBlue Guest

    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
     
    DarkBlue, Sep 27, 2006
    #1
    1. Advertising

  2. DarkBlue

    Duncan Booth Guest

    DarkBlue <> wrote:

    > 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.
     
    Duncan Booth, Sep 27, 2006
    #2
    1. Advertising

  3. DarkBlue

    Guest

    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
     
    , Sep 27, 2006
    #3
  4. DarkBlue

    Paul Boddie Guest

    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/
     
    Paul Boddie, Sep 27, 2006
    #4
  5. DarkBlue

    John Machin Guest

    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
     
    John Machin, Sep 27, 2006
    #5
  6. DarkBlue

    DarkBlue Guest

    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
     
    DarkBlue, Sep 27, 2006
    #6
  7. On Wed, 27 Sep 2006 20:53:10 +0800, DarkBlue <>
    declaimed the following in comp.lang.python:


    >
    > Thank you all for prompt suggestions
    >
    > I am using firebird 1.5.3 with kinterbasdb
    >

    If you haven't found it yet...

    >>> import kinterbasdb
    >>> kinterbasdb.paramstyle

    'qmark'
    --
    Wulfraed Dennis Lee Bieber KD6MOG

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Sep 27, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Baffling question

    Guest, Nov 8, 2003, in forum: ASP .Net
    Replies:
    4
    Views:
    359
    Guest
    Nov 9, 2003
  2. =?Utf-8?B?Q2hhcmxlc0E=?=

    simple but baffling javascript prob

    =?Utf-8?B?Q2hhcmxlc0E=?=, Mar 22, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    5,554
    Gozirra
    Mar 22, 2006
  3. =?Utf-8?B?Q2hhcmxlc0E=?=

    v easy but baffling to me CSS question

    =?Utf-8?B?Q2hhcmxlc0E=?=, Apr 5, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    1,421
    =?Utf-8?B?Q2hhcmxlc0E=?=
    Apr 5, 2006
  4. Replies:
    1
    Views:
    446
    John C. Bollinger
    May 26, 2005
  5. Swengtoo
    Replies:
    2
    Views:
    433
    tom_usenet
    Feb 6, 2004
Loading...

Share This Page