Binding parameters & columns with Perl in MySQL

SQL statements are often dynamically built. A user provides some input and it is built into the statement. A programmer must be cautious every time he deals with an input from a user. It has some serious security implications. The recommended way to dynamically build SQL statements is to use parameter binding.

Binding parameters guards the program against SQL injections. It automatically escapes some special characters and allows them to be handled correctly. Many databases also increase significantly their performance, when we prepare the statements and bind the parameters.

#!/usr/bin/perl use strict;use DBI; my $dbh = DBI->connect(              "dbi:mysql:dbname=mydb",     "user12",                              "34klq*",                              { RaiseError => 1 },         ) or die $DBI::errstr; my $name = "Volkswagen";  my $sth = $dbh->prepare("SELECT * FROM Cars WHERE Name = ?");$sth->execute($name); my ($id, $name, $price) = $sth->fetchrow();print "$id $name $price\n"; $sth->finish();$dbh->disconnect();

The example selects a row from the Cars table for a specific car name.

my $name = "Volkswagen";

This is a value that could come from a user. For example from a HTML form.

my $sth = $dbh->prepare("SELECT * FROM Cars WHERE Name = ?");

The question mark ? is a placeholder for a value. It is added later in the script.

$sth->execute($name);

In the execute() method we bind the value to the placeholder.

The following example is the same as the previous one; this time we use the bind_param() method.

#!/usr/bin/perl use strict;use DBI; my $dbh = DBI->connect(              "dbi:mysql:dbname=mydb",     "user12",                              "34klq*",                              { RaiseError => 1 },         ) or die $DBI::errstr; my $name = "Volkswagen";  my $sth = $dbh->prepare("SELECT * FROM Cars WHERE Name = ?");$sth->bind_param(1, $name);$sth->execute(); my ($id, $name, $price) = $sth->fetchrow();print "$id $name $price\n"; $sth->finish();$dbh->disconnect();

Retrieving a row with a parameter binding, using the bind_param() method.

$sth->bind_param(1, $name);

The bind_param() method takes a value and associates it with the placeholder inside the SQL statement. There can be more placeholders. The placeholders are numbered from 1.

Quoting parameters

Using placeholders and binding parameters to them is the best way to deal with dynamic SQL statement building. Sometimes placeholders cannot be used. For example when we want to dynamically choose a table name. In such cases, we can concatenate the SQL string and use the quote() and quote_identifier() methods. We must use these methods for the variables, otherwise we introduce serious security bugs.

The quote() method quotes a string literal for use as a literal value in an SQL statement. It escapes any special characters (such as quotation marks) contained within the string and adds the required type of outer quotation marks. The quote_identifier() method quotes an identifier (table name etc.) for use in an SQL statement. It escapes any special characters (such as double quotation marks) it contains and adds the required type of outer quotation marks.

#!/usr/bin/perl use strict;use DBI; my $dbh = DBI->connect(              "dbi:mysql:dbname=mydb",     "user12",                              "34klq*",                              { RaiseError => 1 },         ) or die $DBI::errstr; my $table = "Cars";my $name = "Volkswagen"; my $sql = sprintf "SELECT * FROM %s WHERE Name = %s",     $dbh->quote_identifier($table), $dbh->quote($name); my $sth = $dbh->prepare($sql);$sth->execute(); my @row;while (@row = $sth->fetchrow_array()) {    print "@row\n";} $sth->finish();$dbh->disconnect();

In the example we build the SQL statement string dynamically using the quote() and quote_identifier() methods.

my $table = "Cars";my $name = "Volkswagen";

These are the Perl scalars to be used in the SQL statement.

my $sql = sprintf "SELECT * FROM %s WHERE Name = %s",     $dbh->quote_identifier($table), $dbh->quote($name);

We have a more complex SQL statement. It is not possible to build this statement using placeholders. We use the quote methods to quote the supplied scalars.

Binding columns

When using the fetch methods, we copy the returned values to Perl variables. This process may be simplified and made faster by binding columns. The Perl DBI has bind_col() and bind_columns() methods, which associate Perl variables with table columns.

#!/usr/bin/perl use strict;use DBI; my $dbh = DBI->connect(              "dbi:mysql:dbname=mydb",     "user12",                              "34klq*",                              { RaiseError => 1 },         ) or die $DBI::errstr; my $sth = $dbh->prepare( "SELECT * FROM Cars LIMIT 4" );  $sth->execute(); $sth->bind_columns(\my($id, $name, $price));      while ($sth->fetchrow_arrayref()) {    print "$id $name $price\n";} $sth->finish();$dbh->disconnect();

In the example, we bind three columns of the Cars table to the $id, $name, and $price variables.

$sth->bind_columns(\my($id, $name, $price));

We bind the variables to the colums of the Cars table with the bind_columns() method.

while ($sth->fetchrow_arrayref()) {    print "$id $name $price\n";}

We traverse the returned data and print the values to the console.

In this part of the MySQL Perl tutorial we talked about binding parameters.