Possible security flaws

am 04.05.2004 19:01:39 von Jan Eden

Hi all,

could you point out the possible security problems with the following desig=
n (planned)?

I have a database on my ISP's server which is accessible via a read-only an=
d a read/write account.

Now the CGI scripts I wrote to handle the database call the following subro=
utine from a module:

sub mysql_config {
my $mode =3D shift;
my ($server, $db, $username, $password);
if ($mode eq 'local') {
# local configuration
$server =3D 'localhost'; # The database server
$db =3D '************'; # The name of our database.
$username =3D '********' ;# the username=20
$password =3D '************' ;# the password
# editor configuration
} elsif ($mode eq 'editor') {
# pair.com configuration (write)
$server =3D '***********'; # The database server
$db =3D '*******'; # The name of our database
$username =3D '***********' ;# the username for reading/writing=20
$password =3D '*************' ;# the password
} else {
# read-only configuration
$server =3D '**********'; # The database server
$db =3D '******'; # The name of our database
$username =3D '************' ;# the username for reading=20
$password =3D '*********' ;# the password
}
return ($server, $db, $username, $password);
}

So if I call the script via the net in 'editor' mode, I have write access. =
But my editing script utilizes a user check like this:

sub user_check {
my ($user_id, $user_name_input, $user_password_input, $mode) =3D @_;
my ($server, $db, $username, $password) =3D mysql_config($mode);
my $dbh =3D DBI->connect("dbi:mysql:$db:$server", $username, $password,=
{ RaiseError =3D> 1 });
my $query =3D "SELECT user_name, user_password FROM users WHERE user_id=
=3D $user_id";
my $sth =3D $dbh->prepare($query);
$sth->execute();
my ($user_name, $user_password) =3D $sth->fetchrow_array;
$sth->finish();
return 1 if $user_name_input eq $user_name && $user_password_input eq $=
user_password;
return 0;
}

If no username/password are given (or if the wrong values are entered), the=
editing script generates an identification page, otherwise (if user_check =
returns 1), the editing form with the values of the page is printed out.

At which point is this setup vulnerable? So far, I only used local editing,=
and I still bear in mind that there's evil out there. ;-)

Thanks,

Jan
--=20
These are my principles and if you don't like them... well, I have others. =
- Groucho Marx

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Possible security flaws

am 04.05.2004 19:25:51 von apv

I think you should try to avoid ever putting passwords (or usernames)
into scripts directly. MySQL gives a great alternative for loading the
information, the config file. You can have the DB in it too. This is
just one approach, of course.

So, your:
if ($mode eq 'local') {
# local configuration
$server = 'localhost'; # The database server
$db = '************'; # The name of our database.
$username = '********' ;# the username
$password = '************' ;# the password
# editor configuration

can become something like:
my $file_root = '/www/etc';
my %mysq_cnf = ( local => "$file_root/.local_mysql.cnf'.
etc => ... )

where the file, /www/etc/.local_mysql.cnf, outside the htdocs root, has
perms set for the webserver alone to readonly like:
-r-------- 1 nobody nobody 607 Apr 30 23:03 .local_mysql.cnf

file containing something like:
[client]
host=localhost
database=cargobeep
password=cantguessthis
# user is understood to be "nobody" so need not be entered

then you can load it, this way, i think (untested code):
my $connect_file = $mysq_cnf{$mode};

my $dbh = DBI->connect("DBI:mysql;" .
"mysql_read_default_file=$connect_file;"
);

-asdlkajsdfkljfey

On Tuesday, May 4, 2004, at 10:01 AM, Jan Eden wrote:

> Hi all,
>
> could you point out the possible security problems with the following
> design (planned)?
>
> I have a database on my ISP's server which is accessible via a
> read-only and a read/write account.
>
> Now the CGI scripts I wrote to handle the database call the following
> subroutine from a module:
>
> sub mysql_config {
> my $mode = shift;
> my ($server, $db, $username, $password);
> if ($mode eq 'local') {
> # local configuration
> $server = 'localhost'; # The database server
> $db = '************'; # The name of our database.
> $username = '********' ;# the username
> $password = '************' ;# the password
> # editor configuration
> } elsif ($mode eq 'editor') {
> # pair.com configuration (write)
> $server = '***********'; # The database server
> $db = '*******'; # The name of our database
> $username = '***********' ;# the username for reading/writing
> $password = '*************' ;# the password
> } else {
> # read-only configuration
> $server = '**********'; # The database server
> $db = '******'; # The name of our database
> $username = '************' ;# the username for reading
> $password = '*********' ;# the password
> }
> return ($server, $db, $username, $password);
> }
>
> So if I call the script via the net in 'editor' mode, I have write
> access. But my editing script utilizes a user check like this:
>
> sub user_check {
> my ($user_id, $user_name_input, $user_password_input, $mode) = @_;
> my ($server, $db, $username, $password) = mysql_config($mode);
> my $dbh = DBI->connect("dbi:mysql:$db:$server", $username,
> $password, { RaiseError => 1 });
> my $query = "SELECT user_name, user_password FROM users WHERE
> user_id = $user_id";
> my $sth = $dbh->prepare($query);
> $sth->execute();
> my ($user_name, $user_password) = $sth->fetchrow_array;
> $sth->finish();
> return 1 if $user_name_input eq $user_name && $user_password_input
> eq $user_password;
> return 0;
> }
>
> If no username/password are given (or if the wrong values are
> entered), the editing script generates an identification page,
> otherwise (if user_check returns 1), the editing form with the values
> of the page is printed out.
>
> At which point is this setup vulnerable? So far, I only used local
> editing, and I still bear in mind that there's evil out there. ;-)
>
> Thanks,
>
> Jan
> --
> These are my principles and if you don't like them... well, I have
> others. - Groucho Marx
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=apv@sedition.com
>
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Possible security flaws

am 04.05.2004 19:25:51 von apv

Re: Possible security flaws

am 04.05.2004 23:07:26 von David Dick

if you change the following

> my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password, { RaiseError => 1 });
> my $query = "SELECT user_name, user_password FROM users WHERE user_id = $user_id";
> my $sth = $dbh->prepare($query);
> $sth->execute();
> my ($user_name, $user_password) = $sth->fetchrow_array;
> $sth->finish();

to be

my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password, {
RaiseError => 1 });
my $query = "SELECT user_name, user_password FROM users WHERE user_id = ?";
my $sth = $dbh->prepare($query);
$sth->execute($user_id);
my ($user_name, $user_password) = $sth->fetchrow_array;
$sth->finish();

you will have a better chance of surviving a $user_id = qq[user_id] :)
even if you think there is no way $user_id could be compromised,
placeholders are still an excellent security measure.

uru
-Dave

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Possible security flaws

am 04.05.2004 23:07:26 von David Dick

Re: Possible security flaws

am 05.05.2004 00:17:27 von Jim

Hi,

For my personal edification, could you elaborate on
'surviving....qq[user_id]' in the following:

you will have a better chance of surviving a $user_id = qq[user_id] :)
even if you think there is no way $user_id could be compromised,
placeholders are still an excellent security measure.

Thanks,

Jim Cant

----- Original Message -----
From: "David Dick"
To: "Jan Eden"
Cc:
Sent: Tuesday, May 04, 2004 5:07 PM
Subject: Re: Possible security flaws

>
> if you change the following
>
> > my $dbh = DBI->connect("dbi:mysql:$db:$server", $username,
$password, { RaiseError => 1 });
> > my $query = "SELECT user_name, user_password FROM users WHERE
user_id = $user_id";
> > my $sth = $dbh->prepare($query);
> > $sth->execute();
> > my ($user_name, $user_password) = $sth->fetchrow_array;
> > $sth->finish();
>
> to be
>
> my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password, {
> RaiseError => 1 });
> my $query = "SELECT user_name, user_password FROM users WHERE user_id =
?";
> my $sth = $dbh->prepare($query);
> $sth->execute($user_id);
> my ($user_name, $user_password) = $sth->fetchrow_array;
> $sth->finish();
>

> uru
> -Dave
>
> --
> MySQL Perl Mailing List
> For list archives: http://lists.mysql.com/perl
> To unsubscribe: http://lists.mysql.com/perl?unsub=cant_jim@hotmail.com
>
>

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Possible security flaws

am 05.05.2004 00:17:27 von Hotmail

Re: Possible security flaws

am 05.05.2004 09:50:16 von Jan Eden

Thanks Dave and apv,

David Dick wrote on 05.05.2004:

>if you change the following
>
>>my $dbh =3D DBI->connect("dbi:mysql:$db:$server", $username,
>>$password, { RaiseError =3D> 1 }); my $query =3D "SELECT user_name,
>>user_password FROM users WHERE user_id =3D $user_id"; my $sth =3D
>>$dbh->prepare($query); $sth->execute(); my ($user_name,
>>$user_password) =3D $sth->fetchrow_array; $sth->finish();
>
>to be
>
>my $dbh =3D DBI->connect("dbi:mysql:$db:$server", $username, $password,
> { RaiseError =3D> 1 }); my $query =3D "SELECT user_name, user_password
>FROM users WHERE user_id =3D ?"; my $sth =3D $dbh->prepare($query);
>$sth->execute($user_id); my ($user_name, $user_password) =3D
>$sth->fetchrow_array; $sth->finish();
>
>you will have a better chance of surviving a $user_id =3D qq[user_id]
>:) even if you think there is no way $user_id could be compromised,
>placeholders are still an excellent security measure.

I will change my script accordingly.

- Jan
--=20
There are 10 kinds of people: those who understand binary, and those who d=
on't

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Possible security flaws

am 05.05.2004 09:50:16 von Jan Eden

Re: Possible security flaws

am 05.05.2004 14:35:00 von David Dick

G'day Jim,

Jim wrote:
> Hi,
>
> For my personal edification, could you elaborate on
> 'surviving....qq[user_id]' in the following:
>
> you will have a better chance of surviving a $user_id = qq[user_id] :)
> even if you think there is no way $user_id could be compromised,
> placeholders are still an excellent security measure.
>

ok, when the following two statements are executed..

my $user_id = "user_id";
my $sql =
"SELECT user_name, user_password FROM users WHERE user_id = $user_id";

$sql now contains the following...

SELECT user_name, user_password FROM users WHERE user_id = user_id

which of course is always true (if any records at exist in the users
table). Therefore if the attacker can make $user_id hold qq[user_id]
then the attacker can always get into the application. Placeholders
automatically quote everything correctly, so with placeholders, $sql
would hold ...

SELECT user_name, user_password FROM users WHERE user_id = 'user_id'

which would correctly deny access.

Uru
-Dave

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: Possible security flaws

am 05.05.2004 14:35:00 von David Dick

Re: Possible security flaws

am 06.05.2004 08:06:40 von Jan Eden

Hi Dave,

David Dick wrote on 05.05.2004:

>G'day Jim,
>
>Jim wrote:
>>Hi,
>>
>>For my personal edification, could you elaborate on
>>'surviving....qq[user_id]' in the following:
>>
>>you will have a better chance of surviving a $user_id =3D qq[user_id]
>>:) even if you think there is no way $user_id could be compromised,
>>placeholders are still an excellent security measure.
>>
>
>ok, when the following two statements are executed..
>
>my $user_id =3D "user_id"; my $sql =3D "SELECT user_name, user_password
>FROM users WHERE user_id =3D $user_id";
>
>$sql now contains the following...
>
>SELECT user_name, user_password FROM users WHERE user_id =3D user_id
>
>which of course is always true (if any records at exist in the users
>table). Therefore if the attacker can make $user_id hold
>qq[user_id] then the attacker can always get into the application.=20
>Placeholders automatically quote everything correctly, so with
>placeholders, $sql would hold ...
>
>SELECT user_name, user_password FROM users WHERE user_id =3D 'user_id'
>
>which would correctly deny access.

That's a good explanation in the light of which I think your advice - while=
still a good one in general - does not apply to my setup.

I do not check for the existence of a certain user_id, i.e. I do not check =
if the SELECT statement evaluates to true.

In fact, I do not take a user_id from the user at all. Instead, the user ha=
s to enter a name and a password when trying to access a restricted page. F=
or each restricted page (and for write access in general), there is a user_=
id hardcoded into this page's data set. My script reads the page data from =
the database (including the user_id) and passes it to the user_check sub, t=
ogether with the name and password the user entered.

In the next step, the user_id is used to construct the SELECT statement, wh=
ich returns a user name and a password. These two pieces of information are=
compared to the ones the user entered. Only if the two pairs are identical=
, access is granted.

I guess this process is also vulnerable, but entering qq{user_name} or qq{u=
ser_password} should not break it. I also filter the CGI parameter through =
a regex which only allows \w characters.

Thanks again for your suggestion,

Jan
--=20
How many Microsoft engineers does it take to screw in a lightbulb? None. Th=
ey just redefine "dark" as the new standard.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: Possible security flaws

am 06.05.2004 08:06:40 von Jan Eden

Re: [perl-mysql] Re: Possible security flaws

am 06.05.2004 13:54:45 von Dave Cash

On Thu, 6 May 2004, Jan Eden wrote:

> Hi Dave,
>
> David Dick wrote on 05.05.2004:
>
> >G'day Jim,
> >
> >Jim wrote:
> >>Hi,
> >>
> >>For my personal edification, could you elaborate on
> >>'surviving....qq[user_id]' in the following:
> >>
> >>you will have a better chance of surviving a $user_id = qq[user_id]
> >>:) even if you think there is no way $user_id could be compromised,
> >>placeholders are still an excellent security measure.
> >>
> >
> >ok, when the following two statements are executed..
> >
> >my $user_id = "user_id"; my $sql = "SELECT user_name, user_password
> >FROM users WHERE user_id = $user_id";
> >
> >$sql now contains the following...
> >
> >SELECT user_name, user_password FROM users WHERE user_id = user_id
> >
> >which of course is always true (if any records at exist in the users
> >table). Therefore if the attacker can make $user_id hold
> >qq[user_id] then the attacker can always get into the application.
> >Placeholders automatically quote everything correctly, so with
> >placeholders, $sql would hold ...
> >
> >SELECT user_name, user_password FROM users WHERE user_id = 'user_id'
> >
> >which would correctly deny access.
>
> That's a good explanation in the light of which I think your
> advice - while still a good one in general - does not apply to my
> setup.
>
> I do not check for the existence of a certain user_id, i.e. I do
> not check if the SELECT statement evaluates to true.
>
> In fact, I do not take a user_id from the user at all. Instead,
> the user has to enter a name and a password when trying to access
> a restricted page. For each restricted page (and for write access
> in general), there is a user_id hardcoded into this page's data
> set. My script reads the page data from the database (including
> the user_id) and passes it to the user_check sub, together with
> the name and password the user entered.

If by "there is a user_id hardcoded into this page's data set" you
mean the user's user_id is stored in an HTML hidden field or in a
QUERY_STRING somewhere, it's worth mentioning that these two methods
of data persistence can fairly trivially be exploited. One need
only prepare an application-valid QUERY_STRING or POST content
string with a different value for user_id to potentially access
(depending on what other security measures have or have not been
taken) an account owned by someone else.

> In the next step, the user_id is used to construct the SELECT
> statement, which returns a user name and a password. These two
> pieces of information are compared to the ones the user entered.
> Only if the two pairs are identical, access is granted.
>
> I guess this process is also vulnerable, but entering
> qq{user_name} or qq{user_password} should not break it. I also
> filter the CGI parameter through a regex which only allows \w
> characters.

I'm not sure if you're rolling your own or using a CPAN solution,
but in case you don't know about it, CGI::Untaint is a good module
for both untainting and validating data your script gets via the
CGI.

Dave

/L\_/E\_/A\_/R\_/N\_/T\_/E\_/A\_/C\_/H\_/L\_/E\_/A\_/R\_/N\
Dave Cash Power to the People!
Frolicking in Fields of Garlic Right On-Line!
dave@gnofn.org Dig it all.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: [perl-mysql] Re: Possible security flaws

am 06.05.2004 13:54:45 von Dave Cash