Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Parameters with multibyte character sets allow to bypass the character limit of varchar fields [CORE5277] #5555

Closed
firebird-automations opened this issue Jun 13, 2016 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Relate to CORE5304

Simple test case:

isql t.fdb -ch utf8

recreate table t (c varchar(2) character set utf8);

set bulk_insert INSERT INTO T VALUES (?);
--
('abcdefgh')
stop

I suppose 2.5 is also susceptible with a different test case.

------------

Original test case:

CREATE USER <name> allows to specify <name> with length more than 64 characters,which lead to failure when we further execute SELECT * FROM SEC$USERS or SHOW USERS

1) Stop FB server, take empty (non-initialized) security4.fdb and enter command:

C:\MIX\firebird\fb40> echo create user sysdba password 'masterke'; show users; | C:\MIX\firebird\fb40\isql -q -z C:\MIX\firebird\fb40\security4.fdb
ISQL Version: WI-T4.0.0.248 Firebird 4.0 Unstable
Server version:
WI-T4.0.0.248 Firebird 4.0 Unstable
Users in the database
2 #⁠SYSDBA

2) Start FB server

3) Run:
C:\MIX\firebird\fb40>C:\MIX\firebird\fb40\isql -q
SQL> create database 'localhost:c:\temp\tmp201606131619.fdb' user sysdba password 'masterke';
SQL> create or alter user
CON> u2345678901234567890123456789012345678901234567890123456789012345 password 'q';
SQL> commit;

SQL> create or alter user
CON> z234567890123456789012345678901234567890123456789012345678901234567890 password 'z';
SQL> commit;
SQL> set list on;
SQL> select * from sec$users;

Statement failed, SQLSTATE = 22001
find/display record error
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 64, actual 65
SQL> show users;
Statement failed, SQLSTATE = 22001
find/display record error
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 64, actual 65
Command error: show users

Why exception did not raise when we issued first 'create user' command (before commit) ?

Commits: 600b408 0e90030 891c172 36e4fa9 0d46c91 8f02eb0 614489e 9e4325a 258dba5 df233ae 4ec694f 5e3fd2d 234e25f 9fede1e

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

PS.

DDL of sec$users now allows to store user name in 64 UTF8 characters:

C:\MIX\firebird\QA\fbt-repo\tmp>echo show table sec$users;|isql /:e40
SEC$USER_NAME (RDB$USER) CHAR(64) CHARACTER SET UTF8 Nullable
SEC$FIRST_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER SET UTF8 Nullable
SEC$MIDDLE_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER SET UTF8 Nullable
SEC$LAST_NAME (SEC$NAME_PART) VARCHAR(32) CHARACTER SET UTF8 Nullable
...

This new (increased) limit explains why following statement passes without error:

create or alter user
u2345678901234567890123456789012345678901234567890123456789012345u2345678901234567890123456789012345678901234567890123456789012345u2345678901234567890123456789012345678901234567890123456789012345u23456789012345678901234567890123456789012345678901234567890
password 'A';

(length of login = 255 bytes).

So, now i think that there is no error when we add new user with long name, rather command SHOW USERS and query to SEC$USERS should be fixed.

Is it correct guess ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 3.0.0 [ 10740 ]

Component: Engine [ 10000 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

description: 1) Stop FB server, take empty (non-initialized) security4.fdb and enter command:

C:\MIX\firebird\fb40> echo create user sysdba password 'masterke'; show users; | C:\MIX\firebird\fb40\isql -q -z C:\MIX\firebird\fb40\security4.fdb
ISQL Version: WI-T4.0.0.248 Firebird 4.0 Unstable
Server version:
WI-T4.0.0.248 Firebird 4.0 Unstable
Users in the database
2 #⁠SYSDBA

2) Start FB server

3) Run:
C:\MIX\firebird\fb40>C:\MIX\firebird\fb40\isql -q
SQL> create database 'localhost:c:\temp\tmp201606131619.fdb' user sysdba password 'masterke';
SQL> create or alter user
CON> u2345678901234567890123456789012345678901234567890123456789012345 password 'q';
SQL> commit;

SQL> create or alter user
CON> z234567890123456789012345678901234567890123456789012345678901234567890 password 'z';
SQL> commit;
SQL> set list on;
SQL> select * from sec$users;

Statement failed, SQLSTATE = 22001
find/display record error
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 64, actual 65
SQL> show users;
Statement failed, SQLSTATE = 22001
find/display record error
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 64, actual 65
Command error: show users

Why exception did not raise when we issued first 'create user' command (before commit) ?

=>

Simple test case:

isql t.fdb -ch utf8

recreate table t (c varchar(2) character set utf8);

set bulk_insert INSERT INTO T VALUES (?);
--
('abcdefgh')
stop

I suppose 2.5 is also susceptible with a different test case.

------------

Original test case:

CREATE USER <name> allows to specify <name> with length more than 64 characters,which lead to failure when we further execute SELECT * FROM SEC$USERS or SHOW USERS

1) Stop FB server, take empty (non-initialized) security4.fdb and enter command:

C:\MIX\firebird\fb40> echo create user sysdba password 'masterke'; show users; | C:\MIX\firebird\fb40\isql -q -z C:\MIX\firebird\fb40\security4.fdb
ISQL Version: WI-T4.0.0.248 Firebird 4.0 Unstable
Server version:
WI-T4.0.0.248 Firebird 4.0 Unstable
Users in the database
2 #⁠SYSDBA

2) Start FB server

3) Run:
C:\MIX\firebird\fb40>C:\MIX\firebird\fb40\isql -q
SQL> create database 'localhost:c:\temp\tmp201606131619.fdb' user sysdba password 'masterke';
SQL> create or alter user
CON> u2345678901234567890123456789012345678901234567890123456789012345 password 'q';
SQL> commit;

SQL> create or alter user
CON> z234567890123456789012345678901234567890123456789012345678901234567890 password 'z';
SQL> commit;
SQL> set list on;
SQL> select * from sec$users;

Statement failed, SQLSTATE = 22001
find/display record error
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 64, actual 65
SQL> show users;
Statement failed, SQLSTATE = 22001
find/display record error
-arithmetic exception, numeric overflow, or string truncation
-string right truncation
-expected length 64, actual 65
Command error: show users

Why exception did not raise when we issued first 'create user' command (before commit) ?

summary: CREATE USER <name> allows to specify <name> with length more than 64 characters,which lead to failure when we further execute SELECT * FROM SEC$USERS or SHOW USERS => Parameters with multibyte character sets allow to bypass the character limit of varchar fields

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Edited as the source of the problem has nothing to do with CREATE USER.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.1 [ 10730 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE5304 [ CORE5304 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment