How to avoid SQL Injection in Python for Upsert Query to SQL Server?-CodePudding

I have a sql query I'm executing that I'm passing variables into. In the current context I'm passing the parameter values in as f strings, but this query is vulnerable to sql injection. I know there is a method to use a stored procedure and restrict permissions on the user executing the query. But is there a way to avoid having to go the stored procedure route and perhaps modify this function to be secure against SQL Injection?

I have the below query created to execute within a python app.

def sql_gen(tv, kv, join_kv, col_inst, val_inst, val_upd):
    sqlstmt =  f"""
  IF NOT EXISTS (
          SELECT *
          FROM {tv}
          WHERE {kv} = {join_kv}
          )
      INSERT {tv} (
      {col_inst}
      )
      VALUES (
      {val_inst}
      )
  ELSE
      UPDATE {tv}
      SET {val_upd}
      WHERE {kv} = {join_kv};
      """
    engine = create_engine(f"mssql pymssql://{username}:{password}@{server}/{database}")
    connection = engine.raw_connection()
    cursor = connection.cursor()

    cursor.execute(sqlstmt)
    connection.commit()

    cursor.close()

CodePudding user response：

Fortunately, most database connectors have query parameters in which you pass the variable instead of giving in the string inside the query yourself for the risks you mentioned. You can read more on this here: https://realpython.com/prevent-python-sql-injection/#understanding-python-sql-injection

Example:

# Vulnerable
cursor.execute("SELECT admin FROM users WHERE username = '"   username   '");
# Safe
cursor.execute("SELECT admin FROM users WHERE username = %s'", (username, ));

CodePudding user response：

As Amanzer mentions correctly in his reply Python has mechanisms to pass parameters safely.

However, there are other elements in your query (table names and column names) that are not supported as parameters (bind variables) because JDBC does not support those.

If these are from an untrusted source (or may be in the future) you should be sure you validate these elements. This is a good coding practice to do even if you are sure.

There are some options to do this safely:

You should limit your tables and columns based on positive validation - make sure that the only values allowed are the ones that are authorized

If that's not possible (because these are user created?):

You should make sure tables or column names limit the names to use a "safe" set of characters (alphanumeric & dashes, underscores...)
You should enquote the table names / column names - adding double quotes around the objects. If you do this, you need to be careful to validate there are no quotes in the name, and error out or escape the quotes. You also need to be aware that adding quotes will make the name case sensitive.