CODEWITHSUNDEEP
sql-serverpandaspyodbc
mysterious_guy
mysterious_guy

Reputation: 435

Parameterized create query for pyodbc

I am trying to use a parameterized query to avoid SQL injection for pyodbc ; based on the documentation specified in the below link: https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki#Parameters

Below is the dataframe:

    In [57]: df
 Out[57]:
            TXN_KEY SEND_AGENT  PAY_AGENT
0     13273870  ANO080012  API352676
1     13274676  AUK359401  AED002782
2     13274871  ACL000105  ACB020203
3     13275398  AED420319  ASE094882
4     13278566  ARA030210  AII071196
5     13278955  AYM003098  AHX012817
6     13280334  AJ5020114  AED000438
7     13280512  A11171047  AEL051943
8     13281278  AOG010045  ADJ031448
9     13282118  AMX334165  APM033226
10    13283955  APL170095  AE4082002

 x=df.columns.tolist()
 x
 Out[59]: [u'TXN_KEY', u'SEND_AGENT', u'PAY_AGENT']

Below is my cursor command to create the table in the sql server database:

    cursor.execute("""Create table result (?  bigint  PRIMARY KEY  , ?  varchar(9), ?  varchar(9))""",x[0],x[1],x[2])

    ProgrammingError: ('42000', "[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '@P1'. (102) (SQLExecDirectW)")

But 

    x[0]
    Out[63]: u'TXN_KEY'...

And so on. Similarly , I need to write a cursor with parameterized query for alter table add column too. I know how to do that for insert , update and select. I thought this would be on similar lines but it is clearly not.

Upvotes: 2

Views: 1647

Answers (2)

MaxU - stand with Ukraine
MaxU - stand with Ukraine

Reputation: 210982

you can use parameters only for literals.

You can't use it for table names, column names or any other parts of the SQL, beside literals.

Beside that you can't and shouldn't use it for DDLs - like create table, alter table, etc.

But you should use parameterized queries for SELECT/INSERT/UPDATE/DELETE statements.

Upvotes: 1

FlipperPA
FlipperPA

Reputation: 14361

It is ugly, but since you're not binding parameters to columns, you'd need to do something like this (I have tested it):

cursor.execute("""Create table result ({} bigint PRIMARY KEY, {} varchar(9), {} varchar(9))""".format(x[0], x[1], x[2]))

I would highly recommend learning more about pyodbc before digging in too much further. The updated documentation is here, anything on Google Code is very out of date: http://mkleehammer.github.io/pyodbc/ It documents what you'll need to do for SELECTs, INSERTs, DELETEs, and UPDATEs. The last three require a commit() statement afterwards. Good luck!

Upvotes: 1

Related Questions