APEX Security: Why Sequential Primary Keys Can Be A Risk

If you go to a restaurant in Germany or Switzerland, you have to fill out a sheet of paper with personal and contact information because of the COVID-19 pandemic. If another guest gets tested positively after his restaurant visit, he can contact the restaurant, which then can notify and warn all guests that were there at the same time.

The swiss restaurant booking system forAtable thought this can be easier and better with an IT solution. They developed a web app where you can enter your contact information without having to use paper. You have to decide for yourself if this solution seems better, but in terms of data privacy, it is probably worse to have all data on a central server. And after IT-Security specialists Sven Faßbender, Joël Gunzenreiner, Thorsten Schröder from the company modzero took a closer look into this app, it became apparent.

In a blog post, they stated how they found a big security vulnerability that allows them to see the entered data from foreign people. This is a huge flaw because someone with bad intentions could have written a simple script that collects this data and sell that private data for profit. The vulnerability itself is pretty simple. The weakness is the numeric ID in the URL. This ID is generated sequentially and the result of this is that you can just decrease the number from your entry to see entries from persons that used this service before you. You can read more about this case in their blog post.

So the problem is that they used a sequence to populate their primary key and used it as the identifier in their public web application. This story got me thinking if this problem is transferable to APEX and should sequences generally be avoided?

The short answer: yes you can have the same problem in your APEX app. But of course, the APEX Devs thought of this so there is an easy way to avoid this issue. So sequences aren’t generally bad to use or even a vulnerability.

Often you create a report with a link column. The link passes the primary key to another page that loads additional information with the passed key. The generated link looks something like this (new 20.1 URL syntax):

1https://apex.oracle.com/pls/apex/ws/r/app/page?p32_empno=7839&session=...
2

If you have Session State Protection enabled in the shared components under security and on the target page or item, your generated link will also include a checksum. Checksums are a long string of random characters that represents a hash of the passed values. This hash is completely different for a changed value and gets checked by the database. So if you try to change the passed value in the URL and keep the old checksum the database will check the values against the generated hash and if it won’t match it will now you have tampered the URL and throw an error. There is no way for you to calculate the checksum yourself because the needed secret key is sitting in the database and to guess the checksum is also almost impossible.

Session State Protection setting in the shared components under security:

Page level setting: Arguments Must Have Checksum:

Checksum settings can also be controled on specific items with additional settings:

If your page needs to be public and easily linkable without a big checksum or your data needs to be accessible over an API without authorization features, you should not use sequentially generated values as IDs. Oracle provides the sys_guid function which generates global unique identifiers. Another option would be to use the dbms_crypto.randombytes method. This will make it extremely hard to guess an ID. Rate limiting the API would be an effective measurement to prevent brute force attacks.

Important is that the generated key should be long enough and contain a variety of different characters (just like passwords). At the beginning of this year, people noticed more and more foreigners joining Zoom meetings they were not invited in. The background of this is that Zoom meeting IDs consist of only 9-11 digits and by default, did not require a password to join (this may have changed by now).

Alexander Chailytko wrote a script that would brute force random combinations of possible meeting IDs and could quickly find active meetings you could just joint without an invitation. He published his findings in a blog post resulting in many people playing “Zoom roulette” where they would join random meetings with his script.

This scenario is easily avoidable by using a variety of characters instead of numbers and longer IDs and rate limiting your API.

In most cases, sequences are totally fine to use for primary key values. All you need to do is to enable the checksum feature of APEX and the users won’t be able to see data they are not supposed to see. If you deal with a public application or API without authorization features, you should consider using a random key for data access.