Perl: Am I Root?

Testing to see if you are running with root privileges is straight-forward in Perl. Here is some sample code:

[tjinkers@ebl-oel6vm1 perl]$ cat ./testroot.pl
#!/usr/bin/perl

if ( $< eq 0 ){
   print "I'm root\n";
} else {
   print "Not root\n";
}
[tjinkers@ebl-oel6vm1 perl]$ ./testroot.pl
Not root
[tjinkers@ebl-oel6vm1 perl]$ sudo ./testroot.pl
I'm root
[tjinkers@ebl-oel6vm1 perl]$

As the saying goes, "That was easy!"


And the follow-up question - how do you check when using Bash

# Make sure only root can run our script

if [[ $EUID -ne 0 ]]; then

  echo "This script must be run as root" 1>&2

  exit 1

fi

Perl: DB Connection details on the command line: Part 2

The previous post showed a simple routine for extracting out the database connection variables from the command line. It worked, but it had two flaws:

1. It doesn't validate your parameters
2. It requires you to put your password on the command line, visible to a PS command in Unix

The code below seeks to address both these issues. If you enter a paramter that I don't understand, it kicks you out, and it you end your input with a -p, it accepts the password without echoing it back to the screen, just like you hoped it would!

sub dbinfo() {
 use Term::ReadKey;
 my $helpinstructions = "Valid parameters are\n-h {host}\n" .
                        "-d {database}\n" . 
          "-u {user}\n" .
          "-p [password]\n";
 my %mydbinfo = ( db => "",
    host => "",
    user => "",
    passwd => ""
 );


 die $helpinstructions unless ($#ARGV > 5);

 for (my $i=0; $i <= $#ARGV; $i++) {
  $ARGV[$i]= "-?" if  ($ARGV[$i] ne '-h' and
     $ARGV[$i] ne '-d' and
     $ARGV[$i] ne '-u' and
     $ARGV[$i] ne '-p' );
  die $helpinstructions if ( $ARGV[$i] eq "-?" ) ;
  $mydbinfo{db} = $ARGV[++$i] if ( $ARGV[$i] eq "-d" ) ;
  $mydbinfo{host} = $ARGV[++$i] if ( $ARGV[$i] eq "-h" ) ;
  $mydbinfo{user} = $ARGV[++$i] if ( $ARGV[$i] eq "-u" ) ;
  if ( $ARGV[$i] eq "-p" ) {
   if ( $i ne $#ARGV) {
    $mydbinfo{passwd} = $ARGV[++$i];
   }
   else {
    print "\nPlease Enter passwd: ";
    ReadMode 'noecho';
    chomp($mydbinfo{passwd} = ReadLine);
    ReadMode 'normal';
    print "\n\n";
   }
  }
 
 }
 return %mydbinfo;
}
return true;

And the output:

D:\Documents and Settings\timj\perl>testcommandline.pl -h localhost -d cddb -u timj -p

Please Enter passwd:

Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>

The great thing is that you can directly replace your previous version of the dbsettings routine with this one, and there is no need to alter your existing scripts!

Perl: DB Connection details on the command line

What if you want to connect to a database, passing your parameters in on a command line? Here's a handy routine to do that:

First the included code

sub dbinfo() {
 my %mydbinfo = ( db => "",
    host => "",
    user => "",
    passwd => ""
 );


 die "Not Enough Parameters" unless ($#ARGV > 5);

 for (my $i=0; $i <= $#ARGV; $i++) {
  $mydbinfo{db} = $ARGV[++$i] if ( $ARGV[$i] eq "-d" ) ;
  $mydbinfo{host} = $ARGV[++$i] if ( $ARGV[$i] eq "-h" ) ;
  $mydbinfo{user} = $ARGV[++$i] if ( $ARGV[$i] eq "-u" ) ;
  $mydbinfo{passwd} = $ARGV[++$i] if ( $ARGV[$i] eq "-p" ) ;
 
 }
 return %mydbinfo;
}
return true;

Next the code that calls this routine

#!/usr/bin/perl
use strict;
use DBI();

require "D:\\Documents and Settings\\timj\\perl\\commanddbsettings.pl" or
die "Can't Open DB Settings File";


my %mydbinfo = dbinfo();
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1)

and here it is in action

D:\Documents and Settings\timj\perl>testcommandline.pl -d cddb -h localhost -u myuser -p xxx
Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>

It's not perfect, it doesn't do a lot of validation, and it doesn't allow you to enter the password with "echo off", but it's a good starting point.

Perl: DB Connection details in a seperate file

And finally, moving the connection details into a separate file that you can call from any program.
dbsettings.pl

sub dbinfo() {
 my %mydbinfo = ( db => "cddb",
    host => "localhost",
    user => "myuser",
    passwd => "xxx"
 );
 return %mydbinfo;
}
return true;

testrequire.pl

#!/usr/bin/perl
use strict;
use DBI();

require "D:\\Documents and Settings\\timj\\perl\\dbsettings.pl" or
die "Can't Open DB Settings File";


my %mydbinfo = dbinfo();
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1);

Perl: Connection Details in a Subroutine

This example moves the connection information out of the main flow of the program and in to a subroutine. Notice that I have declared the hash %mydbinfo twice. This is because in both cases they are declared as lexically scoped (using 'my'). This means that the %mydbinfo that we see in the subroutine is a totally sepoerate variable from the %mydbinfo in the main body of the code. If like me you earned your living writing COBOL in the 80's this may seem strange - why would you want to do this. The answer is taht it makes it very clear what the scope of the variable is and who can see it. You can define global variables, but it's considered good practice to limit the scope of variables as tightly as possible whilst still achieving your goals.

#!/usr/bin/perl
use strict;
use DBI();

sub dbinfo() {
 my %mydbinfo = ( db => "cddb",
    host => "localhost",
    user => "myuser",
    passwd => "xxx"
 );
 return %mydbinfo;
}
  
my %mydbinfo = dbinfo();
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1)

Perl: Storing your DB Connect in a hash

This is a half-way house script, that uses a hash to store the database information for the connect.

#!/usr/bin/perl
use strict;
use DBI();

my %mydbinfo = ( db => "cddb",
   host => "localhost",
   user => "myuser",
   passwd => "xxx"
 );
  
my $dbconnect = "DBI:mysql:database=" . $mydbinfo{db} . ";host=" . $mydbinfo{host} ;

my $dbh1 = DBI->connect($dbconnect,$mydbinfo{user}, $mydbinfo{passwd}, { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
        print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1);

This has the advantage of putting all the details neatly at the start of the code, but still has the full DB connect details hard coded, which is obviously something to avoid in most circumstances.

Compare the Perl with PHP

I had this idea that I should write exactly the same code in PHP that I had just written in Perl, just to compare.

<?php
function openMyDB()
{
include("DBInfo.inc");
$con = mysql_connect($mydbhost,$mydbuser,$mydbpassword);
if (!$con) {
        die('Could not connect: ' . mysql_error());
}
mysql_select_db("cddb", $con);
return $con;
}

$con = openMyDB();

mysql_select_db("cddb", $con);

print "Name\t\tTitle\n";
$result = mysql_query("SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid");
while ($row = mysql_fetch_array($result)) {
        echo $row['name'] . "\t" . $row['title'] . "\n" ;
}
mysql_free_result($result);
mysql_close($con);
?>

D:\Database\CDDB\Reports>php CDRep.php
Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Database\CDDB\Reports>

So, which do you prefer?

Please note that:

include("DBInfo.inc"); 

is my own code (for what it's worth) that we've seen in previous examples.

D:\Database\CDDB\Reports>more  DBInfo.inc
<?php
$mydbhost="localhost";
$mydbuser="myuser";
$mydbpassword="mypassword";
?>

D:\Database\CDDB\Reports> 

A Perl Script that accesses MySQL

Last year I posted a simple Perl script that accesses a PostgreSQL database using the DBI connector. I decided that I should do the same for MySQL. I'm using Active State Perl on Windows XP. Amusingly, this version does not include the MySQL connectors as default, but it's easy enough to add using perl ppm (Perl's Perl Package Manager).

I want a simple script that lists out artists and their CDs in my database.

#!/usr/bin/perl
use strict;
use DBI();
my $dbh1 = DBI->connect("DBI:mysql:database=cddb;host=localhost","uuuu", "xxxxxx", { raiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";
my $sql = "SELECT artist.name, cd.title FROM artist, cdtable AS cd WHERE artist.id = cd.artid";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $title );
$sth->bind_columns( \$name, \$title );

print "Name\t\tTitle\n";
while( $sth->fetch() ) {
 print "$name\t$title\n";
}
$dbh1->disconnect() if($dbh1)

D:\Documents and Settings\timj\perl>c:\Perl\bin\perl cd.pl
Name            Title
Phatfish        Guaranteed
Phatfish        15
Bethany Dillon  Waking Up
Bethany Dillon  Bethany Dillon
Bethany Dillon  Imagination
Nick Drake      Made To Love Magic
Nick Drake      Five Leaves Left
Nick Drake      Bryter Layter

D:\Documents and Settings\timj\perl>

That's about as simple as it get, I'm sure that there is far more to it than that, but this will do for now!
Tim

Accessing a database via Perl

Here's a small program I wrote to access a Postgresql database.

#!/usr/bin/perl
use DBI;
$dbh1 = DBI->connect( "dbi:Pg:dbname=testdb@localhost","", "", { RaiseError => 1, AutoCommit => 0 })
|| die "Database connection not made: $DBI::errstr";

my $sql = "select vendor, country, freephone, payphone from numbers ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $vendor, $country, $freephone, $payphone );
$sth->bind_columns( \$vendor, \$country, \$freephone, \$payphone );

print "Vendor\tCountry\tFreephone\tPayphone\n";
while( $sth->fetch() ) {
print "$vendor\t$country\t$freephone\t$payphone\n";
}

my $sql = "select name, vendor, leader, participant from myuser ";
my $sth = $dbh1->prepare($sql);

$sth->execute();

my( $name, $vendor, $leader, $participant );
$sth->bind_columns( \$name, \$vendor, \$leader, \$participant );

print "\nName\tVendor\tLeader\tParticipant\n";
while( $sth->fetch() ) {
print "$name\t$vendor\t$leader\t$participant\n";
}

$dbh1->disconnect() if($dbh1);

The output looks like this:

timj@dell-desktop:~/MySync/db$ ./plsql_phones1.pl
Vendor Country Freephone Payphone
Verizon UK 44-20-7075-3246 0808-238-6025
Verizon France 33-1-70-70-74-20 080-563-9647
Verizon USA 1-203-418-3122 866-692-3163
Verizon India 000-800-852-1266

Name Vendor Leader Participant
Tim Verizon 1234567 1234567
timj@dell-desktop:~/MySync/db$