Problem: A seemingly innocuous SQL can turn out to be very strenuous for a database if it needs to do type conversion. Worst case scenario is a full table scan just because the WHERE clause contains mismatching types in a comparison. Imagine, for example, that the column id
in the table customers
below is a VARCHAR. If we're talking to an Oracle database and send it an integer, the resulting execution plan could be near disastrous.
SELECT *
FROM customers
WHERE id = ?
In Perl, an untyped language where strings and integers are interchangeable, how can I ensure that I'm sending a string via DBI to the database if the string only contains numerals?
Solution #1: Wrap the value in a string:
$sth->execute( "$indata" );
Perl is more of a "semi-typed" language than a totally untyped language, and keeps track of whether a scalar is a string or an integer. The only reason the values are interchangeable is because of the transparent type conversion.
Solution #2: Use the explicit bind_param
method and specify the data type:
use DBI qw(:sql_types);
[...]
$sth->bind_param( 1, $indata, SQL_VARCHAR ); # index, value and type
$sth->execute();