| Subject: | Tie::DBI with SQLite - prepare failed, syntax error line 402 - fix included! |
| Date: | Sun, 10 May 2015 22:29:17 -0400 |
| To: | bug-Tie-DBI [...] rt.cpan.org |
| From: | Bruce Reed <bruce [...] brucereed.com> |
I am using Tie::DBI V1.06 with DBD::SQLite V1.46, and I am getting the
following errors when I have a question mark in a text field:
DBD::SQLite::db prepare failed: near "11879": syntax error at Tie/DBI.pm
line 397, <GEN18> line 13.
STORE: near "11879": syntax error at Tie/DBI.pm line 627.
The '11879' value is the record key of the record being updated.
---------------------
The problem is that in the case of CanBind being false, function
_run_query is replacing the placeholders with the field values in the query.
I added some print statements to see what is occurring.
query: insert into transactions (description,key) values (?,?)
values: 'test?','11879'
updated query: insert into transactions (description,key) values
('test'11879'',null)
query: update transactions set description=? where key=?
values: 'test?','11879'
updated query: update transactions set description='test'11879'' where
key=null
---------------------
Function _run_query contains this code at line 393:
while ( ( my $pos = index( $query, '?' ) ) >= 0 ) {
my $value = shift(@bind_variables);
substr( $query, $pos, 1 ) = ( defined($value) ? (
$self->{CanBind} ? $self->{'dbh'}->quote($value) : $value ) : 'null' );
}
The index call is starting from the beginning of the string on each call.
The following code addresses this:
my $pos = 0;
while ( ( $pos = index( $query, '?', $pos ) ) >= 0 ) {
my $value = shift(@bind_variables);
$value = ( defined($value) ? ( $self->{CanBind} ?
$self->{'dbh'}->quote($value) : $value ) : 'null' );
substr( $query, $pos, 1 ) = $value;
$pos += length($value);
}