3

I'm using cx_Oracle in Python and can't get a variable be used as table name, like in this simple example:

query = "select * from some.:usertable.userinfo"

bindvars = {'usertable':usertable}

cursor.execute(query, bindvars)

What is the correct syntax? Variable substition works fine when I use WHERE… etc. but not with table names. I guess I have to separate ":usertable" somehow…

tamasgal
  • 24,826
  • 18
  • 96
  • 135
  • 2
    Database adapters rarely support using parameters for anything that isn't a 'value' (something that needs quoting). Either use string formatting (dodgy, you run the risk of a sql injection) or use a library like SQLAlchemy that let's you produce valid SQL using Python code. – Martijn Pieters Oct 29 '12 at 15:28
  • @septi:I think using table name as bind variable is not possible ,you need to create a dynamic statement ,by appending the table name to youe select statement. – Gaurav Soni Oct 29 '12 at 15:32
  • OK thank's, at least I can stop wasting my time ;-) Could you add an answer, so I can accept it? – tamasgal Oct 29 '12 at 15:34

3 Answers3

4

Database adapters rarely support using parameters for anything that isn't a 'value' (something that needs quoting). Either use string formatting (dodgy, you run the risk of a sql injection) or use a library like SQLAlchemy that let's you produce valid SQL using Python code.

If you are certain your usertable value is sane (checked against a list of existing table names, for example), the following would work:

query = 'select * from some.{usertable}.userinfo'.format(usertable=usertable)
Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
2

You cannot bind an object name in Oracle, only a literal. Oracle does, however, have an inbuilt package dbms_assert, to help prevent SQL injection when using dynamic object names. The most useful function in your case is probably sql_object_name, which:

"... verifies that the input parameter string is a qualified SQL identifier of an existing SQL object."

For instance you could do the following in cx_Oracle.

object_name = cursor.callfunc('sys.dbms_assert.sql_object_name'
                             , cx_Oracle.string, ['usertable'])

It raises ORA-44002, if the name is invalid, which you can capture in cx_Oracle, or if everything's fine continue as Martijn has suggested.

I would recommend reading Oracle's guide to guarding against SQL injection.

Ben
  • 51,770
  • 36
  • 127
  • 149
0

Perhaps it's a bit late to reply, but I was dealing with the same thing 2 days ago.

The solution is, as Martjin says, to format the query.

query = f'select * from {tableName}'

Hope it helps someone as it helped me.

Michi Salazar
  • 136
  • 2
  • 6