byterock

In  DBI Part 1 I introduced how to connect and how to DBI  can run hard-coded SQL statements. But to be truly useful it must be able to bind variables to placeholders.

With the following SQL,

SELECT city FROM locations WHERE location_id = 1800

we would like to replace the 1800 with a placeholder. We could just use Perl for this, which would look something like this:

my $loc_id = 1800;
$sql = "SELECT city FROM locations WHERE location_id = $loc_id";

This is called in-line binding. It can work quite well for simple SQL, but what if we get an SQL statement such as this?

SELECT city FROM locations WHERE location_id = 'IT';

Almost identical statements, but we have to add nasty single quotes to make the statement work. So our Perl will look like this:

my $country_id = 'IT';
$sql = "SELECT city FROM locations
WHERE location_id = '$country_id'";

This leaves us vulnerable to potential bugs. For example, if country_id happened to contain a single quote, you would have to write a whole procedure in Perl to account for it. Of course you could write all of your SQL in-line as long as you are careful with ' and other characters that SQL does not like. DBI even has a method on the database handle called quote(), to fix most of these problems for you. So you may never encounter a problem.

A couple caveats when using in-line binding.

The first pertains to how many RDBMS optimizes an SQL statement, most analyze and then save an execution plan for the query and then reuse the plan when the same query is run again. With in-line coded SQL most optimizers will not reuse execution plans, so in the long run your DB will work faster and more efficient when you use placeholders.

The second warning, when you use in-line binding, you will opening your system to SQL injection attacks.

You really do not want some nasty out there to run this on your DB

SELECT * FROM users WHERE name = 'a';DROP TABLE users; SELECT * FROM userinfo WHERE 't' = 't';

or worse still have a situation like this

Thought there is little change to find such a malevolent mother out there (unless your name is Howard Wolowitz) but it does leads one to the question of data sanitation.  DBI has a number of  functions that can clean data up for you, such as 'neat', 'looks_like_number' and 'sql_type_cast' fortunately most RDBMS today handle this for you, though the sql_type_cast is one that is helpful to any scientision  out there dealing with very small or large number. 

There is always some poor sap out there that has to do allot of UTF8 localization work and  this is when some of the built in functions such as  'data_diff','data_string_diff' and 'data_string_desc' come in handy, but that is for another post.

Using Simple Placeholders

In DBI, the most simple placeholder is the '?' character. So, if we make the following change to our first SQL like this:

$sql = 'SELECT city FROM locations WHERE location_id = ?';

the '?' will act as our placeholder, and all you have to do to make it work is use the database handle’s prepare() method to get a statement handle.

$sth = $dbh->prepare('SELECT city FROM locations
WHERE location_id = ?');

Alternatively:

$sql = 'SELECT city FROM locations WHERE location_id = ?';
$sth = $dbh->prepare($sql);

You can then can pass the parameter to the statement handle with its execute() method.

$sth->execute(1800);

So our code would look like this:

#!/usr/bin/perl

use DBI;
use strict;
use vars qw($dbh $sth $sql);

my $loc_id = 'IT';
$dbh = DBI->connect('dbi:Oracle:', 'hr@localhost/XE', 'hr');
$sql = 'SELECT city FROM locations WHERE location_id = ?';
$sth = $dbh->prepare($sql);
$sth->execute($loc_id);

while (my ($r_city) = $sth->fetchrow()){

print " $r_city \n";

}

$sth->finish();
$dbh->disconnect();

If the $loc_id variable contained a ' character, as in IN'T, DBI would automatically fix this for us when you execute the SQL. So no worries about single quotes - DBI takes care of them, and you know the same execution plan will be reused no matter what value you enter for country_id.

Complex SQLs

What about the case where you have SQL like this?

SELECT city FROM locations
WHERE
(city = 'Rome' or city = 'New York')
AND country_id <> 'IT'
AND state_province <> 'New York'

This could have the parameters like the following.

SELECT city FROM locations
WHERE
(city = ? or city = ?')
AND country_id <> ?
AND state_province <> ?

With a usage like this, DBI allows you to use the execute() statement as:

$sth->execute('Rome', 'New York', 'IT', 'New York');

DBI will substitute the parameters one after another in the same order as it finds '?' characters. So the execute() above would return a set of records.

Given the same SQL as above, and this execute statement:

$sth->execute('Rome', 'New York', 'New York', 'IT');

a different set of records would be returned, as this code corresponds to this SQL:

SELECT city FROM locations
WHERE
(city = 'Rome' or city = 'New York')
AND country_id <> 'New York'
AND state_province <> 'IT'

Next we will deeper into parametrized queries.