By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement . We’ll occasionally send you account related emails.

Snowflake Connector for Python Version: 1.3.14
Python Version: 2.7.13
Platform: Darwin-16.4.0-x86_64-i386-64bit

According to the docs, the following code snippet should work:

self.cnx.cursor().execute("USE ROLE %s", self.sf_role)

However, when the execute() method parses the parameter, it wraps it in single quote so that the resulting SQL query is USE ROLE 'my_role' , which is invalid syntax. Specifically: syntax error line 1 at position 9 unexpected ''my_role''.

Even though Python-style formatting, e.g., self.cnx.cursor().execute("USE ROLE %s" % self.sf_role) , is discouraged because of SQL injection risks, there doesn't seem to be any other way around it.

As this is not a parameter being passed in to use as a value, there should be some option to correctly handle this case.

As you found, object names is not supported for parameter binding but value, e.g., this works:

cur.execute("select * from tbl where col1=%s", (col1,))
but this won't:
cur.execute("select * from %s where col1=%s", (tbl, col1))

The same limitation applies to database, schema, table, view, function, role, stage, ... all of object names.

I understand about your concern of SQL injection, but I have not seen other DB API solves this, so I'm not sure what is the best solution at the moment.

Technically we could add a tag to the parameter, e.g., to indicate the object binding so that Python Connector handles it well; use double quotes with escape?

cur.execute("select * from %(tbl)s where col1=%(col1)s" {"tbl": (tbl, "object"), "col1": col1})

Try https://docs.snowflake.net/manuals/sql-reference/identifier-literal.html with paramstyle='qmark ' mode. https://docs.snowflake.net/manuals/user-guide/python-connector-example.html#binding-data (Python format style won't work as the binding requires to be in the server)

I have not had a chance to add a test case for Identifier in Python Connector, but it should be available for all drivers that support ? placeholder binding. If it doesn't work, let us know.

A test case is being added: Here is a preview where conn_cnx is a wrapper of snowflake.connector.connect and db_parameters includes a set of connection parameters including the table name at name :

def test_binding_identifier(conn_cnx, db_parameters):
    Binding a table name
        with conn_cnx(paramstyle=u'qmark') as cnx:
            data = u'test'
            cnx.cursor().execute("""
create or replace table identifier(?) (c1 string)
""", (db_parameters['name'],))
        with conn_cnx(paramstyle=u'qmark') as cnx:
            cnx.cursor().execute("""
insert into identifier(?) values(?)
""", (db_parameters['name'], data))
            ret = cnx.cursor().execute("""
select * from identifier(?)
""", (db_parameters['name'],)).fetchall()
            assert len(ret) == 1
            assert ret[0][0] == data
    finally:
        with conn_cnx(paramstyle=u'qmark') as cnx:
            cnx.cursor().execute("""
drop table identifier(?)
""", (db_parameters['name'],))
          
For the original request, here is the solution:
self.cnx = snowflake.connector.connect(account=..., paramstyle='qmark')
self.cnx.cursor().execute("USE ROLE identifier(?)", (self.sf_role,))
          
@smtakeda could you please provide an example where the table name and some filter value need to be passed as param?
For example
SELECT *
FROM identifier(?)
WHERE ts BETWEEN '%s 00:00:00' AND %s 23:59:59'
LIMIT 10; 
In this example the table name is properly solved using paramstyle='qmark' but now the Date (as string) is not properly resolved. I've tried using identifier(?), ? and they didn't work neither.

Thanks!
          
@gonzalodiaz If you use paramstyle='qmark', %s is just a string placeholder and not binding parameter, so those should added as parameters of python string, e.g., cursor.execute("... WHERE ts BETWEEN %s 00:00:00' AND '%s 23:59:59'".format(v1,v1), (<binding values>)) where v1 = '2019-05-05'. Btw, a single quote is missing right before the second %s.
          
This is an old thread but I think I figured it out--the parameter passed in needs to be either a tuple or a dictionary. So the original code snippet should be:

self.cnx.cursor().execute("USE ROLE %s", (self.sf_role,))