CGI scripts, security and MySQL

[michael watson (IAH-C)]

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

--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

RE: ODBC

[Ron Goral]

> -Original Message-
> From: Wiggins d Anconia [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 01, 2004 8:33 AM
> To: Rearick, Kenneth N.; '[EMAIL PROTECTED]'
> Subject: Re: ODBC
>
>
> >
> >
> >
> > I have a CGI program in which I am trying to access a database. When I
> run the code in active state feeding it the input from the form it runs
> fine. When I try to run it as a cgi from IE using Apache web server the
> data from the form comes in fine but it can not seem to attach to the
> database.
> >
> > Is there anyway to see the errors that the DBI:ODBC is generating when
> the application is being run from the server? Any ideas why the ODBC
> connect is failing?
> >
>
> You can use CGI::Carp and 'fatalsToBrowser' to have fatal messages
> thrown to the browser, alternatively error messages are generally sent
> to the Apache error log.  Check there for what they have to say. You
> could also turn off DBI's automatic exceptions and catch them yourself,
> but this is a fair amount more work (at least while prototyping).
>
> > $dbh = DBI->connect("DBI:ODBC:$SERVER", $USER, $PASSWORD);
> >
> >
>
> Sorry can't help with the connect issue, I suspect if you can find the
> error output it will.  If not you might try the dbi-users group or maybe
> someone else with ODBC experience will chime in.
>
> http://danconia.org
>

CGI::Carp may not capture errors that occur in the DBI module. However, DBI
has a built in logging functionality called "trace" which allows you to
specify the level of detail you want to see as well as specify where you
want the trace output stored.  Note that trace will log "everything" that is
being done in the name of DBI, so be prepared to wade through alot of info.
Though I would recommend reading the entire documentation, atleast go to
this address and check out this function:

http://search.cpan.org/~timb/DBI-1.43/DBI.pm#TRACING

HTH,
Peace in Christ -
Ron Goral



-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: CGI scripts, security and MySQL

[Chris Devers]

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  [EMAIL PROTECTED]
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'
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: CGI scripts, security and MySQL

[Octavian Rasnita]

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

T

- Original Message - 
From: "Chris Devers" <[EMAIL PROTECTED]>
To: "michael watson (IAH-C)" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Friday, September 03, 2004 3:08 PM
Subject: Re: CGI scripts, security and MySQL


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


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: CGI scripts, security and MySQL

[Chris Devers]

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  [EMAIL PROTECTED]
http://devers.homeip.net:8080/blog/
np: 'Flight vs. Invisibility'
 by John Hodgman
 from 'This American Life: Crimebusters and Crossed Wires'
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: CGI scripts, security and MySQL

[Octavian Rasnita]

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" <[EMAIL PROTECTED]>
To: "Octavian Rasnita" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; "michael watson (IAH-C)"
<[EMAIL PROTECTED]>
Sent: Friday, September 03, 2004 3:31 PM
Subject: Re: CGI scripts, security and MySQL


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


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: CGI scripts, security and MySQL

[Chris Devers]

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  [EMAIL PROTECTED]
http://devers.homeip.net:8080/blog/
np: 'Drama Bug'
 by David Sedaris
 from 'This American Life: Lies, Sissies, and Fiascoes'
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: CGI scripts, security and MySQL

[Octavian Rasnita]

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" <[EMAIL PROTECTED]>
To: "Octavian Rasnita" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; "michael watson (IAH-C)"
<[EMAIL PROTECTED]>
Sent: Friday, September 03, 2004 3:56 PM
Subject: Re: CGI scripts, security and MySQL



-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

RE: ODBC

[Rearick, Kenneth N.]

Thanks for the help. It turned out to be a setup problem with ODBC the DNS was in the 
user area not the system. But the information on tracing errors help identify where 
the problem was located. 

-Original Message-
From: Ron Goral [mailto:[EMAIL PROTECTED] 
Sent: Friday, September 03, 2004 6:35 AM
To: Rearick, Kenneth N.; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: ODBC



> -Original Message-
> From: Wiggins d Anconia [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 01, 2004 8:33 AM
> To: Rearick, Kenneth N.; '[EMAIL PROTECTED]'
> Subject: Re: ODBC
>
>
> >
> >
> >
> > I have a CGI program in which I am trying to access a database. When I
> run the code in active state feeding it the input from the form it runs
> fine. When I try to run it as a cgi from IE using Apache web server the
> data from the form comes in fine but it can not seem to attach to the
> database.
> >
> > Is there anyway to see the errors that the DBI:ODBC is generating when
> the application is being run from the server? Any ideas why the ODBC
> connect is failing?
> >
>
> You can use CGI::Carp and 'fatalsToBrowser' to have fatal messages
> thrown to the browser, alternatively error messages are generally sent
> to the Apache error log.  Check there for what they have to say. You
> could also turn off DBI's automatic exceptions and catch them yourself,
> but this is a fair amount more work (at least while prototyping).
>
> > $dbh = DBI->connect("DBI:ODBC:$SERVER", $USER, $PASSWORD);
> >
> >
>
> Sorry can't help with the connect issue, I suspect if you can find the
> error output it will.  If not you might try the dbi-users group or maybe
> someone else with ODBC experience will chime in.
>
> http://danconia.org
>

CGI::Carp may not capture errors that occur in the DBI module. However, DBI
has a built in logging functionality called "trace" which allows you to
specify the level of detail you want to see as well as specify where you
want the trace output stored.  Note that trace will log "everything" that is
being done in the name of DBI, so be prepared to wade through alot of info.
Though I would recommend reading the entire documentation, atleast go to
this address and check out this function:

http://search.cpan.org/~timb/DBI-1.43/DBI.pm#TRACING

HTH,
Peace in Christ -
Ron Goral


-- 
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
 

Re: CGI scripts, security and MySQL

[Chris Devers]

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  [EMAIL PROTECTED]
http://devers.homeip.net:8080/blog/
np: 'Christmas Freud'
 by David Rakoff
 from 'This American Life: Lies, Sissies, and Fiascoes'
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]