Обсуждение: 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.

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