EMySQL Prepared Statements

Using the EMySQL API – Part 8

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

By: Chrysanthus Date Published: 12 Aug 2016

Introduction

This is part 8 of my series, Using the EMySQL 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 that. 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:

    var prepareStr = `INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Nelly',?,?,'m','2009-03-30',NULL)`;
    con.prepare(prepareStr, function(err, feedback)
        {
            if (err)
                console.log(err);
            else
                console.log('Statement has been prepared');
        });

    var executeStr = `'Marie','wild'`;
    con.execute(executeStr, function(err, feedback)
        {
            if (err)
                console.log(err);
            else
                console.log('Row has been inserted');
        });

    con.statementClose();

Note the use of backticks. Also note the use of the reserved words, prepare and execute. In this construct the semicolon is after the last backtick and not before it.

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:

    var updateStr = `UPDATE pet SET species = ? WHERE name = 'Nelly'`;
    con.prepare(updateStr, function(err, feedback)
        {
            if (err)
                console.log(err);
            else
                console.log('Statement has been prepared');
        });

    var executeStr = `'domestic'`;
    con.execute(executeStr, function(err, feedback)
        {
            if (err)
                console.log(err);
            else
                console.log('Row has been updated');
        });

    con.statementClose();

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:

    var selStr = `SELECT * FROM pet WHERE species = ? OR species = ?`;
    con.prepare(selStr, function(err, feedback)
        {
            if (err)
                console.log(err);
            else
                console.log('Statement has been prepared');
        });

    var executeStr = `'snake', 'bird'`;
    con.execute(executeStr, function(err, result)
        {
            if (err)
                console.log(err);
            else
                {
                 for (i=0; i<result.length; ++i)
                     {
                         tempStr = `${result[i].get('name')}, ${result[i].get('owner')}, ${result[i].get('species')}, ${result[i].get('sex')}, ${result[i].get('birth')}, ${result[i].get('death')}`

                         console.log(tempStr);
                     }
                }
        });

    con.statementClose();

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:

    var prepareStr = `INSERT INTO pet (name, owner, species, sex, birth, death) VALUES ('Piggy',?,?,'m','2009-03-30',NULL)`;
    con.prepare(prepareStr, function(err, feedback)
        {
            if (err)
                console.log(err);
            else
                console.log('Statement has been prepared');
        });

    con.sendLongData('Likes Pig', 0);
    con.sendLongData('pig\'s long info', 1);

    con.execute(function(err, feedback)
        {
            if (err)
                console.log(err);
            else
                console.log('Row has been inserted');
        });

    con.statementClose();

Note the use of the function (method), sendLongData('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 construct, should end with:

    con.statementClose();

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

Chrys

Broad Network

Related Articles

EMySQL Prepared Statements

Using the EMySQL API – Part 8

Introduction

Related Links

Comments