# Encrypt payload and match with DB query

**URL:** <https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784>\
**Category:** General\
**Created:** [9 July 2020 12:47 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784 "2020-07-09T12:47:29Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 12:47 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/1 "2020-07-09T12:47:29Z")

</div>

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.

 ![Match flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/1/b1abb7b550c6a1e7dafb0ba783835852cca144d8.jpeg)

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

```auto
[{"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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 July 2020 14:12 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/2 "2020-07-09T14:12:50Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 14:34 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/3 "2020-07-09T14:34:39Z")

</div>

HI @zenofmud, i wish i kew how 😉

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 14:57 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/4 "2020-07-09T14:57:56Z")

</div>

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 )

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 July 2020 15:02 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/5 "2020-07-09T15:02:48Z")

</div>

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](https://nodered.org/docs/user-guide/context)

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 15:07 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/6 "2020-07-09T15:07:21Z")

</div>

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 😉 . 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 ?

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 15:17 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/7 "2020-07-09T15:17:39Z")

</div>

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 ) 😉

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 July 2020 15:34 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/8 "2020-07-09T15:34:53Z")

</div>

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,

 ![Screen Shot 2020-07-09 at 11.34.20 AM](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/b/ab9458063d83fa6652b69dff213a0857e016d50b.png)

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 15:38 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/9 "2020-07-09T15:38:11Z")

</div>

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 ) 😉

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 15:48 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/10 "2020-07-09T15:48:50Z")

</div>

HI Paul, Sorry no luck here ( @zenofmud)

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

 ![chnage issue](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/e/1e1e5071ea02cf16fdf9b21c8a01aa1235fc448b.jpeg)

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

 ![issue 2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/f/ff0cef799e98aaade89f9f5538546ed98b73296f.jpeg)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 July 2020 15:59 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/11 "2020-07-09T15:59:03Z")

</div>

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](https://nodered.org/docs/user-guide/nodes) and what they can do?

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 16:01 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/12 "2020-07-09T16:01:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 July 2020 16:05 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/13 "2020-07-09T16:05:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 16:24 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/14 "2020-07-09T16:24:20Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 July 2020 17:19 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/15 "2020-07-09T17:19:00Z")

</div>

Set the flow variable to the payload

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 17:19 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/16 "2020-07-09T17:19:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 17:40 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/17 "2020-07-09T17:40:13Z")

</div>

Hey Paul, still same error = undefined

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 17:56 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/18 "2020-07-09T17:56:23Z")

</div>

I did and still gets the same response as : undefined

what is it i am not doing / understanding here ?

 ![issue 3](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/f/0/f0e1ef61657bcb605b6be6c1c6bd89636777e00f.jpeg)

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [9 July 2020 18:43 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/19 "2020-07-09T18:43:56Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/peter/32/25135_2.png) [@Peter](https://discourse.nodered.org/u/Peter)\
**Post date:** [9 July 2020 23:06 UTC](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784/20 "2020-07-09T23:06:01Z")

</div>

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

![sucsess 1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/b/8/b8998cbd6969a9901e57432ccfc22735e9d8657c.jpeg)

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

so , what's next mister miyhagi 😉

[Next page](https://discourse.nodered.org/t/encrypt-payload-and-match-with-db-query/29784.md?page=2)