Python Sql query parameterized

Question

I need to make a function with parameterized values to loop different queries with the same structure, I wrote the following code:

import pandas as pd
cnxn_str = ("Driver={SQL Server Native Client 10.0};"
            "Server=xx.xxx.xxx.xx,xx;"
            "Database=dbase;"
            "UID=sa;"
            "PWD=pswd;")
cnxn = pyodbc.connect(cnxn_str)

cursor = cnxn.cursor()

def TRX(tbl_name, var_value):
  import pandas as pd
  #route by hour----
  query = print("SELECT TOP 100 * FROM [DB].[dbo]." + tbl_name + " WHERE var1 IN ('" + var_value + "')") 
  df = pd.read_sql_query(query, cnxn)
  return(df)

When I run it, for example:

TRX(tbl_name = '[table1]', var_value = 'a')

It returns the following error:

DatabaseError: Execution failed on sql 'None': The first argument to execute must be a string or unicode query.

Which approach should I use?

You are missing a closing double quote on your query. Should be + "')"). When in doubt, write your sql to a variable and do a print to see what the SQL looks like and try to execute in a client like SSMS. It's easier to debug when you can see what's being submitted. It's also worth noting that since you are concatenating together your sql you are open to a sql injection attack. — JNevill
– JNevill, Commented Jul 14, 2022 at 19:03
Also, your pd.read_sql_query() is referencing variable sql_etapas but your sql is in variable query. Perhaps that just a typo when you ported your code to stackoverflow? — JNevill
– JNevill, Commented Jul 14, 2022 at 19:04
Thank you @JNevill , I edited the typos and it appears a new error: "The first argument to execute must be a string or unicode query." and What should I use to avoid the injection attack? — Ariel
– Ariel, Commented Jul 14, 2022 at 19:24
Regarding the sql injection attack, unfortunately because you are dynamically referencing a table, there isn't much you can do about that particular variable concatenation. Making sure that tablename is as sanitized as possible is your best bet. It may make sense to perform that concat (or string.format()) and then bind your var_value using parameterized query (whatever your library/module for sql server connection syntax is). — JNevill
– JNevill, Commented Jul 14, 2022 at 20:19
The worry here is that your user of this application says that the table name is sometable;DROP TABLE sometable-- which, if permissions allow it in your database, would cause your sometable to disappear off the face of the earth. — JNevill
– JNevill, Commented Jul 14, 2022 at 20:21

Shamim · Accepted Answer · 2022-07-14 19:15:24Z

1

I think this should work

query_sql = ''' SELECT COL FROM {} WHERE COL = '{}' '''.format(table_name, col_value)

answered Jul 14, 2022 at 19:15

Shamim

695 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Python Sql query parameterized

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related