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 库中的格式字符串来避免注入(inject)问题

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;//)")

希望你能认出这是一个有问题的陈述......或者他们可以更改你的管理员密码或其他......