From: tjk@tksoft.com <tjk@tksoft.com>
To: areiner@tph.tuwien.ac.at
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] security: escaping user-supplied data
Date: Fri, 15 Oct 1999 12:00:39 -0700 (PDT)
Message-ID: <199910151900.MAA09356@uno.tksoft.com> (raw)
In-Reply-To: <19991015140149.D598@frithjof>
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>
> ---------------------------------------------------------------------------
>
> ************
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tjk@tksoft.com, areiner@tph.tuwien.ac.at
Subject: Re: [SQL] security: escaping user-supplied data
In-Reply-To: <199910151900.MAA09356@uno.tksoft.com>
References: <19991015140149.D598@frithjof> <199910151900.MAA09356@uno.tksoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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