Dot Qualify Everything?

There is talk about Dot Qualifying Everything in your PL/SQL Code. But what are the pros and cons in this approach?

Let’s say we have a rather simple function which returns a string with alternatively upper and lower case characters. Normally I would write it as follows:

create or replace function wavey(string_in in varchar2) return varchar2 is
  l_returnvalue varchar2(30);
begin
  for indx in 1 .. length(string_in) loop
    l_returnvalue := l_returnvalue || case mod(indx, 2)
                       when 0 then
                        upper(substr(string_in, indx, 1))
                       else
                        lower(substr(string_in, indx, 1))
                     end;
  end loop;
  dbms_output.put_line(l_returnvalue);
  return l_returnvalue;
end;

The output for this function using the ENAME column of the EMP table is like this:

sMiTh
aLlEn
wArD
jOnEs
mArTiN
bLaKe
cLaRk
sCoTt
kInG
tUrNeR
aDaMs
jAmEs
fOrD
mIlLeR

But what if the was a malicious user that created a package names DBMS_OUTPUT which included all the programs in the original DBMS_OUTPUT package, but with some code added. Let’s create a simple package like this:

create or replace package dbms_output is
  procedure put_line(a in varchar2);
end;
create or replace package body dbms_output is
  procedure put_line(a in varchar2)
  is
  begin
    sys.dbms_output.put('changed :');
    sys.dbms_output.put_line(a);
  end;
end;

Notice I just included the PUT_LINE procedure in here and I am not really doing anything malicious here. The output of my function would now be:

changed :sMiTh
changed :aLlEn
changed :wArD
changed :jOnEs
changed :mArTiN
changed :bLaKe
changed :cLaRk
changed :sCoTt
changed :kInG
changed :tUrNeR
changed :aDaMs
changed :jAmEs
changed :fOrD
changed :mIlLeR

Not exactly the way I would want. How can I prevent this from happening? The answer is actually really simple. Qualify the call to DBMS_OUTPUT with the schema name where the packages resides:

create or replace function wavey(string_in in varchar2) return varchar2 is
  l_returnvalue varchar2(30);
begin
  for indx in 1 .. length(string_in) loop
    l_returnvalue := l_returnvalue || case mod(indx, 2)
                       when 0 then
                        upper(substr(string_in, indx, 1))
                       else
                        lower(substr(string_in, indx, 1))
                     end;
  end loop;
  sys.dbms_output.put_line(l_returnvalue);
  return l_returnvalue;
end;

The output is back to what we expected

sMiTh
aLlEn
wArD
jOnEs
mArTiN
bLaKe
cLaRk
sCoTt
kInG
tUrNeR
aDaMs
jAmEs
fOrD
mIlLeR

But should you Dot Qualify everything in your code? Like this?

create or replace function demo.wavey(string_in in sys.standard.varchar2) return sys.standard.varchar2 is
  l_returnvalue sys.standard.varchar2(30);
begin
  <<theloop>>
  for indx in 1 .. length(wavey.string_in) loop
    wavey.l_returnvalue := wavey.l_returnvalue || case mod(theloop.indx, 2)
                             when 0 then
                              sys.standard.upper(standard.substr(wavey.string_in, theloop.indx, 1))
                             else
                              sys.standard.lower(standard.substr(wavey.string_in, theloop.indx, 1))
                           end;
  end loop;
  sys.dbms_output.put_line(wavey.l_returnvalue);
  return wavey.l_returnvalue;
end;

I don’t think this adds to the readability of the code, but it sure makes your code safer against malicious users that want to implement code that can be executed by your programs. Please add your thoughts on this subject.

4 thoughts on “Dot Qualify Everything?

  1. Patrick,

    Here’s my opinion on this.
    If it appears to be based on wrong or incomplete assumptions, I would love to hear why I’m wrong.

    ‘Dot qualification’ can do wonders for clarity about which of the objects called ‘x’ spread out over several schema’s is actually called.
    It is easier to determine this way, than to investigate if there are private/public synonyms and what they refer to.

    Even within a single compilation unit it could clarify which of the seven variables ‘v_dummy’ declared at different levels you’re referring to, and will even make absolutely sure you refer to the right one.

    However, I disagree with the argument that it makes your code safer.
    If that’s what you think, then using dot qualification gives you a false sense of safety.
    The example you give sounds frightening, and it is!
    However, in that case your problem is not the lack of dot qualification but the fact that a person with bad intentions has “create procedure” privileges in the application schema.

    If I don’t have that privilege, all I can do is create the bad-boy package in my own schema.
    But since you call the good-guy package without dot qualification you either have it in your application schema or access it through a private or public synonym that is redirecting you to ‘good-guy’, not ‘bad-boy’.
    So my version will not bother you.

    So, you’re out of luck, I have this “create procedure” privilege in the application schema.
    Let’s assume you will spend most of next week changing all of your code to always use dot qualification.
    Great. Well done.
    Your weekend always starts early 😉 and its start is announced on twitter, so next weekend I have all the time I need to log in to do some bad stuff.

    I create a dbms_output package and obviously test if my plan to take over the world works.
    It doesn’t, because of your hard work.
    Does this mean I’m done playing around?
    By no means. Since I can create packages, I can alter them as well.
    Every application has general-purpose packages used all over the place that are developed early in the life of the application and hardly ever get looked at again. At least not in a ‘what does this code do’ kind of way.
    Like packages for logging, instrumentation etc.

    So I decide to add my malicious code to your my_app_logging package.
    Too detectable?
    Well I argue that people will a lot sooner think “what the hell is that dbms_output package doing in the application schema” than “let’s go over the code in this logging package that has been working flawlessly for the past 5 years”.
    But OK, I see your point.
    So I create a new package with a name that doesn’t stick out among the other packages in the application.
    Let’s call it my_app_legal_obligations, and put my malicious code in procedure do_auditing.
    Now I compile that package wrapped so nobody can see what it does and add the following lines to my_app_logging:
    — For legal reasons this next line may *never* be disabled
    my_app_schema.my_app_legal_obligations.do_auditing;

    People might stumble upon this code, but I can assure you it will take some time before that will be somebody with the nerve to question what is going on.
    (do not underestimate the power of demanding comments)
    In fact it will take a lot longer than in the case of an out of place dbms_output.

    So you see, if I have the power I need to mislead an application that does not use dot qualification, I have the power to mislead the application. Period.


  2. I agree with you, it doesn’t make your code more readable. But qualifying local variables like wavey.l_returnvalue and theloop.indx don’t add anything to security.
    And using things like sys.standard.upper and sys.standard.varchar2 gives you only the false impression that you have done everything you can to keep mister bad guy out. But even in a simple example as this you make enough mistakes to prevent that: you use standard.substr without qualifying the owner sys and you forget to qualify the function length at all. And what about user defined operators? Qualify every operator too? And what will you do if you use for instance apex packages which have a different owner for every version/release?
    So I agree with Erik, it won’t make your code a bit safer


  3. @Erik: I agree that when someone has access to you database with enough right to create code and maybe even reroute [public] synonyms you have a bigger problem at your hands, but consider this scenario: One of your employees has access to the database, which he needs for his day-to-day job but he has a dispute which he can’t solve in the usual way. He might create a package which has the same signature as an existing package, reroutes all the [public] synonyms and does his malicious work, like transferring funds to himself, without anyone noticing, or at least for a certain period. I think that when you dot qualify all the programs you use from other schema’s and don’t rely on [public] synonyms you are much more protected from this kind of behavior. Maybe I should make my example more explicit, using different schema’s and using synonyms.
    The example you give, when the same name of the variable is used all over the shows bad practice I think. Variable names should have useful names, but you can use dot qualification to help you out.
    @Anton: The second example was to show just that. Using dot qualification to the max will give you a false sense of security and you are, of course, right: I missed a couple schema prefixes. Although, in APEX, for instance in plugins, it is good practice to dot qualify especially the SYS packages, since they are so common used and your application SHOULD use the supplied packages. Might be a good idea for the APEX development team to create a schema with a standard name instead of a name that changes every release ;-).


  4. “when the same name of the variable is used all over the shows bad practice I think”
    Absolutely true. But it happens. A lot. All I said was that in such a case, although some renaming would be better, dot qalification could be helpfull.

    As for the rogue employee, that is in my opinion exactly the kind of bad guy you will not be able to defend yourself against by simple dor qualification.
    That is precisely the kind of person that could come up with the kind of workaround I described.

    I would love to see a scenario where dot qualification closes the door on a person with bad intentions.


Leave a Reply to Patch72 Cancel reply

Your email address will not be published. Required fields are marked *