SQL injection in duckdb query on pandas dataframe

Question content

In a project, I am using duckdb to perform some queries on a dataframe. For one of the queries, I need to add some user input to the query. That's why I want to know if sql injection is possible in this case. Can a user harm an application or system through input? If so, how can I prevent this from happening? It seems duckdb has no preparedstatement for dataframe queries.

I've looked in the documentation (https://duckdb.org/docs/api/python/overview.html) but can't find anything useful. The method duckdb.execute(query,parameters) seems to only work with databases with real sql connections, not dataframes.

There is also a question on stackoverflow on this topic (syntax for duckdb > python sql with parameter\variable), but the answer only works for real sql connections, and the version with f-strings seemed to me Not safe.

Here is a small code example to illustrate what I mean:

import duckdb
import pandas as pd

df_data = pd.DataFrame({'id': [1, 2, 3, 4], 'student': ['student_a', 'student_a', 'student_b', 'student_c']})
    
user_input = 3  # fetch some user_input here
    
# How to prevent sql-injection, if its even possible in this case?
result = duckdb.query("SELECT * FROM df_data WHERE id={}".format(user_input))

So how will you solve this problem? Is sql injection possible? Thanks for your help and if you need more information please feel free to ask for more details!

Edit: Fixed syntax error in code

Correct answer

Looks like it is possible:

>>> duckdb.execute("""SELECT * FROM df_data WHERE id=?""", (user_input,)).df()

   id    student
0   3  student_b

The above is the detailed content of SQL injection in duckdb query on pandas dataframe. For more information, please follow other related articles on the PHP Chinese website!