[DB-SIG] Including variables in SQL queries

[Jekabs Andrushaitis]

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

Most commonly used are Python-style and positional bind variables.
Advantage in using bind variables is that their values are not actually
inserted into SQL, but passed to DB engine directly, thus allowing to
use any values for the variables. Without that one has to eliminate or
prefix all the special symbols from string variables.

Python-style syntax:
	args=3D {"a":"a","b":1}
	sql=3D "select a,b from c where a=3D%(a)s and b=3D%(b)d"
	sqlc.execute(sql,args)

Positional style syntax:
	args=3D ("a",1)
	sql=3D "select a,b from c where a=3D:1 and b=3D:2"
	sqlc.execute(sql,args)

If the values of fields used in SQL statements are user-supplied, it
is highly recommended to use bind variables and not insert the values
of string constants directly into SQL statements, as it might become
potential security hole, point of failure etc :)

Example:
	username=3D "asd';delete from users;commit;select name from users where
name=3D'aa"
	sql=3D "select password from users where name=3D'%s'" % username
	sqlc.execute(sql)

This would execute a statement:
	select password from users where name=3D'asd';delete from users;commit;s=
elect
name from users where name=3D'aa'

For more details you might want to look into your databases Python module
docs!

P.S. Sorry if some of this is incomplete or innacurate :)

Jekabs Andrushaitis
Senior system analyst
Tieto Konts Financial Systems Ltd.
41 Lacplesa Str.
Riga, LV-1011, Latvia
Tel: +371 7286660
Fax: +371 7243000
E-mail: j.andrusaitis@konts.lv

> -----Original Message-----
> From: db-sig-admin@python.org
> [mailto:db-sig-admin@python.org]On Behalf
> Of Turcotte, Paul
> Sent: pirmdiena, 2002. gada 18. mart=D7 17:05
> To: 'Hugh'
> Cc: 'db-sig@python.org'
> Subject: RE: [DB-SIG] Including variables in SQL queries
>
>
> Something like this may work for you:
>
> query =3D "select password from table where userid like '" +
> formid + "'"
> c.execute(query)
>
> No doubt there are many other ways to do the same, likely
> many of them more
> elegant than my newbie code, but it works for me.  Also, you
> can tweak it
> depending on what type formid is.
>
> Hope it helps,
> PT
> ________________________________
> Paul Turcotte
> Director of Computer Technology
> Brooklyn Botanic Garden
> paulturcotte@bbg.org
> 718.623.7232  |  fax 622.7839
>
>
> -----Original Message-----
> From: Hugh [mailto:h.e.w.frater@cs.cf.ac.uk]
> Sent: Saturday, March 17, 2001 4:43 PM
> To: db-sig@python.org
> Subject: [DB-SIG] Including variables in SQL queries
>
>
> Hi All,
> 	I got DCOracle2 installed and working a while back and am
> progressing with the project OK. What I want to know, is how
> to include
> variables in an SQL query. This is so I can select an
> encryted password from
> a table given the userID which is parsed using the cgi.py
> module into a
> variable in my python script. The code I've got is as
> follows: Note I have
> yet to try this because the tables are not yet finalised.
>
> formid =3D form.getvalue('id')
> .......
> c.execute("select password from tblborrower where userid =3D formid")
>
> I have a suspision that this won't work because it will try
> and use "formid"
> as the value for userid instead of the value of the string formid.
>
> I know there are probably better ways to make a secure login,
> but it's only
> a first year project, and it doesn't matter that much. Thanks
> in advance for
> any help. Hugh Frater
>
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig
>
> _______________________________________________
> DB-SIG maillist  -  DB-SIG@python.org
> http://mail.python.org/mailman/listinfo/db-sig