a question about mysqldb

[Bruno Desthuilliers]

Evan a écrit :
> a simple problem but I do not know why...:(, could anyone help me?
> 
> MySQLdb nominally uses just the %s placeholder style, in my script, i
> got error if you want to use placeholder(%s) for table name:

db-api placeholders won't work for table names - or for anything that 
isn't supposed to be a value FWIW. String args are quoted, so you end up 
with you sql looking like:

   select tID, tNote from 'tmp' where tID=1

instead of

   select tID, tNote from tmp where tID=1


You may want to try this instead:

tablename = "tmp"
sql = "select tID, tNote from %s where tID=%%s" % tablename
args = (1,)

s.dbptr.execute(sql, args)

> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>> str="select tID,tNote from %s where tID=1"       <-------- check here
>>>>
>>>> e=["tmp"]
> 
>>>> s.dbptr.execute(str,e)
> Traceback (most recent call last):
>   File "<stdin>", line 1, in ?
>   File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
> 166, in execute
>     self.errorhandler(self, exc, value)
>   File "/usr/lib/python2.4/site-packages/MySQLdb/connections.py", line
> 35, in defaulterrorhandler
>     raise errorclass, errorvalue
> _mysql_exceptions.ProgrammingError: (1064, "You have an error in your
> SQL syntax; check the manual that corresponds to your MySQL server
> version for the right syntax to use near ''tmp') where tID=1' at line
> 1")
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> 
> 
> But sql worked but the I got no query result:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>> str="select tID,tNote from tmp where %s = %s"      <----------check here
>>>> e=["tID",int(1)]

<ot>
- 1 is an int already, so make this e = ["tID", 1]
- str is a very bad choice for an identifier. It's not only 
uninformative, but it will also shadow the builtin str type
</ot>

>>>> s.dbptr.execute(str,e)
> 0L    <------------------ check here
>>>> s.dbptr.fetchall()
> ()
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Same problem. Here you end up with something like:

   select tID, tNote from tmp where 'tID'=1

You want:

field = "tID"
sql = "select tID,tNote from tmp where %%s = %s" % field
args = (1,)

> 
> And then, it worked if I do:
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>>>> str="select tID,tNote from %s where %s = %s" % ("tmp","tID",1)
>>>>
>>>> str
> 'select tID,tNote from tmp where tID = 1'
>>>> s.dbptr.execute(str)
> 1L
>>>> s.dbptr.fetchall()
> ({'tID': 1L, 'tNote': 'kao'},)
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Since your not using the db-api quoting mechanism, this of course works 
as you expect. *But* this is a potential security hole (perfect 
candidate for an sql-injection attack). Use the db-api quoting mechanism 
for args, use string formatting for anything else.