Everything

php

PDO integer changes in 8.1 will stop many websites I support

php.internals

Matty The Mad

5 years ago

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

David Gebler

5 years ago

Hi Matthew,
1. 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.

2. 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.

3. 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."

4. 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.

5. If you really need ints converted to string, turn
on PDO::ATTR_STRINGIFY_FETCHES.

Hope this helps.

Dave

On Sat, Feb 27, 2021 at 8:49 AM Matty The Mad <matty@themad.com.au> wrote:

Stephen Reay

5 years ago

> On 27 Feb 2021, at 15:56, Matty The Mad <matty@themad.com.au> 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 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
>
> --
> PHP Internals - PHP Runtime Development Mailing List
> 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.

David Gebler

5 years ago

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:

Pierre

5 years ago

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

Rowan Collins

5 years ago

On 27/02/2021 14:03, Pierre wrote:

> 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]

Pierre

5 years ago

Le 27/02/2021 à 15:39, Rowan Tommins a écrit :

> On 27/02/2021 14:03, Pierre wrote:
>> 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

Rowan Collins

5 years ago

On 27/02/2021 15:51, Pierre wrote:

> 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]

Kamil Tekiela

5 years ago

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

Nikita Popov

5 years ago

On Sat, Feb 27, 2021 at 9:50 AM Matty The Mad <matty@themad.com.au> 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 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
>

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