Thread: SQL injection in a ~ or LIKE statement
Hello,
I'm concerned about whether the usual parameter escaping mechanism is enough in a LIKE or regular expression search.
I run a recent Postgres version and use the Python connector psycopg2 for a web application. I understand that if I
alwaysescape as in
dBres=dBcsr.execute('SELECT docText FROM documents WHERE name=%(storyName)s',{'storyName':storyName})
then I am doing the right thing. Suppose now that I want to search the text of those documents? I have been unable to
findif I need to anything more for a LIKE or regex search, and also unable to find any assurance that it is enough.
(Nodoubt I've not looked in the right place; sorry.)
I plan to add full text searching also; is the escaping mechanism enough there?
Thank you for your help,
Jim
On Oct 20 05:07, hefferon9@adelphia.net wrote:
> I'm concerned about whether the usual parameter escaping mechanism is
> enough in a LIKE or regular expression search.
>
> I run a recent Postgres version and use the Python connector psycopg2
> for a web application. I understand that if I always escape as in
>
> dBres=dBcsr.execute('SELECT docText FROM documents WHERE
> name=%(storyName)s',{'storyName':storyName})
>
> then I am doing the right thing.
Please pay attention that [IIRC] psycopg2 uses its own escaping
mechanism. Therefore, you should better ask this question on psycopg2
ml.
> I plan to add full text searching also; is the escaping mechanism
> enough there?
If I were you, I'd ask psycopg2 developers to implement parameters that
are natively supported by PostgreSQL. With parameters, you won't mess up
with any escaping or injection related issue.
Regards.
On Sunday 22 October 2006 12:32, Volkan YAZICI wrote:
> On Oct 20 05:07, hefferon9@adelphia.net wrote:
> > I'm concerned about whether the usual parameter escaping mechanism is
> > enough in a LIKE or regular expression search.
> >
> > I run a recent Postgres version and use the Python connector psycopg2
> > for a web application. I understand that if I always escape as in
> >
> > dBres=dBcsr.execute('SELECT docText FROM documents WHERE
> > name=%(storyName)s',{'storyName':storyName})
> >
> > then I am doing the right thing.
>
> Please pay attention that [IIRC] psycopg2 uses its own escaping
> mechanism. Therefore, you should better ask this question on psycopg2
> ml.
>
> > I plan to add full text searching also; is the escaping mechanism
> > enough there?
>
> If I were you, I'd ask psycopg2 developers to implement parameters that
> are natively supported by PostgreSQL. With parameters, you won't mess up
> with any escaping or injection related issue.
psycopg2 supports parameters which are escaped properly.
Uwe
--
Open Source Solutions 4U, LLC 1618 Kelly St
Phone: +1 707 568 3056 Santa Rosa, CA 95401
Cell: +1 650 302 2405 United States
Fax: +1 707 568 6416
On Oct 22 02:33, Uwe C. Schroeder wrote: > On Sunday 22 October 2006 12:32, Volkan YAZICI wrote: > > If I were you, I'd ask psycopg2 developers to implement parameters that > > are natively supported by PostgreSQL. With parameters, you won't mess up > > with any escaping or injection related issue. > > psycopg2 supports parameters which are escaped properly. You're wrong. psycopg uses Python style parameters and escapes the specified input before inserting into query string. See documentation of PQexecParams() for the "parameters" I mentioned. I think, you're confusing terms. I just checked psycopg2 source code and couldn't see any parameter implementation. Regards.
psycopg2 supports parameters which are escaped properly.
adding: Judging from the mails of Frederico, developer of psycopg2, he was also in the "early notify circle" of the 8.13->8.14 escaping improvement. So, if done correctly the DB API way, all escaping with psycopg2 is fine.
Harald
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.
On Mon, Oct 23, 2006 at 07:58:30AM +0200, Harald Armin Massa wrote: > adding: Judging from the mails of Frederico, developer of psycopg2, he was > also in the "early notify circle" of the 8.13->8.14 escaping improvement. > So, if done correctly the DB API way, all escaping with psycopg2 is fine. On the psycopg2 list Frederico writes that, technically, psycopg2 currently uses PQEscapeStringConn and he plans on adding out-of-query bind parameter support at some point in the future. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346