How should I prevent abuse when using web.py's web.database?

Question

I'm writing a quick web.py app and take data from web.input...

import web
urls = (
    '/', 'something',
)
app = web.application(urls, globals())
db = web.database(dbn='postgres', db='database', user='username', password='password', host='127.0.0.1')
class something:
    def GET(self):
        i = web.input()
        return db.select('foo.table', where="column=$variable", vars={'variable':i.input, })
if __name__ == "__main__": app.run()

Should I worry about passing i.input to db.select (or query etc.) as I do as part of vars? SQL injection possibilities etc. ?

---

Edit: I have been playing around with this myself, trying to get something nasty happenning. Playing with quoting for example, http://localhost:8080/?id=13' or 'x' ='x results in nicely escaped sql being shown in Exceptions:

<sql: 'select * from foo.table where id = "13\' or \'x\'=\'x"'>

I've tried a few other common tests that the internet puts forward and think I'm quite happy web.py is dealing with the sanitisation... Would anyone else be able to comment?

Answer 1

http://webpy.org/cookbook/query says:

To prevent SQL injection attacks, db.query also accepts the "vars" syntax as described in db.select:

results = db.query("SELECT * FROM users WHERE id=$id", vars={'id':10})

This will escape user input, if you're trusting them for the "id" variable.

So I guess its as simple as that.

Of course I realise that I still need to validate user input if I'm going to be inserting it places...

Answer 2

The "nicely escaped sql" you see does not matter as it's never sent to the database engine.

In all cases, unless you manually insert the values into the SQL request string, you're safe against an SQL injection. This includes select/insert/update/delete methods as well as the $variable_name substitution style. In both cases the SQL request is not fully assembled as text, but properly converted into a SQL prepared statement and compiled by the DB engine as such. Only after that the parameters are actually substituted for statement execution. So, unless you build the SQL query string and/or parts of it by hand using the untrusted data, you're safe.

Unfortunately I'm unable to provide a link to any source better than the source code of the module as it was my only source of information.