0

I was given a database where the DOB column is encrypted with crypto. Assuming that the column has to stay encrypted, how would I go about getting/updating the age for each user?

Currently, I calculate the age using the code below : (userBirthday is given in YYMMDD format and I calcualte the age before encrypting DOB)

function get_age(time) {
    var year = time.slice(0,4);
    var month = time.slice(4,6);
    var day = time.slice(6);
    //month is 0-indexed
    var years_elapsed = (new Date(new Date().getTime() - (new Date().getTimezoneOffset() * 60000)) - new Date(year, month - 1, day))/(MILLISECONDS_IN_A_YEAR);
    return Math.floor(years_elapsed); 
}

let birthdayFull = req.body.userBirthday;
let birthYr = birthdayFull.slice(0,2);
let currentYear = new Date().getFullYear().toString().slice(2);

if(birthYr < currentYear) {
    birthdayFull = '20' + birthdayFull;
} else {
    birthdayFull = '19' + birthdayFull;
}
let age = get_age(birthdayFull);

Depending on the year, I either add '19' or '20' to get the date into a YYYYMMDD format. However, if I run the get_age function, I get an inaccurate answer. For example, if DOB is '19920215', the return value is 29, even though the user is still 28.

My question is 1. How would I calculate the user age accurately (getting the actual age, aka adding a year only if the actual birthday has passed) and 2. if I want to check for age updates everyday at midnight (to check if someone's age changed), how would I implement it if the DOB column is encrypted?

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Michael Lee
  • 89
  • 1
  • 9
  • 2
    What threat are you protecting against? If I could a) observe an age column and b) on two successive days see it change, I pretty well know the DOB. So aren't you defeating the point of encrypting this value if your other requirements are met? – Damien_The_Unbeliever Feb 09 '21 at 11:33
  • How do you encrypt the DOB? – RiggsFolly Feb 09 '21 at 12:21
  • It's gonna take a year, but I reckon I can hack this database. – Strawberry Feb 09 '21 at 12:32
  • I don't see any SQL in this question. What programming language is that? –  Feb 09 '21 at 13:34
  • @Damien_The_Unbeliever I understand that by having a separate age column, the DOB would be eventually revealed if someone like Strawberry wanted to hack the db. I assume that the encryption is there so that the company is not solely responsible when there is a data breach (kind of as a formality). – Michael Lee Feb 09 '21 at 23:51
  • @RiggsFolly I used the crypto package to implement asymmetric encryption. `const encryptedNum = crypto.publicEncrypt(...);` – Michael Lee Feb 09 '21 at 23:54

1 Answers1

0

Answers to your two questions:

  1. Subtract DoB from the current date, convert to years and round down
  2. Holding data values that change based on a user's perspective (i.e. depending on what day they look at it) in a database is a really bad practice. You should calculate this dynamically when the user queries the data

Update Following Comment

  1. Don't encrypt the DoB in the table. Remove user access from the table if you don't want people to be able to see the DoB

  2. Create a View over the table that excludes or masks the DoB column. Add calculated columns to the view that display the age-related information that you want

NickW
  • 8,430
  • 2
  • 6
  • 19
  • There is a category where I could sort users by age group (19-24, 25-29, etc.). If a user were to turn 25 today, then the age column should be updated so that the user would fall under the 25-29 age group(this is why I want to try to check for updates everyday). Could you elaborate on 'calculate dynamically when the user queries the data'? Given that the DOB column is encrypted, I thought it would be unnecessary to decrypt the DOB, loop through the data, and filter results. – Michael Lee Feb 10 '21 at 00:06