A Simple MySQL Client API in PurePerl

Writing MySQL Protocol Packets in PurePerl – Part 7

Writing a Perl Module

Foreword: In this part of the series, I explain the code of a simple MySQL client API in PurePerl.

By: Chrysanthus Date Published: 28 Jan 2015

Introduction

This is part 7 of my series, Writing MySQL Protocol Packets in PurePerl. PurePerl stands for Pure Perl. It is Perl software without any C software underneath. An API (Application Programming Interface) is software that stands between one software and another and facilitates communication between the two software. In this part of the series, I explain the code of a simple MySQL client API in PurePerl. You should have read the previous parts of the series before reaching here, as this is a continuation.

The pure Perl program uses the packages developed in the previous parts of the series. I explain the different code segments of the program from the top of the file to the bottom. You can then assemble the different segments to try the program (file). The file is called, clientmysql.pl.

For the files and packages in this series, if you are not using Aciveperl, then you should precede the file with something like, #!/usr/bin/perl .

The Code Segments
If you have all the pre-knowledge I have been asking of, in the previous series, and if you have been reading this series in the order given, then it should not be difficult for you to understand the code segments below. The first code segment brings in the different packages. It is:

use Sha1;
use Greeting;
use Credentials;
use Command;

The next code segment imposes “use strict” and brings in the Socket Package. It is:

use strict;
use Socket;

The next code segment reads the username and password from the command line and removes the newline characters. It is:

    #input the user name and the password
    print "Type in the user and press Enter: ";
    my $user = <STDIN>;
    print "Type in the password and press Enter: ";
    my $password = <STDIN>;

    chomp($user); chomp($password);

The next code segment creates a socket and connects to MySQL server. Here, MySQL is at localhost. The default MySQL port is 3306. The code segment is:

    my ($iaddr, $paddr, $proto);

    $proto   = getprotobyname("tcp");
    $iaddr = gethostbyname("localhost");
    $paddr   = sockaddr_in(3306, $iaddr);
    socket(SOCK, PF_INET, SOCK_STREAM, $proto) || die "socket: $!";
    connect(SOCK, $paddr) || die "connect: $!";

The next code segment receives the greeting packet from the server. It is:

    my $greet_pket;
    recv (SOCK, $greet_pket, 200, 0);

The next code segment combines the password as typed by the user with the scramble message sent by the server, in a special way. It is:

    #Do: token = SHA1(scramble + SHA1(stage1_hash)) XOR stage1_hash
    #stage1_hash = SHA1(password)
    #Note SHA1(A+B) is the SHA1 of the concatenation of A with B.
    my $stage1_hash = Sha1::sha1($password);
    my $stage2_hash = Sha1::sha1($stage1_hash);

    my %ha_grt_pkt = Greeting::greet($greet_pket);
    my $scramble = $ha_grt_pkt{'scramble'};
    my $addition = $scramble . $stage2_hash;
    my $sha1_add = Sha1::sha1($addition);
    my $token = $sha1_add ^ $stage1_hash;

The next code segment develops the Credentials packet and sends it to the server; it also receives the reply. It is:

    #develop and bring here, the Credentials Packet as a string
    my $credentials_pkt = Credentials::credentials($user, $token);

    #send the credentials packet
    send (SOCK, $credentials_pkt, 0);
    #receive the reply
    my $credentials_reply;
    recv (SOCK, $credentials_reply, 200, 0);

The next code segment develops two command packets: the first is to choose (select) a database and the second is to create a table in the selected database. The SQL statement typed, is not ended with a semicolon. Each of the sub-segments receives a reply. The code segment is:

    #develop and bring here, the command Packet for Select DB as a string
    my $db_pkt = Command::command(2, "test");

    #send the DB packet
    send (SOCK, $db_pkt, 0);
    #receive the reply
    my $db_reply;
    recv (SOCK, $db_reply, 200, 0);

    #develop and bring here, the command Packet for Create Table as a string
    my $cr_tabl = "CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), species VARCHAR(20), sex CHAR(1), birth DATE, death DATE)";
    my $cr_tabl_pkt = Command::command(3, $cr_tabl);

    #send the DB packet
    send (SOCK, $cr_tabl_pkt, 0);
    #receive the reply
    my $cr_tabl_reply;
    recv (SOCK, $cr_tabl_reply, 200, 0);

The next and last code segment closes the connection. It is:

    close (SOCK) || die "close: $!";

So, you can type together all the above code segments in the order given to form the program. For you to execute the program (API) the MySQL server has to be running.

Developing the API
You can develop this program (API) to take care of all the possible packages and client commands, in the same light that the program has been developed. I do that in the next series.

Chrys

Broad Network

Related Articles

A Simple MySQL Client API in PurePerl

Writing MySQL Protocol Packets in PurePerl – Part 7

Writing a Perl Module

Introduction

Related Links

Comments