Api Key Exposure in Sails with Mysql

When a Sails application uses MySQL as its primary datastore, API key exposure can occur through misconfigured models, query construction, or logging practices. Sails models that map directly to MySQL tables may inadvertently expose keys if sensitive columns are included in responses or if associations propagate sensitive fields. Because Sails is convention-driven, developers might rely on automatic model-to-table mapping without explicitly defining which attributes are safe for serialization, leading to keys being returned in API responses.

MySQL does not inherently prevent exposure of key material; it stores and returns data as defined in queries. In Sails, if a model includes fields such as api_key, secret, or token and those fields are not excluded during population or JSON serialization, they can be returned to clients. Additionally, raw MySQL queries built with the mysql npm package or Waterline’s native query methods can leak keys if result sets are passed directly to views or APIs without filtering. Misconfigured blueprints or custom controllers that return full model instances also increase the risk.

Logging and debugging features in Sails can compound the issue. If query results or model instances containing API keys are written to logs—especially in development mode—keys may be persisted in log files or console output. MySQL’s general query log or slow query log, if enabled and improperly secured, might also capture key values in plain text. When combined with insufficient access controls on the MySQL server, an attacker who gains read access to logs or monitoring interfaces could recover exposed keys.

Another vector involves JOINs and associations. In Sails, using associations such as hasMany or belongsTo can cause nested data retrieval that pulls in sensitive MySQL columns. If a parent record’s API key is included in a related child record’s response, the key may be exposed through what appears to be an unrelated endpoint. This is particularly risky when using Waterline’s automatic population without explicitly excluding sensitive attributes across related models.

Finally, differences between development and production configurations can lead to exposure. A Sails app might use a debug-friendly MySQL configuration locally that includes detailed error messages containing query results or schema information. If those configurations are accidentally promoted, API keys embedded in error messages or stack traces can be revealed. Properly scoping MySQL user permissions and ensuring Sails environment-specific settings exclude sensitive data are essential to mitigate this risk.

To prevent API key exposure in a Sails application using MySQL, explicitly control which attributes are serialized and ensure sensitive fields are never included in responses. Use the toJSON method in models to omit keys, and sanitize query results before transmission. Below are targeted code examples that demonstrate secure practices.

1. Exclude sensitive fields in model serialization

Define a toJSON method in your Sails model to remove sensitive attributes such as api_key or secret from serialized output.

// api/models/User.js
module.exports = {
  attributes: {
    username: { type: 'string', required: true },
    email: { type: 'string', required: true },
    api_key: { type: 'string', required: true },
    secret: { type: 'string', required: true },

    toJSON: function () {
      const obj = this.toObject();
      delete obj.api_key;
      delete obj.secret;
      return obj;
    },

    toJSONForAdmin: function () {
      const obj = this.toObject();
      // Optionally expose keys only to admin contexts
      return obj;
    }
  }
};

2. Use safe query methods with explicit field selection

When using the mysql package directly, select only necessary columns and avoid SELECT *. This prevents unintended exposure of key fields stored in the database.

// api/services/UserService.js
const mysql = require('mysql');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'app_user',
  password: 'secure_password',
  database: 'sails_db'
});

module.exports.getUserPublicData = function (userId, cb) {
  const query = 'SELECT id, username, email FROM users WHERE id = ?';
  connection.query(query, [userId], function (err, results) {
    if (err) return cb(err);
    // Keys are not selected, so they cannot be exposed
    return cb(null, results[0]);
  });
};

3. Restrict MySQL user permissions

Ensure the MySQL user used by Sails does not have unnecessary privileges. For application read operations, grant only SELECT on required columns and tables, avoiding broad access that could facilitate key extraction.

-- Example MySQL user setup
CREATE USER 'sails_app'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT (id, username, email, created_at, updated_at) ON sails_db.users TO 'sails_app'@'localhost';
FLUSH PRIVILEGES;

4. Avoid logging sensitive query results

Disable or filter sensitive data in Sails logs. Override the default logger behavior to prevent keys from being written to console or files.

// config/log.js
module.exports.log = {
  level: 'info',
  stringify: false,
  // Custom transport to filter sensitive fields
  custom: function (level, msg) {
    if (typeof msg === 'object' && msg.api_key) {
      // Redact API keys before logging
      msg.api_key = '[REDACTED]';
    }
    console.log(level, msg);
  }
};

5. Validate and sanitize input to prevent injection-based leaks

Use input validation to ensure queries do not inadvertently expose data. Waterline validation can prevent malformed queries that might return unexpected key-containing rows.

// api/models/User.js
module.exports = {
  attributes: {
    username: { type: 'string', required: true, maxLength: 50 },
    email: { type: 'email', required: true },
    api_key: { type: 'string', minLength: 32, allowNull: false },

    // Ensure only valid updates
    toObject: function () {
      return _.pick(this, ['id', 'username', 'email']);
    }
  }
};