2 Replies Latest reply on Jul 10, 2018 2:11 PM by fac586

    Username Validation Check

    JakeMorgan

      Hi All,

       

      I'm attempting to create a very simple vallidation check on an APEX (5.0) login page.

       

      I want the validation check to make sure the username (:P101_USERNAME) exists in the "acl_users" table and additionally, to ensure they have an access level more than 0.

       

      I've tried using a "Rows returned" validation type and the sql query I'm using is:

       

           select count(*)

           from acl_users aclu

           where upper(aclu.username) = :P101_USERNAME

           and aclu.acl_clearing_level > 0

           and rownum = 1;

       

       

      Testing this outside of APEX returns rows as expected (using a valid username).  However, the application seems to be letting me login, regardless of the value of "acl_clearing_level".

       

      Conversely, if I set the type to "No rows returned", every login request is blocked.

       

      Any advice greatly appreciated.

       

      Capture.PNG

        • 1. Re: Username Validation Check
          JakeMorgan

          I decided to use a PL/SQL function body (returning boolean)

           

            declare

               l_cnt integer;

             begin

               select count(*)

                 into l_cnt

                 from acl_users

                where upper(username) = upper(:P101_USERNAME)

                and acl_clearing_level > 0

                and rownum = 1

                ;

           

               if( l_cnt >= 1 )

               then

                 return true;

               else

                 return false;

               end if;

             end;

           

          This works successfully.

          • 2. Re: Username Validation Check
            fac586

            JakeMorgan wrote:

             

            Hi All,

             

            I'm attempting to create a very simple vallidation check on an APEX (5.0) login page.

             

            I want the validation check to make sure the username (:P101_USERNAME) exists in the "acl_users" table and additionally, to ensure they have an access level more than 0.

             

            I've tried using a "Rows returned" validation type and the sql query I'm using is:

             

            select count(*)

            from acl_users aclu

            where upper(aclu.username) = :P101_USERNAME

            and aclu.acl_clearing_level > 0

            and rownum = 1;

             

             

            Testing this outside of APEX returns rows as expected (using a valid username). However, the application seems to be letting me login, regardless of the value of "acl_clearing_level".

             

            Conversely, if I set the type to "No rows returned", every login request is blocked.

             

            Any advice greatly appreciated.

             

            This is due to a logical error in your code. select count(*) ... without a group by will always return one row (which may contain the value "0").

             

            Do not perform tests for existence using select count(*) from ... where ... . Not only is this prone to this type of logic issue, but depending on the predicates used and the absence of indexes/partitions on the table(s) used it may also be very inefficient.

             

            All that the APEX built-in Rows returned condition requires is a simple query containing the required predicates:

             

            select null
            from acl_users aclu
            where upper(aclu.username) = :P101_USERNAME
            and aclu.acl_clearing_level > 0
            

             

            To perform existence checks in your own code, use this pattern:

             

            ...
            select
                count(*) n
            into
                n    
            from
                dual
            where
                exists (select
                            null
                        from
                            acl_users aclu
                        where
                            upper(aclu.username) = :P101_USERNAME
                        and aclu.acl_clearing_level > 0);
            
            if n > 0 ...
            

             

            This will check that the predicates are satisfied as efficiently as possible and always return one row containing the value 0 or 1, hence it is both performance and unambiguous.

            1 person found this helpful