3

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!

8
  • 1
    Does this answer your question? Is this Python code vulnerable to SQL injection? (SQLite3)
    – Brad Koch
    Oct 20, 2021 at 13:03
  • 1
    What library / framework are you using for executing the queries ?
    – Kris
    Oct 20, 2021 at 13:03
  • 1
    Any SQL query constructed from basic string formatting methods is vulnerable to injection, you'll need to use your database library's parameterization utilities to ensure the values are escaped properly.
    – Brad Koch
    Oct 20, 2021 at 13:06
  • 3
    Surely I can't be the only one who frowns upon the idea to use Sql in an API ? That mostly shows the API is lacking certain functionalities.
    – LukStorms
    Oct 20, 2021 at 13:21
  • 1
    @Sprint21 can you add a little more information about the API? if you can't mention it by name can you see if it has any options for parameterization (see my answer below for an example)? Oct 20, 2021 at 13:57

1 Answer 1

0

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)
2
  • 1
    The OP isn't directly interacting with a DBMS, they have to submit a SQL query to an API.
    – Brad Koch
    Oct 20, 2021 at 13:28
  • 1
    @BradKoch I see the update now. While I tend to agree with LukStorms that an API accepting SQL isn't the best course of action, I'll happily update my answer if the OP can add some specifics Oct 20, 2021 at 13:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.