René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Quoted usernames are not supported","articleBody":"### Description\r\n\r\nAn 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.\r\n\r\n### utPLSQL Version\r\n\r\nv3.1.7.2808-develop\r\n\r\n### To Reproduce\r\n\r\n#### 1. Create user\r\n\r\n```\r\ncreate user \";drop table t;\" identified by \"demo\";\r\ngrant connect, resource to \";drop table t;\"\r\n```\r\n\r\n#### 2. Create test\r\n\r\n```\r\nconnect \";drop table emp;\"/demo\r\n\r\ncreate table t (c varchar2(10 char));\r\n\r\ncreate or replace package test_p is\r\n\r\n   --%suite\r\n\r\n   --%test\r\n   procedure p;\r\n   \r\nend test_p;\r\n/\r\n\r\ncreate or replace package body test_p is\r\n\r\n   procedure p is\r\n   begin\r\n      ut.expect(1).to_equal(1);\r\n   end p;\r\n   \r\nend test_p;\r\n/\r\n```\r\n\r\n### Run Test\r\n\r\n```\r\nset serveroutput on size unlimited\r\nexecute ut.run(ut_varchar2_list());\r\n```\r\n\r\nThe server output is: \r\n\r\n```\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 45\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 69\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 652\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_RUNNER\", line 136\r\n\r\nORA-20202: Invalid path format: ;drop table t;\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 45\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 69\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 652\r\n\r\n\r\n\r\nError starting at line : 2 in command -\r\nBEGIN ut.run(ut_varchar2_list()); END;\r\nError report -\r\nORA-20202: Invalid path format: ;drop table t;\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_RUNNER\", line 172\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 45\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 69\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_SUITE_MANAGER\", line 652\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT_RUNNER\", line 136\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT\", line 128\r\nORA-06512: at \"UT3_LATEST_RELEASE.UT\", line 465\r\nORA-06512: at line 1\r\n```\r\n\r\nIn this case the test just failed without side effects. However, it shows two things:\r\n\r\na) utPLSQL expects and supports certain style of usernames only (no enquoted user names)\r\n\r\nb) the potential risks of SQL injection.\r\n\r\n\r\n### Expected Behavior\r\n\r\nThe test case above works. 1 test executed successfully.\r\n\r\n\r\n### Additional Context\r\n\r\n@krisrice suggested to change the code in [ut_utils.pkb](https://github.com/utPLSQL/utPLSQL/blob/467078f121f2410638fc71ec7b81eed26bf38dd7/source/core/ut_utils.pkb#L550)\r\n\r\nfrom \r\n\r\n```\r\n  function ut_owner return varchar2 is\r\n  begin\r\n    return sys_context('userenv','current_schema');\r\n  end;\r\n```\r\n\r\nto \r\n\r\n```\r\n  function ut_owner return varchar2 is\r\n  begin\r\n    return sys.dbms_assert.enquote_name(sys_context('userenv','current_schema'));\r\n  end;\r\n```\r\n","author":{"url":"https://github.com/PhilippSalvisberg","@type":"Person","name":"PhilippSalvisberg"},"datePublished":"2019-05-11T16:08:28.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":2},"url":"https://github.com/920/utPLSQL/issues/920"}