PERL CGI Beginners - CGI scripts, security and MySQL

Author

CGI scripts, security and MySQL

Michael Watson

2004-09-04, 8:55 am

Hi

I need to know what is the accepted way of handling the following. I
have a MySQL database, and a host of CGI scripts which present forms to
the users as web pages, they fill them in and then the data is written
to the database. I need to make this secure such that only users I want
can use the system. I want to set up a username and password so that
users can log in once at the beginning of a session, carry out their
work filling in various forms and writing to the database, and then
log-out at the end.

What is the best way to do this? I've thought about creating a MySQL
user/password for each person who needs to enter data, but I don't want
them to have to enter their username and password on every form. I
guess what I need is some sort of persistant DBI connection that is
present over multiple runs of various CGI scripts (until the person logs
off or the browser is closed...)

I am running Suse Linux 8.2, MySQL 4, apache 1.3.28 and perl 5.8.0

Cheers
Mick

Chris Devers

2004-09-04, 8:55 am

On Fri, 3 Sep 2004, michael watson (IAH-C) wrote:

> I need to make this secure such that only users I want can use the
> system. I want to set up a username and password so that users can
> log in once at the beginning of a session, carry out their work
> filling in various forms and writing to the database, and then log-out
> at the end.
>
> What is the best way to do this? I've thought about creating a MySQL
> user/password for each person who needs to enter data, but I don't
> want them to have to enter their username and password on every form.
> I guess what I need is some sort of persistant DBI connection that is
> present over multiple runs of various CGI scripts (until the person
> logs off or the browser is closed...)
>
> I am running Suse Linux 8.2, MySQL 4, apache 1.3.28 and perl 5.8.0

Think about what your requirements are here; you seem to have a grab bag
of good ideas that are all mixed up together.

* System authentication

You're asking for a way to avoid making people fill out their username &
password with each form. A proper authentication system won't allow this
situation. The two basic ways you can do authentication are at the
server level, with Apache-enforced HTTP authentication (this is the
version where the, and at the application level, with code in your CGI
scripts that manages user account details.

I personally think Apache-level authentication is easier -- if you just
add the right directives to your httpd.conf, it's magically turned on
for you. For whatever reason though, this isn't often done these days --
it's more popular to reinvent this particular wheel over and over again.

If you go for the more popular application level logins, the general
approach will mean storing the user's account name in a cookie, and then
checking this cookie with each request. As long as the cookie has the
right information, they won't have to log in with each page -- it will,
in effect, do that automatically in the background.

* Database users:

I suspect it's not so important to control who's user account is writing
to the database, as much as it is to know who wrote what data in the
database. Make sense? With that in mind, you could do either or both of
[a] add fields to the tables that note who last touched each row, or
(probably better) [b] maintain a log of what changes are being made and
by who -- this log could even be as simple as a datestamp, the user
name, and the SQL statement.

This should make maintainence of the database easier, as you don't have
to maintain separate MySQL accounts for each user along with the other
accounts they are going to need.

* DBI connection persistence:

It makes sense to maintain a connection to the database, but not so much
because of user access control considerations, but just for performance:
being able to avoid building up & tearing down a DB connection with
every page view gets very expensive. The best way to get around this is
probably to use mod_perl instead of regular CGI scripts, and then turn
on Apache::DBI for database connection pooling. This can help a lot.

Does this help ? More questions ?

--
Chris Devers cdevers@pobox.com
http://devers.homeip.net:8080/blog/

np: 'Mr. Loh's Not Afraid to Be Naked'
by Sandra Tsing Loh
from 'This American Life: Lies, Sissies, and Fiascoes'

Octavian Rasnita

2004-09-04, 8:55 am

Hi,

You can put something like this, in httpd.conf file:

include ...path_to_file

And make the directory where sits that file readable only by the root user.

Then, in that file, put something like:

SetEnv usr user_name
SetEnv pass parolissima

Those 2 environment variables will be seen by any script that runs on that
server.

If you want them to be seen only by the scripts which are ran by a certain
virtualhost, put that "include" line between <virtualhost...> and
</virtualhost>.

T

----- Original Message -----
From: "Chris Devers" <cdevers@pobox.com>
To: "michael watson (IAH-C)" <michael.watson@bbsrc.ac.uk>
Cc: <beginners-cgi@perl.org>
Sent: Friday, September 03, 2004 3:08 PM
Subject: Re: CGI scripts, security and MySQL

[color=darkred]
> On Fri, 3 Sep 2004, michael watson (IAH-C) wrote:
>

Chris Devers

2004-09-04, 8:55 am

On Fri, 3 Sep 2004, Octavian Rasnita wrote:

> You can put something like this, in httpd.conf file:
>
> include ...path_to_file
>
> And make the directory where sits that file readable only by the root user.
>
> Then, in that file, put something like:
>
> SetEnv usr user_name
> SetEnv pass parolissima
>
> Those 2 environment variables will be seen by any script that runs on that
> server.

How is this supposed to scale for a system that is going to have lots of
users? Update the environment variables with each request? That doesn't
sound very reliable to me...

--
Chris Devers cdevers@pobox.com
http://devers.homeip.net:8080/blog/

np: 'Flight vs. Invisibility'
by John Hodgman
from 'This American Life: Crimebusters and Crossed Wires'

Octavian Rasnita

2004-09-04, 8:55 am

No, the environment variables are set a single time at the start (or
restart) of the web server.
The problem is that if there is any change in httpd.conf file, the server
must be restarted and this might not be very easy for a system with very
many users, but it is not impossible.
Teddy

----- Original Message -----
From: "Chris Devers" <cdevers@pobox.com>
To: "Octavian Rasnita" <orasnita@fcc.ro>
Cc: <beginners-cgi@perl.org>; "michael watson (IAH-C)"
<michael.watson@bbsrc.ac.uk>
Sent: Friday, September 03, 2004 3:31 PM
Subject: Re: CGI scripts, security and MySQL

[color=darkred]
> On Fri, 3 Sep 2004, Octavian Rasnita wrote:
>
user.

Chris Devers

2004-09-04, 8:55 am

On Fri, 3 Sep 2004, Octavian Rasnita wrote:

> No, the environment variables are set a single time at the start (or
> restart) of the web server.

Ok, that's what I thought.

So in what way does this help manage a pool of several users?

This seems to be a solution in search of some other problem...

--
Chris Devers cdevers@pobox.com
http://devers.homeip.net:8080/blog/

np: 'Drama Bug'
by David Sedaris
from 'This American Life: Lies, Sissies, and Fiascoes'

Octavian Rasnita

2004-09-04, 8:55 am

This might be helpful for more users, because a system admin can create
automaticly a special dir where the users can put their config files, and
"insert" that file in httpd.conf.
After that, every user can create its own config file, with any variables
they want (their names should not be only "user" and "pass"), and after that
chmod that special dir in order to be viewd only by the root.

T

Teddy

----- Original Message -----
From: "Chris Devers" <cdevers@pobox.com>
To: "Octavian Rasnita" <orasnita@fcc.ro>
Cc: <beginners-cgi@perl.org>; "michael watson (IAH-C)"
<michael.watson@bbsrc.ac.uk>
Sent: Friday, September 03, 2004 3:56 PM
Subject: Re: CGI scripts, security and MySQL

Chris Devers

2004-09-04, 8:55 am

On Fri, 3 Sep 2004, Octavian Rasnita wrote:

> This might be helpful for more users, because a system admin can
> create automaticly a special dir where the users can put their config
> files, and "insert" that file in httpd.conf.
>
> After that, every user can create its own config file, with any
> variables they want (their names should not be only "user" and
> "pass"), and after that chmod that special dir in order to be viewd
> only by the root.

This is all nice to know, but it doesn't appear to have anything at all
to do with the questions that the guy was asking.

He wants web site user accounts, with control &/or a record of who is
doing what in the database.

He wants some kind of database connection persistance.

He is not trying to set environment variables in Apache.

It isn't obvious how setting variables like this gets him anywhere near
the stated requirements of the system.

--
Chris Devers cdevers@pobox.com
http://devers.homeip.net:8080/blog/

np: 'Christmas Freud'
by David Rakoff
from 'This American Life: Lies, Sissies, and Fiascoes'

Sponsored Links