Repository navigation
Quoted usernames are not supported #920
Description
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;
Activity
[-]Quoted Usernames are not supported[/-][+]Quoted usernames are not supported[/+]krisrice commentedon 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 commentedon Jul 23, 2019
An assert with
ut_utils.qualified_sql_nameshould do the job in this case.Resolve security issues around insecure username for utplsql owner