CODEWITHSUNDEEP
pythonsqlsqlitevariables
user3560090
user3560090

Reputation: 11

SQL query with variables in python

I'm making a program that is a user interface for quizes set by teachers in a primary school. I am trying this query which is using data typed in by the user on the previous page. it is looking for people in the database who match the username and quiz number concerned. this is so the teacher can see how well pupils are doing on certain quizes.

Here is my code.

dbDatabase = sqlite3.connect('c:\\xampp\\cgi-bin\\MakingATable.db')
cuDatabase = dbDatabase.cursor()

Fieldstorage = cgi.FieldStorage() #what you typed in on the webpage

Quizno = Fieldstorage.getvalue("quizno")
UserID = Fieldstorage.getvalue("username")

#print (Quizno)
#print (UserID)

cuDatabase.execute ("""
    SELECT Result
    FROM resultstable
    WHERE QuizID = '""" + str(Quizno) + """
    AND UserID = '""" + UserID + "'")

for (row) in cuDatabase:
    print (row)

dbDatabase.commit()
cuDatabase.close()

Here is the error message i am getting when i run my webpage:

     40         FROM resultstable
     41         WHERE QuizID = '""" + str(Quizno) + """
=>   42         AND UserID = '""" + UserID + "'")
     43 
     44     for (row) in cuDatabase:
AND undefined, UserID = 'HuPa1'


OperationalError: near "HuPa1": syntax error 
      args = ('near "HuPa1": syntax error',) 
      with_traceback = <built-in method with_traceback of OperationalError object>

Also should I use an OR instead of AND so that if the user hasn't done that quiz it will display any quiz the user does. or so that if lots of people have done one Quiz then the teacher will see everyone who, for example, have done quiz 1?

Upvotes: 1

Views: 29393

Answers (2)

Martijn Pieters
Martijn Pieters

Reputation: 1121486

You should use SQL parameters:

cuDatabase.execute ("""
    SELECT Result
    FROM resultstable
    WHERE QuizID = ?
    AND UserID = ?""", (Quizno, UserID))

The ? placeholders will be replaced by your values, automatically quoted to prevent SQL injection attacks (and operational errors).

Quoting from the sqlite3 module documentation:

Usually your SQL operations will need to use values from Python variables. You shouldn’t assemble your query using Python’s string operations because doing so is insecure; it makes your program vulnerable to an SQL injection attack (see http://xkcd.com/327/ for humorous example of what can go wrong).

Instead, use the DB-API’s parameter substitution. Put ? as a placeholder wherever you want to use a value, and then provide a tuple of values as the second argument to the cursor’s execute() method. (Other database modules may use a different placeholder, such as %s or :1.)

Use a separate query to ask the database for other quizzes if this query doesn't return a result; if you use OR instead of AND otherwise, you will get both quizz results that other users have done, and anything this user has completed.

Upvotes: 7

Michał Niklas
Michał Niklas

Reputation: 54292

The best solution is to use prepared statement:

cuDatabase.execute("SELECT Result FROM resultstable WHERE QuizID=? AND UserID=?", Quizno, UserID)

In prepared statement mode all variables are replaced by question marks in query text and are parameters to execute(). But not all databases or db drivers support it. Sometimes instead of question mark you will have to use %s (one of PostgreSQL drivers works this way).

Worse, but working solution is to use Python % operator, but with this solution you will have to use your own quote() function that escapes dangerous characters that may appear in data (prevents from SQL injection):

cuDatabase.execute("SELECT Result FROM resultstable WHERE QuizID='%s' AND UserID='%s'" % (quote(Quizno), quote(UserID)))

Upvotes: 0

Related Questions