loading...
Cover image for Handling sensitive fields with sequelize.js

Handling sensitive fields with sequelize.js

a0viedo profile image Alejandro Oviedo ・3 min read

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

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

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:

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

Posted on by:

a0viedo profile

Alejandro Oviedo

@a0viedo

Alejandro has been involved with the Node.js project and its community for some time, helping to grow its community in latam. He enjoys speaking on web security, Node.js performance and serverless.

Discussion

markdown guide