Why does my interpolated SQL query have these extra quotation marks?

Question

I set queries like following . I'd like to replace bucket_name and file_name with variables

This query is executed by psycopg2

query = '''copy table.test
                from 's3://%(bucket_name)s/%(file_name)s.txt'
                iam_role 'arn:aws:iam::123453215125:role/test'
                timeformat as 'auto'
                ACCEPTINVCHARS 
                delimiter '\t';'''

And I get bucket name and file name

    bucket_name = get_bucket_name(event)
    file_name = get_file_name(event)

After that, I executed query, but it returned errors

cur.execute(query, {'bucket_name':bucket_name,'file_name':file_name})

[ERROR] SyntaxError: syntax error at or near "expired" LINE 2: from 's3://'expired-test-bucket... ^ Traceback (most recent cal

It seems that bucket_name is replaced with single quotation 'expired-test-bucket'

My desired result is expired-test-bucket

How can I fix this?

You can try to use python's string format() with {} placeholders for bucket name and filename. — Aravind, Jul 08 '21 at 05:49
Maybe [this thread](https://stackoverflow.com/questions/18656545/postgresql-syntax-error-at-or-near) can help a little bit. — Qiyu Zhong, Jul 08 '21 at 05:50

score 2 · Accepted Answer · answered Jul 08 '21 at 05:49

Parameters in parameterized queries do not work like string interpolation, even if you use pyformat binding style that happens to look like old-school %-based string interpolation. The quotes are there because the database engine is expecting you to use the placeholder to represent an entire parameter, and putting quotes (and potentially various forms of escaping) into the final query text by design, specifically to prevent the security hole that results from interpolating user data directly into a query.

If you need to use user data to build up a value that is used in a query, then do just that, as separate steps: first, use Python string formatting to create the raw underlying value, and then use the SQL engine's functionality to make it safe to put that value into a query.

So, something like:

bucket_name = get_bucket_name(event)
file_name = get_file_name(event)
url = f's3://{bucket_name}/{file_name}.txt'

query = '''copy table.test
           from %s
           iam_role 'arn:aws:iam::123453215125:role/test'
           timeformat as 'auto'
           ACCEPTINVCHARS 
           delimiter '\t';'''

cur.execute(query, (url,))

Why does my interpolated SQL query have these extra quotation marks?

1 Answers1