CODEWITHSUNDEEP
snowflake-cloud-data-platformsql-injectionstring-interpolation
Ezra Citron
Ezra Citron

Reputation: 113

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

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.

Upvotes: 4

Views: 3588

Answers (1)

Mike Walton
Mike Walton

Reputation: 7369

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.

Upvotes: 6

Related Questions