[DB-SIG] Including variables in SQL queries

[paul@boddie.net]

Hugh <h.e.w.frater@cs.cf.ac.uk> 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" <arthuralbano@hotmail.com> wrote:
>
>Maybe this:
>
> >>>a="World!"
> >>>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" <paulturcotte@bbg.org> wrote:
>
>Something like this may work for you:
>
>query = "select password from table where userid like '" + formid + "'"
>c.execute(query)

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 
formid:

  "' 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 ''='" + \
  "'"

...which becomes...

  "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" <j.andrusaitis@konts.lv> 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 example:

  # 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:
  cursor.execute(query, (formid,))

I hope this helps!

Paul

-- 
Get your firstname@lastname email at http://Nameplanet.com/?su