Skip Menu |
 

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

Report information
The Basics
Id: 62458
Status: open
Priority: 0/
Queue: DBD-mysql

People
Owner: Nobody in particular
Requestors: yael.elan [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in:
  • 4.013
  • 4.014
  • 4.015
  • 4.016
  • 4.017
Fixed in: (no value)



Subject: mysql_bind_type_guessing broken for strings on update
Download (untitled) / with headers
text/plain 182b
when mysql_bind_type_guessing attribute is turned on, strings containing 'e' may be cast as numbers on sql update. (insert works as expected). test is attached. perl 5.8.8 suse linux
Subject: bind_type_guessing.diff
--- t/51bind_type_guessing.t.orig 2010-10-26 12:19:43.000000000 +0200 +++ t/51bind_type_guessing.t 2010-10-26 14:08:41.000000000 +0200 @@ -18,7 +18,7 @@ plan skip_all => "ERROR: $DBI::errstr. Can't continue test"; } -plan tests => 25; +plan tests => 34; ok $dbh->do("DROP TABLE IF EXISTS $table"), "drop table if exists $table"; @@ -82,4 +82,23 @@ ok $rows= $sth3->execute(' 1'); $sth3->finish(); +my $original_string = '123456e1'; +ok $sth3= $dbh->do("insert into $table (str, num) values (?, ?)", undef, $original_string, 9); +ok $sth3= $dbh->prepare("select * from $table where num = ?"); +ok $rows= $sth3->execute(9); +my ($str, $num) = $sth3->fetchrow_array; +is $str, $original_string, 'insert'; +$sth3->finish(); + +my $sth4; +ok $sth4= $dbh->prepare("update $table set str = ?, num = ?"); +ok $rows= $sth4->execute($original_string, '9'); +$sth4->finish(); + +ok $sth4= $dbh->prepare("select * from $table where num = ?"); +ok $rows= $sth4->execute(9); +($str, $num) = $sth4->fetchrow_array; +is $str, $original_string, 'update'; +$sth4->finish(); + ok $dbh->disconnect;
Download (untitled) / with headers
text/plain 112b
Making matters worse, dates in the format "2015-05-10" count as numeric. CentOS 6 DBD::mysql 4.027 perl 5.10.1
Download (untitled) / with headers
text/plain 474b
Included patch does the following: - rename parse_number to is_number - fixes is_number so it more closesly follows what MySQL expects - exposes it as DBD::mysql::_is_number - adds a test for all sorts of valid and invalid numbers Note that this patch still thinks the string "12345e6" is a number. Debate could go either way on whether it should be one or not. The argument against is that the string "12e3" is the number 12 in Perl, not 12000 as it is in MySQL.
Subject: Philip_Gwyn-DBD_mysql_is_number_01.patch
diff -rub DBD-mysql-4.032_01-orig/dbdimp.c DBD-mysql-4.032_01-PG/dbdimp.c --- DBD-mysql-4.032_01-orig/dbdimp.c 2015-04-16 17:04:02.000000000 -0400 +++ DBD-mysql-4.032_01-PG/dbdimp.c 2015-05-26 12:15:03.942881366 -0400 @@ -43,8 +43,6 @@ # define ASYNC_CHECK_RETURN(h, value) #endif -static int parse_number(char *string, STRLEN len, char **end); - DBISTATE_DECLARE; typedef struct sql_type_info_s @@ -583,7 +581,7 @@ valbuf= SvPV(ph->value, vallen); ph->type= SQL_INTEGER; - if (parse_number(valbuf, vallen, &end) != 0) + if (mysql_is_number(valbuf, vallen, &end) != 0) { ph->type= SQL_VARCHAR; } @@ -754,7 +752,7 @@ } /* (note this sets *end, which we use if is_num) */ - if ( parse_number(valbuf, vallen, &end) != 0 && is_num) + if ( mysql_is_number(valbuf, vallen, &end) != 0 && is_num) { if (bind_type_guessing) { /* .. not a number, so apparerently we guessed wrong */ @@ -5320,19 +5318,54 @@ } #endif -static int parse_number(char *string, STRLEN len, char **end) +/* + * Are numbers: + * 0 + * -0 + * 1234 + * -1234 + * 1234.0 + * 1234.0110 + * .15 + * -.15 + * -1234.123 + * +1234.888 + * 1e2 + * 1e-2 + * 1e+2 + * +1e+2 + * -1e2 + * -123e-3 + + * Aren't numbers: + * 2015-10-03 + * 1.2.3 + * 1e2.3 + * 1e-+3 +*/ + + +int mysql_is_number(char *string, STRLEN len, char **end) { int seen_neg; + int may_neg; int seen_dec; + int may_dec; int seen_e; + int may_e; int seen_plus; + int may_plus; int seen_digit; char *cp; seen_neg= seen_dec= seen_e= seen_plus= seen_digit= 0; + may_e= 0; + may_neg= may_plus= may_dec= 1; + if (len <= 0) { len= strlen(string); + /* fprintf( stderr, "len=%i string=%s\n", (int)len, string ); */ } cp= string; @@ -5345,6 +5378,7 @@ { if ('-' == *cp) { + if( !may_neg ) break; /* not allow a - here */ if (seen_neg >= 2) { /* @@ -5353,48 +5387,82 @@ break; } seen_neg += 1; + if( seen_e ) { + may_dec= 0; /* 1e-3.3 invalid */ + may_neg= 0; /* 1e--3 invalid */ + may_plus= 0; /* 1e-+3 invalid */ + may_e= 0; /* 1ee3 invalid */ + } + else { + may_dec= 1; /* -.3 valid */ + may_neg= 0; /* --3 invalid */ + may_plus= 0; /* -+3 invalid */ + may_e= 0; /* -e3 invalid */ + } } else if ('.' == *cp) { + if( !may_dec ) break; /* not allow a . here */ + if( seen_e ) break; /* 1e2.3 invalid */ if (seen_dec) { /* second '.' */ break; } seen_dec= 1; + + may_dec= 0; /* 3..3 invalid */ + may_neg= 0; /* .-3 invalid */ + may_plus= 0; /* .+3 invalid */ + may_e= 1; /* 1.e3 invalid */ } else if ('e' == *cp) { + if( !may_e ) break; /* not allow a e here */ if (seen_e) { /* second 'e' */ break; } seen_e= 1; + may_dec= 0; /* 1e3.3 invalid */ + may_neg= 1; /* 1e-3 valid */ + may_plus= 1; /* 1e+3 valid */ + may_e= 0; /* 1ee3 invalid */ } else if ('+' == *cp) { + if( !may_plus ) break; /* not allow a + here */ if (seen_plus) { /* second '+' */ break; } seen_plus= 1; + may_dec= 1; /* +.3 valid */ + may_neg= 0; /* +-3 invalid (it's math, not string) */ + may_plus= 0; /* ++3 invalid (it's math, not string) */ + may_e= 0; /* +e3 invalid */ } else if (!isdigit(*cp)) { - /* Not sure why this was changed */ - /* seen_digit= 1; */ break; } + else { + seen_digit += 1; + may_dec= 1; /* 1. valid */ + may_neg= 0; /* 1- invalid */ + may_plus= 0; /* 1+ invalid */ + may_e= 1; /* 1e2 valid */ + } } *end= cp; /* length 0 -> not a number */ - /* Need to revisit this */ - /*if (len == 0 || cp - string < (int) len || seen_digit == 0) {*/ - if (len == 0 || cp - string < (int) len) { + int seen = cp - string; + /* fprintf( stderr, "len=%i seen=%i digits=%i\n", (int)len, seen, seen_digit ); */ + if (len == 0 || seen < (int) len || seen_digit == 0) { return -1; } Only in DBD-mysql-4.032_01-PG: dbdimp.c~ diff -rub DBD-mysql-4.032_01-orig/dbdimp.h DBD-mysql-4.032_01-PG/dbdimp.h --- DBD-mysql-4.032_01-orig/dbdimp.h 2015-01-26 22:02:15.000000000 -0500 +++ DBD-mysql-4.032_01-PG/dbdimp.h 2015-05-26 11:46:40.636953219 -0400 @@ -373,6 +373,8 @@ extern int mysql_db_reconnect(SV*); int mysql_st_free_result_sets (SV * sth, imp_sth_t * imp_sth); +int mysql_is_number(char *string, STRLEN len, char **end); + #if MYSQL_ASYNC int mysql_db_async_result(SV* h, MYSQL_RES** resp); int mysql_db_async_ready(SV* h); diff -rub DBD-mysql-4.032_01-orig/mysql.xs DBD-mysql-4.032_01-PG/mysql.xs --- DBD-mysql-4.032_01-orig/mysql.xs 2015-01-26 22:02:15.000000000 -0500 +++ DBD-mysql-4.032_01-PG/mysql.xs 2015-05-26 12:21:27.795315748 -0400 @@ -44,6 +44,24 @@ OUTPUT: RETVAL +SV * +_is_number(str) + char *str; + PROTOTYPE: $ + CODE: + { + /* this function is exposed so we can easily write a test for it */ + int retval; + char *bidon; + retval = mysql_is_number(str, 0, &bidon); + /* warn( "retvat=%i", retval ); */ + RETVAL = boolSV(retval==0); + } + OUTPUT: + RETVAL + + + MODULE = DBD::mysql PACKAGE = DBD::mysql::dr diff -rub DBD-mysql-4.032_01-orig/t/rt62458-is_number.t DBD-mysql-4.032_01-PG/t/rt62458-is_number.t --- DBD-mysql-4.032_01-orig/t/rt62458-is_number.t 2015-05-26 12:18:26.595110614 -0400 +++ DBD-mysql-4.032_01-PG/t/rt62458-is_number.t 2015-05-26 12:20:55.539279136 -0400 @@ -0,0 +1,46 @@ +#!/usr/bin/perl + +use strict; +use warnings; + +use DBI; +use Test::More ( tests => 29 ); + +my $drh = DBI->install_driver('mysql'); + +ok DBD::mysql::_is_number( "0" ), "0 number"; +ok DBD::mysql::_is_number( "1" ), "1 number"; +ok DBD::mysql::_is_number( "-1" ), "-1 number"; +ok DBD::mysql::_is_number( "+1" ), "+1 number"; + +# For the next 4, SELECT returns what you expect +# but they are math, not numbers. +ok !DBD::mysql::_is_number( "--1" ), "--1 not a number"; +ok !DBD::mysql::_is_number( "++1" ), "++1 not a number"; +ok !DBD::mysql::_is_number( "-+1" ), "-+1 not a number"; +ok !DBD::mysql::_is_number( "+-1" ), "+-1 not a number"; + +ok DBD::mysql::_is_number( "1.1" ), "1.1 number"; +ok DBD::mysql::_is_number( "-1.1" ), "-1.1 number"; +ok DBD::mysql::_is_number( "+1.1" ), "+1.1 number"; +ok DBD::mysql::_is_number( ".1" ), ".1 number"; +ok DBD::mysql::_is_number( "-.1" ), "-.1 number"; +ok DBD::mysql::_is_number( "+.1" ), "+.1 number"; +ok DBD::mysql::_is_number( "0.1" ), "0.1 number"; + +ok DBD::mysql::_is_number( "1e1" ), "1e1 number"; +ok DBD::mysql::_is_number( "-1e1" ), "-1e1 number"; +ok DBD::mysql::_is_number( "+1e1" ), "+1e1 number"; +ok DBD::mysql::_is_number( "1e-1" ), "1e-1 number"; +ok DBD::mysql::_is_number( "-1e-1" ), "-1e-1 number"; + +# everything else is not a number +ok !DBD::mysql::_is_number( "-1e.1" ), "-1e.1 not a number"; +ok !DBD::mysql::_is_number( "hello" ), "hello not a number"; +ok !DBD::mysql::_is_number( "1e.1" ), "1e.1 not a number"; +ok !DBD::mysql::_is_number( "e.1" ), "e.1 not a number"; +ok !DBD::mysql::_is_number( "e1" ), "e1 not a number"; +ok !DBD::mysql::_is_number( "2015-12-02" ), "2015-12-02 not a number"; +ok !DBD::mysql::_is_number( "0-1" ), "0-1 not a number"; +ok !DBD::mysql::_is_number( "1+1" ), "1+1 not a number"; +ok !DBD::mysql::_is_number( "" ), "empty string not a number";


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.