Re: Error passing string date from Perl to MSSQL

What's wrong with simply adding the required single quotes?

my $sql = "INSERT INTO TableName(Date,FileName) VALUES('$Date',$file)"
+;
[download]

Or even better, use DBI placeholders:

my $sql = 'INSERT INTO TableName(Date,FileName) VALUES(?,?)';

my $stmt = $settle_dbh->prepare($sql);

$stmt->execute($Date, $file); # no quoting needed
[download]

Comment on Re: Error passing string date from Perl to MSSQL Select or Download Code

Replies are listed 'Best First'.
Re^2: Error passing string date from Perl to MSSQL by JobyJ (Initiate) on Apr 20, 2017 at 09:03 UTC
Awesome the my $sql = "INSERT INTO TableName(Date,FileName) VALUES('$Date',$file)"; , solved the issue. Also can you let me know how the convert the date into the format 2017-01-21 00:00:00.000 before passing it to SQL.	[reply]
Re^3: Error passing string date from Perl to MSSQL by Corion (Patriarch) on Apr 20, 2017 at 09:05 UTC
My approach to splitting up a date into its constituent parts is a regular expression: `if( $Date !~ /^(20\d\d)([01]\d)([0123]\d)$/ ) { die "Invalid date '$Date'; } $Date = "$1-$2-$3 00:00:00.000";` [download]	[reply] [d/l]
Re^3: Error passing string date from Perl to MSSQL by MidLifeXis (Monsignor) on Apr 20, 2017 at 17:28 UTC
Depending on the lineage of the data, I would highly recommend looking at the placeholder information, or heavily validating that the data is as expected. Inadvertent or intentionally bad data can break things otherwise (see bobby tables, for example). While not a panacea, both placeholders and heavy data validation of content originating from potentially hostile sources are very good tools to know how to use. --MidLifeXis	[reply]
Re^3: Error passing string date from Perl to MSSQL by chacham (Prior) on Apr 20, 2017 at 18:34 UTC
Please try Corion's second suggestion to use placeholders. The query as it is currently written uses variables in the actual statement, which is known as dynamic sql. Amongst the many issues with dynamic sql is that it is not secure. Using placeholders will make it secure, and can end up helping with other issues as well.	[reply]
Re^3: Error passing string date from Perl to MSSQL by cormanaz (Deacon) on Apr 20, 2017 at 18:01 UTC
You can use Date::Manip to parse the date, and use the UnixDate function to format it.	[reply]