mssql - npm Package Compare versions

Comparing version 1.0.1 to 1.0.2

CHANGELOG.txt

@@ -0,1 +1,6 @@
+v1.0.2 (2014-07-04)
+-------------------
+[new] Added support for input and output parameters in batch method.
+[new] Added checks for SQL injection in parameter names
+
 v1.0.1 (2014-07-04)
@@ -2,0 +7,0 @@ -------------------

lib/datatypes.js

 // Generated by CoffeeScript 1.7.1
 (function() {
-  var TYPES, key, value, _fn;
+  var TYPES, key, value, zero, _fn;
 
@@ -198,3 +198,3 @@ TYPES = {
   module.exports.declare = function(type, options) {
-    var _ref, _ref1, _ref2, _ref3, _ref4;
+    var _ref, _ref1, _ref2, _ref3, _ref4, _ref5;
     switch (type) {
@@ -205,13 +205,15 @@ case TYPES.VarChar:
         return "" + type.declaration + " (" + (options.length > 8000 ? 'MAX' : (_ref = options.length) != null ? _ref : 'MAX') + ")";
+      case TYPES.NVarChar:
+        return "" + type.declaration + " (" + (options.length > 4000 ? 'MAX' : (_ref1 = options.length) != null ? _ref1 : 'MAX') + ")";
       case TYPES.Char:
       case TYPES.NChar:
       case TYPES.Binary:
-        return "" + type.declaration + " (" + ((_ref1 = options.length) != null ? _ref1 : 1) + ")";
+        return "" + type.declaration + " (" + ((_ref2 = options.length) != null ? _ref2 : 1) + ")";
       case TYPES.Decimal:
       case TYPES.Numeric:
-        return "" + type.declaration + " (" + ((_ref2 = type.precision) != null ? _ref2 : 18) + ", " + ((_ref3 = options.scale) != null ? _ref3 : 0) + ")";
+        return "" + type.declaration + " (" + ((_ref3 = type.precision) != null ? _ref3 : 18) + ", " + ((_ref4 = options.scale) != null ? _ref4 : 0) + ")";
       case TYPES.Time:
       case TYPES.DateTime2:
       case TYPES.DateTimeOffset:
-        return "" + type.declaration + " (" + ((_ref4 = options.scale) != null ? _ref4 : 7) + ")";
+        return "" + type.declaration + " (" + ((_ref5 = options.scale) != null ? _ref5 : 7) + ")";
       default:
@@ -222,2 +224,56 @@ return type.declaration;
 
+  module.exports.cast = function(value, type, options) {
+    var ns, scale, _ref;
+    if (value == null) {
+      return null;
+    }
+    switch (typeof value) {
+      case 'string':
+        return "N'" + (value.replace(/'/g, '\'\'')) + "'";
+      case 'number':
+        return value;
+      case 'boolean':
+        if (value) {
+          return 1;
+        } else {
+          return 0;
+        }
+      case 'object':
+        if (value instanceof Date) {
+          ns = value.getUTCMilliseconds() / 1000;
+          if (value.nanosecondDelta != null) {
+            ns += value.nanosecondDelta;
+          }
+          scale = (_ref = options.scale) != null ? _ref : 7;
+          if (scale > 0) {
+            ns = String(ns).substr(1, scale + 1);
+          } else {
+            ns = "";
+          }
+          return "N'" + (value.getUTCFullYear()) + "-" + (zero(value.getUTCMonth() + 1)) + "-" + (zero(value.getUTCDate())) + " " + (zero(value.getUTCHours())) + ":" + (zero(value.getUTCMinutes())) + ":" + (zero(value.getUTCSeconds())) + ns + "'";
+        } else if (Buffer.isBuffer(value)) {
+          return "0x" + (value.toString('hex'));
+        } else {
+          return null;
+        }
+        break;
+      default:
+        return null;
+    }
+  };
+
+  zero = function(value, length) {
+    var i, _i, _ref;
+    if (length == null) {
+      length = 2;
+    }
+    value = String(value);
+    if (value.length < length) {
+      for (i = _i = 1, _ref = length - value.length; 1 <= _ref ? _i <= _ref : _i >= _ref; i = 1 <= _ref ? ++_i : --_i) {
+        value = "0" + value;
+      }
+    }
+    return value;
+  };
+
 }).call(this);

lib/main.js

@@ -418,2 +418,5 @@ // Generated by CoffeeScript 1.7.1
     PreparedStatement.prototype.input = function(name, type) {
+      if (/(--| |\/\*|\*\/|')/.test(name)) {
+        throw new PreparedStatementError("SQL injection warning for param '" + name + "'", 'EINJECT');
+      }
       if (arguments.length < 2) {
@@ -452,2 +455,5 @@ throw new PreparedStatementError("Invalid number of arguments. 2 arguments expected.", 'EARGS');
     PreparedStatement.prototype.output = function(name, type) {
+      if (/(--| |\/\*|\*\/|')/.test(name)) {
+        throw new PreparedStatementError("SQL injection warning for param '" + name + "'", 'EINJECT');
+      }
       if (arguments.length < 2) {
@@ -990,2 +996,5 @@ throw new PreparedStatementError("Invalid number of arguments. 2 arguments expected.", 'EARGS');
     Request.prototype.input = function(name, type, value) {
+      if (/(--| |\/\*|\*\/|')/.test(name)) {
+        throw new RequestError("SQL injection warning for param '" + name + "'", 'EINJECT');
+      }
       if (arguments.length === 1) {
@@ -1041,2 +1050,5 @@ throw new RequestError("Invalid number of arguments. At least 2 arguments expected.", 'EARGS');
       }
+      if (/(--| |\/\*|\*\/|')/.test(name)) {
+        throw new RequestError("SQL injection warning for param '" + name + "'", 'EINJECT');
+      }
       if (type === TYPES.Text || type === TYPES.NText || type === TYPES.Image) {
@@ -1043,0 +1055,0 @@ throw new RequestError("Deprecated types (Text, NText, Image) are not supported as OUTPUT parameters.", 'EDEPRECATED');

lib/tedious.js

 // Generated by CoffeeScript 1.7.1
 (function() {
-  var DECLARATIONS, Pool, TYPES, Table, UDT, createColumns, getMssqlType, getTediousType, parameterCorrection, tds, util, valueCorrection,
+  var DECLARATIONS, Pool, TYPES, Table, UDT, cast, createColumns, declare, getMssqlType, getTediousType, parameterCorrection, tds, util, valueCorrection, _ref,
     __hasProp = {}.hasOwnProperty,
@@ -13,3 +13,3 @@ __extends = function(child, parent) { for (var key in parent) { if (__hasProp.call(parent, key)) child[key] = parent[key]; } function ctor() { this.constructor = child; } ctor.prototype = parent.prototype; child.prototype = new ctor(); child.__super__ = parent.prototype; return child; };
 
-  TYPES = require('./datatypes').TYPES;
+  _ref = require('./datatypes'), TYPES = _ref.TYPES, declare = _ref.declare, cast = _ref.cast;
 
@@ -228,3 +228,3 @@ DECLARATIONS = require('./datatypes').DECLARATIONS;
   parameterCorrection = function(value) {
-    var col, tvp, _i, _len, _ref;
+    var col, tvp, _i, _len, _ref1;
     if (value instanceof Table) {
@@ -235,5 +235,5 @@ tvp = {
       };
-      _ref = value.columns;
-      for (_i = 0, _len = _ref.length; _i < _len; _i++) {
-        col = _ref[_i];
+      _ref1 = value.columns;
+      for (_i = 0, _len = _ref1.length; _i < _len; _i++) {
+        col = _ref1[_i];
         tvp.columns.push({
@@ -270,3 +270,3 @@ name: col.name,
       TediousConnection.prototype.connect = function(config, callback) {
-        var cfg, cfg_pool, key, value, _base, _base1, _base2, _base3, _base4, _base5, _ref, _ref1, _ref2, _ref3;
+        var cfg, cfg_pool, key, value, _base, _base1, _base2, _base3, _base4, _base5, _ref1, _ref2, _ref3, _ref4;
         cfg = {
@@ -285,6 +285,6 @@ userName: config.user,
         if ((_base2 = cfg.options).connectTimeout == null) {
-          _base2.connectTimeout = (_ref = (_ref1 = config.connectionTimeout) != null ? _ref1 : config.timeout) != null ? _ref : 15000;
+          _base2.connectTimeout = (_ref1 = (_ref2 = config.connectionTimeout) != null ? _ref2 : config.timeout) != null ? _ref1 : 15000;
         }
         if ((_base3 = cfg.options).requestTimeout == null) {
-          _base3.requestTimeout = (_ref2 = config.requestTimeout) != null ? _ref2 : 15000;
+          _base3.requestTimeout = (_ref3 = config.requestTimeout) != null ? _ref3 : 15000;
         }
@@ -344,5 +344,5 @@ if ((_base4 = cfg.options).tdsVersion == null) {
         if (config.pool) {
-          _ref3 = config.pool;
-          for (key in _ref3) {
-            value = _ref3[key];
+          _ref4 = config.pool;
+          for (key in _ref4) {
+            value = _ref4[key];
             cfg_pool[key] = value;
@@ -456,3 +456,3 @@ }
       TediousRequest.prototype.query = function(command, callback) {
-        var columns, errors, handleError, recordset, recordsets, started;
+        var batchHasOutput, batchLastRow, columns, errors, handleError, recordset, recordsets, started;
         columns = {};
@@ -463,2 +463,4 @@ recordset = [];
         errors = [];
+        batchLastRow = null;
+        batchHasOutput = false;
         handleError = (function(_this) {
@@ -476,3 +478,3 @@ return function(err) {
           return function(err, connection) {
-            var doneHandler, name, param, req, _ref, _ref1;
+            var assigns, declarations, doneHandler, name, param, req, selects, _ref1;
             if (!err) {
@@ -497,4 +499,4 @@ if (_this.verbose) {
               req = new tds.Request(command, function(err) {
-                var elapsed, error, _i, _len, _ref;
-                if (err && err.message !== ((_ref = errors[errors.length - 1]) != null ? _ref.message : void 0)) {
+                var elapsed, error, name, value, _i, _len, _ref1;
+                if (err && err.message !== ((_ref1 = errors[errors.length - 1]) != null ? _ref1.message : void 0)) {
                   err = RequestError(err);
@@ -506,2 +508,21 @@ if (_this.stream) {
                 }
+                if (batchHasOutput) {
+                  if (!_this.stream) {
+                    batchLastRow = recordsets.pop()[0];
+                  }
+                  for (name in batchLastRow) {
+                    value = batchLastRow[name];
+                    if (!(name !== '___return___')) {
+                      continue;
+                    }
+                    if (_this.verbose) {
+                      if (value === tds.TYPES.Null) {
+                        _this._log("   output: @" + name + ", null");
+                      } else {
+                        _this._log("   output: @" + name + ", " + (_this.parameters[name].type.declaration.toLowerCase()) + ", " + value);
+                      }
+                    }
+                    _this.parameters[name].value = value === tds.TYPES.Null ? null : value;
+                  }
+                }
                 if (_this.verbose) {
@@ -539,3 +560,9 @@ if (errors.length) {
                 if (_this.stream) {
-                  return _this.emit('recordset', columns);
+                  if (_this._batch) {
+                    if (columns["___return___"] == null) {
+                      return _this.emit('recordset', columns);
+                    }
+                  } else {
+                    return _this.emit('recordset', columns);
+                  }
                 }
@@ -600,3 +627,11 @@ });
                 if (_this.stream) {
-                  return _this.emit('row', row);
+                  if (_this._batch) {
+                    if (row["___return___"] != null) {
+                      return batchLastRow = row;
+                    } else {
+                      return _this.emit('row', row);
+                    }
+                  } else {
+                    return _this.emit('row', row);
+                  }
                 } else {
@@ -606,26 +641,57 @@ return recordset.push(row);
               });
-              if (!_this._batch) {
-                _ref = _this.parameters;
-                for (name in _ref) {
-                  param = _ref[name];
-                  if (!(param.io === 1)) {
-                    continue;
-                  }
+              if (_this._batch) {
+                if (Object.keys(_this.parameters).length) {
+                  declarations = (function() {
+                    var _ref1, _results;
+                    _ref1 = this.parameters;
+                    _results = [];
+                    for (name in _ref1) {
+                      param = _ref1[name];
+                      _results.push("@" + name + " " + (declare(param.type, param)));
+                    }
+                    return _results;
+                  }).call(_this);
+                  assigns = (function() {
+                    var _ref1, _results;
+                    _ref1 = this.parameters;
+                    _results = [];
+                    for (name in _ref1) {
+                      param = _ref1[name];
+                      _results.push("@" + name + " = " + (cast(param.value, param.type, param)));
+                    }
+                    return _results;
+                  }).call(_this);
+                  selects = (function() {
+                    var _ref1, _results;
+                    _ref1 = this.parameters;
+                    _results = [];
+                    for (name in _ref1) {
+                      param = _ref1[name];
+                      if (param.io === 2) {
+                        _results.push("@" + name + " as [" + name + "]");
+                      }
+                    }
+                    return _results;
+                  }).call(_this);
+                  batchHasOutput = selects.length >  0;
+                  req.sqlTextOrProcedure = "declare " + (declarations.join(', ')) + ";select " + (assigns.join(', ')) + ";" + req.sqlTextOrProcedure + ";" + (batchHasOutput ? 'select 1 as [___return___], ' + selects.join(', ') : '');
+                }
+              } else {
+                _ref1 = _this.parameters;
+                for (name in _ref1) {
+                  param = _ref1[name];
                   if (_this.verbose) {
                     if (param.value === tds.TYPES.Null) {
-                      _this._log("    input: @" + param.name + ", null");
+                      _this._log("   " + (param.io === 1 ? " input" : "output") + ": @" + param.name + ", null");
                     } else {
-                      _this._log("    input: @" + param.name + ", " + (param.type.declaration.toLowerCase()) + ", " + param.value);
+                      _this._log("   " + (param.io === 1 ? " input" : "output") + ": @" + param.name + ", " + (param.type.declaration.toLowerCase()) + ", " + param.value);
                     }
                   }
-                  req.addParameter(param.name, getTediousType(param.type), parameterCorrection(param.value), {
-                    length: param.length,
-                    scale: param.scale,
-                    precision: param.precision
-                  });
-                }
-                _ref1 = _this.parameters;
-                for (name in _ref1) {
-                  param = _ref1[name];
-                  if (param.io === 2) {
+                  if (param.io === 1) {
+                    req.addParameter(param.name, getTediousType(param.type), parameterCorrection(param.value), {
+                      length: param.length,
+                      scale: param.scale,
+                      precision: param.precision
+                    });
+                  } else {
                     req.addOutputParameter(param.name, getTediousType(param.type), parameterCorrection(param.value), {
@@ -678,3 +744,3 @@ length: param.length,
           return function(err, connection) {
-            var name, param, req, _ref;
+            var name, param, req, _ref1;
             if (!err) {
@@ -699,4 +765,4 @@ if (_this.verbose) {
               req = new tds.Request(procedure, function(err) {
-                var elapsed, error, _i, _len, _ref;
-                if (err && err.message !== ((_ref = errors[errors.length - 1]) != null ? _ref.message : void 0)) {
+                var elapsed, error, _i, _len, _ref1;
+                if (err && err.message !== ((_ref1 = errors[errors.length - 1]) != null ? _ref1.message : void 0)) {
                   err = RequestError(err);
@@ -808,5 +874,5 @@ if (_this.stream) {
               });
-              _ref = _this.parameters;
-              for (name in _ref) {
-                param = _ref[name];
+              _ref1 = _this.parameters;
+              for (name in _ref1) {
+                param = _ref1[name];
                 if (_this.verbose) {
@@ -813,0 +879,0 @@ if (param.value === tds.TYPES.Null) {

package.json

@@ -20,3 +20,3 @@ {
 	],
-	"version": "1.0.1",
+	"version": "1.0.2",
 	"main": "index.js",
@@ -23,0 +23,0 @@ "repository": {

README.md

@@ -442,4 +442,5 @@ # node-mssql [![Dependency Status](https://david-dm.org/patriksimek/node-mssql.png)](https://david-dm.org/patriksimek/node-mssql) [![NPM version](https://badge.fury.io/js/mssql.png)](http://badge.fury.io/js/mssql)
 
-__Errors__
+__Errors__ (synchronous)
 - EARGS (`RequestError`) - Invalid number of arguments.
+- EINJECT (`RequestError`) - SQL injection warning.
 
@@ -466,2 +467,6 @@ ---------------------------------------
 
+__Errors__ (synchronous)
+- EARGS (`RequestError`) - Invalid number of arguments.
+- EINJECT (`RequestError`) - SQL injection warning.
+
 ---------------------------------------
@@ -472,3 +477,3 @@
 
-Execute the SQL command. To execute commands like `create procedure` use [batch](#batch) instead.
+Execute the SQL command. To execute commands like `create procedure` or if you plan to work with local temporary tables, use [batch](#batch) instead.
 
@@ -520,4 +525,6 @@ __Arguments__
 
-Execute the SQL command. There is no param support, and unlike [query](#query), it doesn't use `sp_executesql`, so is not likely that SQL Server will reuse the execution plan it generates for the SQL. Use this only in special cases, for example when you need to execute commands like `create procedure` which can't be executed with [query](#query).
+Execute the SQL command. Unlike [query](#query), it doesn't use `sp_executesql`, so is not likely that SQL Server will reuse the execution plan it generates for the SQL. Use this only in special cases, for example when you need to execute commands like `create procedure` which can't be executed with [query](#query). Also you should use this if you're plan to work with local temporary tables ([more information here](http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx)).
 
+NOTE: Table-Valued Parameter (TVP) is not supported in batch.
+
 __Arguments__
@@ -532,3 +539,3 @@
 var request = new sql.Request();
-request.batch('create procedure #temporary', function(err, recordset) {
+request.batch('create procedure #temporary as select * from table', function(err, recordset) {
     // ... error checks
@@ -743,4 +750,5 @@ });
 
-__Errors__
+__Errors__ (synchronous)
 - EARGS (`PreparedStatementError`) - Invalid number of arguments.
+- EINJECT (`PreparedStatementError`) - SQL injection warning.
 
@@ -766,4 +774,5 @@ ---------------------------------------
 
-__Errors__
+__Errors__ (synchronous)
 - EARGS (`PreparedStatementError`) - Invalid number of arguments.
+- EINJECT (`PreparedStatementError`) - SQL injection warning.
 
@@ -1013,4 +1022,6 @@ ---------------------------------------
 `RequestError` | EARGS | Invalid number of arguments.
+`RequestError` | EINJECT | SQL injection warning.
 `RequestError` | ENOCONN | No connection is specified for that request.
 `PreparedStatementError` | EARGS | Invalid number of arguments.
+`PreparedStatementError` | EINJECT | SQL injection warning.
 `PreparedStatementError` | EALREADYPREPARED | Statement is already prepared.
@@ -1017,0 +1028,0 @@ `PreparedStatementError` | ENOTPREPARED | Statement is not prepared.

New alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Fixed alerts

License Policy Violation

License

This package is not allowed per your license policy. Review the package's license to ensure compliance.

Found 1 instance in 1 package

Improved metrics

Total package byte prevSize

265823

increased by3.82%

Lines of code

4048

increased by3.37%

Number of lines in readme file

1181

increased by0.94%

No dependency changes