The pfSense Store

Author Topic: Perl MySQL Driver Support  (Read 950 times)

0 Members and 1 Guest are viewing this topic.

Offline fredfred5

  • Newbie
  • *
  • Posts: 19
  • Karma: +0/-0
    • View Profile
Perl MySQL Driver Support
« on: July 20, 2012, 09:03:56 am »
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:

Code: [Select]
#!/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:

Code: [Select]
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:

Code: [Select]
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!
« Last Edit: July 20, 2012, 09:31:59 am by fredfred5 »

Offline marcelloc

  • Hero Member
  • *****
  • Posts: 9996
  • Karma: +4/-0
    • View Profile
Re: Perl MySQL Driver Support
« Reply #1 on: July 20, 2012, 09:59:04 am »
Try to change dbi to sqlite instead of mysql. IIRC query sintax on perl dbi will be the same.

Offline fredfred5

  • Newbie
  • *
  • Posts: 19
  • Karma: +0/-0
    • View Profile
Re: Perl MySQL Driver Support
« Reply #2 on: July 20, 2012, 10:15:17 am »
OK Thanks, that works a little better.

Now I get this error:

Code: [Select]
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.

Offline marcelloc

  • Hero Member
  • *****
  • Posts: 9996
  • Karma: +4/-0
    • View Profile
« Last Edit: July 20, 2012, 10:31:18 am by marcelloc »