How can I know if my SQL query not expose to SQL injection

user_3271251 0
Viewed 152

I have an originally SQL query:

f"SELECT FIELDS(ALL) from xxxx WHERE CreatedDate >= {start_time}"

I wanted to make that query safe from sql injection attack but I could not see how can I know that I did it right. This is the new version that should be safe:

f"SELECT FIELDS(ALL) from xxxx WHERE CreatedDate >= %s" % (start_time,)

I'm using it in an API call. The query itself will be excecated in the other side (third party). I want to send the query as parameter in the api call I would like to get some tips regarding this issue Thank you!

sql-injection sql python
1 Answers

Anytime you are directly creating the string in your code you are exposing yourself to SQL injection. You want to pass the handling of data off to the DBMS. Using an ORM like SQLAlchemy will handle a lot of that (if you use the ORM and don't pass your SQL in directly). Most libraries for connecting to a database follow python's DB api standard. Since you haven't mentioned what you're using I'll use pyodbc as an example.

Copied from the docs:

Inserting Data To insert data, pass the insert SQL to Cursor execute(), along with any parameters > necessary:

cursor.execute("insert into products(id, name) values ('pyodbc', 'awesome library')")
cnxn.commit()

or, parameterized:

cursor.execute("insert into products(id, name) values (?, ?)", 'pyodbc', 'awesome library')
cnxn.commit()

Notice the parameterized version. This is what you want. Here pyodbc is handing both your query and your data to the DBMS. The DBMS will handle sanitizing the data. This form is called qmark notation (notice the question marks). There are a few other notations but the important part is you are using parameterization and passing the data as separate from your query. With most libraries this looks something like:

cursor.execute(query_string_with_qmark_notation, data_or_tuple_of_data)
user_92246780
Related