How can I insert safely (sql injection) identifiers in a query using snowflake python connector?

user_90580 0
Viewed 1794

In order to avoid SQL injection attacks I want to use bind variables / query parameters, but those doesn't seem to work for identifiers (like a table name, or a user name) 

import snowflake.connector
import os
def myfunc(username):
    with snowflake.connector.connect(user=os.environ['SF_USER'], password=os.environ['SF_PASSWORD'], account=os.environ['SF_ACCOUNT']) as ctx:
        cs = ctx.cursor().execute('''SHOW GRANTS TO USER %s''', (username))
        print(cs.rowcount)
        rint(cs.fetchone())
myfunc('TEST2') # Raises a ProgrammingError: 001003 (42000): SQL compilation error: syntax error line1 at position 20 unexpected ''TEST2''

I guess in the above test TEST2 is inserted as a literal 'TEST2`` and not as an identifier"TEST2"`.

If I can't use bind variables / query parameters for this, I guess I would need to construct the string myself f'''SHOW GRANTS TO USER {username}''' but what options do I have to sanitize the input before the string interpolation?

By the way, I know that instead of SHOW GRANTS TO USER "<username>" I could use select * from "SNOWFLAKE"."ACCOUNT_USAGE"."GRANTS_TO_USERS" where deleted_on is null and grantee_name = '<username2' which avoid the problem since the in the latter query the username is used as a literal and not an identifier. But I found the SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS to be lagging (does NOT show the latest changes immediately, delayed by more that 20 minutes in my case).

So what can I use to sanitize the values I'll use as identifiers to prevent SQL injection attacks?

snowflake-cloud-data-platform
1 Answers

As @SimeonPilgrim pointed out in a comment there is a function IDENTIFIER that can be used to convert a bind variable to an identifier.

So instead of ... TO USER %s use ... TO USER IDENTIFIER(%s) 

import snowflake.connector
import os
def myfunc(username):
    with snowflake.connector.connect(user=os.environ['SF_USER'], password=os.environ['SF_PASSWORD'], account=os.environ['SF_ACCOUNT']) as ctx:
        cs = ctx.cursor().execute('''SHOW GRANTS TO USER IDENTIFIER(%s)''', (username))
        print(cs.rowcount)
        rint(cs.fetchone())
myfunc('TEST2') # Works!
user_905800
Related