sql data formatting and sql injections-CodePudding

I have a database with 2 tables: students, employees and I want to update one of those tables:

import sqlite3

db_file = "school.db"

def update_address(identifier, user_address, user_id):
    with sqlite3.connect(db_file) as conn:
        c = conn.cursor()
        c.execute(f"""
        UPDATE {identifier}
        SET address = ?
        WHERE id = ?;
        """,
        (user_address, user_id))

update_address("students", "204 Sycamore Street", 2)

The above code works, the problem is I know that using python string formatting in an sql operation can lead to vulnerabilities per sqlite3 docs:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see https://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method.

The placeholder '?' works when it comes to inserting values but not for sql identifiers. Output: sqlite3.OperationalError: near "?": syntax error

So the question here is: can an sql injection occur if I use python string formatting on an sql identifier or does it only occur on values ?

If it also occurs on identifiers is there a way to format the string in a safe manner?

CodePudding user response：

Yes, if you interpolate any content into an SQL query unsafely, it is an SQL injection vulnerability. It doesn't matter if the content is supposed to be used as a value in the SQL expression, or an identifier, SQL keyword, or anything else.

It's pretty common to format queries from fragments of SQL expressions, if you want to write a query with a variable set of conditions. These are also possible SQL injection risks.

The way to mitigate the SQL injection risk is: don't interpolate untrusted input into your SQL query.

For identifiers, you should make sure the content matches a legitimate name of a table (or column, or other element, if that's what you're trying to make dynamic). I.e. create an "allowlist" of tables known to exist in your database that are permitted to update using your function. If the input doesn't match one of these, then don't run the query.

It's also a good idea to use back-ticks to delimit identifiers, because if one of the table names happens to be a reserved keyword in SQLite, that will allow the table to be used in the SQL query.

    if identifier not in ["table1", "table2", "table3"]:
        raise Exception("Unknown table name: '{identifier}'")
    c.execute(f"""
    UPDATE `{identifier}`
    SET address = ?
    WHERE id = ?;
    """,
    (user_address, user_id))