Thread: Re: [SQL] security: escaping user-supplied data

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
> >
> >
> >
> > ************
> >
> >
>
>

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>
 
---------------------------------------------------------------------------

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>
> ---------------------------------------------------------------------------
>
> ************
>
>