From: Ralf Neubauer
Date: February 22 2012 4:05pm
Subject: RE: bind parameter guessing corrupts results
List-Archive: http://lists.mysql.com/perl/4419
Message-Id: <DA5D2621C9566F488C32ACE506A7BB89020BA163@wido-exch01.wido.bv.aok.de>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable

Hi!

> -----Original Message-----
> From: Marc Lehmann [mailto:schmorp@stripped]
> Sent: Monday, February 13, 2012 4:02 AM

> For example, in (my-)sql, the following statements do not have the
> same
> semantics:
>=20
>    col < 10
>    col < "10"
>=20
> For col =3D 5, the first is true, the second is false, and an update
> statement
> using newer DBD::mysql would update the wrong rows, apart from
> potentially
> being slower.

I just tried it (with 5.5.10 and 5.1.44):

select t.*,
  i < 15, s < 15, i < '15', s < '15'
from (
  select i, concat(i) s
  from (
    select 1 i union select 2 union select 3 union select 4 union select
5 union select 6
    union select 7 union select 8 union select 9 union select 10 union
select 11
    union select 12 union select 13 union select 14 union select 15
union select 16
    union select 17 union select 18 union select 19 union select 20
  ) t
) t
order by i

As you can see, everything is ok. Quoted numbers are converted to
numbers by MySQL, if
the column they are compared with is numeric. On the other hand, if the
column is a
string and the constant is a number, the column is forced to be a number
(which can be a
real problem, if there are non-numeric values in the column). Only if
you compare a
string column with a string constant, both are compared as strings.

I assume that is what you do in your program, you store numbers in a
string column and
want them to be compared as a number. If you do this (e.g. if you use
VARCHAR(255) as a
'generic' column, which can be very useful), you have to cast indeed,
but in this case
you can't use indexes also, because you are hiding the real data type
from the database.

Not quoting numbers would be more dangerous, I think. The only
exceptions are LIMIT and
GROUP BY <column index>, where the numbers must not be quoted (in the
latter case the
behavior is changed silently, you just group by a constant). To really
do the Right Thing,
DBD::mysql would have to parse the Statement and determine the column
types, so this
is left to MySQL, which seems to behave reasonable.

> Could there be a mode implemented that goes by the deduced type of the
> perl scalar (e.g. SvPOKp =3D> string, otherwise number)? That works
reliably
> if the user is consistent in the arguments he/she supplied.

This would be consistent in itself, but not consistent to perl's
duck-typing.
In my experience all data you get from DBI comes as strings, so if you
use
data from one statement to parametrize another one, you have to add 0 to
all
numbers to get it right. The same happens when you read CSV files or
user input.

> The only way to correctly use numbers with current DBD::mysql seems to
be
> to always use a cast expression on the sql level, which seems like
total
> overkill.

See above. Use *CHAR() for numeric types and you have to cast. Otherwise
not.

Ralf Neubauer