Backslash problem with DBI
I am new to using DBI for SQL queries in a perl script. The problem I am having relates to data in fields that have a forward slash. I want to use variables as input for the where clause, but it does what the DBI intends to do for the forward slash: stop the query. I tried many different works from bindings, quotes, etc. but none worked, is it possible? The data are consistent in this. The line with my $ sql variable is where the problem lies.
#!/usr/bin/perl
# Modules
use DBI;
use DBD::Oracle;
use strict;
use warnings;
# Connection Info
$platform = "Oracle";
$database = "mydb";
$user = "user";
$pw = "pass";
# Data Source
$ds = "dbi:Oracle:$database";
my $dbh = DBI->connect($ds, $user, $pw);
# my $dbh = DBI->connect();
my $XCOD = $dbh->quote('cba');
my $a = $dbh->quote('abc');
my $b = $dbh->quote('123');
# tried this as well my $pid = $dbh->quote('$a/$b');
my $sql = "SELECT P_ID FROM MyTable WHERE P_ID=$a/$b AND XCOD=$XCOD";
my $sth = $dbh->prepare($sql);
$sth->execute();
my $outfile = 'superunique.txt';
open OUTFILE, '>', $outfile or die "Unable to open $outfile: $!";
while(my @re = $sth->fetchrow_array) {
print OUTFILE @re,"\n";
}
close OUTFILE;
$sth->finish();
$dbh->disconnect();
source to share
You have been provided with the correct solution to your problem ( use placeholders ), but you might be interested to know why what you are doing is not working.
The problem is that you seem to be misunderstanding the method quote
. The documentation says the following:
Submit a string literal to be used as a literal value in an SQL statement by excluding any special characters (such as quotation marks) contained within the string and adding the required type of outer quotation marks.
You are using quote
these three lines.
my $XCOD = $dbh->quote('cba');
my $a = $dbh->quote('abc');
my $b = $dbh->quote('123');
It would be instructive to print the values $XCOD
, $a
and $b
(since aside $a
and $b
are really bad variable names - besides their insatiable nature, they are also special variables used in sorting).
I suspect that you will see "cba"
, "abd"
and "123"
. The method didn't find any special characters to exit, so all it did was add quotation marks around the lines.
Then you interpolate those values into your SQL.
my $sql = "SELECT P_ID FROM MyTable WHERE P_ID=$a/$b AND XCOD=$XCOD";
Again, you should carefully read what it $sql
contains after executing this statement. It will look something like this:
SELECT P_ID FROM MyTable WHERE P_ID="abc"/"123" AND XCOD="cba"
The first part of the WHERE clause is probably the problem. Oracle sees this as a split. And who knows what Oracle does when you split one string onto another. So you end up looking for a string where P_ID is a weird (possibly undefined) value.
So this looks like an example where the simplest of debugging methods (a few statements print
in the code) would point you in the right direction.
source to share