Issue 99: Substitution possibilities
Status:  WontFix
Owner: ----
Closed:  May 2010
Reported by gerald.b...@gmail.com, May 28, 2010
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.


May 28, 2010
Project Member #1 mkleehammer
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.
Status: WontFix
May 28, 2010
#2 gerald.b...@gmail.com
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.