Placeholder in an sql query

[dn_perl]

I want to use placeholder to escape special characters directly
via DBI while constructing an sql query.

The following code works properly :

=====================================
use vars qw($check_name) ;

$check_name = "Peter" ;
$dstmt = $dbh->prepare("select count(*) from student
where st_name='$check_name' "); # statement 2
$dstmt->execute() or die "sql call failed"; # statement 3
$num_entries = $dstmt->fetchrow() ;
$dstmt->finish ;
=====================================


I get the correct value in $num_entries and it happens to be 3.

But if I change statements 2 and 3 to use placeholder, I get
$num_entries = 0 (instead of 3). I am wondering why.

New statements #s 2 and 3

$dstmt = $dbh->prepare("select count(*) from student
where st_name=? ");
$dstmt->execute($check_name) or die "sql call failed";


Please advise.

 

[Tore Aursand]

The following code works properly :

=====================================
use vars qw($check_name) ;

$check_name = "Peter" ;
$dstmt = $dbh->prepare("select count(*) from student
where st_name='$check_name' "); # statement 2
$dstmt->execute() or die "sql call failed"; # statement 3
$num_entries = $dstmt->fetchrow() ;
$dstmt->finish ;
=====================================


I get the correct value in $num_entries and it happens to be 3.

But if I change statements 2 and 3 to use placeholder, I get
$num_entries = 0 (instead of 3). I am wondering why.

New statements #s 2 and 3

$dstmt = $dbh->prepare("select count(*) from student
where st_name=? ");
$dstmt->execute($check_name) or die "sql call failed";

I can't see what's wrong, actually, but I would have cleaned up the code a
little just to be sure. Also make sure you're using 'strict' and
'warnings';

my $check_name = 'Peter';
my $dstmt = $dbh->prepare( 'SELECT COUNT(*) FROM student WHERE st_name = ?' );
$dstmt->execute( $check_name );
my ( $num_entries ) = $dstmt->fetchrow_array();
$dstmt->finish();

 

[dn_perl]

I had written -

I want to use placeholder to escape special characters directly
via DBI while constructing an sql query.

I am guessing now that the problem could be somehow associated with
a specific field named 'part' in a table. Is 'part' a reserved
word? But even that cannot be the case because without placeholder,
the query works just fine.

(I am using strict, but I tried to use warnings as well, as
per Mr Aursand's suggestion and got an error that warnings.pm
was not present in @INC. I will try to fix this problem tomorrow.)


The following code works properly :

=====================================
use vars qw($check_part $check_location) ;

$check_part = "clock" ;
$check_location = "room_1" ;
# stmt X1 follows
my $dstmt = $dbh->prepare("select count(*) from inventory
where part = '$check_part' and loc = '$check_location' ");
$dstmt->execute() or die "sql call failed";
my $num_entries = $dstmt->fetchrow() ;
$dstmt->finish ;
=====================================
I get correct value in $num_entries and it happens to be 1.


If I use placeholder for 'loc' field, I get the expected result.
my $dstmt = $dbh->prepare("select count(*) from inventory
where part = '$check_part' and loc = ? ");
$dstmt->execute($check_location) or die "sql call failed";


But if I use placeholder for 'part' field, things go wrong.
my $dstmt = $dbh->prepare("select count(*) from inventory
where part = ? and loc = '$check_location' ");
$dstmt->execute($check_part) or die "sql call failed";
my $num_entries = $dstmt->fetchrow() ;
I get $num_entries = 0 instead of 1 .


Similarly, even where just one condition is being checked,
'part' field is giving problems.
# the following works with placeholder
my $dstmt = $dbh->prepare("select count(*) from inventory
where loc = ? ");
# the following does not work with placeholder
my $dstmt = $dbh->prepare("select count(*) from inventory
where part = ? "); # (But this works without placeholder)


Please advise.

 

[Eric Bohlman]

(e-mail address removed) ([email protected]) wrote in

If I use placeholder for 'loc' field, I get the expected result.
my $dstmt = $dbh->prepare("select count(*) from inventory
where part = '$check_part' and loc = ? ");
$dstmt->execute($check_location) or die "sql call failed";


But if I use placeholder for 'part' field, things go wrong.
my $dstmt = $dbh->prepare("select count(*) from inventory
where part = ? and loc = '$check_location' ");
$dstmt->execute($check_part) or die "sql call failed";
my $num_entries = $dstmt->fetchrow() ;
I get $num_entries = 0 instead of 1 .

You say it's the "part" field giving you the problem while the "loc" field
works OK, but another possible interpretation of the problem is that the
first (or only) field in a WHERE clause gives you a problem while the
second one works OK. Try reversing the order of the two and see what
happens.

 

[Heinrich Mislik]

I had written -

I am guessing now that the problem could be somehow associated with
a specific field named 'part' in a table. Is 'part' a reserved
word? But even that cannot be the case because without placeholder,
the query works just fine.

What database do you use? The following is true for Oracle but may happen with other DBs as well:

If the type of a field is CHAR(n), placeholders sometimes do not work, because the driver uses VARCHAR for placeholders as default. The reason is, that comparing CHAR(n) to CHAR(m) implicit pads with blanks, whereas comparing CHAR(n) with VARCHAR doesn't pad. And constants are considerd CHAR.


Consider PART CHAR(10) has a value of 'foo ':

WHERE part = 'foo'

will do

'foo ' = 'foo '

which is true.

WHERE part = ?

will do

'foo ' = 'foo'

which is false.

As a workaround try padding yourself:

WHERE part = RPAD(?,10)

If yoer database does not hav RPAD, do it in perl:

$check_part = sprintf("%-10s",$check_part);

Hth

cheers

Heinrich

 

[dn_perl]

(Heinrich Mislik) wrote -

If the type of a field is CHAR(n), placeholders sometimes do not work, because the driver uses VARCHAR for placeholders as default. The reason is, that comparing CHAR(n) to CHAR(m) implicit pads with blanks, whereas comparing CHAR(n) with VARCHAR doesn't pad. And constants are considerd CHAR.
As a workaround try padding yourself:
WHERE part = RPAD(?,10)
------------------

Thanks! Your suggestion worked. But a problem remains still.

Say the table is : students(name CHAR(8))
Entries in Students are : ' ' (8 blanks),
'bob ' and 'dave ' .

===> case A) If the query is :
my $st_name = "dave" ; # non-blank name
my $dstmt = $dbh->prepare("select count(*) from students
where name = RPAD(?,8) ") ;
$dstmt->execute($st_name) or die "sql call failed";
my $num_entries = $dstmt->fetchrow() ;
$dstmt->finish ;

things work well for case A.


===> but case B) If the query is :
my $st_name = " " ; # one-blank in name
my $dstmt = $dbh->prepare("select count(*) from students
where name = RPAD(?,8) ") ;
$dstmt->execute($st_name) or die "sql call failed";
my $num_entries = $dstmt->fetchrow() ;
$dstmt->finish ;

I get $num_entries = 0. WHY? If I don't use placeholder,
I get $num_entries = 1, which is proper. It looks to me that
if query_field = ' ' (one-blank), I am running into problems.


In fact, instead of using RPAD, I would prefer using 'trim',
as in RPAD I must find out lengths of all fields.
But :
my $dstmt = $dbh->prepare("select count(*) from students
where trim(name) = trim(?) ") ;
doesn't work either when $st_name is ' ' (one-blank) .

Please help.

-------------