How to escape a native SQL function in perl bind_param()?

Question

So I have the following snippet code:

my $sql = "INSERT INTO mytbl (first_name, last_name, birthdate) VALUES (?, ?, ?)";
my $sth = $dbh->prepare($sql) or die "Error:".$dbh->errstr;                 

$sth->bind_param(1, $fname);
$sth->bind_param(2, $lname);
$sth->bind_param(3, $bdate);
$sth->execute() or die "Execution failed: " . $dbh->errstr;
$sth->finish();

When performing insertion in Microsoft SQL with the following data for birthdate, $bdate = "2010-01-06"; I have to cast it into the datetime function with SQL,

$bdate = "cast ('$eml_form_end_dt' as datetime)";

This, however, caused problem as perl doesn't recognize it. What I had to do instead is:

$bdate = "cast ('$eml_form_end_dt' as datetime)";
my $sql = "INSERT INTO mytbl (first_name, last_name, birthdate VALUES (?, ?, '$bdate')";
my $sth = $dbh->prepare($sql) or die "Error:".$dbh->errstr;                 

$sth->bind_param(1, $fname);
$sth->bind_param(2, $lname);
# This causes problem
# $sth->bind_param(3, "cast ('$eml_form_end_dt' as datetime)");
$sth->execute() or die "Execution failed: " . $dbh->errstr;
$sth->finish();

How do I escape the SQL function in this case?

EDIT: this is the error message I received: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid precisi on value (SQL-HY104) at filename.pl.

Answer 1

I think what you really want is

use DBI qw(:sql_types);
...
$sth->bind_param(3, $bdate, SQL_DATETIME);

to tell DBI the correct type to use (it defaults to varchar). You might need to use SQL_DATE instead of SQL_DATETIME; I don't use SQL Server myself.

Answer 2

I'm pretty sure MS SQL will convert the string to a date without any casting. Did you have a problem binding '2010-01-06' to the placeholder? If that doesn't work, then you can use the 3rd argument of bind_param to tell what type the argument is. Or you can try:

my $sql = "INSERT INTO mytbl (first_name, last_name, birthdate) VALUES (?, ?, cast(? as datetime))";

and bind the string version of the date to that placeholder.

Update: Amazing how many people (including me) missed the missing right paren.

Answer 3

The issue is that bind parameters only allow you to pass values, not arbitrary SQL expressions. This is very much by design, since the other way would eliminate the main benefits of bind variables: caching compiled statements and preventing SQL injection attacks. So the function has to go into your SQL:

my $sql = "INSERT INTO mytbl (first_name, last_name, birthdate VALUES (?, ?, cast(? as datetime))";
my $sth = $dbh->prepare($sql) or die "Error:".$dbh->errstr;                 

$sth->bind_param(1, $fname);
$sth->bind_param(2, $lname);
$sth->bind_param(3, $eml_form_end_dt);
$sth->execute() or die "Execution failed: " . $dbh->errstr;
$sth->finish();

That ought to work.

Answer 4

Shouldn't this:

INSERT INTO mytbl (first_name, last_name, birthdate VALUES (?, ?, cast(? as datetime))

be this:

INSERT INTO mytbl (first_name, last_name, birthdate) VALUES (?, ?, cast(? as datetime))