Re: [SQL] security: escaping user-supplied data - Mailing list 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>
> ---------------------------------------------------------------------------
>
> ************
>
>