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!

Sprint21
  • 89
  • 6
  • 1
    Does this answer your question? [Is this Python code vulnerable to SQL injection? (SQLite3)](https://stackoverflow.com/questions/13613037/is-this-python-code-vulnerable-to-sql-injection-sqlite3) – Brad Koch Oct 20 '21 at 13:03
  • 1
    What library / framework are you using for executing the queries ? – Kris Oct 20 '21 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 '21 at 13:06
  • Actually 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 – Sprint21 Oct 20 '21 at 13:07
  • 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 '21 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)? – General4077 Oct 20 '21 at 13:57
  • Sure. Thank you. I'm using BMC Remedyforce REST API – Sprint21 Oct 20 '21 at 14:06
  • @Sprint21 I don't see an endpoint in the documentation that says it accepts SQL. can you provide a link? – General4077 Oct 20 '21 at 14:19

1 Answers1

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)
General4077
  • 435
  • 1
  • 8
  • 17
  • 1
    The OP isn't directly interacting with a DBMS, they have to submit a SQL query to an API. – Brad Koch Oct 20 '21 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 – General4077 Oct 20 '21 at 13:55