Why does Snowflake variable binding in query throw error with table name but not integer?

Question

I am following the Snowflake Python Connector docs for variable binding to avoid SQL injection. I successfully set up a db connection with the following dict of credentials:

import snowflake.connector CONN = snowflake.connector.connect( user=snowflake_creds['user'], password=snowflake_creds['password'], account=snowflake_creds['account'], warehouse=snowflake_creds["warehouse"], database=snowflake_creds['database'], schema=snowflake_creds['schema'], ) cur = CONN.cursor(snowflake.connector.DictCursor)

The following block works fine and I get back query results, hard-coding the table name and using the standard format binding:

command = ("SELECT * FROM TEST_INPUT_TABLE WHERE batch_id = %s") bind_params = (2) results = cur.execute(command % bind_params).fetchall()

Similarly, this block works fine, using the pyformat binding:

command = ("SELECT * FROM TEST_INPUT_TABLE WHERE batch_id = %(id)s") bind_params = {"id": 2} results = cur.execute(command, bind_params).fetchall()

But the following two blocks both result in a ProgrammingError (pasted below the second block):

command = ("SELECT * FROM %s WHERE batch_id = %s") bind_params = ("TEST_INPUT_TABLE", 2) results = cur.execute(command, bind_params).fetchall() command = ("SELECT * FROM %(tablename)s WHERE batch_id = %(id)s") bind_params = { "tablename": "TEST_INPUT_TABLE", "id": 2 } results = cur.execute(command, bind_params).fetchall() ProgrammingError: 001011 (42601): SQL compilation error: invalid URL prefix found in: 'TEST_INPUT_TABLE'

Is there some difference between how strings and ints get interpolated? I would not think it would make a difference but that is all I can think of. Am I missing something simple here? I don't want to have to choose between hard-coding the table name and putting the system at risk of SQL injection. Thanks for any guidance.

Mike Walton · Accepted Answer · 2020-12-24 21:23:37Z

6

You should be wrapping your bind variables with an INDENTIFER() function when they reference an object, rather than a string literal. For example:

command = ("SELECT * FROM IDENTIFIER(%(tablename)s) WHERE batch_id = %(id)s")

https://docs.snowflake.com/en/sql-reference/identifier-literal.html

Give that a try.

answered Dec 24, 2020 at 21:23

Mike Walton

7,4592 gold badges14 silver badges26 bronze badges

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

3 Comments

Ezra Citron Over a year ago

Thank you. I did not (but probably should have) included the fully qualified name that I was going for, which is <database>.<schema>.<tablename> ... now I'm trying ...IDENTIFIER(%(database)s.%(schema)s.%(tablename)s)... and various variations on this, but nothing works and I get ProgrammingError: 001003 (42000): SQL compilation error: syntax error....unexpected '.'. Is there a way to use the fully qualified name with the bindings? The docs indicate yes, but there are no examples with the pyformat binding I'm using.

Mike Walton Over a year ago

Try concatenating the database, schema, and tablename into a single variable and using that inside the IDENTIFIER function. I think that is how I've done this in the past.

Ezra Citron Over a year ago

Ah, the concatenation works perfectly. Thank you!

Collectives™ on Stack Overflow

Why does Snowflake variable binding in query throw error with table name but not integer?

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related