Perl & MySQL

Perl & MySQL help. Here we will give some help on some common issues with regard to MySQL database access from within your Perl programs.

Requirements: Perl 5 (preferably the latest production release), Perl DBI (downloadable from CPAN), DBD::mysql (see CPAN) and MySQL database server.

Simple script that illustrates how to connect to a MySQL database and issues a range of select, delete and update queries:

#! /usr/bin/perl -wT

use strict;
use DBI;

$ENV{PATH} = "/bin:/usr/bin";
delete @ENV{'IFS', 'CDPATH', 'ENV', 'BASH_ENV'};


# we assume that the name of your database is DATABASE, that the username 
# to connect is UN and the password is PW. Here we also assume that your 
# MySQL server is running on localhost (so your are connecting through 
# the Unix socket file).

my $dbh = DBI->connect('DBI:mysql:DATABASE:localhost', 'USERNAME', 'PASSWORD')
  or die "Cannot connect: " . $DBI::errstr;

# or:

my $dbh = DBI->connect('DBI:mysql:database=DATABASE;host=localhost','USERNAME','PASSWORD')
  or die "Cannot connect: " . $DBI::errstr;


# select a single field:

my $sql = qq`SELECT field FROM table WHERE otherfield=1`;
my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();
my $field = $sth->fetchrow_array();
$sth->finish();

print "field is $field \n";


# select multiple fields:

$sql = qq`SELECT fielda,fieldb,fieldc FROM table WHERE otherfield=1`;
$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();
my ($fielda,$fieldb,$fieldc) = $sth->fetchrow_array();
$sth->finish();

print "fielda is $fielda , fieldb is $fieldb , fieldc is $fieldc \n";


# select multiple fields with multiple rows:

$sql = qq`SELECT fielda,fieldb,fieldc FROM table`;
$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();
my @row;
my @fields;
while(@row = $sth->fetchrow_array()) {
  my @record = @row;
  push(@fields, \@record);
}
$sth->finish();


# now process the fields

if (@fields != 0) {
  my $i=0;
  foreach $line (@fields) {
    print "row $i - a is @$line[0] , b is @$line[1] , c is @$line[2] \n";
    $i++;
  }
}


# delete query

$sql = qq`DELETE FROM table WHERE field=1`;
$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();
$sth->finish();


# update query

$sql = qq`UPDATE table SET field=1 WHERE otherfield=2`;
$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();
$sth->finish();


# To increase your application security and prevent SQL injection attacks,
# it is advisable to use placeholders in queries (prepared statements):

$sql = qq`SELECT field FROM table WHERE name=? AND password=?`;
$sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute($name, $password) or die "Cannot execute: " . $sth->errstr();
my $field = $sth->fetchrow_array();
$sth->finish();