Preventing delete where 1 using typeof in node-mysql

Question

I am using node-mysql.

I have noticed that when I pass in 1 to a delete query, my whole table gets dropped. This makes sense with my syntax, however I want to protect against accidental drops.

Right now, I'm prevent this by making sure you pass in a object to the query using typeof. Is this reliable?

exports.destroy = function (sel, next) {
  if (typeof sel !== 'object') {
    //it defaults to id if you don't sepcify and object.
    sel = {'id' : sel};
  }
  db.query('DELETE FROM users WHERE ?', sel, next);
};

Answer 1

Firstly, it's not table being dropped, but all rows being deleted. Secondly, use “prepared statements”.

Following is somewhat bloated, but the main idea is using function similar to removeQuery. If you need condition different from equality, you can pass it to modified version as a string like post_rating < ? (you and not user, resulting sql should be someting like DELETE FROM posts WHERE post_rating < ?;).

Though, validation, schemas and similar stuff should go to ORM of some kind.

var util = require('util');

var db = {
  query: function (sql, params, callback) {
    console.log('executing sql `%s` with params `%j`', sql, params);
    callback();
  }
};

function removeQuery(db, table, whereField, type) {
  var sql = util.format('DELETE FROM %s WHERE %s = ?;', table, whereField);
  var fn  = db.query.bind(db, sql);

  return function (value, callback) {
    callback = callback || function () {};
    if (typeof value != type) {
      var message = util.format('Invalid type of `%s` field: should be `%s`; got `%s`',
                                whereField, type, typeof value);
      var err = new Error(message);
      console.error(err);
      return process.nextTick(callback.bind(null, err));
    }

    fn([value], callback);
  };
}

exports.removeUserByUsername = removeQuery(db, 'users', 'username', 'string');
exports.removePostByUserId   = removeQuery(db, 'posts', 'user_id', 'number');

[42, 'tony', undefined].forEach(function (val) {
  exports.removeUserByUsername(val);
  exports.removePostByUserId(val);
});