How to use multiple where varibles in my sql select statement in python?

Question

Question I have a simple application to query some of my oracle tables I want to be able to grab the value of my text boxes which work but, I keep getting this error any help would be greatly appreciated!

test.py

def grabEnts():
 global date 
 connection = cx_Oracle.connect('xxxxxxxx/[email protected]:xxxx/xxx')
 cursor = connection.cursor()
 startdate = e1.get()
 enddate = e2.get()
 #fs = datetime.date(startdate)
 #fe = datetime.date(endate)
 //this works but, I would like to use both startdate and endate
 #cursor.execute("SELECT EMP_ID FROM TO_ENTRIES WHERE LEAVE_START >= '%s'" % startdate)
 //Like this but I can't get it to work I keep getting this error 
 File "test.py", line 62, in grabEnts
 cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START >=%s AND LEAVE_END <=%s', startdate, enddate)
 TypeError: function takes at most 2 arguments (3 given)
 cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START >=%s AND LEAVE_END <=%s', startdate, enddate)
 for row in cursor:
 outputent.writerow(row)
 cursor.close()
 connection.close()

• W.Prins: How many parameters are you passing to cursor.execute()? How many does it expect? What is therefore wrong? ;-) By the way, and I can't stress this enough, in general inserting user input directly into SQL opens you up to a so-called SQL injection attack and should therefore be avoided like the plague. You should instead use parameterized queries. See here: bobby-tables.com

Answer 1

When providing multiple values for a format string, you need to wrap them in parentheses:

"My name is %s. Your name is %s." % ("Sam", "Joe")

• Snowman288: Okay so how would I do this then ('SELECT * FROM TO_ENTRIES WHERE LEAVE_START >= %s AND LEAVE_END <= %s' % (startdate, enddate)) because this will not work either ... I get this error NameError: global name 'stardate' is not defined @TomKarzes
• Snowman288: I tried it this way as well and got this error cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START >=%s AND LEAVE_END <=%s' % ("stardate", "enddate")) DatabaseError: ORA-00904: "ENDDATE": invalid identifier @TomKarzes
• Tom Karzes: Did you type "startdate" or "stardate"? Make sure you spell it correctly.
• Tom Karzes: It looks like you're doing the right thing, you just need to type your variable names correctly.
• Tom Karzes: And no, don't put the variable names in quotes.

Answer 2

Using string formatting to generate SQL is general a bad idea. If a user inputs something like ; DROP TABLE blah this will be executed in your code. This is a typical example of SQL injection...

To avoid, use parameters in your query, like so:

cursor.execute('SELECT EMP_FIRST_NAME FROM TO_ENTRIES WHERE LEAVE_START between :start AND :end', {'start': 1, 'end': 10})