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