SQL injection with PostgreSQL

[huguesdk]

Backslashes are not escaped properly in strings when using PostgreSQL, leading to possible SQL injections.

The following code drops the students table.

var Sequelize, Student, sequelize;

Sequelize = require('sequelize');

sequelize = new Sequelize('database', 'user', 'password', {
  dialect: 'postgres',
  host: 'school.example',
  logging: false,
  define: {
    timestamps: false
  }
});

Student = sequelize.define('student', {
  name: Sequelize.STRING
});

Student.create({
  name: 'Robert\\\'); DROP TABLE "students"; --'
}).then(function(result) {
  console.log('Successfully created one student:', result.get());
  return Student.findAll();
}).then(function(result) {
  return console.log('Number of students:', result.length);
})["catch"](function(error) {
  return console.error('Oh no! Here strikes the mom again:', error.message);
});

The console output is:

Successfully created one student: { id: null, name: 'Robert\\\'); DROP TABLE "students"; --' }
Oh no! Here strikes the mom again: relation "students" does not exist

Tested with PostgreSQL 8.2.

[mickhansen]

Hmm that's highly strange.
Will figure out a fix immediately

[mickhansen]

What version of sequelize?

[mickhansen]

Unable to prove attack vector in: 99d8ced

Could you tell me where i'm wrong in the unit test or ammend it so it fails in a PR?

[huguesdk]

Wow, that’s a quick reaction! Thanks!

I tested with Sequelize 2.0.5. I think the test should check that the generated query contains the value as (in SQL):

'Robert\\''); DROP TABLE "students"; --'

that is, escaped in JavaScript:

\'Robert\\\\\'\'); DROP TABLE "students"; --\'

instead of currently (in SQL):

'Robert\''); DROP TABLE "students"; --'

that is, escaped in JavaScript:

\'Robert\\\'\'); DROP TABLE "students"; --\'

[mickhansen]

@huguesdk You are welcome to ammend the test to show what you mean. Although i would think that simply testing that the query passes (and doesn't throw a table does not exist error) should be sufficient.

[mickhansen]

Even with your modifications the attack doesn't work as far as i can tell.

[huguesdk]

I just saw in http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE that the default setting for string escaping with backslashes in normal strings changed in PostgreSQL 9.1. Beginning with that version, they are disabled by default for normal strings, and work only if the string is prefixed with E. I tested this with PostgreSQL 8.2 so I think this explains it (and also why this wasn’t discovered before). I wonder how other ORMs work around this. Could the standard_conforming_strings configuration parameter (which controls this behavior) be set to true for each session?

[mickhansen]

Yes we could probably set that on connect.
Although do keep in mind that not all features in Sequelize work for Postgresql 8.2.
Our findOrCreate needs featureso nly in 9.2 and up (IIRC)

[huguesdk]

In SQLAlchemy they do this:

self._backslash_escapes = self.server_version_info < (8, 2) or \
    connection.scalar(
    "show standard_conforming_strings"
) == 'off'

They escape all backslashes or not depending on this value. The version check is because standard_conforming_strings doesn’t exist for PostgreSQL earlier than 8.2.

Even with PostgreSQL 9.2, Sequelize would be vulnerable to this if standard_conforming_strings is set to false on the server. So we should either do the same as SQLAlchemy or force standard_conforming_strings to true on connect.

[janmeier]

Fixed by settting standard_conforming_strings=on on connection. pg 8.2 may have reached its EOL in december 2011, put since this was not introduced before 9.1 it's worth it to fix this on the sequelize side.

Thanks for bringing this to our attention