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

Quoted usernames are not supported #920

Closed
PhilippSalvisberg opened this issue May 11, 2019 · 2 comments · Fixed by #988

Comments

@PhilippSalvisberg
Copy link
Member

commented May 11, 2019

Description

An unfriendly DBA could inject SQL by creating a user. All parts building up a dynamic SQL or PL/SQL statements must be asserted accordingly. Even expressions like sys_context('userenv','current_schema'). Special thanks to @krisrice for pointing that out.

utPLSQL Version

v3.1.7.2808-develop

To Reproduce

1. Create user

create user ";drop table t;" identified by "demo";
grant connect, resource to ";drop table t;"

2. Create test

connect ";drop table emp;"/demo

create table t (c varchar2(10 char));

create or replace package test_p is

   --%suite

   --%test
   procedure p;
   
end test_p;
/

create or replace package body test_p is

   procedure p is
   begin
      ut.expect(1).to_equal(1);
   end p;
   
end test_p;
/

Run Test

set serveroutput on size unlimited
execute ut.run(ut_varchar2_list());

The server output is:

ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 45
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 69
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 652
ORA-06512: at "UT3_LATEST_RELEASE.UT_RUNNER", line 136

ORA-20202: Invalid path format: ;drop table t;
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 45
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 69
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 652



Error starting at line : 2 in command -
BEGIN ut.run(ut_varchar2_list()); END;
Error report -
ORA-20202: Invalid path format: ;drop table t;
ORA-06512: at "UT3_LATEST_RELEASE.UT_RUNNER", line 172
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 45
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 69
ORA-06512: at "UT3_LATEST_RELEASE.UT_SUITE_MANAGER", line 652
ORA-06512: at "UT3_LATEST_RELEASE.UT_RUNNER", line 136
ORA-06512: at "UT3_LATEST_RELEASE.UT", line 128
ORA-06512: at "UT3_LATEST_RELEASE.UT", line 465
ORA-06512: at line 1

In this case the test just failed without side effects. However, it shows two things:

a) utPLSQL expects and supports certain style of usernames only (no enquoted user names)

b) the potential risks of SQL injection.

Expected Behavior

The test case above works. 1 test executed successfully.

Additional Context

@krisrice suggested to change the code in ut_utils.pkb

from

  function ut_owner return varchar2 is
  begin
    return sys_context('userenv','current_schema');
  end;

to

  function ut_owner return varchar2 is
  begin
    return sys.dbms_assert.enquote_name(sys_context('userenv','current_schema'));
  end;

@PhilippSalvisberg PhilippSalvisberg changed the title Quoted Usernames are not supported Quoted usernames are not supported May 11, 2019

@krisrice

This comment has been minimized.

Copy link

commented May 12, 2019

That's just 1 option to change ut_owner I did not check into all usages. If the function return is used to say compare SCHEMA_NAME = SCHEMA_NAME, then the added quotes would cause issues.

@PhilippSalvisberg

This comment has been minimized.

Copy link
Member Author

commented Jul 23, 2019

An assert with ut_utils.qualified_sql_name should do the job in this case.

jgebal added a commit that referenced this issue Sep 2, 2019

@jgebal jgebal closed this in #988 Sep 3, 2019

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.