PurePerl MySQL Prepared Statements

Using the PurePerl MySQL API – Part 8

Foreword: In this part of the series, I talk about MySQL prepared statements.

By: Chrysanthus Date Published: 31 Aug 2016

Introduction

This is part 8 of my series, Using the PurePerl MySQL API. It is possible to evaluate a SQL statement for the MySQL database using placeholders (?s) ; then after, you send the values for the placeholders. You need MySQL prepared statements for that.  In this part of the series, I talk about MySQL prepared statements. I will use the INSERT, UPDATE and SELECT statements for illustration. You should have read the previous parts of the series before coming here, as this is a continuation. Prepared statements also provide added security (limit SQL injection) especially when input comes directly from the user.

Inserting
Consider the normal INSERT statement,

    INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Nelly','Marie','wild','m','2009-03-30',NULL)

If you want to send the values 'Marie' and 'wild' later, then the prepared statement will be:

    INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Nelly',?,?,'m','2009-03-30',NULL)

where the two ?s are placeholders for the values, 'Marie' and 'wild'.

The EMySQL construct to do this is:

    my $prepareStr = "INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Nelly',?,?,'m','2009-03-30',NULL)";
        if (Mysql::prepare($prepareStr) != 1)
            {
                print $Mysql::Error_msg, "\n";
            }

    my $executeStr = "'Marie','wild'";

    if (Mysql::execute($executeStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "statement executed \n";
        }

    Mysql::stmt_close;

Note the use of the reserved words, prepare and execute.

Updating
Consider the statement:

    UPDATE pet SET species = 'domestic' WHERE name = 'Nelly'

You may want to send the statement with a placeholder for 'domestic' and then send 'domestic' afterwards. The construct to do this is:

    my $updateStr = "UPDATE pet SET species = ? WHERE name = 'Nelly'";
        if (Mysql::prepare($updateStr) != 1)
            {
                print $Mysql::Error_msg, "\n";
            }

    my $executeStr = "'domestic'";
    if (Mysql::execute($executeStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "statement executed \n";
        }

    Mysql::stmt_close;

Note the use of backticks and also the use of the prepare and execute function calls.

Selecting
Consider the statement,

    SELECT * FROM pet WHERE species = 'snake' OR species = 'bird'

The values 'snake' and 'bird' can be sent as placeholders in a prepared statement; then after, the actual values are sent in an execute statement. The following construct does this:

    my $selStr = "SELECT * FROM pet WHERE species = ? OR species = ?";
    if (Mysql::prepare($selStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }

    my $executeStr = "'snake', 'bird'";
    if (Mysql::execute($executeStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            for (my $i=0; $i<$Mysql::No_of_Rows; ++$i)
                {
                   print $Mysql::Result[$i]{'name'},  ', ';
                   print $Mysql::Result[$i]{'owner'},  ', ';
                   print $Mysql::Result[$i]{'species'},  ', ';
                   print $Mysql::Result[$i]{'sex'},  ', ';
                   print $Mysql::Result[$i]{'birth'},  ', ';
                   print $Mysql::Result[$i]{'death'},  ', ';
                   print "\n";
                }
        }

    Mysql::stmt_close;

Long Cell Values
A cell value for a placeholder may be long. The following construct uses an INSERT statement to illustrate how to send such values:

    my $prepareStr = "INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Piggy',?,?,'m','2009-03-30',NULL)";
    if (Mysql::prepare($prepareStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }

    Mysql::send_long_value('Likes Pig', 0);
    Mysql::send_long_value("pig\'s long info", 1);

    if (Mysql::execute() != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "statement executed \n";
        }

    Mysql::stmt_close;

Note the use of the function (method), send_long_value('long text', index) . Here, index counting begins from zero and it is for the placeholders.

Closing a Statement
Any prepared construct has to be closed. So all the above constructs, should end with:

    Mysql::stmt_close;

Resetting Long Value Action
Any statement prepared has to be executed. After sending long values and you change your mind, you have to reset the action before executing. In this way, the long values will not be saved into the database. The following code illustrates this:

    my $prepareStr = "INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Fatty',?,?,'m','2009-03-30',NULL)";
    if (Mysql::prepare($prepareStr) != 1)
        {
            print $Mysql::Error_msg, "\n";
        }

    Mysql::send_long_value('long one', 0);
    Mysql::send_long_value("long two", 1);

    if (Mysql::stmt_reset() != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "action reversed \n";
        }

    if (Mysql::execute() != 1)
        {
            print $Mysql::Error_msg, "\n";
        }
    else
        {
            print "statement executed \n";
        }

    Mysql::stmt_close;

That is it for this part of the series. We stop here and continue in the next part.

Chrys

Broad Network

Related Articles

PurePerl MySQL Prepared Statements

Using the PurePerl MySQL API – Part 8

Introduction

Comments