Uibuilder security with MS-SQL

I think this topic might be the right choice. Does someone know how to handle async code here?

/* globals module */
 /* globals module */
 * Copyright (c) 2020-2021 Julian Knight (Totally Information)
 * https://it.knightnet.org.uk
 * Licensed under the Apache License, Version 2.0 (the 'License');
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * http://www.apache.org/licenses/LICENSE-2.0
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an 'AS IS' BASIS,
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * Template security functions for uibuilder.
 * Only used if the node.useSecurity flag is set.
 * Please replace with your own code.
 * You MUST export the following functions:
 *   - userValidate - based on an id, lookup the user data to see if the user is valid.
 *                    MUST return a boolean or object of type userValidation.
 *                    Called from the server's logon process. (uiblib.js::logon)
 * Each function MUST at least return true/false. See each function for more details.
 *   1) IF there is an error in this JavaScript, it is very likely that Node-RED will terminate.
 *   2) You can use different security.js files for different instances of uibuilder.
 *      Simply, place a securiy.js file in the instances root folder (e.g ~/.node-red/uibuilder/<url>/security.js)
 *      Note, however, that this means that the security.js file can be edited using the admin Editor.
 *      You have to restart Node-RED to pick up the new file.
'use strict'

/** Define the _auth object
 * @typedef {Object} _auth The standard auth object used by uibuilder security. See docs for details.
 * Note that any other data may be passed from your front-end code in the uibAuth object.
 * @property {String} id Required. A unique user identifier.
 * @property {String} [password] Required for input to login only.
 * @property {String} [jwt] Required if logged in. Needed for ongoing session validation and management.
 * @property {String} [sessionExpiry] Required if logged in. ISO8601 formatted date/time string. Needed for ongoing session validation and management.
 * @property {boolean} [userValidated] Required after user validation. Whether the input ID (and optional additional data from the _auth object) validated correctly or not.
 * @property {Object=} [info] Optional metadata about the user.

const sql = require('mssql')
const bcrypt = require('bcrypt')

var queryResult = async  id => {
		var result
		try {
		var config = {
		  // make sure that any items are correctly URL encoded in the connection string
		 await sql.connect(config)
                 //TODO: Sanitizing ID
		 result = await sql.query`select * from dbo.users where UserName = ${id}`
		 } catch (err) {
	return result

module.exports = {
    /** Validate user against your own user data.
     * The minimum input data is _auth.id which must represent a unique ID.
     * Called from the logon function (uiblib.js::logon) which is triggered by a uibuilder control msg from the client of type 'logon'.
     * May also be called to revalidate users at any time.
     * @param {_auth} _auth Required. 
     * @return {_auth} Object of type _auth
    userValidate: function(_auth) {
       //console.log(`[uibuilder:.common/security.js] userValidate Security from '${__filename}' used. Replace this template with your own code. _auth:`, _auth
	var password = _auth.password
	var id = _auth.id
	_auth.userValidated = false
	var result

	 queryResult(id).then(result => {
	if(result.recordset.length > 0){
		bcrypt.compare(password, result.recordset[0].Hash,(err, response)=> { //function(err,result){
				_auth.userValidated = true
				_auth.info = {"UserName": result.recordset[0].UserName, "FirstName": result.recordset[0].FirstName, "LastName": result.recordset[0].LastName, "Scope": result.recordset[0].Scope}
			else {
				_auth.userValidated = false
	return _auth
    } // ---- End of userValidate ---- //


I'm kind of working on authenticating with mssql, but have some problems validating the user. QueryResult is async, so I'm chaining promises. How can I wait for my async code to finish? Making the userValidate async and wraping everything in a promise and awaiting it, does not do the trick, since userValidate is called synchrounous throughout the uibuilder auth system. So how do I wait for _auth to be set? Any Ideas? A quickfix might be a loop checking some variable to be set repeatedly, but that is some nasty bad code to me.

[admin] Moved to new topic

Really, the "best" way to achieve this is to not use uibuilder's security at all right now. Instead, it would be better to use a reverse proxy like NGINX with a suitable authentication extension.

I am still working on uibuilder's security model and I keep hitting up against some serious issues in trying to get websockets actually secured properly. It is a complex and hard problem. As I am working through the issues at the moment, I'm fairly sure that the next release is going to look quite different for security which is why I advise not spending much time on it right now.

Instead, using NGINX or similar would be a good investment of your time and effort because that will work no matter what changes with uibuilder and it is likely that it will have been much more comprehensively tested and battle hardened.

Hi TotallyInformation,

unfortunately there is already an nginx authentication with ad, but there is still a need of "local" auth. For some reason it is not possible to have another auth for each Uibuilder node. So I need to make this in-built auth work.

Is there a timeline for the next update?

best regards


I'm afraid not at present. Work and family commitments getting in the way at the moment :frowning:

So the best way is either use sqlite which seems to work and is synchronous or wait until the auth stuff gets reworked?

Or is there another possible working auth solution you can think of?

Well, it should be possible to unravel it but I'm afraid my async/promises foo isn't quite up to the task.

Don't know whether this provides any clues?

javascript - How to connect to mssql server synchronously in node.js - Stack Overflow

It seems that the latest versions of Node.js support top-level await.