User input variables in cx_Oracle?

Question

I'm using cx_Oracle to access our database. I would like the user to be able to input the station ID, for example:

stationID=(whatever the user inputs upon prompting)

cursor.execute('''select cruise, station, stratum
          from union_fscs_svsta
          where station=stationID
          order by cruise''')

Because the statement needs to be a string, how do I incorporate a user-defined variable?

Dyeing

Are you asking how to do it safely, or just how to do it? (Never trust user input... SQL injection, etc.) – Greece

This is true, re: input... is there a safe (safer) way to do it? – Dyeing

Answer 1

How not to do it:

id = raw_input("Enter the Station ID")
query = "select foo from bar where station={station_id}"
cursor.execute(query.format(station_id=id))

If someone enters a malicious sql string, it will be executed.

Instead of using python to format the string, let the database backend handle it for you. Exactly how you do this depends on the database you're using. I think (?) this is correct for Oracle, but I can't test it. Some databases use different characters (e.g. ? instead of %s in the case of SQLite).

id = raw_input("Enter the Station ID")
query = "select foo from bar where station=%s"
cursor.execute(query, [id])

Edit: Apparently, cx_Oracle defaults to a "named" paramstyle (You can check this by having a look at cx_Oracle.paramstyle.). In that case, you'd do something like this:

query = "select foo from bar where station=:station_id"
cursor.execute(query, station_id=id)

Greece

This didn't work for me - I had to submit the parameters as a dictionary - cursor.execute(query, {'station_id':id}). Otherwise, spot on, thanks! – Joettajoette

You can use positional parameters as ":0", ":1", etc. with cx_Oracle. – Polyglot

Can u give an example @Polyglot select * from :0.:1 if 0 is schemaname and 1 is tablename – Anurous