Re: Problem with MySQL cursor
Carsten Haese wrote: On Thu, 2007-10-11 at 15:14 +0200, Florian Lindner wrote: Hello, I have a function that executes a SQL statement with MySQLdb: def executeSQL(sql, *args): print sql % args cursor = conn.cursor() cursor.execute(sql, args) cursor.close() it's called like that: sql = INSERT INTO %s (%s) VALUES (%s) executeSQL(sql, DOMAIN_TABLE, DOMAIN_FIELD, domainname) You can't use parameter binding to substitute table names and column names, or any other syntax element, into a query. You can only bind parameters in places where a literal value would be allowed (more or less, the real rules are more complicated, but this rule of thumb gets you close enough). You have to construct the query string like this, for example: sql = INSERT INTO +DOMAIN_TABLE+(+DOMAIN_FIELD+) VALUES (%s) executeSQL(sql, domainname) Ok, I understand it and now it works, but why is limitation? Why can't I just the string interpolation in any playes and the cursor function escapes any strings so that they can't do harm to my query? Regards, Florian -- http://mail.python.org/mailman/listinfo/python-list
Re: Problem with MySQL cursor
On Fri, 2007-10-12 at 13:12 +0200, Florian Lindner wrote: Carsten Haese wrote: sql = INSERT INTO +DOMAIN_TABLE+(+DOMAIN_FIELD+) VALUES (%s) executeSQL(sql, domainname) Ok, I understand it and now it works, but why is limitation? Why can't I just the string interpolation in any playes and the cursor function escapes any strings so that they can't do harm to my query? What's happening here is not string interpolation. It's called parameter binding, and the SQL standard defines exactly what parameter binding can and can not do. (The SQL standard also defines that parameter markers are question marks, and the fact that DB-API v2 modules are allowed to blur the distinction between string interpolation and parameter binding by choosing to use %s markers is very unfortunate.) A primary purpose of parameter binding is the ability to prepare a query once and then execute it many times over with different values. This saves time because the query only needs to be parsed and planned once. For this to be useful, parameters can't be bound to anything that would alter the query plan. Consequently, parameter binding can't substitute any of the following: * The type of statement (SELECT/UPDATE/etc.) * The tables involved * The columns involved * The structure of the join and where clauses * The group by, order by, and having clauses * The names of called functions and procedures * Probably a whole lot of other things I'm not thinking of right now Once you exclude all the things that parameter binding can't substitute, you're left with only a very small segment of the SQL language that parameter binding *can* substitute, which basically boils down to anything that could be a literal value. HTH, -- Carsten Haese http://informixdb.sourceforge.net -- http://mail.python.org/mailman/listinfo/python-list
Re: Problem with MySQL cursor
Florian Lindner wrote: Carsten Haese wrote: On Thu, 2007-10-11 at 15:14 +0200, Florian Lindner wrote: Hello, I have a function that executes a SQL statement with MySQLdb: def executeSQL(sql, *args): print sql % args cursor = conn.cursor() cursor.execute(sql, args) cursor.close() it's called like that: sql = INSERT INTO %s (%s) VALUES (%s) executeSQL(sql, DOMAIN_TABLE, DOMAIN_FIELD, domainname) You can't use parameter binding to substitute table names and column names, or any other syntax element, into a query. You can only bind parameters in places where a literal value would be allowed (more or less, the real rules are more complicated, but this rule of thumb gets you close enough). You have to construct the query string like this, for example: sql = INSERT INTO +DOMAIN_TABLE+(+DOMAIN_FIELD+) VALUES (%s) executeSQL(sql, domainname) Ok, I understand it and now it works, but why is limitation? Why can't I just the string interpolation in any playes and the cursor function escapes any strings so that they can't do harm to my query? Because the function doesn't know that you wanted select * from user where email = '[EMAIL PROTECTED]' instead of select * from user where email = 'foo'; drop table user where '' = '' And of course you don't gain anything from using USER INPUT in the string-interpolation for creating the sql statement!!! Diez -- http://mail.python.org/mailman/listinfo/python-list
Re: Problem with MySQL cursor
Carsten Haese wrote: On Fri, 2007-10-12 at 13:12 +0200, Florian Lindner wrote: Carsten Haese wrote: sql = INSERT INTO +DOMAIN_TABLE+(+DOMAIN_FIELD+) VALUES (%s) executeSQL(sql, domainname) Ok, I understand it and now it works, but why is limitation? Why can't I just the string interpolation in any playes and the cursor function escapes any strings so that they can't do harm to my query? [...] Thanks for your good explanation! Florian -- http://mail.python.org/mailman/listinfo/python-list
Re: Problem with MySQL cursor
On Thu, Oct 11, 2007 at 03:14:30PM +0200, Florian Lindner wrote regarding
Problem with MySQL cursor:
Traceback (most recent call last):
File manage.py, line 90, in ?
addDomain(domainName)
File manage.py, line 27, in addDomain
executeSQL(sql, DOMAIN_TABLE, DOMAIN_FIELD, domainname)
File manage.py, line 22, in executeSQL
cursor.execute(sql, args)
File /usr/lib/python2.4/site-packages/MySQLdb/cursors.py, line 163, 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 ''domains' ('domain') VALUES ('xgm.de')' at
line 1)
I see the error: 2 opening quotes but only 1 closing around domains. But
where do they come from?
Note that there are no quotes at print sql % args.
No, there are no double quote issues. The first quote is provided by the error
message, and is paired with the quote after the closed parenthesis in
('xgm.de')'. It is not part of your SQL.
Cheers,
Cliff
--
http://mail.python.org/mailman/listinfo/python-list
RE: Problem with MySQL cursor
I don't think you can substitute the table name and column names in the
execute, just values ( I could be wrong)
try building it like this:
sql = INSERT INTO %s %s VALUES % (taablename, columnstuple, '(%s)')
cursor.execute(sql, values)
Hello,
I have a function that executes a SQL statement with MySQLdb:
def executeSQL(sql, *args):
print sql % args
cursor = conn.cursor()
cursor.execute(sql, args)
cursor.close()
it's called like that:
sql = INSERT INTO %s (%s) VALUES (%s)
executeSQL(sql, DOMAIN_TABLE, DOMAIN_FIELD, domainname)
The statement that is printed looks ok (missing quotes, but AFAIK
cursor.execute does that):
INSERT INTO domains (domain) VALUES (xgm.de)
but MySQL prints an error:
Traceback (most recent call last):
File manage.py, line 90, in ?
addDomain(domainName)
File manage.py, line 27, in addDomain
executeSQL(sql, DOMAIN_TABLE, DOMAIN_FIELD, domainname)
File manage.py, line 22, in executeSQL
cursor.execute(sql, args)
File /usr/lib/python2.4/site-packages/MySQLdb/cursors.py,
line 163, 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 ''domains' ('domain') VALUES
('xgm.de')' at
line 1)
I see the error: 2 opening quotes but only 1 closing around
domains. But
where do they come from?
Note that there are no quotes at print sql % args.
Thanks,
Florian
--
http://mail.python.org/mailman/listinfo/python-list
--
http://mail.python.org/mailman/listinfo/python-list
Re: Problem with MySQL cursor
On Thu, 2007-10-11 at 15:14 +0200, Florian Lindner wrote: Hello, I have a function that executes a SQL statement with MySQLdb: def executeSQL(sql, *args): print sql % args cursor = conn.cursor() cursor.execute(sql, args) cursor.close() it's called like that: sql = INSERT INTO %s (%s) VALUES (%s) executeSQL(sql, DOMAIN_TABLE, DOMAIN_FIELD, domainname) You can't use parameter binding to substitute table names and column names, or any other syntax element, into a query. You can only bind parameters in places where a literal value would be allowed (more or less, the real rules are more complicated, but this rule of thumb gets you close enough). You have to construct the query string like this, for example: sql = INSERT INTO +DOMAIN_TABLE+(+DOMAIN_FIELD+) VALUES (%s) executeSQL(sql, domainname) HTH, -- Carsten Haese http://informixdb.sourceforge.net -- http://mail.python.org/mailman/listinfo/python-list
