Cheshire Cat Computing

Hi,
for my use, i had implemented a simple mysql autentication function with relative configuration parameter in routers2.conf:

in [web] section of routers2.conf i put:

mysql-server = 127.0.0.1
mysql-database = authdb
mysql-user = root
mysql-pass = password

and i modify routers2.cgi in this way:

in user_verify function i add after readconf( 'web' );

if( defined( $config{'web-mysql-server'} ) ) {
$rv = mysql_verify($u,$p);
return $rv if($rv);
}

and after sub ldap_verify i create the new function:

sub mysql_verify($$) {
my($u, $p) = @_;
require DBI;

my $dsn = "DBI:mysql:database=".$config{'web-mysql-database'}.";host=".$config{'web-mysql-server'}.";mysql_socket=/var/lib/m
ysql/mysql.sock";

my $dbh = DBI->connect("$dsn", $config{'web-mysql-user'}, $config{'web-mysql-pass'})
or die "Couldn't connect to database: " . DBI->errstr;

my $sthini = $dbh->prepare("SELECT PASSWORD(?),pass FROM users WHERE user=?")
or die "Couldn't prepare statement: " . $dbh->errstr;
$sthini->execute($p,$u)
or die "Couldn't execute statement:" . $dbh->errstr;

my @row = $sthini->fetchrow_array;
$sthini->finish;

if ($row[0] eq $row[1]) { return 1; }
return 0;
}


in mysql i create db authdb with this table structure:

CREATE TABLE `users` (
`user` varchar(15) NOT NULL,
`pass` varchar(50) NOT NULL,
PRIMARY KEY (`user`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

For add a user just a simple:

INSERT INTO `authdb`.`users` (`user` ,`pass`) VALUES ('username', PASSWORD( 'something' ));

And it works well for my needs.
If you want to put this in future version i think is not so bad (it's a very simple mod).
Also i think that if it's possible, it's a good idea to put in table structure also personalized configuration option that i must now put in routers2.conf [user-something] section.
Bye.
Marco Chiavacci

This is really good, and I've been considering mySQL support for a long time. I'll add this to the next version... this of course uses a table to hold username/password rather than using the mySQL bind as a test, possibly giving both methods would be preferable. I'll see what I can do next week...

Thanks for this contribution!

Steve

_________________
Steve Shipway
UNIX Systems, ITSS, University of Auckland, NZ
Woe unto them that rise up early in the morning... -- Isaiah 5:11

I've now incorporated this into v2.20beta3.

I've had to make a few changes (to give option of bind auth or table auth, to pass error messages in $debugmessage, explicitly disconnect (for mod_perl users) and to catch 'require' errors) but all looks OK - I'll need to run a few tests though so nothing just yet.

_________________
Steve Shipway
UNIX Systems, ITSS, University of Auckland, NZ
Woe unto them that rise up early in the morning... -- Isaiah 5:11