Your leaking thatched hut during the restoration of a pre-Enlightenment state.

 

Hello, my name is Judas Gutenberg and this is my blaag (pronounced as you would the vomit noise "hyroop-bleuach").

links

decay & ruin
Biosphere II
Chernobyl
dead malls
Detroit
Irving housing

got that wrong
Paleofuture.com

appropriate tech
Arduino μcontrollers
Backwoods Home
Fractal antenna

fun social media stuff


Like asecular.com
(nobody does!)

Like my brownhouse:
   somewhat-pathological predisposition towards self-reliance
Wednesday, January 9 2019
This Electron app I am building uses Node.js on the backend to do all the usual backend things, which (in this case) mostly entails communicating with databases. The databases are Microsoft SQL Server databases, and so the Node.js module I use to communicate with them is called mssql. It works reasonably well, but there is an infuriating issue with it that has been causing me to write potentially less secure code. The module, you see, has terrible for support for parameterized SQL. Parameterization makes it easier to write secure backends because using it makes it impossible for users to sneak malicious SQL commands into their data (so-called "SQL injection"). The data being merged into parameterized SQL is systematically scrubbed of characters that could switch the context of the data from data to command. The problem with the mssql module is that there's no easy way to bulk-merge an arbitrary amount of data with an arbitrary SQL expression. Instead, one is forced add a method call of (".input(...).input(...)") for each parameter. This means one has to explicitly refer to every merged parameter with every SQL call. Here's an absurd example: 

  var sql = "INSERT INTO BasePenaltyRateBunker(GiraffeCodeKey, RateTangerineCodeKey, RatePtr, PenaltyYear, CollectionType, PenaltyRate, Description, ";
  sql+="                        AlfalfaAmount, PctIncDec, ChargeCode, PenaltyPrintSortCode, PriorPenaltyRate, PenaltyRatePercentChange, ";
  sql+="                        DistributionMethod, Collection_ID, PenniesDistributionMethod, PenaltyGroup, PenaltySeqNo, AlfalfaOrder, ";
  sql+="                        GiraffePenalty) ";
  sql+="          VALUES (@giraffe_code, @tangerine, @pointer, @year, @collection_type, @rate, @description, @alfalfa, ";
  sql+="          @percent_increase, @charge_code, @penalty_print_sort_code, @prior_penalty_rate, ";
  sql+="          @penalty_rate_percent_change, @distribution_method, @collection_id, @manure_distribution_method, ";
  sql+="          @penalty_group, @penalty_seq_no, @alfalfa_order, @giraffe_penalty) ";
  //var configObj = {"server":globals.globals.databaseConfig.server,"user":globals.globals.databaseConfig.user, "password":globals.globals.databaseConfig.password, "database":globals.globals.databaseConfig.database};
  mssql.close();
  var sqlAction = function() {
    var dbRequest = new mssql.Request()
    .input('giraffe_code', mssql.VarChar(250), rate_data.giraffe_code)
    .input('tangerine', mssql.VarChar(250), rate_data.tangerine)
    .input('pointer', mssql.VarChar(250), rate_data.pointer)
    .input('year', mssql.VarChar(250), rate_data.year)
    .input('collection_type', mssql.VarChar(250), rate_data.collection_type)
    .input('rate', mssql.VarChar(250), rate_data.rate)
    .input('description', mssql.VarChar(250), rate_data.description)
    .input('alfalfa', mssql.VarChar(250), rate_data.alfalfa)
    .input('percent_increase', mssql.VarChar(250), rate_data.percent_increase)
    .input('charge_code', mssql.VarChar(250), rate_data.charge_code)
    .input('penalty_print_sort_code', mssql.VarChar(250), rate_data.penalty_print_sort_code)
    .input('prior_penalty_rate', mssql.VarChar(50), rate_data.prior_penalty_rate)
    .input('penalty_rate_percent_change', mssql.VarChar(250), rate_data.penalty_rate_percent_change)
    .input('distribution_method', mssql.VarChar(250), rate_data.distribution_method)
    .input('collection_id', mssql.VarChar(250), rate_data.collection_id)
    .input('manure_distribution_method', mssql.VarChar(250), rate_data.manure_distribution_method)
    .input('penalty_group', mssql.VarChar(250), rate_data.penalty_group)
    .input('penalty_seq_no', mssql.VarChar(250), rate_data.penalty_seq_no)
    .input('alfalfa_order', mssql.VarChar(250), rate_data.alfalfa_order)
    .input('giraffe_penalty', mssql.VarChar(250), rate_data.giraffe_penalty)

Unlike in other systems I've used, one can't just tell the SQL to look into some dictionary object and find the values there (replacing all those .input lines with a simple specification of the dictionary).
Beyond such clumsiness is a common problem to parameterized SQL systems generally: that it's usually impossible to produce the string of SQL with all the parameters replaced with actual data. Sometimes it's really important to get that SQL so testing can be done with it.
Yesterday and today, I spent more time than I'd care to admit trying to get the mssql parameterization system to work for me. Eventually I gave up and tried another system, but it was even worse, since it seemed to work by violating the usual string-delimiting norms of Javascript. Ultimately I gave up and created my own SQL parameterization system. It consisted of about a dozen lines of code and took less than ten minutes to write.: 

  paramSubstitute: function(stringIn, params, donotQuoteNumerics = true) {
    for(var key in params) {
      var value = params[key];
      if(value == null) {
        stringIn = stringIn.replace(new RegExp('@' + key, 'g'), "null");
      } else if(donotQuoteNumerics && parseFloat(value) == value) {
        stringIn = stringIn.replace(new RegExp('@' + key, 'g'), value);
      } else {
	  	//MSSQL escapes single quotes with a single quote (!!)
        stringIn = stringIn.replace(new RegExp('@' + key, 'g'), "'" + value.toString().replace(/'/g,"''")  + "'"); 
      }
    }
    return stringIn
  },
Notice that it returns a SQL string, one I can look at and test in a query analyzer. Does your SQL parameterization system do that?

This did nothing but further calcify my somewhat-pathological predisposition towards self-reliance in all things.

Tonight when Ramona and I got off work, I drove us to the Home Depot, mostly to get more copper fittings for the chandelier I will be installing in the kitchen. Also, since the gas stove top was now supposedly working back home, I went to ShopRite to buy some foods that require a stove top to prepare (particularly mushrooms).
Back at the house, the stove top (and new butcher block) looked a little too nice to be in a kitchen in house I would ever own. Unfortunately, the plumber had hooked up the gas in such a way that the copper pipe was preventing the installation of the middle drawer in in the set of drawers beneath it. Later, after Gretchen came home (and took over the cooking operations I'd begun), I tried to bend the copper gas pipe out away from where it was interfering with the back of the middle drawer using a pipe vise to get the necessary leverage. But mostly all I managed to do was put a few dents in the pipe. Clearly something else will have to be done. I think redoing the gas connection is probably easier than customizing the middle drawer.
Another thing that had happened today was that Colin had installed the floating shelves, meaning they could now be used to store things.
To celebrate having a mostly-functional kitchen again, I poured Gretchen and myself both shots of sherry after we'd finished eating a meal of pasta with cream sauce, broccoli, mushrooms, and onions.


For linking purposes this article's URL is:
http://asecular.com/blog.php?190109

feedback
previous | next