At 01:55 PM 8/31/2001 -0700, Curtis Poe wrote:
>--- "Michael R. Fahey" <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > I was looking at a perl script where the developer used different names
> > for the incoming parameters and the database field names. He told me
> > that this was done for security reasons-- to ensure that malicious users
> > would not be able to discover the field names in the database being
> > updated or queried. How dangerous is this? I think it would be easier to
> > work with a hash of parameters from the input form.
> >
> > I'm using cg.pm, DBI, and postgresql.
> >
> > Thanks.
> >
> > Michael Fahey
>
>Hi Michael,
>
>Great question! This raises some security issues that many people just
>don't consider.
This is true. Kudos to any beginner asking about security at all!
>Wether or not this is a security risk depends upon exactly how you use the
>incoming data. Let's
>look at a short script that does this horribly, horribly wrong:
>
> #!/usr/bin/perl -w
> use warnings;
> use strict;
> use CGI qw/:standard/;
> use DBI;
>
> my $dbh = DBI->connect( 'dbi:ODBC:stuff', 'name', 'password',
> { RaiseError => 1} ) or die DBI->errstr;
>
> my %data = map { $_, param( $_ ) } param();
>
> my ( @fields, @values, $name, $value );
>
> while ( ($name, $value) = each %data ) {
> push @fields, $name;
> push @values, $dbh->quote( $value );
> }
>
> my $sql = "INSERT INTO theTable (" .
> join ( ",",@fields) .
> ") VALUES (" .
> join ( ",",@values ) .
> ")";
>
> print header,
> start_html,
> p( $sql ),
> end_html;
>
>So far, everything might look okay. The developer was even security
>conscious and quoted the
>values. Imagine what happens if this script is called with the following URL:
>
> http://www.somehost.com/cgi-bin/db.cgi?name=Ovid&color=red
>
>You get back a nice Web page with the following SQL:
>
> INSERT into theTable ( name,color) VALUES (Ovid,red)
>
>That's all well and good. Now, let's alter the URL slightly:
>
>
>http://www.somehost.com/cgi-bin/db.cgi?name%29%20VALUES%20%28%20%27Ovid%27%20%29%3BDROP%20TABLE%20theTable%3BINSERT%20INTO%20theTable%20%28name=Ovid
>
>Hmm, that's a bit more complicated. I wonder what the SQL is?
>
> INSERT INTO theTable (name) VALUES ( 'Ovid' );DROP TABLE
> theTable;INSERT INTO theTable (name)
>VALUES ('Ovid')
>
>Oops. We might lose that table. Now, by crafting a good query string, we
>can attempt to execute
>arbitrary SQL against the database (there are many, many variations of
>this attack). These
>attacks are kind of tricky because you usually need to craft the URL in
>such a way as to ensure
>that *all* of the SQL is valid at the time it's evaluated, but it's still
>a possible exploit.
>
>There are ways to make this secure and still use the field names, but I
>wouldn't suggest it. While
>I am not an advocate of Security by Obscurity, I do advocate not revealing
>information that you
>don't need to reveal.
An important additional point is that simply renaming your form parameters
to be different from the database, you would still usually have a 1 to 1
mapping from the form to the database field name somewhere in your code.
So the above code that was given as an example could possible screw things
over for you even if you renamed the variables. And conceivably, you could
play tricks if you knew more about the database to issue alternative
queries in-line if a section of the CGI script is issuing queries itself.
While it is conceivable that security through obscurity has SOME value, I
am not sure that in what might be a large program that such a mapping helps
your programmer's thinking process in terms of writing and maintaining the
code later.
If the form vars are renamed with an obvious prefix like fname in the
database becomes form_fname then it should be obvious to a cracker that
form_ is a standard prefix. So the best way to rename the variables is
fairly randomly. But this will suck for your maintenance of the code. I
suppose you could have a filter function that takes CGI.pm and remaps the
form values inside of it, but it's still harsh on the forms developer for
debugging and always remembering the mapping. What a yucky thing to have to
work on then!
I would tend, therefore, to shy away from security through obscurity in
this case and focus more on how to make the SQL secure itself. As has been
pointed out in the last post well, validation of input is very important.
For an introduction of the basic issues, any documentation having to do
with taintmode is quite reasonable to read. eg
http://www.gunther.web66.com/FAQS/taintmode.html
Also, if you are writing SQL code, you should definitely consider the
following three issues:
1) The quoting issue was discussed by the previous poster.
But there was no solution presented. Here are two reasonable ones:
a) Use DBI's quote() method and run it through all the values to be set in
the SQL.
b) Use prepare() and binding of columns in DBI rather than simply shooting
off the select or insert.
However, be careful with this. Most DBD drivers will treat this as being
much better than (a) because their protocol is strongly typed and will
truly map column values back on the server side.
I am not sure about this, but I think it is possible that some DBD drivers
MAY not natively support prepare and may be implemented as simple
constructions of the original SELECT statement again rather than true
binding. Of course, those implementations should use quote() when
generating the value, but the issue is still there to be careful.
2) If you allow arbitrary column names as form input (like a dynamic query
generator)... then you need to be extra careful and the solution in 1)
doesnt work.
This is more power than most programs should ever give. But if you must,
then I recommend providing a config with a list of VALID fieldnames and
always match those arbitrary column names in the query interface to the
list of VALID fieldnames so that no weird input can be put in place because
there is no "quote"ing for fieldnames.
Same goes for tablenames.
3) Record Ids
Most web databases use a handle to a record for performing updates. But
they usually do so based on usernames and the like. If you have an
authenticated database, then you need to protect your record id.
If user A inserts record_id = 1, user B inserts record_id = 2, then user B
should not see record_id = 1.
So when you do your update, deletes, selects, you need to always remember
to back it up with a username = in your clause. When I am looking at code
in the field, I see many systems that fail to implement a clean security
policy. eg They back up the permission for selects and for display of
forms, but not for the actual routine that does the modification (a bad
oversight).
So be careful with this.
Note that changing the name record_id in the form to hfdjhjdsf is
obfuscation, but I dare say that the pattern would be apparent soon enough
to a cracker unless you were also obfuscating the value as well which would
really make your code quite hard to work with.
Later,
Gunther
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
