使用python中的变量进行SQL查询 [英] 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.

这是我的代码.

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>

我还应该使用 OR 而不是 AND 以便如果用户没有完成该测验，它将显示用户所做的任何测验.或者，如果很多人都做了一个测验，那么老师会看到每个人，例如，做了测验 1?

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?

推荐答案

你应该使用 SQL 参数:

You should use SQL parameters:

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

? 占位符将被您的值替换，自动引用以防止 SQL 注入攻击(和操作错误).

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

引用 sqlite3 模块文档:

Quoting from the sqlite3 module documentation:

通常您的 SQL 操作需要使用 Python 变量中的值.你不应该使用 Python 的字符串操作来组装你的查询，因为这样做是不安全的；它使您的程序容易受到 SQL 注入攻击(请参阅 http://xkcd.com/327/可能出错的示例).

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).

相反，使用 DB-API 的参数替换.将 ? 作为占位符放在任何你想使用值的地方，然后提供一个值元组作为游标的 execute() 方法的第二个参数.(其他数据库模块可能使用不同的占位符，例如 %s 或 :1.)

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.)

如果此查询未返回结果，请使用单独的查询向数据库询问其他测验；如果您使用 OR 而不是 AND 否则，您将获得 其他 用户已经完成的测验结果，和这个用户已经完成的任何事情.

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.

这篇关于使用python中的变量进行SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！