PHP 8.1 PDO has been changed so that when MySQL returns an integer it
will no longer be returned as a string, but as an int.
The problem is this breaks any sites that use UNSIGNED ZEROFILL integer
fields in MySQL.
I support a number of websites where the phone numbers and post codes
are all UNSIGNED ZEROFILL.
The post codes in MySQL are stored as 0800.
In PHP 8.0 returned as string 0800
In PHP 8.1 returned as integer 800
8.0.2
string(10) "0742000000"
8.1.0-dev
int(742000000)
PDO shouldn't really be changing the data.
I propose that:
• any ZEROFILL integers are returned as string [or]
• there's an option to control this when making the connection (I don't
want to str_pad() at every place I've used ZEROFILL in the past) [or]
• this backwards compatibility breaking change is removed until PHP 9.
Matthew Asia
Hi Matthew,
-
PHP 8 has not introduced a change to PDO_MYSQL in respect of how values
are fetched. What makes the difference as to whether you get MySQL INT
values fetched as strings or integers is whether you are running PHP with
the mysqlnd https://dev.mysql.com/downloads/connector/php-mysqlnd/ and
emulation mode is off. -
ZEROFILL in MySQL does not change how integers are stored, only how they
are displayed by MySQL clients. 00123 as a INT(5) ZEROFILL is still stored
as integer 123. -
ZEROFILL is deprecated in MySQL 8. MySQL documentation advises "consider
using an alternative means of producing the effect of these attributes. For
example, applications can use the LPAD() function to zero-pad numbers up to
the desired width, or they can store the formatted numbers in CHAR columns." -
Storing phone numbers and postal codes as integers is asking for
trouble, they are types of data which are fundamentally better represented
in storage as character types. -
If you really need ints converted to string, turn
on PDO::ATTR_STRINGIFY_FETCHES.
Hope this helps.
Dave
PHP 8.1 PDO has been changed so that when MySQL returns an integer it
will no longer be returned as a string, but as an int.The problem is this breaks any sites that use UNSIGNED ZEROFILL integer
fields in MySQL.I support a number of websites where the phone numbers and post codes
are all UNSIGNED ZEROFILL.The post codes in MySQL are stored as 0800.
In PHP 8.0 returned as string 0800
In PHP 8.1 returned as integer 8008.0.2
string(10) "0742000000"8.1.0-dev
int(742000000)PDO shouldn't really be changing the data.
I propose that:
• any ZEROFILL integers are returned as string [or]
• there's an option to control this when making the connection (I don't
want tostr_pad()at every place I've used ZEROFILL in the past) [or]
• this backwards compatibility breaking change is removed until PHP 9.Matthew Asia
--
To unsubscribe, visit: https://www.php.net/unsub.php
PHP 8.1 PDO has been changed so that when MySQL returns an integer it will no longer be returned as a string, but as an int.
The problem is this breaks any sites that use UNSIGNED ZEROFILL integer fields in MySQL.
I support a number of websites where the phone numbers and post codes are all UNSIGNED ZEROFILL.
The post codes in MySQL are stored as 0800.
In PHP 8.0 returned as string 0800
In PHP 8.1 returned as integer 8008.0.2
string(10) "0742000000"8.1.0-dev
int(742000000)PDO shouldn't really be changing the data.
I propose that:
• any ZEROFILL integers are returned as string [or]
• there's an option to control this when making the connection (I don't want tostr_pad()at every place I've used ZEROFILL in the past) [or]
• this backwards compatibility breaking change is removed until PHP 9.Matthew Asia
--
To unsubscribe, visit: https://www.php.net/unsub.php
I agree it should be configurable, but storing digit strings as integers is asking for trouble.
Edit: Whoops, sending again from on-list address.
Apologies, in respect of point 1 in my reply, I misread your original
message and didn't realise you were referring to the upcoming 8.1 change,
thought you were referring to the current 8.0.1 release.
This change AFAIK only affects emulated prepares and is an improvement, as
previously you needed to have emulation off to get the correct data types.
With modern PHP, MySQL and mysqnd there is not really a reason for anyone
to be using emulated prepares anyway so my advice on points 2-5 remains the
same.
Dave
On Sat, Feb 27, 2021 at 12:55 PM Stephen Reay php-lists@koalephant.com
wrote:
PHP 8.1 PDO has been changed so that when MySQL returns an integer it
will no longer be returned as a string, but as an int.The problem is this breaks any sites that use UNSIGNED ZEROFILL integer
fields in MySQL.I support a number of websites where the phone numbers and post codes
are all UNSIGNED ZEROFILL.The post codes in MySQL are stored as 0800.
In PHP 8.0 returned as string 0800
In PHP 8.1 returned as integer 8008.0.2
string(10) "0742000000"8.1.0-dev
int(742000000)PDO shouldn't really be changing the data.
I propose that:
• any ZEROFILL integers are returned as string [or]
• there's an option to control this when making the connection (I don't
want tostr_pad()at every place I've used ZEROFILL in the past) [or]
• this backwards compatibility breaking change is removed until PHP 9.Matthew Asia
--
To unsubscribe, visit: https://www.php.net/unsub.php
I agree it should be configurable, but storing digit strings as integers
is asking for trouble.Edit: Whoops, sending again from on-list address.
To unsubscribe, visit: https://www.php.net/unsub.php
Le 27/02/2021 à 14:18, David Gebler a écrit :
Apologies, in respect of point 1 in my reply, I misread your original
message and didn't realise you were referring to the upcoming 8.1 change,
thought you were referring to the current 8.0.1 release.This change AFAIK only affects emulated prepares and is an improvement, as
previously you needed to have emulation off to get the correct data types.
With modern PHP, MySQL and mysqnd there is not really a reason for anyone
to be using emulated prepares anyway so my advice on points 2-5 remains the
same.Dave
Hello,
I'm curious, why are you stating that emulated prepares are not useful
anymore ?
Using the prepare() function as I remember correctly seems to be the
only way to execute a query that allows you to pass an array of
parameters instead of having to call PDOStatement::bindParam() for each
parameter. That's the only reason I use emulated prepares anyway, it
allows to use this API without actually preparing the queries, which is
what I want most of the time. Did I miss an improvement on that side ?
After reading twice what I said, I'm thinking that calling many times
bindParam() in a naive foreach would work anyway. Is there any actual
downside using emulated prepares this way ? Performance wise ? Feature
wise ?
--
Pierre
I'm curious, why are you stating that emulated prepares are not useful
anymore ?
Emulated prepares are a slightly dangerous idea, because they give the
impression of separating query from data, but actually use string
escaping internally. Historically, I believe there were previously
scenarios where real prepared queries were not available, which no
longer apply.
Using the prepare() function as I remember correctly seems to be the
only way to execute a query that allows you to pass an array of
parameters instead of having to call PDOStatement::bindParam() for
each parameter.
Regardless of whether you use emulated or real prepares, the
PDOStatement's execute method can be called with an array of parameters,
and as far as I know always could.
That's the only reason I use emulated prepares anyway, it allows to
use this API without actually preparing the queries, which is what I
want most of the time.
This is a slightly different point: when running a query once, it seems
to currently require two database round trips: one to prepare, and one
to execute. I don't know what the underlying drivers support here, but
it would be useful to have a method on the PDO object itself that let
you provide a parameterised string and its data in one go. That would be
safer than using emulated prepares, and (hopefully) more efficient than
using real prepares.
Regards,
--
Rowan Tommins
[IMSoP]
Le 27/02/2021 à 15:39, Rowan Tommins a écrit :
I'm curious, why are you stating that emulated prepares are not
useful anymore ?
Emulated prepares are a slightly dangerous idea, because they give the
impression of separating query from data, but actually use string
escaping internally. Historically, I believe there were previously
scenarios where real prepared queries were not available, which no
longer apply.
Yes I was aware of this, I do more explicit prepares when I really need
those, it doesn't happen quite often. I still do from time to time but
using the ext-pgsql extension. I don't use MySQL anymore since I
succeeded in freeing myself from Drupal :) Drupal uses emulate prepare
because it allows it set value placeholders in queries, which is very
convenient for their query builder.
Using the prepare() function as I remember correctly seems to be the
only way to execute a query that allows you to pass an array of
parameters instead of having to call PDOStatement::bindParam() for
each parameter.
Regardless of whether you use emulated or real prepares, the
PDOStatement's execute method can be called with an array of
parameters, and as far as I know always could.
Yes, but PDOStatement::execute() explicitly documents that it can be
used only for prepared queries. If that's true that means there is no
alternative for binding value params in a query than using it. I didn't
remember (long time I didn't use PDO when I wrote my first email) only
prepare() allows you to write value placeholders in queries. ext-pgsql
has the pg_query_params() for doing this, which is very convenient, PDO
can only do this using emulate prepare.
That's the only reason I use emulated prepares anyway, it allows to
use this API without actually preparing the queries, which is what I
want most of the time.This is a slightly different point: when running a query once, it
seems to currently require two database round trips: one to prepare,
and one to execute. I don't know what the underlying drivers support
here, but it would be useful to have a method on the PDO object itself
that let you provide a parameterised string and its data in one go.
That would be safer than using emulated prepares, and (hopefully) more
efficient than using real prepares.Regards,
I'm not sure it requires two round trips when you use prepare() along
with emulated prepare, at least I never noticed it (and I did profile by
observing SQL side logs more than once, for example one thing that do a
lot of roundtrips is the getColumnMeta() call). 2 roundtrips are
required if you really prepare the query.
Anyway, thank you very much for answering. It's sad PDO doesn't have a
pg_query_params() equivalent method, most people would stop using
prepare() if that was the case.
Regards,
--
Pierre
Yes, but PDOStatement::execute() explicitly documents that it can be
used only for prepared queries.
The same is true of bindParam and bindValue - the only way of using
parameters is to first "prepare" a query. Whether that's a real prepare
(on the database server) or an emulated prepare doesn't make any
difference, as far as I know.
I'm not sure it requires two round trips when you use prepare() along
with emulated prepare
indeed, that was my point: currently, the only way to avoid the extra
round-trip is emulated prepares, which means you don't get the full
security of parameterised queries.
It's sad PDO doesn't have a pg_query_params() equivalent method, most
people would stop using prepare() if that was the case.
Agreed. :)
Although it would also require people to stop using the term "prepared
query" to mean "parameterised query", which I see a lot.
Regards,
--
Rowan Tommins
[IMSoP]
Hi Matthew,
As was pointed out before ZEROFILL is getting deprecated. It has been
deprecated by MySQL and I assume MariaDB will follow soon.
The change you are referring to is only to fix inconsistency in PHP. The
problem with ZEROFILL has been present since forever as far as I know.
The setting PDO::ATTR_STRINGIFY_FETCHES is not going to affect the result
either, because the value isn't stored with zeros. The length of integers
columns only signifies how MySQL displays it in the result.
Storing non-numerical values like phone numbers or zip codes in integer
columns is just a terrible idea. If the zip code has a leading zero that is
part of the data then it should be stored in a text column. MySQL doesn't
store the leading zeros. It only pads the output.
If you want you can always cast the number to a character SELECT
CAST(ZEROFILL as CHAR) FROM zerofill which will tell MySQL to pad the
output.
The change that Nikita did is the correct one. As to whether PHP should do
anything with regards to ZEROFILL_FLAG is a separate question.
Re Pierre's confusion: I believe you are confusing prepared statements with
emulation of prepared statements, but this is orthogonal to this
discussion.
Kind Regards,
Kamil
PHP 8.1 PDO has been changed so that when MySQL returns an integer it
will no longer be returned as a string, but as an int.The problem is this breaks any sites that use UNSIGNED ZEROFILL integer
fields in MySQL.I support a number of websites where the phone numbers and post codes
are all UNSIGNED ZEROFILL.The post codes in MySQL are stored as 0800.
In PHP 8.0 returned as string 0800
In PHP 8.1 returned as integer 8008.0.2
string(10) "0742000000"8.1.0-dev
int(742000000)PDO shouldn't really be changing the data.
I propose that:
• any ZEROFILL integers are returned as string [or]
• there's an option to control this when making the connection (I don't
want tostr_pad()at every place I've used ZEROFILL in the past) [or]
• this backwards compatibility breaking change is removed until PHP 9.Matthew Asia
To clarify, what changed in PHP 8.1 is that emulated prepares now produce
the same results as native prepares. Or from a technical perspective, that
you get the same results independently of whether the text protocol or the
binary protocol is used to communicate with the MySQL server.
The handling of ZEROFILL does look like a bug to me. We should return
values with leading zeros as strings for both protocols. For reference,
this issue is tracked at https://bugs.php.net/bug.php?id=80808.
Regards,
Nikita