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
Download (untitled) / with headers
text/plain 788b
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)
Download (untitled) / with headers
text/plain 1.3k
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.
Download (untitled) / with headers
text/plain 130b
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.


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.