Skip Menu |

This queue is for tickets about the DBD-SQLite CPAN distribution.

Report information
The Basics
Id: 67581
Status: resolved
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: abraxxa [...] cpan.org
Cc:
AdminCc:

Bug Information
Severity: Critical
Broken in: 1.31
Fixed in: (no value)



Subject: bind_param SQL_INTEGER numifies value
When passing a non-numeric value to a numeric bind, the value will be numified and used without an error. This leads to unexpected sucess and data in the database. The following litte script shows the bug: #!/usr/bin/env perl use strict; use warnings; use DBI qw(:sql_types); # Don't forget this my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); $dbh->do("CREATE TABLE producer ( producerid INTEGER PRIMARY KEY NOT NULL, name varchar(100) NOT NULL );"); my $sth = $dbh->prepare("INSERT INTO producer ( producerid, name ) VALUES( ?, ? )"); $sth->bind_param(1, 'foo', SQL_INTEGER); $sth->bind_param(2, 'bar', SQL_VARCHAR); $sth->execute(); Only this warning is thrown: Argument "foo" isn't numeric in subroutine entry at ./dbd-sqlite-bind-bug.pl line 14.
Subject: POTENTIAL DATA CORRUPTION! (bind_param SQL_INTEGER numifies value)
On Tue Apr 19 12:00:03 2011, ABRAXXA wrote: Show quoted text
> When passing a non-numeric value to a numeric bind, the value will be > numified and used without an error. > This leads to unexpected sucess and data in the database. > > The following litte script shows the bug: > > #!/usr/bin/env perl > use strict; > use warnings; > use DBI qw(:sql_types); # Don't forget this > > my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); > $dbh->do("CREATE TABLE producer ( > producerid INTEGER PRIMARY KEY NOT NULL, > name varchar(100) NOT NULL > );"); > my $sth = $dbh->prepare("INSERT INTO producer ( producerid, name ) > VALUES( ?, ? )"); > $sth->bind_param(1, 'foo', SQL_INTEGER); > $sth->bind_param(2, 'bar', SQL_VARCHAR); > $sth->execute(); > > Only this warning is thrown: > Argument "foo" isn't numeric in subroutine entry at > ./dbd-sqlite-bind-bug.pl line 14.
Just to raise the severity of this bug - the behavior as it is now leads to data corruption with an incomprehensible warning. It is not obvious that a non-numeric blah, will result in the blah being converted to '0'. Either DBD::SQLite needs to preserve the value (while still warning about it), or it must throw. The current DBIC workaround is as follows: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-Class.git;a=commitdiff;h=632d1e0fcec8dd0b2ba1c2f9f616bf92aa22e2b3 Cheers
This is hopefully fixed in the trunk. If you find some edge cases, please let us know. As of this writing, something like $h->bind_param(1, " 4", SQL_INTEGER); is allowed for backward compatibility. Thanks. On Wed Apr 20 01:00:03 2011, ABRAXXA wrote: Show quoted text
> When passing a non-numeric value to a numeric bind, the value will be > numified and used without an error. > This leads to unexpected sucess and data in the database. > > The following litte script shows the bug: > > #!/usr/bin/env perl > use strict; > use warnings; > use DBI qw(:sql_types); # Don't forget this > > my $dbh = DBI->connect("dbi:SQLite:dbname=test.db","",""); > $dbh->do("CREATE TABLE producer ( > producerid INTEGER PRIMARY KEY NOT NULL, > name varchar(100) NOT NULL > );"); > my $sth = $dbh->prepare("INSERT INTO producer ( producerid, name ) > VALUES( ?, ? )"); > $sth->bind_param(1, 'foo', SQL_INTEGER); > $sth->bind_param(2, 'bar', SQL_VARCHAR); > $sth->execute(); > > Only this warning is thrown: > Argument "foo" isn't numeric in subroutine entry at > ./dbd-sqlite-bind-bug.pl line 14.
Closed this ticket as DBD::SQLite 1.34_02 with a fix is out. Please reopen this if you still experience the same problem. Thanks.