Creating a basic application: Oracle APEX vs Symfony

Last week I was reading an article on how to create your first Symfony application. Symfony is a popular open-source PHP web application framework, following the MVC architecture pattern. That’s actually all I know about Symfony. Anyway, I was surprised by the length of the article and the amount of code that was required to build a simple application. That triggered me to write this blog post in which I’ll show how the same application can be built in Oracle Application Express.

The title of this blog post might be a bit misleading, since I have absolutely no intention to make a comparison between Oracle APEX and Symfony. I believe there’s no such thing as the better technology. Each technology has its strengths and weaknesses. It’s a matter of finding the right tool for the job you need to do.

get the right tool for the job

Building the application

The application we’ll build is not very spectacular. Probably the best thing about it is that it has something to do with Game of Thrones.

We’ll be building a simple character listing app with Symfony 3.1 like we did with Laravel here. Our app will simply list 10 Game of Thrones characters and their real names. Once we add authentication to the app, all logged-in users will have the privilege of knowing these celebrity characters personally.

An experienced APEX developer will describe this type of application as a walk in the park. Here’s what I did to create the GoT application in Oracle APEX.

1. Create two database tables to hold the user and character data. I also wrote some insert statements for the GOT_CHARACTERS table to load the sample data.

ERD

2. Create a new APEX application. I simply had to accept the default values in the Create Application wizard.

3. Put a classic report on the Home page to show the list of Game of Thrones characters. My extensive SQL knowledge really paid off here.

select character_name as "Character",
       actor_name as "Real Name"
from got_characters;

4. Create a new public page where people can register. I simply went through the Form on a Table or View page wizard, based on the GOT_USERS table. I then manually edited the form to include two more password items. One for the confirm password functionality, and one to hold the hashed password. I also created two basic validations: passwords match and email is unique.

5. I then created the GOT_SECURITY package to handle password hashing and authentication. Here’s the code for those who are interested.

create or replace package body got_security is

  function hash_password(
    in_username in varchar2,
    in_password in varchar2
  ) return got_users.password%type is
    l_hashed_password got_users.password%type;
  begin
    -- dbms_obfuscation_toolkit is deprecated
    -- better use the dbms_crypto package
    l_hashed_password := dbms_obfuscation_toolkit.md5(
                           input_string => upper(in_username) || in_password
                         );

    return l_hashed_password;
  end hash_password;

  function authentication(
    p_username in varchar2,
    p_password in varchar2
  ) return boolean is
    l_user_is_authenticated number;
  begin
    select count(*)
    into l_user_is_authenticated
    from got_users
    where upper(email) = upper(p_username)
    and password = hash_password(
                     in_username => p_username,
                     in_password => p_password
                   );

    if l_user_is_authenticated = 0 then
      return false;
    else
      return true;
    end if;
  end authentication;

end got_security;

I’m using the deprecated DBMS_OBFUSCATION_TOOLKIT package to generate MD5 password hashes. This is actually not a very secure solution. I wanted to use the DBMS_CRYPTO package instead, but I don’t have the required privileges on apex.oracle.com to do so. Anyhow, Symfony is in the advantage here because it can use bcrypt, one of the best password hashing functions presently available.

6. As a last step, I created a custom authentication scheme in which I reference the GOT_SECURITY.AUTHENTICATION function. That way, registered users will be able to successfully authenticate and see the GoT character list.

Conclusion

As you can see, it doesn’t take a lot of time and effort to develop this type of application with Oracle APEX. After reading the Symfony article, you’ll notice how different it is from the APEX development approach.

Perhaps the most striking difference between the two solutions is the total lines of code. The Symfony application counts 552 lines of code, while APEX shaved that figure down to 97. That’s 17.5% of the Symfony application’s code base. Fewer lines of code doesn’t necessarily mean better code, but I can think of the following advantages:

• less complexity
• easier to introduce new developers to the project
• faster development
• smaller budgets
• a reduced chance of bugs

The Game of Thrones Characters APEX application is available on apex.oracle.com here. I have little confidence in my deprecated password hashing function, so I advise you to use a dummy password when registering.

Advertisement