From: Ken Williams
Date: March 23 1999 4:32am
Subject: Bug with placeholders & select
List-Archive: http://lists.mysql.com/mysql/824
Message-Id: <199903230432.XAA18702@forum.swarthmore.edu>

>Description:
   Placeholders seem to retain their "knowledge" of whether they're strings
	or numbers, from one execute() to the next.  This causes mysterious
	errors when the string can't be interpreted as a number.  Since Perl
	treats 0 and "0" as pretty much the same thing (with loose typing), this
	was most unexpected.

   It seems like one solution could be to use the table information to set
   the binding type, not the internal perl data typing (which is loose
	anyway).  So if I bind to a varchar column, the value is always bound as
	a string, regardless of whether I'm binding a number or a string or
	whatever.

>How-To-Repeat:
   my $sth = $dbh->prepare("SELECT 1 FROM messages WHERE thread=? AND message=?");
   $sth->execute(68207,0);
   $sth->fetchrow_array;
   $sth->execute(68207,'01m');
   $sth->fetchrow_array;

   DBD::mysql::st execute failed: Unknown column '01m' in 'where clause'
   at mysqlbug.pl line 11.

   mysql> show columns from messages;
   +---------+-----------------------+------+-----+---------+-------+
   | Field   | Type                  | Null | Key | Default | Extra |
   +---------+-----------------------+------+-----+---------+-------+
   | thread  | mediumint(8) unsigned |      | PRI | 0       |       |
   | message | varchar(4)            |      | PRI |         |       |
   +---------+-----------------------+------+-----+---------+-------+

>Fix:  
   (a) Make sure you coerce all bound values into being the correct internal
	Perl type, or

   (b) Don't use bindings, use statements which aren't prepared in advance,
	and quoted values in the SQL.

>Submitter-Id:	<submitter ID>
>Originator:	Ken Williams
>Organization:
 The Math Forum (http://forum.swarthmore.edu/)
>MySQL support: none
>Synopsis:	Some execute() statements fail when query has placeholders
>Severity:	serious
>Priority:	medium
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-3.22.20a (TCX binary)
>Environment:
	
System: OSF1 forum.swarthmore.edu V4.0 564 alpha
Machine: alpha
Some paths:  /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/alpha-dec-osf4.0b/2.8.1/specs
gcc version 2.8.1
Compilation info: CC='cc -std1'  CFLAGS='-O3'  CXX='gcc'  CXXFLAGS='-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti'  LDFLAGS=''
Configure command: ./configure  --prefix=/usr/local/mysql '--with-comment=TCX binary' --with-low-memory --disable-shared
Perl: This is perl, version 5.004_04 built for alpha-dec_osf