Handling sensitive fields with sequelize.js

I've started working with sequelize again for the last few months and at first I struggled with a few things like TypeScript support and associations (so many different ways to configure them!) among other features. One of the things that I encountered recently was keeping specific fields (e.g passwords) out of default queries.
I want to walk you through my first solution to have a rationale, elaborate on hooks and then move into why it didn't worked as expected.

Setting up the models

We will go with a good ol' fashioned e-commerce for simplicity.

UML diagram of the models describing their associations

The UML diagram above tries to describe the following:

• a company can have many products
• a product can have many purchase orders
• a user can have many purchase orders
• a product must have one company
• a purchase order must have one user
• a purchase order can have many products

We will define the models altogether but keep in mind that in a real-word scenario I would suggest creating a different file for each model:

const User = sequelize.define('User', {
  firstName: types.string,
  lastName: types.string,
  email: types.string,
  password: types.string
});

const Product = sequelize.define('Product', {
  description: types.string,
  price: types.string,
});

const Company = sequelize.define('Company', {
  name: types.string,
  url: types.string,
});

const PurchaseOrder = sequelize.define('PurchaseOrder', {
  totalCost: {
    type: DataTypes.DOUBLE,
    allowNull: false
  },
});

// one-to-many
Company.hasMany(Product, {
  as: 'products',
  foreignKey: {
    name: 'companyId',
    allowNull: false
  }
});
Product.belongsTo(Company, {
  as: 'company',
  foreignKey: {
    name: 'companyId',
    allowNull: false
  }
});

// one-to-many
User.hasMany(PurchaseOrder, {
  as: 'purchaseOrders',
  foreignKey: {
    name: 'userId',
    allowNull: false
  }
});
PurchaseOrder.belongsTo(User, {
  as: 'user',
  foreignKey: {
    name: 'userId',
    allowNull: false
  }
});

// many-to-many
Product.belongsToMany(PurchaseOrder, {
  through: 'ProductPurchaseOrder',
  as: 'purchaseOrders'
});
PurchaseOrder.belongsToMany(Product, {
  through: 'ProductPurchaseOrder',
  as: 'products'
});

The problem

I've seen in many parts of the codebase that I'm working currently something similar to:

const user = User.findByPk(1);
console.log('User retrieved:', user.toJSON())

Why is that a problem? Just use the exclude property

One might be keen to suggest. Excluding the property for that specific line of code could work but it wouldn't be advocating for security by default. If, by human error, someone forgets to exclude the password for the next query it will still be a problem.

(Not) A Solution

While searching for different alternatives I found myself looking at the list of hooks that Sequelize has available: afterCreate, afterFind, and many others. I was already using those for something unrelated to the problem in hand and thought it could be a good approach too. After a few tests I added this to my model definition:

const User = sequelize.define('User', {
  /* props */
}, {
  hooks: {
    afterFind: result => {
      if(Array.isArray(result)) {
        for(const elem of result) {
          delete elem.dataValues.password;
        }
      } else {
        delete result.dataValues.password;
      }

      return result;
    }
  },
});

Pushed the code my model repository, updated my services and voilá, I was dealing with better query results by default.

A better solution

After starting to work on a different feature for another model I saw a response from one of my APIs:

"po":{
  "user": {
    "password": "howdy"
  }

Needless to say I grabbed my face for a slow but piercing facepalm. It turns out afterFind hooks will not run through queries with associations linking to that model.

Visual explanation of when afterFind hooks are executed

Finally, I found what I think is the best way for dealing with these type of properties: scopes. It's a powerful feature and allowed me to define the following:

const User = sequelize.define('User', {
  /* props */
}, {
  defaultScope: {
    attributes: {
      exclude: ['password']
    }
  }
});

All the associations of the User model will be able to include users without including the password field by default.

Feel free to play with the result on the Replit included here or checkout the repository on Github:

a0viedo / sequelize-sqlite3-example

Note to the reader: do not store your passwords in plain text. The examples here are especially crafted to increase the OMG! reaction.

Comments

[mikemcg52]

Good article but I've run into a problem using this approach. I need to retrieve the user record, including password, to validate login but the afterFind always removes it. I can't find any documentation on the options block and I wonder if there is a way to execute a query that does return the password value in a specific situation. Otherwise the option seems to be to drop the afterFind approach and create methods that return a "normal" data set in most situations, without password, and returns the full data set, including password, for login validation.

[Zain Zafar]

that's how I did it

 defaultScope: {
        attributes: { exclude: ['password'] },
      },
      scopes: {
        withPassword: {
          attributes: {},
        },
      },

if I have to access the password

User.scope('withPassword').findOne({ where: { email } });

ref: sequelize.org/docs/v6/other-topics...

[utkarshjosh]

thats exactly the problem why I am wandering and landed to this article. I am planning to use two scopes

[davidroberthoare]

Thank you for this - - it worked for me when using FeathrsJS. However, an important note is that the {defaultScopes...} definition needed to appear immediately after the field definitions. When I tried putting it at the end of the model definition statements it did nothing, but immediately after the fields, it worked fine. Cheers!