String substitution VS proper mysql escaping

Discussion in 'Python' started by Íßêïò, Aug 18, 2010.

  1. Íßêïò

    Íßêïò Guest

    ===============================
    cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page =
    '%s' ORDER BY date DESC ''' % (page) )
    ===============================

    Someone told me NOT to do string substitution ("%") on SQL statements
    and to let MySQLdb do it
    for me, with proper escaping like the following

    ===============================
    cursor.execute('''SELECT host, hits, date FROM visitors WHERE page=%s
    ORDER BY date DESC''', (page,))
    ===============================

    The difference is that if some external source can control "page",
    and
    they put in a value like
    100 ; DELETE FROM visitors; SELECT * FROM visitors
    i will be losing my database table data.


    a) I wanted to ask what is proper escaping mean and why after variable
    page syntax has a comma

    and as i have the script now

    why don't my code as i have it now for string reproduction

    ===============================
    http://webville.gr/index.html?page="100 ; DELETE FROM visitors; SELECT
    * FROM visitors "
    ===============================

    don't reproduce the problem of actual deleting my data. I don't care
    losing it!

    I just want to see that happening with my own eyes!
     
    Íßêïò, Aug 18, 2010
    #1
    1. Advertising

  2. On 17Aug2010 20:15, Îίκος <> wrote:
    | ===============================
    | cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page =
    | '%s' ORDER BY date DESC ''' % (page) )
    | ===============================
    |
    | Someone told me NOT to do string substitution ("%") on SQL statements
    | and to let MySQLdb do it
    | for me, with proper escaping like the following
    |
    | ===============================
    | cursor.execute('''SELECT host, hits, date FROM visitors WHERE page=%s
    | ORDER BY date DESC''', (page,))
    | ===============================
    |
    | The difference is that if some external source can control "page",
    | and
    | they put in a value like
    | 100 ; DELETE FROM visitors; SELECT * FROM visitors
    | i will be losing my database table data.

    That other difference is that the mysql dialect support knows how to
    correctly escape a string for insertion into an SQL statement. You may
    not, or may forget to pre-escape the string, etc. Using the MySQLdb
    stuff do it for you is reliable and robust.

    | a) I wanted to ask what is proper escaping mean and why after variable
    | page syntax has a comma

    Because this:

    (page)

    means the same thing as:

    page

    i.e. the argument to the "%" operator is just the string in page.

    This:

    (page,)

    is a _tuple_ containing a single element, the page variable.
    A bit like:

    [page]

    which is a list containing a single element. The trailing comma is
    needed to tell python you want to use a tuple, not the bare string.

    The "%" operator has special knowledge that is it is passed as string instead
    of a list or tuple or other sequence then it should act _as_ _if_ it had been
    passed a single element tuple containing the string.

    Otherwise, because a string _is_ a sequence the "%" might want to treat
    the string "foo" as the sequence:

    ("f", "o", "o")

    Run these three loops to see the difference:

    for s in "foo":
    print s
    for s in ("foo"):
    print s
    for s in ("foo",):
    print s

    Cheers,
    --
    Cameron Simpson <> DoD#743
    http://www.cskk.ezoshosting.com/cs/

    I couldn't think of anything else to do with it, so I put it on the web.
     
    Cameron Simpson, Aug 18, 2010
    #2
    1. Advertising

  3. Íßêïò

    Nik Gr Guest

    Στις 18/8/2010 7:31 πμ, ο/η Cameron Simpson έγÏαψε:
    > On 17Aug2010 20:15, Îίκος<> wrote:
    > | ===============================
    > | cursor.execute( ''' SELECT host, hits, date FROM visitors WHERE page =
    > | '%s' ORDER BY date DESC ''' % (page) )
    > | ===============================
    > |
    > | Someone told me NOT to do string substitution ("%") on SQL statements
    > | and to let MySQLdb do it
    > | for me, with proper escaping like the following
    > |
    > | ===============================
    > | cursor.execute('''SELECT host, hits, date FROM visitors WHERE page=%s
    > | ORDER BY date DESC''', (page,))
    > | ===============================
    > |
    > | The difference is that if some external source can control "page",
    > | and
    > | they put in a value like
    > | 100 ; DELETE FROM visitors; SELECT * FROM visitors
    > | i will be losing my database table data.
    >
    > That other difference is that the mysql dialect support knows how to
    > correctly escape a string for insertion into an SQL statement. You may
    > not, or may forget to pre-escape the string, etc. Using the MySQLdb
    > stuff do it for you is reliable and robust.


    Can you please tell me what escaping means by giving me an example of
    what is escaped and whats isn't?

    Also hwo can i delete my data for testing purposes as?

    http://webville.gr/index.html?page="100 ; DELETE FROM visitors; SELECT *
    FROM visitors"

    I get an error...
    > | a) I wanted to ask what is proper escaping mean and why after variable
    > | page syntax has a comma
    >
    > Because this:
    >
    > (page)
    >
    > means the same thing as:
    >
    > page
    >
    > i.e. the argument to the "%" operator is just the string in page.
    >
    > This:
    >
    > (page,)
    >
    > is a _tuple_ containing a single element, the page variable.
    > A bit like:
    >
    > [page]
    >
    > which is a list containing a single element. The trailing comma is
    > needed to tell python you want to use a tuple, not the bare string.
    >
    > The "%" operator has special knowledge that is it is passed as string instead
    > of a list or tuple or other sequence then it should act _as_ _if_ it had been
    > passed a single element tuple containing the string.
    >

    %s and %d is behaving the same due to % expecting a string instead of an
    integer?

    > Otherwise, because a string _is_ a sequence the "%" might want to treat
    > the string "foo" as the sequence:
    >
    > ("f", "o", "o")

    cursor.execute('''SELECT host, hits, date FROM visitors WHERE page=%s
    ORDER BY date DESC''', page)

    But it alss might treat it an entity, i mean since 'page' is a variable containing a string why not just 'page' as it is expecting 'page' variable to give its value when asked?


    > Run these three loops to see the difference:
    >
    > for s in "foo":
    > print s
    > for s in ("foo"):
    > print s
    > for s in ("foo",):
    > print s
    >
    > Cheers,
    >>> for s in "nikos":

    print s


    n
    i
    k
    o
    s

    # this handles the string "nikos" as a series of chars right?

    >>> for s in ("nikos"):

    print s


    n
    i
    k
    o
    s

    # this handles the string "nikos" as a series of chars too but what si
    the difference with the above in htis with the parentheses? is "nikos"
    is handles still as string here?

    >>> for s in ("nikos",):

    print s


    nikos

    # Here yes it handles "nikos" as the 1st item of a tuple

    nikos
    >>> for s in ["nikos"]:

    print s


    nikos

    # Here? why is it behaving fifferent than the above ("nikos") and is
    proccessign it all chars in one?

    >>> for s in ["nikos",]:

    print s


    nikos

    # Here it handles "nikos" as the 1st item of a list right?
     
    Nik Gr, Aug 18, 2010
    #3
    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. Amy G

    escaping % in a string???

    Amy G, Feb 27, 2004, in forum: Python
    Replies:
    3
    Views:
    4,286
    Duncan Booth
    Feb 27, 2004
  2. Grant Olson

    Safe string escaping?

    Grant Olson, Mar 8, 2005, in forum: Python
    Replies:
    2
    Views:
    421
    Bengt Richter
    Mar 8, 2005
  3. Cameron Simpson
    Replies:
    10
    Views:
    584
  4. Nik Gr
    Replies:
    21
    Views:
    682
    Dennis Lee Bieber
    Aug 31, 2010
  5. sunckell

    Proper Technique for DBD::mysql install

    sunckell, Apr 7, 2009, in forum: Perl Misc
    Replies:
    0
    Views:
    144
    sunckell
    Apr 7, 2009
Loading...

Share This Page