Skip site navigation (1) Skip section navigation (2)

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

From: "tjk(at)tksoft(dot)com" <tjk(at)tksoft(dot)com>
To: areiner(at)tph(dot)tuwien(dot)ac(dot)at (Albert REINER)
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] security: escaping user-supplied data
Date: 1999-10-15 19:00:39
Message-ID: 199910151900.MAA09356@uno.tksoft.com (view raw or flat)
Thread:
Lists: 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(at)tksoft(dot)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(at)tph(dot)tuwien(dot)ac(dot)at>
> ---------------------------------------------------------------------------
> 
> ************
> 
> 


In response to

pgsql-sql by date

Next:From: Ivanko AlexandrDate: 1999-10-16 09:31:01
Subject: help
Previous:From: soundar rajanDate: 1999-10-15 13:32:42
Subject: help on jdbc driver

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group