python variable in string inserting single quote

Question

I have the following bit of python code:

insert_table = settings.INSERT_TABLE_NAME

        self.execute(
            """
            INSERT INTO `%(insert_table)s`
            (time, rem_host, username, our_result,
              upstream_result, reason, solution, difficulty)
            VALUES
            (FROM_UNIXTIME(%(time)s), %(host)s,
              %(uname)s,
              %(lres)s, 'N', %(reason)s, %(solution)s, %(difficulty)s)
            """,
            {
                "insert_table": insert_table,
                "time": v[4],
                "host": v[6],
                "uname": v[0],
                "lres": v[5],
                "reason": v[9],
                "solution": v[2],
                "difficulty": v[3]
            }
        )

The problem I'm having is that the result of the SQL query contains single quotes around the 'insert_table' variable so the result is:

INSERT INTO `'my_table_name'`

so it's obviously breaking the query.

Anyone have any suggestion on how to correctly configure this?

Martijn Pieters · Accepted Answer · 2014-03-07 15:48:39Z

1

You cannot use SQL parameters to interpolate table names. You'll have to use classic string formatting for those parts.

That's the point of SQL parameters; they quote values so they cannot possibly be interpreted as SQL statements or object names.

The following works, but you need to be careful where your table names come from:

"""
INSERT INTO `{}`
(time, rem_host, username, our_result,
  upstream_result, reason, solution, difficulty)
VALUES
(FROM_UNIXTIME(%(time)s), %(host)s,
  %(uname)s,
  %(lres)s, 'N', %(reason)s, %(solution)s, %(difficulty)s)
""".format(insert_table),
{
    "time": v[4],
    "host": v[6],
    "uname": v[0],
    "lres": v[5],
    "reason": v[9],
    "solution": v[2],
    "difficulty": v[3]
}

If insert_table is user-sourced, your best bet is to first vet it against a pre-determined list of existing tablenames.

answered Mar 7, 2014 at 15:48

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rabbie Over a year ago

Thanks for this! it worked like a charm. I'm not worried about the source of the tablename because it's hard coded and secure. Not derived from user input :)

Collectives™ on Stack Overflow

python variable in string inserting single quote

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related