PostgreSQL › PostgreSQL - general

to_tsquery, plainto_... avoiding bad input, injections. Is there a builtin function for this ? Escaping?

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

6 messages Options

Moe-25

to_tsquery, plainto_... avoiding bad input, injections. Is there a builtin function for this ? Escaping?

Hi, I am wondering whether or not there exists any built in function for making sure a query/textinput is not harmful or one that escapes them. If not, what kind of things should I watch out for ?

As of now, I get errors on the quote ( ' ) if it is entered in an input and in to_tsquery also on space. What other tokens should I be careful about? How should I handle these ? How do I escape them ?

When fulltext indexing my text, is there any risk that the text being indexed could be harmful if it contains certain characters ?

/ Moe

Moe-25

Re: to_tsquery, plainto_... avoiding bad input, injections. Is there a builtin function for this ? Escaping?

..... any one?

On Wed, Jan 7, 2009 at 8:07 PM, Mohamed < > wrote:

Hi, I am wondering whether or not there exists any built in function for making sure a query/textinput is not harmful or one that escapes them. If not, what kind of things should I watch out for ?

As of now, I get errors on the quote ( ' ) if it is entered in an input and in to_tsquery also on space. What other tokens should I be careful about? How should I handle these ? How do I escape them ?

When fulltext indexing my text, is there any risk that the text being indexed could be harmful if it contains certain characters ?

/ Moe

regme please

Re: to_tsquery, plainto_... avoiding bad input, injections. Is there a builtin function for this ? Escaping?

Maybe I'm missing the point, but have read about quote_ident() and
quote_literal() at chapter 9.4 "String Functions and Operators"?

BR

--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

On Thursday 08 January 2009 09:52:29 Mohamed wrote:

> ..... any one?
>
> On Wed, Jan 7, 2009 at 8:07 PM, Mohamed < > wrote:
> > Hi, I am wondering whether or not there exists any built in function for
> > making sure a query/textinput is not harmful or one that escapes them. If
> > not, what kind of things should I watch out for ?
> > As of now, I get errors on the quote ( ' ) if it is entered in an input
> > and in to_tsquery also on space. What other tokens should I be careful
> > about? How should I handle these ? How do I escape them ?
> >
> > When fulltext indexing my text, is there any risk that the text being
> > indexed could be harmful if it contains certain characters ?
> >
> > / Moe

--
Sent via pgsql-general mailing list ( )
To make changes to your subscription:

Christopher Swingley-2

Re: to_tsquery, plainto_... avoiding bad input, injections. Is there a builtin function for this ? Escaping?

Greetings!

> Wed, Jan 7, 2009 at 8:07 PM, Mohamed < >
> > Hi, I am wondering whether or not there exists any built in
> > function for making sure a query/textinput is not harmful or one
> > that escapes them. If not, what kind of things should I watch out
> > for ?
>
> * Reg Me Please < > [2009-Jan-08 00:20 AKST]:
> Maybe I'm missing the point, but have read about quote_ident() and
> quote_literal() at chapter 9.4 "String Functions and Operators"?

quote_literal() does seem like a good choice for getting the quoting
correct. As far as protecting yourself from SQL injection attacks, you
may want to look at the options available in the programming language
you are using to get user input. In Python, for example, you can run
queries as follows:

parameters = (12, "bar", True)
query = "INSERT INTO foo VALUES (%d, %s, %s);"
cursor.execute(query, parameters)
cursor.commit()

Python fills the '%X' fields with the parameters after verifying they
are safe. Probably best to test how much protection this offers.

I believe the risk isn't so much a question of quoting or special
characters, but carefully crafted input variables. For example, what if
the second parameter was:

"'bar', True); DELETE FROM foo; INSERT INTO foo VALUES (1, 'bar',"

Cheers,

Chris
--
Christopher S. Swingley
http://swingleydev.com/
< >

--
Sent via pgsql-general mailing list ( )
To make changes to your subscription:

Moe-25

Re: to_tsquery, plainto_... avoiding bad input, injections. Is there a builtin function for this ? Escaping?

Yeah, would Python protect you from that ? I am using Groovy on Grails and not sure how these things work here. Most of the time I use GORM to do my queries, but now I am stuck with SQL because of fulltext search with Postgres. Perhaps there is some similiar things in Groovy to run, I will check into that.

/ Moe

On Thu, Jan 8, 2009 at 5:20 PM, Christopher Swingley < > wrote:

Greetings!

> Wed, Jan 7, 2009 at 8:07 PM, Mohamed < >

> > Hi, I am wondering whether or not there exists any built in
> > function for making sure a query/textinput is not harmful or one
> > that escapes them. If not, what kind of things should I watch out
> > for ?
>

> * Reg Me Please < > [2009-Jan-08 00:20 AKST]:

> Maybe I'm missing the point, but have read about quote_ident() and
> quote_literal() at chapter 9.4 "String Functions and Operators"?

quote_literal() does seem like a good choice for getting the quoting
correct. As far as protecting yourself from SQL injection attacks, you
may want to look at the options available in the programming language
you are using to get user input. In Python, for example, you can run
queries as follows:

parameters = (12, "bar", True)
query = "INSERT INTO foo VALUES (%d, %s, %s);"
cursor.execute(query, parameters)
cursor.commit()

Python fills the '%X' fields with the parameters after verifying they
are safe. Probably best to test how much protection this offers.

I believe the risk isn't so much a question of quoting or special
characters, but carefully crafted input variables. For example, what if
the second parameter was:

"'bar', True); DELETE FROM foo; INSERT INTO foo VALUES (1, 'bar',"

Cheers,

Chris
--
Christopher S. Swingley
http://swingleydev.com/
< >

--
Sent via pgsql-general mailing list ( )
To make changes to your subscription:

Will Rutherdale (rutherw)

Re: to_tsquery, plainto_... avoiding bad input, injections. Is there a builtin function for this ? Escaping?

I’m not familiar with Python, but Ihave used the Perl DBI library for a long time. The DBI library gives you a databasespecific quote() function, and also something much stronger: prepare() andexecute().

This works well with most applications,but I’m not sure how it would tie into fulltext queries.

-Will

From: [mailto: ] On Behalf Of Mohamed
Sent: 8 January 2009 11:33
To:
Subject: Re: [GENERAL] to_tsquery,plainto_... avoiding bad input, injections. Is there a builtin function forthis ? Escaping?

Yeah, would Python protect you from that ? I am using Groovy on Grailsand not sure how these things work here. Most of the time I use GORM to do myqueries, but now I am stuck with SQL because of fulltext search with Postgres.Perhaps there is some similiar things in Groovy to run, I will check into that.

/ Moe

On Thu, Jan 8, 2009 at 5:20 PM, Christopher Swingley < > wrote:

Greetings!

> Wed, Jan 7, 2009 at 8:07 PM, Mohamed < >

> > Hi, I am wondering whether or not there exists any built in
> > function for making sure a query/textinput is not harmful or one
> > that escapes them. If not, what kind of things should I watch out
> > for ?
>

> * Reg Me Please < >[2009-Jan-08 00:20 AKST]:

> Maybe I'm missingthe point, but have read about quote_ident() and
> quote_literal() at chapter 9.4 "String Functions and Operators"?

quote_literal() does seem like a good choice for getting the quoting
correct. As far as protecting yourself from SQL injection attacks, you
may want to look at the options available in the programming language
you are using to get user input. In Python, for example, you can run
queries as follows:

parameters = (12, "bar", True)
query = "INSERT INTO foo VALUES (%d, %s, %s);"
cursor.execute(query, parameters)
cursor.commit()

Python fills the '%X' fields with the parameters after verifying they
are safe. Probably best to test how much protection this offers.

I believe the risk isn't so much a question of quoting or special
characters, but carefully crafted input variables. For example, what if
the second parameter was:

"'bar', True); DELETE FROM foo; INSERT INTO foo VALUES (1,'bar',"

Cheers,

Chris
--
Christopher S. Swingley
http://swingleydev.com/
< >

--
Sent via pgsql-general mailing list ( )
To make changes to your subscription: