0

I'm building a code to query a database using python. Input is from a dictionary. I have written code for passing one parameter(key) in the query,

cursor = conn.execute("SELECT * FROM table1 WHERE param = '%s'" % kwargs['param'])

The dictionary items count I'm gonna pass to this line may vary. So, after WHERE the code need to be written to query all the keys of dictionary to database.

My code is almost completed except for this part. Tried Python Dynamic Parameterized Query. But it throws some Operational error. I'm using sqlite3 here.

Tim
  • 2,510
  • 1
  • 22
  • 26
Jose25X
  • 5
  • 1
  • 4
  • Always include error messages (and not just the name of the exception), these contain vital information that helps to isolate your issue. – Tim Mar 28 '20 at 10:56

1 Answers1

2

Without seeing the error message, I can't be sure what the error is. But you were not doing a parameterized query. The way you want to be doing the query is as follows:

Passing actual value(s) as a tuple:

cursor = conn.execute("SELECT * FROM table1 WHERE param = %s", (kwargs['param'],))

Or passing actual value(s) as a list:

cursor = conn.execute("SELECT * FROM table1 WHERE param = %s", [kwargs['param']])

Note:

  1. There are no quotes, ', around the %s parameters.
  2. The actual values for the %s parameters are supplied in either a list or tuple.

Note above that when passing the actual value in a tuple, the tuple is specified as (kwargs['param'],). The expression (kwargs['param']) (without the comma) would be interpreted as a simple term with a parentheses around it and not as a tuple, so the comma at the end is required when you have a single value.

You were doing textual substitution of kwargs['param'] for %s and then surrounding the result with quotes, which is altogether different (what if kwargs['param'] contained a single quote?). And depending on the source of kwargs['param'], you would be leaving yourself open to a SQL Injection attack (you should investigate this topic).

Update

If you have a dictionary, kwargs, whose keys are the names of the columns to be used in the WHERE clause, for example:

kwargs = {'param1': 1, 'param2': 'a', 'param3': 'x'}

then:

>>> kwargs = {'param1': 1, 'param2': 'a', 'param3': 'x'}
>>> where_clause = 'WHERE ' + ' AND '.join(['`' + k + '` = %s' for k in kwargs.keys()])
>>> where_clause
'WHERE `param1` = %s AND `param2` = %s AND `param3` = %s'
>>> values = list(kwargs.values())
>>> values
[1, 'a', 'x']

And so we get:

where_clause = 'WHERE ' + ' AND '.join(['`' + k + '` = %s' for k in kwargs.keys()])
values = list(kwargs.values())
sql = "SELECT * FROM table1 " + where_clause
cursor.execute(sql, values)
Booboo
  • 38,656
  • 3
  • 37
  • 60
  • I have passed a dictionary to kwargs, for instance kwargs contain {'param':'value', 'param2':'value2', 'param3':'value3'}. Since code need to be completed, I have limited code with one key-value pair(kwargs['param']). **This number of dict values are varying based on input**. I'm stuck now with part after WHERE - %s (number of placeholder to be there for kwargs) & passing all kwargs key to the placeholder. On searching, read about SQL injection. Still learning about that :). Thanks for info on passing passing tuple '''(kwargs['param'],)''' – Jose25X Mar 29 '20 at 16:16
  • I have updated my answer what may be useful information. – Booboo Mar 29 '20 at 16:56
  • Did some small changes to get the output[https://docs.python.org/2/library/sqlite3.html], `where_clause='WHERE'+'AND'.join([''+k+'=?'for k in kwargs.keys()])` On using '%s' as placeholder in where_clause(**Edited**where_clause='WHERE'+'AND'.join(['' +k+'=\'%s\''for k in kwargs.keys()])) I observed error, `cursor=conn.execute(sql,values) sqlite3.ProgrammingError:Incorrect number of bindings supplied. The current statement uses 0, and there are 2 supplied` Found lot of solutions but none for _current statement uses 0_. Asking this for my undersanding. Huge thanks for that solution. – Jose25X Mar 30 '20 at 20:15
  • And may I ask any reference to understand that .join function used.I'm clear with .join functionality. But, in the mentioned solution, it is quite difficult for me to understand(e.g Key values getting placed between WHERE & AND). – Jose25X Mar 30 '20 at 20:17
  • Does this help? https://docs.python.org/3/library/stdtypes.html?highlight=join#str.join For example, `','.join(['a','b','c']) -> 'a,b,c'` – Booboo Mar 30 '20 at 21:48
  • Yes, It does. I have mentioned the change done in suggested solution in above comments. – Jose25X Apr 07 '20 at 08:57
  • I have written a filter utility that does exactly just like this - joining kwargs taken from url query parameter then injecting them to the raw SQL query string. It does work, however I start to question whether this approach leads to SQL injection? – Michael Nov 24 '20 at 15:17
  • @Booboo would be too confusing if I'd post a separate question. Just wanna verify if the approach above using `kwargs` and `'WHERE' + 'AND'` could possibly allow SQL injection... – Michael Nov 24 '20 at 16:02
  • If you are calling `execute` where the first argument is a string like `... WHERE param1 = %s AND param2 = %s AND param3 = %s`, that is without quotes around the `%s` occurrences (in other words using actual prepared statement placeholders), and the second argument is a tuple or list providing the actual values for the placeholders, then SQL injection will not be a problem. – Booboo Nov 24 '20 at 16:36