cursor.execute(query, params) error while trying to set table name

[cdonate]

I'm trying to dynamically set the table name on my queries. To avoid SQL Injection I'm using the option curso.execute(query, params).

When I try to set the table name I get the error:

[PARSE_SYNTAX_ERROR] Syntax error at or near ''my_table_name''(line 1, pos 14)

== SQL ==
SELECT * FROM 'my_table_name'
--------------^^^

To reproduce:

with sql.connect(server_hostname=self.hostname, http_path=self.path, access_token=self.token) as connection:
    with connection.cursor() as cursor:
         cursor.execute("SELECT * FROM %(table_name)s", {"table_name": "my_table_name"})
         result = cursor.fetchall()

         for row in result:
             print(row)

It seems the table name can't have quotes. Only way I can do this is with:

cursor.execute("SELECT * FROM {}".format("my_table_name"))

Or other unsafe string substitution.

Am I doing something wrong?

[susodapop]

I can confirm that, while disappointing, this is the expected behaviour of the parameter escaping.

Since the Spark thrift server doesn't support parameterised SQL (yet) the implementation makes a best effort to escape inputs as strings. But table names in Spark SQL cannot be string literals. The way parameter escaping works right now it will only write valid SQL as part of a WHERE clause. This is a consequence of trying to implement in the client what should instead be (and eventually will be) implemented by the server.

[cdonate]

Ok, that's a bummer. Thank you for the quick response.

I got the example from the code comments, look at:

databricks-sql-python/src/databricks/sql/client.py

Lines 460 to 462 in 258b961

	operation = "SELECT * FROM %(table_name)s"
	parameters = {"table_name": "my_table_name"}
	Will result in the query "SELECT * FROM 'my_table_name' being sent to the server

It is not technically wrong, since it does result in that SQL query, but it is an invalid query.

Do you think using SQLAlchemy would help me on this use case?

[susodapop]

Thanks for noting the bad example! I've opened a PR to fix that example to be more accurate.

It is not technically wrong, since it does result in that SQL query, but it is an invalid query.

Agreed.

Do you think using SQLAlchemy would help me on this use case?

It depends! If you're just using basic SQLAlchemy primitives like engine.execute(sqlalchemy.text("...query text..").bindparams(some_param=some_value)) then it will behave exactly like the raw DBAPI driver and will fail.

But if you are using SQLAlchemy's ORM capabilities to define your models ahead of time, then it will know how to write the table names without escaping.

For example:

class SampleObject1base):

    __tablename__ = "mySampleTable1"

    name = Column(String(255), primary_key=True)
    episodes = Column(Integer)
    some_bool = Column(BOOLEAN)

class SampleObject2base):

    __tablename__ = "mySampleTable2"

    name = Column(String(255), primary_key=True)
    episodes = Column(Integer)
    some_bool = Column(BOOLEAN)

for model in [SampleObject1, SampleObject2]:
   model.select().all()

SQLAlchemy will emit these statements:

• select * from mySampleTable1
• select * from mySampleTable2