Perl MySQL Driver Support



  • 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!



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



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



Log in to reply