Substitution possibilities

[GoogleCodeExporter]

What steps will reproduce the problem?
1. execute a sql statement substituting column names and values
2.
3.

What is the expected output? What do you see instead?
Expect substitution will work anywhere in query string

What version of the product are you using? On what operating system?
Latest version as of 28 May 2010, on Windows XP

Please provide any additional information below.

I have an application that reads both the column names and values from a 
file.  I would like to be able to do something this:

conn = pyodbc.connect(...)
curs = conn.cursor()
columns = ('A', 'B', 'C)  # Normally read from a file
values = (1, 2, 3)        # Normally read from a file
curs.execute('insert into foo (?, ?, ?) values (?, ?, ?)', columns + 
values)

However, it appears that the '?' substitution only works for values, since 
I get an error message when I try this.

Original issue reported on code.google.com by gerald.b...@gmail.com on 28 May 2010 at 7:32

[GoogleCodeExporter]

One of the important constraints I've put on pyodbc is that it *never* modifies 
your 
SQL.  The SQL is passed directly to the ODBC driver.  There are a number of 
reasons 
for this, but two of the most important are (1) security and (2) some drivers 
allow 
non-standard SQL, so pyodbc could never know what to expect.

In the case of parameter substitution like

  insert into foo(a, b, c) values (?, ?, ?)

The parameters are not insert into the SQL statement.  Instead, the SQL is 
sent, 
question marks and all, directly to the driver.  Usually the driver sends it 
directly 
to the database.  Second, the three parameters are sent *separately*.

One of the very important benefits is eliminating potential SQL Injection 
attacks: 
http://en.wikipedia.org/wiki/SQL_injection

Therefore, I'm going to have to close this one with no changes.

Now, if you are inserting multiple rows, do build the SQL statement with the 
actual 
row names, but leave the question marks for the parameters.  If you execute the 
same 
statement but with different parameter values, you often get a performance 
boost.  
The SQL is not sent to the database over and over - instead it is sent the 
first time 
and we reuse it.

Sorry about that.

Original comment by mkleehammer on 28 May 2010 at 7:39

• Changed state: WontFix

[GoogleCodeExporter]

Yes, I understand that the SQL is not being modified, but the parameters are 
supplied 
for the question marks as needed.  Since I do not know the order the columns 
will be 
in the file I am using to build insert statements, I am unable to pass them on 
the 
insert statement.  The file itself is CSV and the field names are in the first 
line. 
That is why I would like to be able to pass the column names as parameters 
using the 
question-mark substitution scheme.  I am also worried about injection attacks, 
which 
is just the reason I would like to do things this way.  Currently I am building 
the 
insert statement dynamically using the field names read from the file, hence 
the 
risk.

Original comment by gerald.b...@gmail.com on 28 May 2010 at 8:02