How to safely insert parameters into a SQL query and get the resulting query?

Question

I have to use a non DBAPI-compliant library to interact with a database (qds_sdk for Qubole). This library only allows to send raw SQL queries without parameters. Thus I would like a SQL injection-proof way to insert parameters into a query and get the resulting formatted query in Python. Something like the format_sql function in the example below:

sql = 'select * from table where id = ?'
formatted_sql = format_sql(sql, (123,))  # 'select * from table where id = 123'

Is this possible at all or is it too RDBMS-specific?

See [`cursor.execute`](https://www.python.org/dev/peps/pep-0249/#id15). It takes parameters as an argument. — zvone, Jan 04 '21 at 16:53
Thanks but as I said, the library is precisely not DBAPI-compliant. It has no concept of cursor at all. — Roméo Després, Jan 04 '21 at 16:55

score 1 · Accepted Answer · answered Jan 04 '21 at 17:44

I don't know too much about Quoble and the dialect of SQL it accepts and there is a whole range of datatype you might be dealing with. But in many cases converting the argument to a string and then escaping single quote characters by either doubling them up or preceding them with a backslash (MySQL, for example, allows both methods), is probably the best you can do. I would use %s as the placeholder for your pseudo-prepared statement:

from datetime import date

def format_sql(query, args):
    new_args = []
    for arg in args:
        new_args.append(str(arg).replace("'", "''"))
    return query.replace("%s", "'%s'") % tuple(new_args)

print(format_sql("insert into mytable(x, y, z) values(%s, %s, %s)", ("Booboo's car", date.today(), 2)))

Prints:

insert into mytable(x, y, z) values('Booboo''s car', '2021-01-04', '2')

If there is any possibility of %s appearing in your SQL in some context other than as a placeholder, then you need to place the single quotes around those occurrences that are actual placeholders and not have function format_sql perform that function:

from datetime import date

def format_sql(query, args):
    new_args = []
    for arg in args:
        new_args.append(str(arg).replace("'", "''"))
    return query % tuple(new_args)

print(format_sql("insert into mytable(x, y, z) values('%s', '%s', '%s')", ("Booboo's car", date.today(), 2)))

How to safely insert parameters into a SQL query and get the resulting query?

1 Answers1