2

I am using pg8000 to connect to a PostgreSQL database via Python. I would like to be able to send in dates as parameters via the cursor.execute method:

def info_by_month(cursor, year, month):
    query = """
    SELECT *
    FROM info
    WHERE date_trunc('month', info.created_at) =
          date_trunc('month', '%s-%s-01')
    """
    cursor.execute(query, (year, month))
    return cursor

This will raise the error: InterfaceError: '%s' not supported in a quoted string within the query string. It's possible to use Python's string formatting to insert the date in there. The use of the string formatting mini language provides a measure of data validation to prevent SQL injection attacks, but it's still pretty ugly.

def info_by_month(cursor, year, month):
    query = """
    SELECT *
    FROM info
    WHERE date_trunc('month', info.created_at) =
          date_trunc('month', '{:04}-{:02}-01')
    """.format(year, month)
    cursor.execute(query)
    return cursor

How do I sent a quoted string into the cursor.execute method?

Tim McNamara
  • 18,019
  • 4
  • 52
  • 83

2 Answers2

1

Do the format ahead of time, and then pass the resulting string into execute. That way you avoid the SQL injection potential, but still get the formatting you want.

e.g. the query becomes:

query = """
    SELECT *
    FROM info
    WHERE date_trunc('month', info.created_at) =
          date_trunc('month', %s)"""

And then the format and execute becomes:

dateStr = "{:04}-{:02}-01".format(year, month)
cursor.execute(query, dateStr)

I use psycopg2, but it appears pg8000 adheres to the same DBI standard, so I would expect this to work in pg8000, too.

khampson
  • 14,700
  • 4
  • 41
  • 43
  • FYI that's what I mention in the second code snippet. I was wondering ig it's possible to improve on that further. – Tim McNamara Jan 27 '15 at 00:07
  • My code is actually different than your 2nd snippet. In your 2nd snippet, you're doing the `format` and then passing the whole string to `execute`. That means you aren't having the *SQL* library do any of the interpolation. In my example, I am doing the `format`, and then passing it as an argument to `execute`, where the *SQL* library then can do interpolation as needed, averting potential *SQL injection*. Basically, my code snippet is the answer your your question **"How do I sent a quoted string into the cursor.execute method?"** – khampson Jan 27 '15 at 01:04
  • Marking as correct because it's more sensible than anything I can think of! – Tim McNamara Jan 27 '15 at 02:15
0

It's possible to do this via concatenation, to the detriment of readability. 

query = """
  SELECT *
  FROM info
  WHERE date_trunc('month', info.created_at) =
        date_trunc('month', %s || '-' || %s || '-01')
"""
cursor.execute(query, (year, month))
Tim McNamara
  • 18,019
  • 4
  • 52
  • 83