pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
security: escaping user-supplied data
8+ messages / 5 participants
[nested] [flat]

* security: escaping user-supplied data
@ 1999-10-12 00:31 Jason Uhlenkott <jpu31@uhlenkott.net>
  1999-10-12 09:53 ` Re: [SQL] security: escaping user-supplied data Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 1 reply; 8+ messages in thread

From: Jason Uhlenkott @ 1999-10-12 00:31 UTC (permalink / raw)
  To: pgsql-sql

I am writing a C program that will collect data from various untrusted
sources, and put it in a postgresql database by PQexec'ing an INSERT
INTO statement.

The statements I generate are usually of the form:
INSERT INTO foo (bar, bas) VALUES ('abc', 'def');
but the 'abc' and 'def' come from an untrusted source, so if they supply
a string like "def'); delete from foo; '" they can make me do this:
INSERT INTO foo (bar, bas) VALUES ('abc', 'def'); delete from foo; '');

What do I need to do to prevent this?  My current plan is to prepend a
backslash to every single-quote, backslash, and semicolon in the
untrusted string.  Are there any other special characters I should watch
out for?  Is it possible to do something evil despite your special
characters being prepended with a backslash?



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] security: escaping user-supplied data
  1999-10-12 00:31 security: escaping user-supplied data Jason Uhlenkott <jpu31@uhlenkott.net>
@ 1999-10-12 09:53 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1999-10-12 12:22   ` Re: [SQL] security: escaping user-supplied data Albert REINER <areiner@tph.tuwien.ac.at>
  1999-10-13 20:54   ` Re: [SQL] security: escaping user-supplied data tjk@tksoft.com <tjk@tksoft.com>
  0 siblings, 2 replies; 8+ messages in thread

From: Herouth Maoz @ 1999-10-12 09:53 UTC (permalink / raw)
  To: Jason Uhlenkott <jpu31@uhlenkott.net>; pgsql-sql

At 02:31 +0200 on 12/10/1999, Jason Uhlenkott wrote:


> The statements I generate are usually of the form:
> INSERT INTO foo (bar, bas) VALUES ('abc', 'def');
> but the 'abc' and 'def' come from an untrusted source, so if they supply
> a string like "def'); delete from foo; '" they can make me do this:
> INSERT INTO foo (bar, bas) VALUES ('abc', 'def'); delete from foo; '');
>
> What do I need to do to prevent this?  My current plan is to prepend a
> backslash to every single-quote, backslash, and semicolon in the
> untrusted string.  Are there any other special characters I should watch
> out for?  Is it possible to do something evil despite your special
> characters being prepended with a backslash?

I don't see why you would want to escape a semicolon. If you escape single
quotes and backslashes, the above situation won't happen - the string won't
be finished until the first unescaped quote - yours - is encountered.
Semicolons are not special in strings.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] security: escaping user-supplied data
  1999-10-12 00:31 security: escaping user-supplied data Jason Uhlenkott <jpu31@uhlenkott.net>
  1999-10-12 09:53 ` Re: [SQL] security: escaping user-supplied data Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-10-12 12:22   ` Albert REINER <areiner@tph.tuwien.ac.at>
  1999-10-12 20:57     ` Re: [SQL] security: escaping user-supplied data Bruce Momjian <maillist@candle.pha.pa.us>
  1 sibling, 1 reply; 8+ messages in thread

From: Albert REINER @ 1999-10-12 12:22 UTC (permalink / raw)
  To: pgsql-sql

On Tue, Oct 12, 1999 at 11:53:44AM +0200, Herouth Maoz wrote:
> At 02:31 +0200 on 12/10/1999, Jason Uhlenkott wrote:
> 
> 
> > The statements I generate are usually of the form:
> > INSERT INTO foo (bar, bas) VALUES ('abc', 'def');
> > but the 'abc' and 'def' come from an untrusted source, so if they supply
> > a string like "def'); delete from foo; '" they can make me do this:
> > INSERT INTO foo (bar, bas) VALUES ('abc', 'def'); delete from foo; '');
> >
> > What do I need to do to prevent this?  My current plan is to prepend a
> > backslash to every single-quote, backslash, and semicolon in the
> > untrusted string.  Are there any other special characters I should watch
> > out for?  Is it possible to do something evil despite your special
> > characters being prepended with a backslash?
> 
> I don't see why you would want to escape a semicolon. If you escape single
> quotes and backslashes, the above situation won't happen - the string won't
> be finished until the first unescaped quote - yours - is encountered.
> Semicolons are not special in strings.
> 
> Herouth

I once posted a similar question to the pgsql-novice mailing
list. There, David Rugge (1 Aug 1999) told me to escape ', ", and %,
even though I am not quite sure why you have to escape " and %. But
now that I think of it: you also need to escape \, of course, or
backslashes will either get lost or, even worse, may escape the
closing quote (think of $def="\"). Thus, using Perl and Pg, you would
do:

use Pg;
$conn = ...;

$abc="abc";
$def="def";
$conn->exec("INSERT INTO foo (bar, bas) VALUES ('" .
    &stdstr($abc) . "', '" . &stdstr($def) . "')";

sub stdstr {
    local $or = $_[0];
    $or =~ s /\'/\\\'/g;
    $or =~ s /\"/\\\"/g;
    $or =~ s /%/\\%/g;
    $or =~ s /\\/\\\\/g;
    return $or;
}

Hope that helps,

Albert.

-- 

---------------------------------------------------------------------------
  Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
---------------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] security: escaping user-supplied data
  1999-10-12 00:31 security: escaping user-supplied data Jason Uhlenkott <jpu31@uhlenkott.net>
  1999-10-12 09:53 ` Re: [SQL] security: escaping user-supplied data Herouth Maoz <herouth@oumail.openu.ac.il>
  1999-10-12 12:22   ` Re: [SQL] security: escaping user-supplied data Albert REINER <areiner@tph.tuwien.ac.at>
@ 1999-10-12 20:57     ` Bruce Momjian <maillist@candle.pha.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Bruce Momjian @ 1999-10-12 20:57 UTC (permalink / raw)
  To: Albert REINER <areiner@tph.tuwien.ac.at>; +Cc: pgsql-sql

PHP has some function that auto-escapes a query string so it is safe. 
You may want to check that.


> On Tue, Oct 12, 1999 at 11:53:44AM +0200, Herouth Maoz wrote:
> > At 02:31 +0200 on 12/10/1999, Jason Uhlenkott wrote:
> > 
> > 
> > > The statements I generate are usually of the form:
> > > INSERT INTO foo (bar, bas) VALUES ('abc', 'def');
> > > but the 'abc' and 'def' come from an untrusted source, so if they supply
> > > a string like "def'); delete from foo; '" they can make me do this:
> > > INSERT INTO foo (bar, bas) VALUES ('abc', 'def'); delete from foo; '');
> > >
> > > What do I need to do to prevent this?  My current plan is to prepend a
> > > backslash to every single-quote, backslash, and semicolon in the
> > > untrusted string.  Are there any other special characters I should watch
> > > out for?  Is it possible to do something evil despite your special
> > > characters being prepended with a backslash?
> > 
> > I don't see why you would want to escape a semicolon. If you escape single
> > quotes and backslashes, the above situation won't happen - the string won't
> > be finished until the first unescaped quote - yours - is encountered.
> > Semicolons are not special in strings.
> > 
> > Herouth
> 
> I once posted a similar question to the pgsql-novice mailing
> list. There, David Rugge (1 Aug 1999) told me to escape ', ", and %,
> even though I am not quite sure why you have to escape " and %. But
> now that I think of it: you also need to escape \, of course, or
> backslashes will either get lost or, even worse, may escape the
> closing quote (think of $def="\"). Thus, using Perl and Pg, you would
> do:
> 
> use Pg;
> $conn = ...;
> 
> $abc="abc";
> $def="def";
> $conn->exec("INSERT INTO foo (bar, bas) VALUES ('" .
>     &stdstr($abc) . "', '" . &stdstr($def) . "')";
> 
> sub stdstr {
>     local $or = $_[0];
>     $or =~ s /\'/\\\'/g;
>     $or =~ s /\"/\\\"/g;
>     $or =~ s /%/\\%/g;
>     $or =~ s /\\/\\\\/g;
>     return $or;
> }
> 
> Hope that helps,
> 
> Albert.
> 
> -- 
> 
> ---------------------------------------------------------------------------
>   Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
> ---------------------------------------------------------------------------
> 
> ************
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] security: escaping user-supplied data
  1999-10-12 00:31 security: escaping user-supplied data Jason Uhlenkott <jpu31@uhlenkott.net>
  1999-10-12 09:53 ` Re: [SQL] security: escaping user-supplied data Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-10-13 20:54   ` tjk@tksoft.com <tjk@tksoft.com>
  1 sibling, 0 replies; 8+ messages in thread

From: tjk@tksoft.com @ 1999-10-13 20:54 UTC (permalink / raw)
  To: herouth@oumail.openu.ac.il; +Cc: jpu31@uhlenkott.net; pgsql-sql

Here's a function in perl which should work for cleaning
a string which is sent in a query:

# The checks for already backslashed apostrophies and backslashes
# prevent tricks with these, and also allow us to run clean_text()
# several times.

sub clean_text {
   my $text = shift;
   return "" if (! defined $text || ! length($text));

   # First change backslashed backslashes back to single backslashes.
   $text =~ s/\\\\/\\/g;
   # Find backslash single quote combinations and convert them to single quotes.
   # while... is theoretically not necessary. It's there just as an assurance.
   while ($text =~ /\\\'/) {
      $text =~ s/\\\'/'/g;
   }
   # Now there should be no single quotes preceded by backslashes left.

   # Then find all backslashes and convert them to doubles.
   $text =~ s/\\/\\\\/g;
   # Now all characters preceded by a backslash should be in their
   # original state, while all backslashed single quotes are
   # just single quotes.
   # Find all single quotes and backslash them.
   # (Note: There are no single quotes preceded by a backslash, so
   # it is not possible to have a backslash which would hide a
   # backslash preceding a single quote.)
   $text =~ s/\'/\\'/g;
   return $text;
}


The function could be more efficient if you made $text a reference.
If you know how to use references in Perl, then the conversion
is easy. Just drop the "return" from the end and change all
references to $text with $$text. You should also add a check
such as "! defined $text || ! ref $text ||" to the second line's if 
statement.

If you find a way to exploit this, I would like to know.

Troy



> 
> At 02:31 +0200 on 12/10/1999, Jason Uhlenkott wrote:
> 
> 
> > The statements I generate are usually of the form:
> > INSERT INTO foo (bar, bas) VALUES ('abc', 'def');
> > but the 'abc' and 'def' come from an untrusted source, so if they supply
> > a string like "def'); delete from foo; '" they can make me do this:
> > INSERT INTO foo (bar, bas) VALUES ('abc', 'def'); delete from foo; '');
> >
> > What do I need to do to prevent this?  My current plan is to prepend a
> > backslash to every single-quote, backslash, and semicolon in the
> > untrusted string.  Are there any other special characters I should watch
> > out for?  Is it possible to do something evil despite your special
> > characters being prepended with a backslash?
> 
> I don't see why you would want to escape a semicolon. If you escape single
> quotes and backslashes, the above situation won't happen - the string won't
> be finished until the first unescaped quote - yours - is encountered.
> Semicolons are not special in strings.
> 
> Herouth
> 
> --
> Herouth Maoz, Internet developer.
> Open University of Israel - Telem project
> http://telem.openu.ac.il/~herutma
> 
> 
> 
> ************
> 
> 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] security: escaping user-supplied data
@ 1999-10-13 23:30 tjk@tksoft.com <tjk@tksoft.com>
  1999-10-15 12:01 ` Re: [SQL] security: escaping user-supplied data Albert REINER <areiner@tph.tuwien.ac.at>
  0 siblings, 1 reply; 8+ messages in thread

From: tjk@tksoft.com @ 1999-10-13 23:30 UTC (permalink / raw)
  To: pgsql-sql

Here's a function in perl which should work for cleaning
a string which is sent in a query:

# The checks for already backslashed apostrophies and backslashes
# prevent tricks with these, and also allow us to run clean_text()
# several times.

I added the ">" tags, so the listserv wouldn't bounce the email.

> 
> sub clean_text {
>    my $text = shift;
>    return "" if (! defined $text || ! length($text));
> 
>    # First change backslashed backslashes back to single backslashes.
>    $text =~ s/\\\\/\\/g;
>    # Find backslash single quote combinations and convert them to single quotes.
>    # while... is theoretically not necessary. It's there just as an assurance.
>    while ($text =~ /\\\'/) {
>       $text =~ s/\\\'/'/g;
>    }
>    # Now there should be no single quotes preceded by backslashes left.
> 
>    # Then find all backslashes and convert them to doubles.
>    $text =~ s/\\/\\\\/g;
>    # Now all characters preceded by a backslash should be in their
>    # original state, while all backslashed single quotes are
>    # just single quotes.
>    # Find all single quotes and backslash them.
>    # (Note: There are no single quotes preceded by a backslash, so
>    # it is not possible to have a backslash which would hide a
>    # backslash preceding a single quote.)
>    $text =~ s/\'/\\'/g;
>    return $text;
> }
> 
 
The function could be more efficient if you made $text a reference.
If you know how to use references in Perl, then the conversion
is easy. Just drop the "return" from the end and change all
references to $text with $$text. You should also add a check
such as "! defined $text || ! ref $text ||" to the second line's if 
statement.
 
If you find a way to exploit this, I would like to know.
 
Troy



> > 
> > At 02:31 +0200 on 12/10/1999, Jason Uhlenkott wrote:
> > 
> > 
> > > The statements I generate are usually of the form:
> > > INSERT INTO foo (bar, bas) VALUES ('abc', 'def');
> > > but the 'abc' and 'def' come from an untrusted source, so if they supply
> > > a string like "def'); delete from foo; '" they can make me do this:
> > > INSERT INTO foo (bar, bas) VALUES ('abc', 'def'); delete from foo; '');
> > >
> > > What do I need to do to prevent this?  My current plan is to prepend a
> > > backslash to every single-quote, backslash, and semicolon in the
> > > untrusted string.  Are there any other special characters I should watch
> > > out for?  Is it possible to do something evil despite your special
> > > characters being prepended with a backslash?
> > 
> > I don't see why you would want to escape a semicolon. If you escape single
> > quotes and backslashes, the above situation won't happen - the string won't
> > be finished until the first unescaped quote - yours - is encountered.
> > Semicolons are not special in strings.
> > 
> > Herouth
> > 
> > --
> > Herouth Maoz, Internet developer.
> > Open University of Israel - Telem project
> > http://telem.openu.ac.il/~herutma
> > 
> > 
> > 
> > ************
> > 
> > 
> 
> 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] security: escaping user-supplied data
  1999-10-13 23:30 Re: [SQL] security: escaping user-supplied data tjk@tksoft.com <tjk@tksoft.com>
@ 1999-10-15 12:01 ` Albert REINER <areiner@tph.tuwien.ac.at>
  1999-10-15 19:00   ` Re: [SQL] security: escaping user-supplied data tjk@tksoft.com <tjk@tksoft.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Albert REINER @ 1999-10-15 12:01 UTC (permalink / raw)
  To: pgsql-sql

On Wed, Oct 13, 1999 at 04:30:55PM -0700, tjk@tksoft.com wrote:
> Here's a function in perl which should work for cleaning
> a string which is sent in a query:

Well, this obviously depends on the type of data you expect from the
user; I don't really see why you would like to get rid of double
backslashes (maybe the data is TeX-Code, and \\ has been assigned to
something useful). Furthermore, you don't really prevent the user from
storing \\, he just has to type \\\ instead --- a rather confusing
behavior:

    $ perl -e '$a="\\\\\\"; print "$a\n"; $a =~ s/\\\\/\\/g; print "$a\n"; '
    \\\
    \\

But if you really want to prevent more than one \ in the string (which
may be useful for some applications), consider s/\\+/\\/g. I guess it
all depends on what type of data you want to store. By the way, for me
the need to run the modification several times has never arisen.

By the way, your clean_text() does not take care of %, which might be
important in LIKE-clauses: "select field where field like '%"
. clean_text('50% increase') . "'" might not do what you (or the user)
want.

Albert.


-- 

---------------------------------------------------------------------------
  Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
---------------------------------------------------------------------------



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: [SQL] security: escaping user-supplied data
  1999-10-13 23:30 Re: [SQL] security: escaping user-supplied data tjk@tksoft.com <tjk@tksoft.com>
  1999-10-15 12:01 ` Re: [SQL] security: escaping user-supplied data Albert REINER <areiner@tph.tuwien.ac.at>
@ 1999-10-15 19:00   ` tjk@tksoft.com <tjk@tksoft.com>
  0 siblings, 0 replies; 8+ messages in thread

From: tjk@tksoft.com @ 1999-10-15 19:00 UTC (permalink / raw)
  To: areiner@tph.tuwien.ac.at; +Cc: pgsql-sql

Albert,

Good point about checking for %. 
The same problem would occur with regular expressions, though.
I will think about this. 
It might be possible to create a separate set of generic rules for
verifying like queries and regular expression queries, but I
won't try to come up with something here.

On the double backslashes, you are correct too. 
I think my comments didn't explain the desired behavior clearly enough.

Here's a brief list of what is supposed to happen.

# User input  -> data sent to query
# \           -> \\
# \\          -> \\
# \\\         -> \\\\
# The same when there is a letter after the backslash.
# (Obvious, but I wanted to clarify the point.)
# \X          -> \\X
# \\X         -> \\X
# \\\X        -> \\\\X
# Any single quotes can only have one backslash behind them.
# \'          -> \'
# \\'         -> \'
# \\\'        -> \'

I haven't run across a need to store tex documents, but
since they tend to be longer than 8k, you would probably 
want to store them in files anyway, and/or use large objects.

I wanted to make sure the data remains the same after several
runs so you don't have keep track of whether you've already processed
a piece of data or not. Also, it makes debugging the function itself 
easier.

Thanks for the good input.


Troy


> 
> On Wed, Oct 13, 1999 at 04:30:55PM -0700, tjk@tksoft.com wrote:
> > Here's a function in perl which should work for cleaning
> > a string which is sent in a query:
> 
> Well, this obviously depends on the type of data you expect from the
> user; I don't really see why you would like to get rid of double
> backslashes (maybe the data is TeX-Code, and \\ has been assigned to
> something useful). Furthermore, you don't really prevent the user from
> storing \\, he just has to type \\\ instead --- a rather confusing
> behavior:
> 
>     $ perl -e '$a="\\\\\\"; print "$a\n"; $a =~ s/\\\\/\\/g; print "$a\n"; '
>     \\\
>     \\
> 
> But if you really want to prevent more than one \ in the string (which
> may be useful for some applications), consider s/\\+/\\/g. I guess it
> all depends on what type of data you want to store. By the way, for me
> the need to run the modification several times has never arisen.
> 
> By the way, your clean_text() does not take care of %, which might be
> important in LIKE-clauses: "select field where field like '%"
> . clean_text('50% increase') . "'" might not do what you (or the user)
> want.
> 
> Albert.
> 
> 
> -- 
> 
> ---------------------------------------------------------------------------
>   Post an / Mail to / Skribu al: Albert Reiner <areiner@tph.tuwien.ac.at>
> ---------------------------------------------------------------------------
> 
> ************
> 
> 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~1999-10-15 19:00 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-10-12 00:31 security: escaping user-supplied data Jason Uhlenkott <jpu31@uhlenkott.net>
1999-10-12 09:53 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-10-12 12:22   ` Albert REINER <areiner@tph.tuwien.ac.at>
1999-10-12 20:57     ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-10-13 20:54   ` tjk@tksoft.com <tjk@tksoft.com>
1999-10-13 23:30 Re: [SQL] security: escaping user-supplied data tjk@tksoft.com <tjk@tksoft.com>
1999-10-15 12:01 ` Albert REINER <areiner@tph.tuwien.ac.at>
1999-10-15 19:00   ` tjk@tksoft.com <tjk@tksoft.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox