Re: String substitution VS proper mysql escaping

Discussion in 'Python' started by Nik Gr, Aug 20, 2010.

  1. Nik Gr

    Nik Gr Guest

    Στις 20/8/2010 8:22 πμ, ο/η Cameron Simpson έγÏαψε:
    > [...snip...]
    > | Why does the page variable which is actually a string needs to be a
    > | tuple or a list and not just as a string which is what it actually
    > | is?
    >
    > With regard to the "%" operator, it considers the string on the left to
    > be a format string with multiple %blah things in it to replace. The
    > thing on the right is a sequence of items to place into the format
    > string.
    >

    I didn't undersatnd.

    > So the thing on the right is_supposed_ to
    > | I have a strong desire to use it like this:
    > | cursor.execute( '''SELECT hits FROM counters WHERE page = %s''' , page )
    > | opposed to tuple.
    >
    > Hmm. This isn't the python "%" format operator at all.
    > This is the database API's .execute() method.
    > If it expects its second argument to be a sequence of parameters
    > (which is does) then you need to supply a sequence of parameters.
    > It is that simple!
    >
    > In you usage above you're supplying "page" instead of "(page,)".
    > The latter matches the .execute() method's requirements.

    I don't follow either.
     
    Nik Gr, Aug 20, 2010
    #1
    1. Advertising

  2. On 20 ΑÏγ, 09:04, Nik Gr <> wrote:
    > With regard to the "%" operator, it considers the string on the left to
    > be a format string with multiple %blah things in it to replace. The
    > thing on the right is a sequence of items to place into the format
    > string.


    Can you please clarify what you mean by that?

    > In you usage above you're supplying "page" instead of "(page,)".
    > The latter matches the .execute() method's requirements.


    I tried it and "page" as a string and not a as a single element tuple
    works ok.
     
    Îίκος, Aug 28, 2010
    #2
    1. Advertising

  3. Nik Gr

    MRAB Guest

    On 28/08/2010 20:10, Îίκος wrote:
    > On 20 ΑÏγ, 09:04, Nik Gr<> wrote:
    >> With regard to the "%" operator, it considers the string on the left to
    >> be a format string with multiple %blah things in it to replace. The
    >> thing on the right is a sequence of items to place into the format
    >> string.

    >
    > Can you please clarify what you mean by that?
    >

    Basically:

    format_string % (item_1, item_2, item_3)

    >> In you usage above you're supplying "page" instead of "(page,)".
    >> The latter matches the .execute() method's requirements.

    >
    > I tried it and "page" as a string and not a as a single element tuple
    > works ok.


    Although the .execute() method might accept a single string:

    cursor.execute(sql_query, page)

    as well as a tuple containing the string:

    cursor.execute(sql_query, (page, ))

    try to be consistent. As I said before:

    """When there's more than one value you provide a tuple. It's makes sense
    from the point of view of consistency that you also provide a tuple when
    there's only one value."""
     
    MRAB, Aug 28, 2010
    #3
  4. On 28 ΑÏγ, 22:35, MRAB <> wrote:
    > On 28/08/2010 20:10, Îίκος wrote:> On 20 ΑÏγ, 09:04, Nik Gr<>  wrote:
    > >> With regard to the "%" operator, it considers the string on the left to
    > >> be a format string with multiple %blah things in it to replace. The
    > >> thing on the right is a sequence of items to place into the format
    > >> string.

    >
    > > Can you please clarify what you mean by that?

    >
    > Basically:
    >
    >      format_string % (item_1, item_2, item_3)


    I still don't follow by means that i dotn see the point here...

    >
    > >> In you usage above you're supplying "page" instead of "(page,)".
    > >> The latter matches the .execute() method's requirements.

    >
    > > I tried it and "page" as a string and not a as a single element tuple
    > > works ok.

    >
    > Although the .execute() method might accept a single string:
    >
    >      cursor.execute(sql_query, page)
    >
    > as well as a tuple containing the string:
    >
    >      cursor.execute(sql_query, (page, ))
    >
    > try to be consistent. As I said before:
    >
    > """When there's more than one value you provide a tuple. It's makes sense
    > from the point of view of consistency that you also provide a tuple when
    > there's only one value."""


    cursor.execute(sql_query, (page, ))

    is different than?

    cursor.execute(sql_query, page, )

    ?

    ===========================
    Why in mysql string substitution example i have to use page='%s' and
    in the comma way(automatic mysql convertion i dont need the single
    quotes and use it as page=%s ?
    What is the diff?
    ===========================
     
    Îίκος, Aug 28, 2010
    #4
  5. On 28 ΑÏγ, 22:35, MRAB <> wrote:

    > """When there's more than one value you provide a tuple. It's makes sense
    > from the point of view of consistency that you also provide a tuple when
    > there's only one value."""


    Can you write something that make use of more than one value?


    Perhaps you mena somethign like?

    cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
    = %s and host = %s''' , (page,) )

    Is this what you mean?

    All those special format strign identifiers will grab their values out
    of the tuple?
     
    Îίκος, Aug 28, 2010
    #5
  6. 2010/8/29 Îίκος <>:
    > On 28 ΑÏγ, 22:35, MRAB <> wrote:
    >
    >> """When there's more than one value you provide a tuple. It's makes sense
    >> from the point of view of consistency that you also provide a tuple when
    >> there's only one value."""

    >
    > Can you write something that make use of more than one value?
    >
    >
    > Perhaps you mena somethign like?
    >
    > cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
    > = %s and host = %s''' , (page,) )
    >
    > Is this what you mean?
    >
    > All those special format strign identifiers will grab their values out
    > of the tuple?


    Yes, that's exactly right -- they'll try to grab values out of the
    tuple, and since in that particular code snippet the tuple doesn't
    contain enough items, you'll get an error :)

    HTH,
    Rami

    --
    Rami Chowdhury
    "Never assume malice when stupidity will suffice." -- Hanlon's Razor
    408-597-7068 (US) / 07875-841-046 (UK) / 0189-245544 (BD)
     
    Rami Chowdhury, Aug 28, 2010
    #6
  7. Nik Gr

    MRAB Guest

    On 28/08/2010 20:48, Îίκος wrote:
    > On 28 ΑÏγ, 22:35, MRAB<> wrote:
    >> On 28/08/2010 20:10, Îίκος wrote:> On 20 ΑÏγ, 09:04, Nik Gr<> wrote:
    >>>> With regard to the "%" operator, it considers the string on the left to
    >>>> be a format string with multiple %blah things in it to replace. The
    >>>> thing on the right is a sequence of items to place into the format
    >>>> string.

    >>
    >>> Can you please clarify what you mean by that?

    >>
    >> Basically:
    >>
    >> format_string % (item_1, item_2, item_3)

    >
    > I still don't follow by means that i dotn see the point here...
    >
    >>
    >>>> In you usage above you're supplying "page" instead of "(page,)".
    >>>> The latter matches the .execute() method's requirements.

    >>
    >>> I tried it and "page" as a string and not a as a single element tuple
    >>> works ok.

    >>
    >> Although the .execute() method might accept a single string:
    >>
    >> cursor.execute(sql_query, page)
    >>
    >> as well as a tuple containing the string:
    >>
    >> cursor.execute(sql_query, (page, ))
    >>
    >> try to be consistent. As I said before:
    >>
    >> """When there's more than one value you provide a tuple. It's makes sense
    >> from the point of view of consistency that you also provide a tuple when
    >> there's only one value."""

    >
    > cursor.execute(sql_query, (page, ))
    >
    > is different than?
    >
    > cursor.execute(sql_query, page, )
    >
    > ?
    >

    Yes.

    The first has 2 arguments: a string and a tuple containing the value of
    'page'.

    The second has 2 arguments: a string and the value of 'page'.

    > ===========================
    > Why in mysql string substitution example i have to use page='%s' and
    > in the comma way(automatic mysql convertion i dont need the single
    > quotes and use it as page=%s ?
    > What is the diff?
    > ===========================


    In the first case you're doing the substitution yourself, but you might
    not get it right, leaving your website open an SQL injection attacks.

    In the second case you're letting the .execute method do the
    substitution. It will have been written to do it correctly and safely.
     
    MRAB, Aug 28, 2010
    #7
  8. Nik Gr

    MRAB Guest

    On 28/08/2010 20:51, Îίκος wrote:
    > On 28 ΑÏγ, 22:35, MRAB<> wrote:
    >
    >> """When there's more than one value you provide a tuple. It's makes sense
    >> from the point of view of consistency that you also provide a tuple when
    >> there's only one value."""

    >
    > Can you write something that make use of more than one value?
    >
    >
    > Perhaps you mena somethign like?
    >
    > cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
    > = %s and host = %s''' , (page,) )
    >
    > Is this what you mean?
    >
    > All those special format strign identifiers will grab their values out
    > of the tuple?


    Your example contains 3 placeholders, so it needs 3 values:

    cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    date = %s and host = %s''', (page, date, host))

    This will be safe. Any quoting that's needed will be done by .execute().
     
    MRAB, Aug 28, 2010
    #8
  9. On 28 ΑÏγ, 23:12, MRAB <> wrote:
    > On 28/08/2010 20:51, Îίκος wrote:
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > > On 28 ΑÏγ, 22:35, MRAB<>  wrote:

    >
    > >> """When there's more than one value you provide a tuple. It's makes sense
    > >> from the point of view of consistency that you also provide a tuple when
    > >> there's only one value."""

    >
    > > Can you write something that make use of more than one value?

    >
    > > Perhaps you mena somethign like?

    >
    > > cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
    > > = %s and host = %s''' , (page,) )

    >
    > > Is this what you mean?

    >
    > > All those special format strign identifiers will grab their values out
    > > of the tuple?

    >
    > Your example contains 3 placeholders, so it needs 3 values:
    >
    >      cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s''', (page, date, host))
    >
    > This will be safe. Any quoting that's needed will be done by .execute().


    Will this also work without the parentheses?

    > cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s''', page, date, host)


    or python will not allow it cause it might think there are 4 args
    isntead of two?


    > cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    > date = '%s' and host = '%s' ''', (page, date, host))


    Whats happens if i attempt to also quote by single or double quoting
    the above although now i'm aware that .execute method does the quoting
    for me?
     
    Îίκος, Aug 29, 2010
    #9
  10. Nik Gr

    MRAB Guest

    On 29/08/2010 06:13, Îίκος wrote:
    > On 28 ΑÏγ, 23:12, MRAB<> wrote:
    >> On 28/08/2010 20:51, Îίκος wrote:
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>
    >>> On 28 ΑÏγ, 22:35, MRAB<> wrote:

    >>
    >>>> """When there's more than one value you provide a tuple. It's makes sense
    >>>> from the point of view of consistency that you also provide a tuple when
    >>>> there's only one value."""

    >>
    >>> Can you write something that make use of more than one value?

    >>
    >>> Perhaps you mena somethign like?

    >>
    >>> cursor.execute( '''SELECT hits FROM counters WHERE page = %s and date
    >>> = %s and host = %s''' , (page,) )

    >>
    >>> Is this what you mean?

    >>
    >>> All those special format strign identifiers will grab their values out
    >>> of the tuple?

    >>
    >> Your example contains 3 placeholders, so it needs 3 values:
    >>
    >> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    >> date = %s and host = %s''', (page, date, host))
    >>
    >> This will be safe. Any quoting that's needed will be done by .execute().

    >
    > Will this also work without the parentheses?
    >

    Have you tried it?

    I did. It didn't like it!

    It likes the values to be in a tuple. If there's one value, that's a
    1-tuple: (page, ).

    >> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    >> date = %s and host = %s''', page, date, host)

    >
    > or python will not allow it cause it might think there are 4 args
    > isntead of two?
    >

    Not Python (the language) as such, but the method. As I said, it
    expects the value(s) to be in a tuple.
    >
    >> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    >> date = '%s' and host = '%s' ''', (page, date, host))

    >
    > Whats happens if i attempt to also quote by single or double quoting
    > the above although now i'm aware that .execute method does the quoting
    > for me?


    The method will put in any quoting that's needed. If you also put in
    quotes then that'll result in 2 sets of quoting, one inside the other
    (or something like that).

    Why make more work for yourself? Let the method do it for you, safely
    and correctly!
     
    MRAB, Aug 29, 2010
    #10
  11. On 29 ΑÏγ, 21:34, MRAB <> wrote:

    > It likes the values to be in a tuple. If there's one value, that's a
    > 1-tuple: (page, ).


    I noticed that if we are dealing with just a single value 'page' will
    do, no need to tuple for 1-value.
    it handles fine as a string.

    > >> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    > >> date = %s and host = %s''', page, date, host)

    >
    > > or python will not allow it cause it might think there are 4 args
    > > isntead of two?

    >
    > Not Python (the language) as such, but the method. As I said, it
    > expects the value(s) to be in a tuple.


    If i dont parenthesize the execute method instead of getting 2
    args(sql_query and tuple value) as it expects by deficition, it gets 4
    args instead and thats why it fails? I need to know why ti fails. Is
    that it?

    Also in here,

    page, date, host is 3 separate variable values here

    while

    (page, date, host) is 3 separate variables values also but withing a
    tuple. Is this correct?


    > >> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    > >> date = '%s' and host = '%s' ''', (page, date, host))

    >
    > > Whats happens if i attempt to also quote by single or double quoting
    > > the above although now i'm aware that .execute method does the quoting
    > > for me?

    >
    > The method will put in any quoting that's needed. If you also put in
    > quotes then that'll result in 2 sets of quoting, one inside the other
    > (or something like that).
    >
    > Why make more work for yourself? Let the method do it for you, safely
    > and correctly!


    I'am askign this because i'm tryong to see why

    On 29 ΑÏγ, 21:34, MRAB <> wrote:

    > It likes the values to be in a tuple. If there's one value, that's a
    > 1-tuple: (page, ).


    I noticed that if we are dealing with just a single value 'page' will
    do, no need to tuple for 1-value.
    it handles fine as a string.

    > >> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    > >> date = %s and host = %s''', page, date, host)

    >
    > > or python will not allow it cause it might think there are 4 args
    > > isntead of two?

    >
    > Not Python (the language) as such, but the method. As I said, it
    > expects the value(s) to be in a tuple.


    If i dont parenthesize the execute method instead of getting 2
    args(sql_query and tuple value) as it expects by deficition, it gets 4
    args instead and thats why it fails? I need to know why ti fails. Is
    that it?

    ========================
    Also in here,

    page, date, host is 3 separate variable values here

    while

    (page, date, host) is 3 separate variables values also but withing a
    tuple. Is this correct?


    =========================
    I'm asking this to see why

    cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    date = '%s' and host = '%s' ''' % (page, date, host) )

    does work, while same thign qithout the quotes

    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date
    = %s and host = %s ''' % (page, date, host) )

    doesn't. Dont know why but quotes somehopw confuse me both in strings
    and sql_queries as well when it comes to substitutions.
     
    Îίκος, Aug 30, 2010
    #11
  12. Nik Gr

    MRAB Guest

    On 30/08/2010 02:38, Îίκος wrote:
    > On 29 ΑÏγ, 21:34, MRAB<> wrote:
    >
    >> It likes the values to be in a tuple. If there's one value, that's a
    >> 1-tuple: (page, ).

    >
    > I noticed that if we are dealing with just a single value 'page' will
    > do, no need to tuple for 1-value.
    > it handles fine as a string.
    >

    I tried it with sqlite3, which it didn't like it. For consistency, and
    compatibility with other SQL engines, I recommend that you always
    provide a tuple.

    >>>> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    >>>> date = %s and host = %s''', page, date, host)

    >>
    >>> or python will not allow it cause it might think there are 4 args
    >>> isntead of two?

    >>
    >> Not Python (the language) as such, but the method. As I said, it
    >> expects the value(s) to be in a tuple.

    >
    > If i dont parenthesize the execute method instead of getting 2
    > args(sql_query and tuple value) as it expects by deficition, it gets 4
    > args instead and thats why it fails? I need to know why ti fails. Is
    > that it?
    >

    If the SQL query contains placeholder(s), the .execute method expects
    the value(s) to be provided in a tuple. It's as simple as that.

    > Also in here,
    >
    > page, date, host is 3 separate variable values here
    >
    > while
    >
    > (page, date, host) is 3 separate variables values also but withing a
    > tuple. Is this correct?
    >

    It doesn't care about the variables as such, only their values. You're
    putting the values into a tuple and then passing that tuple because
    that's what the method wants.
    >
    >>>> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    >>>> date = '%s' and host = '%s' ''', (page, date, host))

    >>
    >>> Whats happens if i attempt to also quote by single or double quoting
    >>> the above although now i'm aware that .execute method does the quoting
    >>> for me?

    >>
    >> The method will put in any quoting that's needed. If you also put in
    >> quotes then that'll result in 2 sets of quoting, one inside the other
    >> (or something like that).
    >>
    >> Why make more work for yourself? Let the method do it for you, safely
    >> and correctly!

    >
    > I'am askign this because i'm tryong to see why
    >
    > On 29 ΑÏγ, 21:34, MRAB<> wrote:
    >
    >> It likes the values to be in a tuple. If there's one value, that's a
    >> 1-tuple: (page, ).

    >
    > I noticed that if we are dealing with just a single value 'page' will
    > do, no need to tuple for 1-value.
    > it handles fine as a string.
    >

    As I've said, for consistency I recommend that you always provide a
    tuple because some SQL engines require it, and if you need to provide
    multiple values then you'll need to anyway.

    >>>> cursor.execute('''SELECT hits FROM counters WHERE page = %s and
    >>>> date = %s and host = %s''', page, date, host)

    >>
    >>> or python will not allow it cause it might think there are 4 args
    >>> isntead of two?

    >>
    >> Not Python (the language) as such, but the method. As I said, it
    >> expects the value(s) to be in a tuple.

    >
    > If i dont parenthesize the execute method instead of getting 2
    > args(sql_query and tuple value) as it expects by deficition, it gets 4
    > args instead and thats why it fails? I need to know why ti fails. Is
    > that it?
    >
    > ========================
    > Also in here,
    >
    > page, date, host is 3 separate variable values here
    >
    > while
    >
    > (page, date, host) is 3 separate variables values also but withing a
    > tuple. Is this correct?
    >
    >
    > =========================
    > I'm asking this to see why
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    > date = '%s' and host = '%s' ''' % (page, date, host) )
    >
    > does work, while same thign qithout the quotes
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date
    > = %s and host = %s ''' % (page, date, host) )
    >
    > doesn't. Dont know why but quotes somehopw confuse me both in strings
    > and sql_queries as well when it comes to substitutions.


    Don't quote the placeholders yourself. Let the method do it.
     
    MRAB, Aug 30, 2010
    #12
  13. On 30 ΑÏγ, 05:04, MRAB <> wrote:

    when iam trying to pass a tuple to the execute methos should i pass it
    like this?

    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    date = %s and host = %s ''' % (page, date, host) )


    or like

    tuple = (page, host, date)

    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    date = %s and host = %s ''' % (tuple) )


    Or is it the same thing?

    > > =========================
    > > I'm asking this to see why

    >
    > > cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    > > date = '%s' and host = '%s' ''' % (page, date, host) )

    >
    > > does work, while same thign qithout the quotes

    >
    > > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date
    > > = %s and host = %s ''' % (page, date, host) )

    >
    > > doesn't. Dont know why but quotes somehopw confuse me both in strings
    > > and sql_queries as well when it comes to substitutions.

    >
    > Don't quote the placeholders yourself. Let the method do it.


    No, iam taking substitution here not mysql escaping.

    Cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    date = '%s' and host = '%s' ''' % (page, date, host) )

    As it is above it works , with double quotes still works but if i
    leave it unquoted it doesn't.

    This is because without sigle or double quotes the the method doesn't
    know where a value begins and here it ends? That why it needs quoting?
     
    Nik the Greek, Aug 30, 2010
    #13
  14. Nik Gr

    MRAB Guest

    On 30/08/2010 03:33, Nik the Greek wrote:
    > On 30 ΑÏγ, 05:04, MRAB<> wrote:
    >
    > when iam trying to pass a tuple to the execute methos should i pass it
    > like this?
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' % (page, date, host) )
    >
    >
    > or like
    >
    > tuple = (page, host, date)
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' % (tuple) )
    >
    >
    > Or is it the same thing?
    >

    'tuple' is the name of a built-in. Don't use it.

    The first example is clearer.

    >>> =========================
    >>> I'm asking this to see why

    >>
    >>> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    >>> date = '%s' and host = '%s' ''' % (page, date, host) )

    >>
    >>> does work, while same thign qithout the quotes

    >>
    >>> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date
    >>> = %s and host = %s ''' % (page, date, host) )

    >>
    >>> doesn't. Dont know why but quotes somehopw confuse me both in strings
    >>> and sql_queries as well when it comes to substitutions.

    >>
    >> Don't quote the placeholders yourself. Let the method do it.

    >
    > No, iam taking substitution here not mysql escaping.
    >
    > Cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    > date = '%s' and host = '%s' ''' % (page, date, host) )
    >
    > As it is above it works , with double quotes still works but if i
    > leave it unquoted it doesn't.
    >
    > This is because without sigle or double quotes the the method doesn't
    > know where a value begins and here it ends? That why it needs quoting?


    Let the method do the substitution:

    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date =
    %s and host = %s ''', (page, date, host) )

    This is the best way.
     
    MRAB, Aug 30, 2010
    #14
  15. On 30 ΑÏγ, 05:48, MRAB <> wrote:
    > On 30/08/2010 03:33, Nik the Greek wrote:
    >
    >
    >
    >
    >
    >
    >
    > > On 30 ΑÏγ, 05:04, MRAB<>  wrote:

    >
    > > when iam trying to pass a tuple to the execute methos should i pass it
    > > like this?

    >
    > > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > > date = %s and host = %s ''' % (page, date, host) )

    >
    > > or like

    >
    > > tuple = (page, host, date)

    >
    > > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > > date = %s and host = %s ''' % (tuple) )

    >
    > > Or is it the same thing?

    >
    > 'tuple' is the name of a built-in. Don't use it.
    >
    > The first example is clearer.



    ok a_tuple = (page, hist, host)

    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    date = %s and host = %s ''' , a_tuple )

    would that syntax be correct? No need to enclose the tuple name inside
    parenthesis here right?


    > >>> =========================
    > >>> I'm asking this to see why

    >
    > >>> cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    > >>> date = '%s' and host = '%s' ''' % (page, date, host) )

    >
    > >>> does work, while same thign qithout the quotes

    >
    > >>> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date
    > >>> = %s and host = %s ''' % (page, date, host) )

    >
    > >>> doesn't. Dont know why but quotes somehopw confuse me both in strings
    > >>> and sql_queries as well when it comes to substitutions.

    >
    > >> Don't quote the placeholders yourself. Let the method do it.

    >
    > > No, iam taking substitution here not mysql escaping.

    >
    > > Cursor.execute(''' SELECT hits FROM counters WHERE page = '%s' and
    > > date = '%s' and host = '%s' ''' % (page, date, host) )

    >
    > > As it is above it works , with double quotes still works but if i
    > > leave it unquoted it doesn't.

    >
    > > This is because without sigle or double quotes the the method doesn't
    > > know where a value begins and here it ends? That why it needs quoting?

    >
    > Let the method do the substitution:
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date =
    > %s and host = %s ''', (page, date, host) )
    >
    > This is the best way.


    Yes i will i just asked to know if i were to substitute what might be
    the problem so to understand why i need the quoting.

    why not like that?

    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and date =
    > %s and host = %s ''' % (page, date, host) )
     
    Nik the Greek, Aug 30, 2010
    #15
  16. Nik the Greek wrote:

    > Yes i will i just asked to know if i were to substitute what might be
    > the problem so to understand why i need the quoting.


    Because if you use % to build a query string, the result must
    be syntactically valid SQL. The values that you substitute
    into the placeholders must end up looking like SQL literals.
    That means string values need to be in quotes, and probably
    dates as well, although numbers don't.

    When you use the execute method's own parameter substitution
    mechanism, things are different. It's not a textual replacement,
    and you don't put quotes around the placeholders. There's no
    particular reason for that, it's just the way it's defined
    to work.

    --
    Greg
     
    Gregory Ewing, Aug 30, 2010
    #16
  17. On 30 ΑÏγ, 11:11, Gregory Ewing <> wrote:
    > Nik the Greek wrote:
    > > Yes i will i just asked to know if i were to substitute what might be
    > > the problem so to understand why i need the quoting.

    >
    > Because if you use % to build a query string, the result must
    > be syntactically valid SQL. The values that you substitute
    > into the placeholders must end up looking like SQL literals.
    > That means string values need to be in quotes, and probably
    > dates as well, although numbers don't.
    >
    > When you use the execute method's own parameter substitution
    > mechanism, things are different. It's not a textual replacement,
    > and you don't put quotes around the placeholders. There's no
    > particular reason for that, it's just the way it's defined
    > to work.
    >
    > --
    > Greg


    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    date = %s and host = %s ''' , a_tuple )

    and

    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    date = %s and host = %s ''' , (a_tuple) )

    are both syntactically correct right?

    buw what about

    cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    date = %s and host = %s ''' , (a_tuple,) )
     
    Nik the Greek, Aug 30, 2010
    #17
  18. Nik the Greek wrote:

    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' , a_tuple )
    >
    > and
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' , (a_tuple) )
    >
    > are both syntactically correct right?
    >
    > buw what about
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' , (a_tuple,) )


    Python has a wonderful interactive mode which is perfect for trying
    this out:

    >>> a_tuple = 1,2,3
    >>> a_tuple

    (1, 2, 3)
    >>> (a_tuple)

    (1, 2, 3)
    >>> (a_tuple,)

    ((1, 2, 3),)
    >>>



    First note, that tuples are not created with parentheses, but with
    the comma. So, the first two are the same. The parens are only
    needed to remove ambiguity in certain situations, but are
    meaningless here.

    The third case is a tuple containing a_tuple as its only element.
     
    Alexander Kapps, Aug 30, 2010
    #18
  19. Nik Gr

    MRAB Guest

    On 30/08/2010 17:09, Nik the Greek wrote:
    > On 30 ΑÏγ, 11:11, Gregory Ewing<> wrote:
    >> Nik the Greek wrote:
    >>> Yes i will i just asked to know if i were to substitute what might be
    >>> the problem so to understand why i need the quoting.

    >>
    >> Because if you use % to build a query string, the result must
    >> be syntactically valid SQL. The values that you substitute
    >> into the placeholders must end up looking like SQL literals.
    >> That means string values need to be in quotes, and probably
    >> dates as well, although numbers don't.
    >>
    >> When you use the execute method's own parameter substitution
    >> mechanism, things are different. It's not a textual replacement,
    >> and you don't put quotes around the placeholders. There's no
    >> particular reason for that, it's just the way it's defined
    >> to work.
    >>
    >> --
    >> Greg

    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' , a_tuple )
    >
    > and
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' , (a_tuple) )
    >
    > are both syntactically correct right?
    >
    > buw what about
    >
    > cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    > date = %s and host = %s ''' , (a_tuple,) )


    That's syntactically correct, but not the same thing.
     
    MRAB, Aug 30, 2010
    #19
  20. Nik Gr

    MRAB Guest

    On 30/08/2010 17:34, Alexander Kapps wrote:
    > Nik the Greek wrote:
    >
    >> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    >> date = %s and host = %s ''' , a_tuple )
    >>
    >> and
    >>
    >> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    >> date = %s and host = %s ''' , (a_tuple) )
    >>
    >> are both syntactically correct right?
    >>
    >> buw what about
    >>
    >> cursor.execute(''' SELECT hits FROM counters WHERE page = %s and
    >> date = %s and host = %s ''' , (a_tuple,) )

    >
    > Python has a wonderful interactive mode which is perfect for trying this
    > out:
    >
    > >>> a_tuple = 1,2,3
    > >>> a_tuple

    > (1, 2, 3)
    > >>> (a_tuple)

    > (1, 2, 3)
    > >>> (a_tuple,)

    > ((1, 2, 3),)
    > >>>

    >
    >
    > First note, that tuples are not created with parentheses, but with the
    > comma. So, the first two are the same. The parens are only needed to
    > remove ambiguity in certain situations, but are meaningless here.
    >

    There's only one exception: the empty tuple ().

    > The third case is a tuple containing a_tuple as its only element.
     
    MRAB, Aug 30, 2010
    #20
    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,301
    Duncan Booth
    Feb 27, 2004
  2. Grant Olson

    Safe string escaping?

    Grant Olson, Mar 8, 2005, in forum: Python
    Replies:
    2
    Views:
    425
    Bengt Richter
    Mar 8, 2005
  3. Íßêïò
    Replies:
    2
    Views:
    493
    Nik Gr
    Aug 18, 2010
  4. Cameron Simpson
    Replies:
    10
    Views:
    588
  5. sunckell

    Proper Technique for DBD::mysql install

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

Share This Page