[Zope-DB] sql query works in database connector but not in ZSQL method

[Charlie Clark]

Am 17.01.2007, 18:19 Uhr, schrieb Re: [Zope-DB] sql query works in  
database connector but not in ZSQL methodrobert rottermann  
<robert at redcor.ch>:

>                 query =  "CALL selectVertexProperties(%s, @error2)" %  
> dbid
> - ----- problem>> result = db.query((query).replace(';', sql_delimiter))
>                 return result

Call db.execute(SQL_string, (*paras)) instead.

ie.
mySQL = """CALL selectVertexProperties(?, @error2)"""
db.execute(mySQL, (username, ))

Not sure if you can combine SQL statements with ";" like this (which I  
don't think you should do anyway) but you should be able to create your  
SQL statements independent of the parameters.

NB. "?" is the ODBC standard for placeholder. Most Python drivers use "%s"  
but this can cause confusion, ie.
db.execute("SELECT * FROM table WHERE user = %s" ,(username, )) is not the  
same as
db.execute("SELECT * FROM table WHERE user = '%s'" %(username, ))

In the first case it is the responsibility of the ODBC driver to pass the  
parameter correctly. In the second case you are generating the entire  
query and passing it to the ODBC driver. Not only  is this less efficient  
but it is also error prone and dangerous because it is open to SQL  
injection.

Charlie