Perl MySQL Driver Support

fredfred5

Hi,

I've got a captive portal up and running using a radius database, I run lightsquid to monitor the internet usage and sites visited.

My lightsquid.cfg has the realname option enabled which gives me this when I look at the report:

Which is fine, however I want more information; specifically the "Real Name" field to be populated.

From a bit of searching I've found one way to do this; edit the /usr/local/etc/lightsquid/realname.cfg file, manually putting in the ip addresses followed by names. I guess this is fine for smaller networks but there are far too many users on my network for me to waste time putting in 100+ names everyday.

So with more searching I came across this post on a blog:

http://itguest.blogspot.co.uk/2012/03/show-user-freeradius-from-lightsquid.html

Which sounded perfect, as I am using freeradius (using a MySQL database) to authenticate my CP users. A little tweaking of the script (it didn't look quite right) gave me this:


#!/usr/bin/perl

use DBI;
use Socket;
#use file;

($sec_now, $min_now, $hour_now, $date_now, $mon_now, $year_now, $dayno_now) = localtime();
$mon_now++;
$year_now += 1900;
$todayni= sprintf("%04d-%02d-%02d",$year_now, $mon_now, $date_now);

$dsn = "DBI:mysql:database=radius;host=IPADDRESS";
$dbh = DBI->connect($dsn,"USER","PASSWORD"); #USER is user for mysql, PASSWORD is password for mysql
$sth = $dbh->prepare("select * from radacct,radcheck where radacct.AcctStartTime >= '".$todayni." 00:00:00' and radacct.AcctStartTime <= '".$todayni." 23:59:59' and radacct.UserName = ' r$ $sth->execute()");
#print "Content-type:text/html\n\n";
open(FILE, ">/usr/local/etc/lightsquid/realname.cfg");
while (my $ref = $sth->fetchrow_hashref()) {
#print FILE "$ref->{'FramedIPAddress'} $ref->{'firstname'} $ref->{'lastname'}\n"; 
print FILE "$ref->{'framedipaddress'} $ref->{'username'} $ref->{'name'}\n";

}
close(FILE);
$sth->finish();
$dbh->disconnect();

(I'm not any good at scripting, but that looked correct to me, if its not please tell me)

I placed the script in /usr/local/www/lightsquid/realname.pl, made it executable with chmod +x and ran it from the pfsense shell.

It failed.

Turns out I need to install DBI support for perl, easy enough with some searching turns out I need to run:


pkg_add -r http://files.pfsense.org/packages/amd64/8/All/p5-DBI-1.616_1.tbz

(I'm running 64bit)

It seemed to work a bit better after that, I no longer got the same error message.

However its now replaced with this error:


install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/local/lib/perl5/5.12.4/BSDPAN /usr/local/lib/perl5/site_perl/5.12.4/mach /usr/local/lib/perl5/site_perl/5.12.4 /usr/local/lib/perl5/5.12.4/mach /usr/local/lib/perl5/5.12.4 .) at (eval 5) line 3.
Perhaps the DBD::mysql perl module hasn't been fully installed,
or perhaps the capitalisation of 'mysql' isn't right.
Available drivers: DBM, ExampleP, File, Gofer, Proxy, Sponge.
 at realname.pl line 14

I can't find a p5-DBD-mysql module in the pfsense package lists all I see is this:

http://files.pfsense.org/packages/amd64/8/All/p5-DBD-SQLite-1.35.tbz

I've added it as a package just to be sure but it still doesn't work I still get the same error.

What do I do now?

I'm sure loads of people would find this useful if we crack it.

Any help would be greatly appreciated!

marcelloc

Try to change dbi to sqlite instead of mysql. IIRC query sintax on perl dbi will be the same.

fredfred5

OK Thanks, that works a little better.

Now I get this error:


DBD::SQLite::db prepare failed: unrecognized token: "' r->execute()" at realname.pl line 15.
Can't call method "fetchrow_hashref" on an undefined value at realname.pl line 18.

marcelloc

Check a sqlite sample on cpan.

using DBD
http://search.cpan.org/~msergeant/DBD-SQLite-0.31/lib/DBD/SQLite.pm
http://mailliststock.wordpress.com/2007/03/01/sqlite-examples-with-bash-perl-and-python/

using DB
http://search.cpan.org/~vxx/SQLite-DB-0.04/lib/SQLite/DB.pm

Remember to create the sqlite database first

att,
Marcello Coutinho