sequelize: Invalid logging of single INSERT and UPDATE SQL queries

What are you doing?

In Sequelize 5.0.0-beta.13:

const connection = new Sequelize(database, username, password, {
    dialect,
    host,
    define: {
        freezeTableName: true,
        underscored: false,
        createdAt: 'createdAt',
        updatedAt: 'updatedAt'
    },
    logging: message => {
        console.log(message);
    }
});

const Task = connection.define('task', {
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    createdAt: {
        field: 'created_at',
        type: Sequelize.DATE,
        allowNull: false
    },
    updatedAt: {
        field: 'updated_at',
        type: Sequelize.DATE,
        allowNull: false
    },
    name: {
        type: Sequelize.TEXT,
        allowNull: false
    },
    status: {
        type: Sequelize.INTEGER,
        allowNull: false,
        defaultValue: 0
    }
});

(async() => {
    ...
    await Task.create({ name: 'First task', status: 1 }, { transaction });
    await Task.update({ status: 2 }, { where: { status: 1 }, transaction });
})();

What do you expect to happen?

Output valid SQL queries with inserted binded parameters’ values in console:

INSERT INTO "task" ("id","created_at","updated_at","name","status") VALUES (DEFAULT,'2018-10-23 13:58:05.380 +00:00','2018-10-23 13:58:05.380 +00:00','First task',1) RETURNING *
UPDATE "task" SET "status"=2,"updated_at"='2018-10-23 13:58:05.398 +00:00' WHERE "status" = 1

What is actually happening?

I get SQL queries with tokens starting with $ instead of parameters’ values:

INSERT INTO "task" ("id","created_at","updated_at","name","status") VALUES (DEFAULT,$1,$2,$3,$4) RETURNING *;
UPDATE "task" SET "status"=$1,"updated_at"=$2 WHERE "status" = $3

Note that bulkCreate method works fine.

Dialect: postgres Dialect version: 7.5.0 Database version: 9.5.14 Sequelize version: 5.0.0-beta.13 Tested with latest release: Yes (latest beta version 5.0.0-beta.13)

About this issue

Original URL
State: closed
Created 6 years ago
Reactions: 2
Comments: 18 (4 by maintainers)

Most upvoted comments

Is there already some kind of solution for this? Today we were forced to update to v5 due to the critical SQL injection vulnerability in Sequelize (https://snyk.io/vuln/SNYK-JS-SEQUELIZE-450221) but now we are lacking the ability to see the insert logs.

eggerand13 on Jun 25, 2019

Works with "sequelize": "^5.21.2" @ezze

const sequelize = new Sequelize(
  process.env.DB_DATABASE_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST,
    port: process.env.DB_PORT,
    dialect: 'mysql',
    logQueryParameters: true,
    logging: statement => { console.log(statement); }
  }
);

However, I agree with you, for debugging, I have to copy params into placeholders before I can execute them at DB.

terrynguyen255 on Feb 27, 2020

Set logQueryParameters: true while creating a new Sequelize class instance.

For eg,

const sequelize = new Sequelize('database', 'username', 'password', {
  host: 'localhost',
  dialect: /* one of 'mysql' | 'mariadb' | 'postgres' | 'mssql' */,
  logQueryParameters: true,
});

hiteshgarg14 on Feb 6, 2020

As a temporary solution, you can get bindings from the logger args.

(sql, ...args) => {
  let instance = args.pop();
  let benchmark = args.pop();

  console.log(sql, benchmark, instance.bind);
}

PS. @sushantdhiman It would be nice to pass a logger args as one object {sql, benchmark, bind, instance}

sheldhur on Mar 23, 2019

Sequelize v5 is using bound parameters for INSERT and UPDATE statements. I guess we can append data in query logs

sushantdhiman on Oct 27, 2018

Ok, setting logQueryParameters to true doesn’t work for me. Result is the same.

const connection = new Sequelize(database, username, password, {
    dialect,
    host,
    define: {
        freezeTableName: true,
        underscored: false,
        createdAt: 'createdAt',
        updatedAt: 'updatedAt'
    },
    logQueryParameters: true,
    logging: message => {
        console.log(message);
    }
});

Anyway, if it would worked, there are still questions for me.

Why do we need logQueryParameters at all and why do we need to log placeholders by default? v4 was working without it.
Current default behavior is useless. Even if we consider that logging placeholders is what we need it doesn’t work as expected. Placeholders are logged for single inserts and updates while values are logged for bulk inserts and selects.

ezze on Feb 26, 2020

Sorry, any updates?

terrynguyen255 on Aug 20, 2019