python secure SQL queries

Question

I was told that writting directly to a cursor is a serious SQL vunlerability and anyone could easily dump my DB... How can I securely do SQL stuff?

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

Here's the SQL im worried about:

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

here's the site right now that im using this on: http://rpg.jeffk.org/questlist

feel free to try and break it

Joran Beasley · Accepted Answer · 2014-04-23 03:47:52Z

4

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

this is fine ... you are using the format strings that are built into python SQL libraries to avoid injection issues

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

would be a potential security flaw as you are just using standard string formatting instead of the SQL mechanisms

when using standard string formatting consider the following user input

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

this would allow them to dump your whole user table as it would get passed as

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

which hopefully you can recognize as being a problematic statement ... or they could change your admin password or whatever ...

edited Apr 23, 2014 at 3:47

answered Apr 23, 2014 at 3:31

Joran Beasley

114k13 gold badges167 silver badges187 bronze badges

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

2 Comments

CrackSmoker9000 Over a year ago

thanks for your reply, before i posted this i was using format() for the variables in the sql query

Joran Beasley Over a year ago

format would not protect against injection at all ... so this other method that you posted in your question is the way to go ...

Collectives™ on Stack Overflow

python secure SQL queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related