How do I use a python variable in a SQLITE SELECT statement

Question

So i was trying to use a local varibale made in python to be used in my SQL statement but I get the error sqlite3.OperationalError: unrecognized token: "1AM"

    if schchoice==int("1"):
                            schchoice=str("12AM")
                    elif schchoice == int("2"):
                            schchoice="1AM"
                    else:
                        print("INVALID")


                    if schchoice2==int("1"):
                        schchoice=str("12AM")
                    elif schchoice2==int("2"):
                        schchoice2="1AM"
                    else:
                            print("INVALID")
                    conn = sqlite3.connect('Employee.db')
                    c = conn.cursor()
                    def read_from_db():
                                c.execute("SELECT FName,LName, monstarthour, monendhour FROM MondayHours WHERE monstarthour ='"+schchoice+"'AND monendhour='"+schchoice2+'"')
                                #data = c.fetchall()
                                for row in c.fetchall():
                                    print(row)
                    read_from_db()

Don't concatenate string variables with query strings. You're liable to SQL Injection. — cs95
– cs95, Commented Mar 5, 2018 at 4:20
Use string.format() instead: c.execute("SELECT FName,LName, monstarthour, monendhour FROM MondayHours WHERE monstarthour='{}' AND monendhour='{}'".format(schoice, schoice2)) — accdias
– accdias, Commented Mar 5, 2018 at 4:43

sjw · Accepted Answer · 2018-03-05 12:30:16Z

1

As mentioned in the comments, you should never use string manipulation to insert variable values into your SQL query, or else you are vulnerable to SQL injection. The safe way to achieve what you are trying to do is to pass in values as parameters to your SQL engine, which will do all of the necessary escaping for you. In your case, something like this should work:

params = (schchoice, schchoice2)
c.execute("SELECT FName,LName, monstarthour, monendhour FROM MondayHours WHERE monstarthour = ? AND monendhour = ?", params)

answered Mar 5, 2018 at 12:30

sjw

6,5512 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I use a python variable in a SQLITE SELECT statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related