[DB-SIG] Including variables in SQL queries
18 Mar 2002 17:24:32 -0000
Hugh <email@example.com> wrote:
[Variables in SQL - my favourite topic, apparently]
>formid = form.getvalue('id')
>c.execute("select password from tblborrower where userid = formid")
If you want to know the best and most secure method, just follow the advice at
the end of this message. Let's look at some of the suggested approaches:
"Arthur Albano" <firstname.lastname@example.org> wrote:
> >>>b="Hello %s" % (a)
Effectively, this is the same as...
b="Hello " + a
And using this basic principle, we have the following suggestion:
"Turcotte, Paul" <email@example.com> wrote:
>Something like this may work for you:
>query = "select password from table where userid like '" + formid + "'"
Apart from the frequently-arising encoding issues that emerge from using this
approach, there's a danger in using the above. Consider the following value for
"' or ''='"
Hopefully, any validation that might be present in an application would filter
this kind of thing out. Otherwise the value of query becomes...
"select password from table where userid like '" + \
"' or ''='" + \
"select password from table where userid like '' or ''=''"
What this query will do is to select all passwords from the table. That's
probably not what you want, unless you really want to allow users to retrieve
other people's passwords. In any case, there are other situations where such
statement manipulation will probably open large security holes.
"Jekabs Andrushaitis" <firstname.lastname@example.org> wrote:
>Most Python DB wrapper modules support "bind variables" in SQL (there
>are some exceptions - for example PostgreSQL module which is part of
>PostgreSQL distribution, but there are other PostgreSQL modules which
>do support bind variables:)
This is the "right answer". One must not get confused by the use of %s as a
paremeter marker in the DB-API, however. It is possible to construct a
statement using such notation, but remember that Python's % operator will not
be used. Moreover, it will never be necessary to put quotes in the statement
either (unless you're actually willing to hard-code certain constants, but even
then it's hardly necessary or desirable).
# For pyPgSQL - where %s is the parameter marker:
query = "select password from table where userid like %s"
# For Sybase modules, typically:
query = "select password from table where userid like ?"
Now, with a cursor:
# The second parameter is a sequence:
I hope this helps!
Get your firstname@lastname email at http://Nameplanet.com/?su