Python安全的SQL查询

Question

有人告诉我，直接对游标进行写操作是一个严重的SQL漏洞，任何人都可以轻易地导出我的数据库……我该如何安全地进行SQL操作呢？

import psycopg2
import web


urls = (
    "/", "Index",
    "/questlist", "Questlist"
)

web.config.debug = True
app = web.application(urls, globals())

render = web.template.render("templates/", base="layout")

con = psycopg2.connect(
                database = "postgres",
                user = "postgres",
                password = "balloons",
                port = "55210502147432"
                    )

class Index(object):
    def __init__(self):
        pass

    def GET(self):
        return render.index()

class Questlist(object):
    def __init__(self):
        pass

    def GET(self):
        try:
            c = con.cursor()
            c.execute("SELECT quest_title, quest_difficulty, quest_post FROM quest_list")
            questlist = c.fetchall()

            return render.quest(Quests = questlist)

        except psycopg2.InternalError as e:
            con.rollback()
            print e
            return "Session error"

        return "wtf did u do,? u really busted her"

    def POST(self):
        form = web.input(quest_title="", quest_difficulty="", quest_post="")
        if len(form.quest_title) + len(form.quest_difficulty) + len(form.quest_post) > 50:
            return "Too many characters submitted"
        try:
            c = con.cursor()
            c.execute("INSERT INTO quest_list (quest_title, quest_difficulty, quest_post) \
                VALUES (%s, %s, %s)", (form.quest_title, form.quest_difficulty, form.quest_post))

            con.commit()

        except psycopg2.InternalError as e:
            con.rollback()
            print e

        except psycopg2.DataError as e:
            con.rollback()
            print e
            return "invalid data, you turkey"

        return render.index()


if __name__ == "__main__":
    app.run()

我现在担心的SQL代码是：

c.execute("INSERT INTO quest_list (quest_title, quest_difficulty, quest_post) \
  VALUES (%s, %s, %s)", (form.quest_title, form.quest_difficulty, form.quest_post))

这是我目前使用这个代码的网站： http://rpg.jeffk.org/questlist

欢迎随意尝试破解它

Answer 1

c.execute("INSERT INTO quest_list (quest_title, quest_difficulty, quest_post) \
  VALUES (%s, %s, %s)", (form.quest_title, form.quest_difficulty, form.quest_post))

这没问题……你正在使用Python SQL库里自带的格式字符串，这样可以避免注入问题。

c.execute("INSERT INTO quest_list(quest_title, quest_difficulty, quest_post)\
          VALUES (%s, %s, %s)"%(form.quest_title, form.quest_difficulty, form.quest_post))

如果你只是用普通的字符串格式化，那可能会有安全隐患，因为这不是SQL的安全机制。

在使用普通字符串格式化时，要考虑以下用户输入。

form.quest_post = "1);SELECT * FROM USERS;//"

这可能会让他们获取你整个用户表的信息，因为它会被传递成。

c.execute("INSERT INTO quest_list(quest_title,quest_dificulty,quest_post)\
           VALUES (something_benign,something_else,1);SELECT * FROM USERS;//)")

希望你能意识到这是一个有问题的语句……或者他们可能会改你的管理员密码之类的……