Bug#314232: libdbd-sqlite3-perl: bind variables change from text to integer

[Jonathan H N Chin]

Package: libdbd-sqlite3-perl
Version: 1.08-1
Severity: important


[ I have marked this as important.
  However it caused me data loss, so perhaps it should be grave. ]

I have textual data that may look like integers.
On insertion, any leading "0" will vanish.
For example:

    sqlite3 test 'create table t ( k text unique, v text );'
    perl -e 'use DBI;
        $db = DBI->connect( "dbi:SQLite:dbname=test" );
        $db->do( qq[REPLACE INTO t VALUES(?,?);],
            undef, "key", "0325763213" );'
    sqlite3 test 'select * from t;'

gives:

    key|325763213

When I asked about this problem on the sqlite-users mailing list,
the responses I got indicated:

1. sqlite doesn't do the conversion, so presumably it is something
   in one of the Perl modules.

    http://www.mail-archive.com/sqlite-users%40sqlite.org/msg08557.html


2. This doesn't appear to be the behaviour on Windows:

    http://www.mail-archive.com/sqlite-users%40sqlite.org/msg08558.html


3. It is suggested that the problem is in DBI or DBD::SQLite,
   possibly to do with lack of the use of a "prepared statement feature":

    http://www.mail-archive.com/sqlite-users%40sqlite.org/msg08560.html


I have tried going through the Perl modules but don't know enough
to attempt any kind of fix. However the place where the conversion
happens is the test for looks_like_number in dbdimp.c.

What would break if that test were removed?
The comment mentions sorting.

A more generic solution might be to extend the DBI do() syntax to
take bind_type as well as bind_values. Another might be to provide
a mechanism to override the automatic conversion to integer of bind
values that look like integers.




-- System Information:
Debian Release: testing/unstable
  APT prefers unstable
  APT policy: (500, 'unstable')
Architecture: i386 (i686)
Shell:  /bin/sh linked to /bin/bash
Kernel: Linux 2.4.27-c800-1
Locale: LANG=C, LC_CTYPE=C (charmap=ANSI_X3.4-1968)

Versions of packages libdbd-sqlite3-perl depends on:
hi  libc6                       2.3.2.ds1-22 GNU C Library: Shared libraries an
hi  libdbi-perl                 1.48-1       Perl5 database interface by Tim Bu
hi  perl                        5.8.7-3      Larry Wall's Practical Extraction 
hi  perl-base [perlapi-5.8.4]   5.8.7-3      The Pathologically Eclectic Rubbis

-- no debconf information