PurePerl MySQL API Prepared Statements

Foreword: In this tutorial I explain how to code, PurePerl MySQL API Prepared Statements.

By: Chrysanthus Date Published: 13 Apr 2016

Introduction

Normal SQL statements have a weakness called SQL injection. SQL injection leads to a wrong SQL statement being executed at the database server. In SQL injection, the attacker inserts wrong input data, which if not validated properly, would cause the SQL statement to be executed differently. A good way to be free from SQL injection, is to use prepared statements as explained in this tutorial. In this tutorial I explain how to code, PurePerl MySQL API Prepared Statements.

Inserting Data
Consider the SQL insert statement:

INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES ('TV Set', 'Entertainment', '425', 26, '25', '30');

With prepared statements, this statement is coded in three stages. The first stage is something like:

prepare("INSERT INTO Products (ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice) VALUES (?, 'Entertainment', ?, 26, '25', '30');");

Here, you have the function, prepare() whose argument is the modified INSERT string. Note that instead of the values, 'TV Set' and '425', question signs have been used. These questions signs are called, bound parameters. They are placeholders for values to be inserted, next.

The next stage is like:

execute("'TV Set','425'");

You have the function, execute(), whose arguments are the corresponding values. In the prepared() function, all the values (data) may be question signs, in which case, the execute function will have all the real values.

The third stage is,

stmt_close;

which closes the prepared scheme.

So, with prepared statements, these three functions have to be entered at the Mysql command prompt, in the order given.

Updating Data
Consider the SQL update statement:

UPDATE Products SET CostPrice = 26 WHERE ProductID = 1;

With prepared statements, this statement is coded in three stages. The first stage is something like:

prepare("UPDATE Products SET CostPrice = 27 WHERE ProductID = ?");

The next stage is like:

execute("1");

The third stage is,

stmt_close;

Selecting Data
Consider the SQL select statement:

SELECT Products.ProductName, Products.CostPrice FROM Products WHERE Products.Category = 'Entertainment' AND Products.SellingPrice = 30;

The first stage is something like:

prepare("SELECT Products.ProductName, Products.CostPrice FROM Products WHERE Products.Category = ? AND Products.SellingPrice = 30");

The next stage is like:

execute("'Entertainment'");

The third stage is,

stmt_close;

Deleting Data
Consider the SQL delete statement:

DELETE FROM Products WHERE ProductID = 1;

The first stage is something like:

prepare("DELETE FROM Products WHERE ProductID = ?");

The next stage is like:

execute("1");

The third stage is,

stmt_close;

That is it for this ttutorial.

Chrys

Broad Network

Related Articles

PurePerl MySQL API Prepared Statements

Introduction

Related Links

Comments