Insert into (...) - can I use "format" instead of "%s" in Python3?

Question

To insert data into MySql I do this in Python 3:

import MySQLdb

# .....
cursor.execute("""INSERT INTO table1(col1, col2, col3) VALUES(%s, %s, %s)""" ,(data["col1"], data["col2"], data["col3"])

Since it's Python 3, can I replace %s with format and how?

Answer 1

No, in this case you can't. The %s usage used in combination with database access is only remotely related to the "normal" usage of %s. Specifically, %s is only one possible way the database can take its parameters. There are several other ways, ? among them, which are database specific.

The big difference is however that the database does proper data handling at its own choice such as prepared statements or data escaping. In both cases, data passed to the database is safe from any injection.

OTOH, if you do your own formatting, be it with % or with .format(), you are NOT safe from SQL injections.

In other words, no, using .format() for database query assembling is not advisable and can easily lead to security vulnerabilities.

So .format() is a great replacement for "%s" % whatever, but not for corsor.execute("... %s ...", whatever), because that's a completely different thing.

Answer 2

Drawing inspiration from the source code of the execute method, you could write:

connection = cursor._getDb()
cursor.execute("""INSERT INTO table1(col1, col2, col3) VALUES({}, {}, {})""".format(
    connection.literal(data["col1"]),
    connection.literal(data["col2"]),
    connection.literal(data["col3"])
))

Now, I didn't say that's what you should do...