Non-ASCII data in SEC$USERS is not read correctly [CORE4301]

[firebird-automations]

Submitted by: @sim1984

Is related to QA584

User comment distorts non-ASCII characters

c:\Program Files\Firebird\Firebird_3_0>chcp 1251 Текущая кодовая страница: 1251

c:\Program Files\Firebird\Firebird_3_0>isql Use CONNECT or CREATE DATABASE to specify a database SQL> set names win1251; SQL> connect 'localhost/3051:test' user 'sysdba' password 'masterkey'; Database: 'localhost/3051:test', User: sysdba SQL> create table T1(id integer); SQL> commit; SQL> comment on table T1 is 'Моя таблица'; SQL> commit; SQL> select r.rdb$description as descr CON> from rdb$relations r CON> where r.rdb$relation_name = 'T1';

        DESCR

================= 0:3

DESCR: Моя таблица

SQL> create user user1 password '123'; SQL> commit; SQL> comment on user user1 is 'Новый пользователь'; SQL> commit; SQL> select u.sec$description as descr CON> from sec$users u CON> where u.sec$user_name = 'USER1';

        DESCR

================= 0:5

DESCR: ?????????? ????????????????????????

SQL> alter user user1 set tags(tag1='привет'); SQL> commit; SQL> select * from sec$user_attributes;

SEC$USER_NAME SEC$KEY SEC$VALUE

=============================== =============================== ========================================================

USER1 TAG1 ????????????

Commits: FirebirdSQL/firebird@79c6b2165b7d574ef6d60425eea2101412fb42ce FirebirdSQL/fbt-repository@07fe6f6308abcb3c9f5ad515782b771b6394d253

====== Test Details ======

For test content of COMMENTS field firebird.conf should contain: UserManager = Srp. Currently it is not so on test-running host, so it was decided to check only content of FIRST_NAME field which is available on any value of `UserManager` parameter.

[firebird-automations]

Commented by: @sim1984

Database create as

CREATE DATABASE 'localhost/3051:TEST' USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 8192 DEFAULT CHARACTER SET WIN1251;

[firebird-automations]

Modified by: @sim1984

description: User comment distorts non\-ASCII characters c:\\Program Files\\Firebird\\Firebird\_3\_0\>chcp 1251 Текущая кодовая страница: 1251 c:\\Program Files\\Firebird\\Firebird\_3\_0\>isql Use CONNECT or CREATE DATABASE to specify a database SQL\> set names win1251; SQL\> connect 'localhost/3051:test' user 'sysdba' password 'masterkey'; Database: 'localhost/3051:test', User: sysdba SQL\> create table T1\(id integer\); SQL\> commit; SQL\> comment on table T1 is 'Моя таблица'; SQL\> commit; SQL\> select r\.rdb$description as descr CON\> from rdb$relations r CON\> where r\.rdb$relation\_name = 'T1'; DESCR ================= 0:3 ============================================================================== DESCR: Моя таблица ============================================================================== SQL\> create user user1 password '123'; SQL\> commit; SQL\> comment on user user1 is 'Новый пользователь'; SQL\> commit; SQL\> select u\.sec$description as descr CON\> from sec$users u CON\> where u\.sec$user\_name = 'USER1'; DESCR ================= 0:5 ============================================================================== DESCR: ?????????? ???????????????????????? ============================================================================== SQL\> =\> User comment distorts non\-ASCII characters c:\\Program Files\\Firebird\\Firebird\_3\_0\>chcp 1251 Текущая кодовая страница: 1251 c:\\Program Files\\Firebird\\Firebird\_3\_0\>isql Use CONNECT or CREATE DATABASE to specify a database SQL\> set names win1251; SQL\> connect 'localhost/3051:test' user 'sysdba' password 'masterkey'; Database: 'localhost/3051:test', User: sysdba SQL\> create table T1\(id integer\); SQL\> commit; SQL\> comment on table T1 is 'Моя таблица'; SQL\> commit; SQL\> select r\.rdb$description as descr CON\> from rdb$relations r CON\> where r\.rdb$relation\_name = 'T1'; DESCR ================= 0:3 ============================================================================== DESCR: Моя таблица ============================================================================== SQL\> create user user1 password '123'; SQL\> commit; SQL\> comment on user user1 is 'Новый пользователь'; SQL\> commit; SQL\> select u\.sec$description as descr CON\> from sec$users u CON\> where u\.sec$user\_name = 'USER1'; DESCR ================= 0:5 ============================================================================== DESCR: ?????????? ???????????????????????? ============================================================================== SQL\> alter user user1 set tags\(tag1='привет'\); SQL\> commit; SQL\> select \* from sec$user\_attributes; SEC$USER\_NAME SEC$KEY SEC$VALUE =============================== =============================== ======================================================== ======================= USER1 TAG1 ???????????? summary: User comment distorts non\-ASCII characters =\> Comment for users and user attributes distorts non\-ASCII characters

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

summary: Comment for users and user attributes distorts non\-ASCII characters =\> COMMENT ON USER with non\-ASCII characters are not saved correctly

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

I don't agree that should have a "Major" priority.

[firebird-automations]

Modified by: @sim1984

priority: Major \[ 3 \] =\> Minor \[ 4 \]

[firebird-automations]

Commented by: @sim1984

I agree.

[firebird-automations]

Commented by: @asfernandes

They are saved correctly. They are not read correctly.

[firebird-automations]

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes \[ asfernandes \]

[firebird-automations]

Modified by: @asfernandes

Version: 3\.0 Alpha 2 \[ 10560 \] summary: COMMENT ON USER with non\-ASCII characters are not saved correctly =\> Non\-ASCII data in SEC$USERS is not read correctly

[firebird-automations]

Modified by: @asfernandes

status: Open \[ 1 \] =\> Resolved \[ 5 \] resolution: Fixed \[ 1 \] Fix Version: 3\.0 Beta 1 \[ 10332 \]

[firebird-automations]

Modified by: @pcisar

Link: This issue is related to [QA584](https://github.com/FirebirdSQL/fbt-repository/issues?q=QA584+in%3Atitle) \[ [QA584](https://github.com/FirebirdSQL/fbt-repository/issues?q=QA584+in%3Atitle) \]

[firebird-automations]

Commented by: @pavel-zotov

How can it be explained that if:

1) create new database:

$ /opt/fb30ss/bin/isql -q SQL> set names utf8; SQL> create database '192.168.0.220/3333:/var/db/fb30/c4301.fdb' default character set utf8; SQL> show version; ISQL Version: LI-T3.0.0.31740 Firebird 3.0 Beta 2 Server version: Firebird/Linux/AMD/Intel/x64 (access method), version "LI-T3.0.0.31740 Firebird 3.0 Beta 2" Firebird/Linux/AMD/Intel/x64 (remote server), version "LI-T3.0.0.31740 Firebird 3.0 Beta 2/tcp (oel64)/P13" Firebird/Linux/AMD/Intel/x64 (remote interface), version "LI-T3.0.0.31740 Firebird 3.0 Beta 2/tcp (oel64)/P13" on disk structure version 12.0

2) and run script with only ASCII characters:

drop user tmp$c4301a; commit; drop user tmp$c4301b; commit; create user tmp$c4301a password '123'; commit; create user tmp$c4301b password '123'; commit; comment on user tmp$c4301a is 'This is user tmp$c4301a'; commit; comment on user tmp$c4301b is 'This is user tmp$c4301b'; commit; set blob all; set list on; select u.SEC$USER_NAME, u.sec$description as descr from sec$users u where upper(u.sec$user_name) starting with upper('tmp$c4301');

-- then result is NULLs in the sec$description:

SEC$USER_NAME TMP$C4301A
DESCR <null>

SEC$USER_NAME TMP$C4301B
DESCR <null>

-- ?

PS. $ cat firebird.conf AuthClient = Legacy_Auth,Srp,Win_Sspi AuthServer = Legacy_Auth,Srp

DefaultDbCachePages = 8K

ExternalFileAccess = Restrict /var/db/fb30 FileSystemCacheThreshold = 65536K

LockMemSize = 64M LockHashSlots = 22111

MaxUserTraceLogSize = 99999 RemoteServicePort = 3333 TempCacheLimit = 2147483647 TempDirectories = /dev/shm;/tmp UserManager = Legacy_UserManager WireCrypt = Disabled

[firebird-automations]

Commented by: @pavel-zotov

Sorry for my previous stupid post, the reason was obvious: usage of UserManager = Legacy_UserManager in config.

If run this test on standard firebird.conf than all OK:

$ /opt/fb30ss/bin/isql -q SQL> set names utf8; SQL> create database 'localhost/3333:/var/db/fb30/c4301.fdb' default character set utf8; SQL> create user u30a password 'u30a'; commit; SQL> create user u30b password 'u30b'; commit; SQL> comment on user u30a is 'это кто-то из наших'; commit; SQL> comment on user u30b is 'é alguém do Brasil'; commit; SQL> select u.SEC$USER_NAME, u.sec$description as descr CON> from sec$users u CON> where upper(u.sec$user_name) starting with upper('u30');

SEC$USER_NAME DESCR =============================== ================= U30A 0:5

DESCR: это кто-то из наших

U30B 0:8

DESCR: é alguém do Brasil

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved \[ 5 \] =\> Resolved \[ 5 \] QA Status: Done with caveats Test Details: For test content of COMMENTS field firebird\.conf should contain: UserManager = Srp\. Currently it is not so on test\-running host, so it was decided to check only content of FIRST\_NAME field which is available on any value of \`UserManager\` parameter\.

[firebird-automations]

Modified by: @pavel-zotov

status: Resolved \[ 5 \] =\> Resolved \[ 5 \] Test Specifics: \[Custom configuration required\]

[firebird-automations]

Commented by: @pcisar

Test created.

[firebird-automations]

Modified by: @pcisar

status: Resolved \[ 5 \] =\> Closed \[ 6 \]