Encrypt payload and match with DB query

Hi Guys,

Overview: authentication project whereby the MQTT node receives a SHA256 hash as payload and then needs to hold the Hash temporary, while it runs an SQLite query on the user's table and digests (Sha256) each user details ( query response) + OTP and matches the 2 Hash values, and if they match, then pass a simple 1 or 0 or true or false

I was wondering if someone of you geniuses can assist/instruct me how to achieve this me with a flow, PLEASE.

Here is what I have :
Hardware - Raspberry Pi 4 ,
Node Red v 1.0.6
SQLite DB and tables sorted.

I have created a flow with dummy data for test convenience:

[{"id":"b4c6e58f.f560c8","type":"tab","label":"Read DB and Mtach ","disabled":false,"info":""},{"id":"8bff06e3.6a7a38","type":"sqlite","z":"b4c6e58f.f560c8","mydb":"9db34194.66aca","sqlquery":"fixed","sql":"SELECT * FROM USERS;\nreturn msg;","name":"psa.db USERS table read ","x":590,"y":540,"wires":[[]]},{"id":"58f29a5b.55dce4","type":"function","z":"b4c6e58f.f560c8","name":"????","func":"// Incoming payload : \n\n// Need only 2 values from payload as:\n\n// 1: \"accreq\":\"0e2934be714a95fb23bba4703435fc9bd7d81343d6006fcf29660efa08033677\"\n// 2: \"OTP\":1234\n\n\n\nreturn[{\"psa001/d1/user_input \":{\"user_emp_id\":102030,\"Name \":\"Peter\",\"Surname\":\"Dever\",\"PIN\":5054,\"OTP\":1234,\"Door ID\":1},\"accreq\":\"0e2934be714a95fb23bba4703435fc9bd7d81343d6006fcf29660efa08033677\"}];","outputs":1,"noerr":0,"x":830,"y":380,"wires":[["43707721.b79398","29ec6f88.a51c5"]]},{"id":"43707721.b79398","type":"function","z":"b4c6e58f.f560c8","name":"?????","func":"// needs to split each object from response qeuiry ,then \n// Use each object values + OTP value ( From top funtion node \" MQTT Payload Dummy Data\"), then \n// pass vaules to \"Encrypt node\" \n// Wait for respone from match node , then\n// if true / 1 / ok  , then stop ,\n// if fals / 0 / no , then use next object vales in line + OTP value ( From top funtion node \" MQTT Payload Dummy Data\")  \n\nreturn msg;","outputs":1,"noerr":0,"x":910,"y":440,"wires":[["213389a0.8cba36"]]},{"id":"a4d8217f.10ab8","type":"function","z":"b4c6e58f.f560c8","name":"MQTT Payload Dummy data ","func":"\nreturn[{\"psa001/d1/user_input \":{\"user_emp_id\":102030,\"Name \":\"Peter\",\"Surname\":\"Dever\",\"PIN\":5054,\"OTP\":1234,\"Door ID\":1},\"accreq\":\"0e2934be714a95fb23bba4703435fc9bd7d81343d6006fcf29660efa08033677\"}];","outputs":1,"noerr":0,"x":580,"y":380,"wires":[["58f29a5b.55dce4","88f939cc.46f298"]]},{"id":"a2294a8b.60b508","type":"inject","z":"b4c6e58f.f560c8","name":"Trigger Read req","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":320,"y":380,"wires":[["a4d8217f.10ab8"]]},{"id":"333c066c.67a18a","type":"inject","z":"b4c6e58f.f560c8","name":"Trigger read req ","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":320,"y":440,"wires":[["6e9c7373.a566bc"]]},{"id":"6e9c7373.a566bc","type":"function","z":"b4c6e58f.f560c8","name":"USERS READ Dummy data ","func":"//array[2]\n//0: object\n//user_name: \"Pieter\"\n//user_surname: \"Hattingh\"\n//User_pin: 5054\n//user_acc_level: 5\n//user_acc_code: 1\n//1: object\n//user_name: \"Jac\"\n//user_surname: \"McDonald\"\n//User_pin: 9874\n//user_acc_level: 5\n//user_acc_code: 1\n\nreturn[{\"user_name\":\"Pieter\",\"user_surname\":\"Hattingh\",\"User_pin\":5054,\"user_acc_level\":5,\"user_acc_code\":1},{\"user_name\":\"Jac\",\"user_surname\":\"McDonald\",\"User_pin\":9874,\"user_acc_level\":5,\"user_acc_code\":1}];","outputs":1,"noerr":0,"x":580,"y":440,"wires":[["43707721.b79398","ec66a8ee.53d7a8"]]},{"id":"ec66a8ee.53d7a8","type":"debug","z":"b4c6e58f.f560c8","name":"db read response - Users table  ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":910,"y":540,"wires":[]},{"id":"88f939cc.46f298","type":"debug","z":"b4c6e58f.f560c8","name":"MQTT1 data debug","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":870,"y":300,"wires":[]},{"id":"29ec6f88.a51c5","type":"match","z":"b4c6e58f.f560c8","name":"","rules":[{"property":"payload","propertyType":"msg","type":"eq","value":""}],"x":1230,"y":380,"wires":[["77a04e64.abbc4"],["34145822.788f48"]]},{"id":"213389a0.8cba36","type":"digest","z":"b4c6e58f.f560c8","name":"Encrypt ","algorithm":"SHA256","x":1080,"y":440,"wires":[["29ec6f88.a51c5"]]},{"id":"34145822.788f48","type":"function","z":"b4c6e58f.f560c8","name":"???? No Match , Try again ","func":"// If NOT match the send  - fals  / 0 \n// And try next usesr ( Obect details ) from quiery \nreturn msg;","outputs":1,"noerr":0,"x":1480,"y":420,"wires":[[]]},{"id":"77a04e64.abbc4","type":"function","z":"b4c6e58f.f560c8","name":"??? Match = true / 1 ","func":"// If msg.payload match = true or 1 or ok \n//Then return true with the user data AND\n// If user_acc_code = 1 \n// Then  retrurn User name + \"Access Granted\"\n// Else return User name + \"Access Restricted\" \nreturn msg;","outputs":1,"noerr":0,"x":1460,"y":340,"wires":[[]]},{"id":"9db34194.66aca","type":"sqlitedb","z":"","db":"psa.db","mode":"RWC"}]

Your help will be greatly appreciated.

Thank you in advance

depending on how often the data comes thru, you could easily store the hash in a flow variable then after the rest of the work is done, when you want to compare the new hash to the original, just grab it from the flow variable and do the compare.

HI @zenofmud, i wish i kew how :wink:

HI Have typed the logic out in the functions nodes on what the desired out should be, can some one please assist me in the method ? ( PLEASE )

What don’t you understand about storing a value in a flow context variable?
Have you read the documentation? https://nodered.org/docs/user-guide/context

HI @zenofmud, thanks for your response , to be honest , yes i have seen that post and try to learn JS for a while , i am a slow learner :wink: . This seems quite complex for me regardless how many times i try, i just dont seem to get this. There is a coulpe of "if and els " statements that i also needs gi into the flows . somehow , somewhere...

what would you approach be ?

There is a few processes involved ...

1 Hold data in var , then
2 GET data from the SQLite db / users table , it response in a list of objects,
3 take each object individually and combine with the OTP value from a other function,
4 send the payload in a string to the Encrypt node to Hash ,
5 then match with the hash received, then
6 if match , return msg with matching users data and some custom text as " Access grante " IF ,
7 the users " access code = 1 , els respond access denied ,
8, then i return this data to the device at the door displaying the msg and opens the door / or not ,

so can all of this be done with only by storing a value in a context variable ?

please excuse if this an stupid question for a complex script ( For me ) :wink:

Let's take it one step at a time. Hold data in var .

There are many ways to store something in a flow variable, one of them is using the change node so create a simple flow with an inject that sends the word 'cat' in msg.payload. Connect the inject node to a change node and see if you can figure out how to set a flow variable called 'animal' to the value in msg.payload. Deploy and click on the inject to run the flow.

Then you can use the sidebar to check if it worked,

Great , Thanks Paul, I agree, thanks for helping me , OK

let me do the first step and see the outcome ...( don't go to far ) :wink:

I Will start at step (1) - get and hold the arrived hash key value ...let me test

HI Paul, Sorry no luck here ( @zenofmud)

"Error: Invalid 'from' property: unsupported type: undefined"

What am i missing ....i hate not to understand

Why did you choose to use the 'change' option of the node? If you read the info tab it says

Change

search & replace parts of the property. If regular expressions are enabled, the "replace with" property can include capture groups, for example $1 . Replace will only change the type if there is a complete match.

Have you read about the core nodes and what they can do?

Im Sorry Paul , i just followed your instruction / example image and responded with the outcome

I'm not 'instructing you. I am directing you so you can solve your issue yourself and learn. If you look at what I first said:

see if you can figure out how to set a flow variable called 'animal'

I left a big hint in that statement, but go read about the core nodes first.

@zenofmud , HI Paul, no im clueless !! i have tried may variations and only get error "undefined "

Set the flow variable to the payload

Thanks for the hint Paul, let me try it out . ( ps Thanks for the response )

Hey Paul, still same error = undefined

I did and still gets the same response as : undefined

what is it i am not doing / understanding here ?

Did I tell you to add this to your flow? Go back and read what I told you and then do what I told you to do.

@zenofmud , Hey Paul, its 4 hours later but i think i have got it right

sucsess 1

ok, I finally understand the "context" of context sore inflow and how to set it for later use.

so , what's next mister miyhagi :wink: