Python cx_Oracle Update

Question

In my Python code when I ask the user to input a string to SELECT, it works but when I try the UPDATE using the same input doesn't allow me to execute

Here is my code after the connection has been successfully done

curs = connection.cursor()
str_input1 = str(input("Input : "))
str_input2 = str(input("Input : "))
statement = "UPDATE table SET variable1 = "+str_input1+" WHERE name = "+str_input2
curs.execute(statement)
connection.commit

In theory this following code should work and update the variable, but instead I get the error at line curs.execute(statement) saying

cx_Oracle.DatabaseError: ORA-00904: John: invalid identifier

John was the str_input2 for where clause

Maybe its the format that was giving me an error but I'm not too sure.

Can someone point out what was the problem with my code?

Answer 1

The error is because you're not quoting the values. You'd get the exact same error from a SELECT statement.

These statements search for rows where the name column matches the string John:

SELECT * FROM table WHERE name = "John"
UPDATE table SET variable1 = "Hi" WHERE name = "John"

These statements search for rows where the name columns matches the John column—and if there is no John column, that's an error:

SELECT * FROM table WHERE name = John
UPDATE table SET variable1 = "Hi" WHERE name = John

So, you could fix this by just putting quotes around the values.

But you really, really, really shouldn't. This opens you up to SQL injection attacks, and stupid bugs where you don't quote or escape special characters properly, and performance problems where the database engine can't tell that you're running the same query over and over, and so on.

What you want to do is to use SQL parameters, instead of trying to format the string. I don't remember which parameter style cx_Oracle uses, but you can just import cx_Oracle; print(cx_Oracle.paramstyle), and look it up in the table to find out. And then do something like:

statement = "UPDATE table SET variable1 = :v WHERE name = :n"
curs.execute(statement, {'v': str_input1, 'n': str_input2})

---

Also, a few side notes:

• connection.commit doesn't do anything; you're just referencing the commit method, not calling it. You need parentheses: connection.commit()
• str(input()) is pointless. The input function always returns a string, so there's no reason to call str on it. (Unless you're using Python 2.x, in which case you should be using raw_input(), which returns a string, instead of using input to eval the string—opening up the same kinds of security problems as the SQL injection attack above—only to convert it back to a string.)