sqlsrv extension modifies the locale settings and possibly breaks other software

[marvinhinz]

+## PHP Driver version or file name
ExtensionVer => 5.6.1
+## SQL Server version
not relevant
+## Client operating system
Debian 10
+## PHP version
7.1.33
+## Microsoft ODBC Driver version
not relevant
+## Table schema
not relevant
+## Problem description
The PHP Extension seems to override the LC_ options. This triggered a Problem in Magento 1 Shops. All prices were printed way to high. But this is not a fault of magento. I dont think a PHP extension should modify the locale settings at all.

Testing the output of localeconv with sqlsrv enabled:

/opt/php-7.1/bin/php  -r "print_r(localeconv());"
Array
(
    [decimal_point] => ,
    [thousands_sep] => .
    [int_curr_symbol] => EUR
    [currency_symbol] => €
    [mon_decimal_point] => ,
    [mon_thousands_sep] => .
    [positive_sign] =>
    [negative_sign] => -
    [int_frac_digits] => 2
    [frac_digits] => 2
    [p_cs_precedes] => 0
    [p_sep_by_space] => 1
    [n_cs_precedes] => 0
    [n_sep_by_space] => 1
    [p_sign_posn] => 1
    [n_sign_posn] => 1
    [grouping] => Array
        (
            [0] => 3
            [1] => 3
        )

    [mon_grouping] => Array
        (
            [0] => 3
            [1] => 3
        )

)

Testing the output of localeconv with sqlsrv disabled :

/opt/php-7.1/bin/php  -r "print_r(localeconv());"
Array
(
    [decimal_point] => .
    [thousands_sep] =>
    [int_curr_symbol] =>
    [currency_symbol] =>
    [mon_decimal_point] =>
    [mon_thousands_sep] =>
    [positive_sign] =>
    [negative_sign] =>
    [int_frac_digits] => 127
    [frac_digits] => 127
    [p_cs_precedes] => 127
    [p_sep_by_space] => 127
    [n_cs_precedes] => 127
    [n_sep_by_space] => 127
    [p_sign_posn] => 127
    [n_sign_posn] => 127
    [grouping] => Array
        (
        )

    [mon_grouping] => Array
        (
        )

)

+## Expected behavior and actual behavior

• The extension should not modify locale config

[yitam]

Hi @marvinhinz what's the output when you run locale? What are your default LC_ options?

For example, in my env, this is what I have:

root@b5112ea0f810:/tmp# locale
LANG=en_US.UTF-8
LANGUAGE=en_US:en
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC="en_US.UTF-8"
LC_TIME="en_US.UTF-8"
LC_COLLATE="en_US.UTF-8"
LC_MONETARY="en_US.UTF-8"
LC_MESSAGES="en_US.UTF-8"
LC_PAPER="en_US.UTF-8"
LC_NAME="en_US.UTF-8"
LC_ADDRESS="en_US.UTF-8"
LC_TELEPHONE="en_US.UTF-8"
LC_MEASUREMENT="en_US.UTF-8"
LC_IDENTIFICATION="en_US.UTF-8"
LC_ALL=en_US.UTF-8
root@b5112ea0f810:/tmp# php --ri sqlsrv

sqlsrv

sqlsrv support => enabled
ExtensionVer => 5.6.1

Directive => Local Value => Master Value
sqlsrv.WarningsReturnAsErrors => On => On
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
root@b5112ea0f810:/tmp# php  -r "print_r(localeconv());"
Array
(
    [decimal_point] => .
    [thousands_sep] => ,
    [int_curr_symbol] => USD 
    [currency_symbol] => $
    [mon_decimal_point] => .
    [mon_thousands_sep] => ,
    [positive_sign] => 
    [negative_sign] => -
    [int_frac_digits] => 2
    [frac_digits] => 2
    [p_cs_precedes] => 1
    [p_sep_by_space] => 0
    [n_cs_precedes] => 1
    [n_sep_by_space] => 0
    [p_sign_posn] => 1
    [n_sign_posn] => 1
    [grouping] => Array
        (
            [0] => 3
            [1] => 3
        )

    [mon_grouping] => Array
        (
            [0] => 3
            [1] => 3
        )

)
root@b5112ea0f810:/tmp# 

[marvinhinz]

$ locale

LANG=de_DE.UTF-8
LANGUAGE=
LC_CTYPE="de_DE.UTF-8"
LC_NUMERIC="de_DE.UTF-8"
LC_TIME="de_DE.UTF-8"
LC_COLLATE="de_DE.UTF-8"
LC_MONETARY="de_DE.UTF-8"
LC_MESSAGES="de_DE.UTF-8"
LC_PAPER="de_DE.UTF-8"
LC_NAME="de_DE.UTF-8"
LC_ADDRESS="de_DE.UTF-8"
LC_TELEPHONE="de_DE.UTF-8"
LC_MEASUREMENT="de_DE.UTF-8"
LC_IDENTIFICATION="de_DE.UTF-8"
LC_ALL=

$ /opt/php-7.1/bin/php --ri sqlsrv

sqlsrv

sqlsrv support => enabled
ExtensionVer => 5.6.1

Directive => Local Value => Master Value
sqlsrv.WarningsReturnAsErrors => On => On
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240

[yitam]

HI @marvinhinz

It's because the driver currently sets the application locale to the system one, which in your case is de_DE.UTF-8. In other words, the driver essentially does this:

php -r "setlocale(LC_ALL, ''); print_r(localeconv());"

[marvinhinz]

Hi @yitam

Okay this is strange, to workaround our price bug, we had to add setlocale(LC_ALL,"C"); in magento core files.

[stollr]

It's because the driver currently sets the application locale to the system one, which in your case is de_DE.UTF-8. In other words, the driver essentially does this:

php -r "setlocale(LC_ALL, ''); print_r(localeconv());"

@yitam but this behaviour has some very unexpected and hard to debug side effects, which breaks other code. This should not happen.

As you know, setlocale(LC_ALL, 0) can be used to retrieve the current locale settings. With sqlserv extension loaded, we get this output:

$ php -r "var_dump(setlocale(LC_ALL, 0));"
string(11) "de_DE.UTF-8"

Without the extension the output looks quite different:

$ php -r "var_dump(setlocale(LC_ALL, 0));"
string(170) "LC_CTYPE=de_DE.UTF-8;LC_NUMERIC=C;LC_TIME=C;LC_COLLATE=C;LC_MONETARY=C;LC_MESSAGES=C;LC_PAPER=C;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=C;LC_IDENTIFICATION=C"

And as @marvinhinz has already pointed out, Magento (version 1) that uses the Zend Framework's locale component (see https://framework.zend.com/manual/1.12/en/zend.locale.functions.html) struggels with the setting done by sqlsrv extension.

Can you tell me, why the extension changes the locale settings?

[yitam]

Hi @marvinhinz, glad to hear you found a workaround.

Hi @Naitsirch, please be reminded that when calling setlocale(LC_ALL, 0), if not all categories have the same values then a string containing all the locales will be returned. That explains why you see a long string of length 170, because some have the default C locale.

To prove my point, try the following:

$ export LANG=C

$ php -r "var_dump(setlocale(LC_ALL, 0));"
string(1) "C"

Or you can try something like this (with sqlsrv disabled):

$ export LANG='en_US.UTF-8'

$ php -r "var_dump(setlocale(LC_ALL, 0));"
string(170) "LC_CTYPE=en_US.UTF-8;LC_NUMERIC=C;LC_TIME=C;LC_COLLATE=C;LC_MONETARY=C;LC_MESSAGES=C;LC_PAPER=C;LC_NAME=C;LC_ADDRESS=C;LC_TELEPHONE=C;LC_MEASUREMENT=C;LC_IDENTIFICATION=C"

$ php -r "setlocale(LC_ALL, 'en_US.UTF-8'); var_dump(setlocale(LC_ALL, 0));"
string(11) "en_US.UTF-8"

$ php -r "setlocale(LC_ALL, 'en_US.UTF-8'); var_dump(setlocale(LC_MESSAGES, 0));"
string(11) "en_US.UTF-8"

$ php -r "setlocale(LC_ALL, 'en_US.UTF-8'); var_dump(setlocale(LC_NUMERIC, 0));"
string(11) "en_US.UTF-8"

We had users in the past who wanted sqlsrv or pdo_sqlsrv drivers to work with their non-UTF-8 locale(s) in their Linux env. Hence, the values of their locale environment variables are used.

That being said, we may consider providing a configurable option for users. I can mark this issue as a feature request if you like.

[stollr]

Thanks for the explanation. An option to define this behavior would be the best solution I think. The question is, what is the default behavior?

IMHO the activation of an extension should not change the behavior of other parts of the language or library per default. If the user definitely want that and defines that via config file or code, it's okay.

Another question: Can you tell me where I can find the code in the sqlsrv extension that changes the locale setting? I tried to find it but wasn't successful.

[yitam]

hi @Naitsirch , you can find it here

We will discuss how / whether to make this configurable, but in order not to introduce a breaking change the default might likely be the way it is now.

[stollr]

@yitam Thanks for pointing me to the right place :-)

I do understand your BC concerns. Maybe an idea to achieve my proposed idea with developer and administrator friendly upgrade path:

• Introduce configuration in next 5.x release and trigger a deprecation message if the configuration is not set. But keep the default as it is now. This will make sure that everyone set the config the way they need it.
• Then in 6.0 the default behavior can be changed to not changing locale settings without explicit config setting.

That's the way BC breaks are handled in projects like Symfony or PHP core.

[yitam]

Thanks @Naitsirch. We will keep your suggestions in mind when we decide our next course of action.

[yitam]

Hi @marvinhinz @Naitsirch

We just released 5.8.0 and please check the latest documentation concerning this issue

[stollr]

Looks good! Thank you

[yitam]

Great! Thanks for the confirmation @Naitsirch. Closing this issue now. Please feel free to reopen if necessary.

[grossmannmartin]

In my opinion, this issue is not solved.
Consider the following scenario.
I have my developer machine set to my national locale (diacritics, etc.), PHP application sets it's own locale settings independently on host machine. After installing the extension, application is broken due to forced locale settings, and the only way how to fix it is to reconfigure the system (LC_ variables are used by other applications too).

I suggest adding a directive to set specific locale or remove this magic setting inside completely.

[yitam]

@grossmannmartin I don't quite understand the issue. Please elaborate.

Did you add the driver setting in the php.ini as explained in the documentation?

[grossmannmartin]

@yitam Sorry, I try to describe the issue better.

1. My notebook is set to Czech locale. I do not have set LC_* variables.

2. Bootstrap of my application contains following

setlocale(LC_CTYPE, 'en_US.utf8');
setlocale(LC_NUMERIC, 'en_US.utf8');

var_dump(localeconv());

output is

array:18 [
  "decimal_point" => "." 
  "thousands_sep" => ""
  "int_curr_symbol" => ""
  "currency_symbol" => ""
...

3. I enable sqlsrv extension

4. output of the code above is

array:18 [
  "decimal_point" => ","
  "thousands_sep" => " "
  "int_curr_symbol" => "CZK "
  "currency_symbol" => "Kč"

5. I set sqlsrv.SetLocaleInfo under [sqlsrv] section to any value (0, 1, 2)

• no effect

6. I have to set environment variable LC_CTYPE to en_US.utf8

• now the output is as it should be

But any other software depending on this variable break Czech language (git for example)
I have to alter my system settings to make the extension work properly even when I set the locale explicitly in the application.

[yitam]

@grossmannmartin did you happen to have installed pdo_sqlsrv as well? If so, you will need to set locale info for pdo_sqlsrv to 0. If not, please create a new issue and reference this one and we will help you from there.

[grossmannmartin]

@yitam Thank you :) it was really a problem related to pdo_sqlsrv.
After setting pdo_sqlsrv.set_locale_info = 0 in my php.ini file everything works like a charm.

Sorry for the fuss. You rock 👍