Inconsistent SQLite node query behaviour

General
,
1

I'm using the node-red-node-sqlite v1.1.0 node.
And I'm reading my database like so:

[
    {
        "id": "38680aaa86807ba1",
        "type": "http in",
        "z": "c35667a7f7c45a02",
        "name": "Submit",
        "url": "/submit-week",
        "method": "get",
        "upload": false,
        "swaggerDoc": "",
        "x": 170,
        "y": 340,
        "wires": [
            [
                "92824352fdd91086"
            ]
        ]
    },
    {
        "id": "92824352fdd91086",
        "type": "function",
        "z": "c35667a7f7c45a02",
        "name": "Request",
        "func": "msg.topic = `\n    SELECT * \n    FROM Record \n    WHERE Name = $device \n    AND strftime('%Y-%W', Time) = $week\n`;\nmsg.payload = [msg.req.query.device, msg.req.query.week];\nreturn msg;",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 320,
        "y": 340,
        "wires": [
            [
                "dc005e1b909b7afe",
                "89e49d05e4aa563e"
            ]
        ]
    },
    {
        "id": "dc005e1b909b7afe",
        "type": "sqlite",
        "z": "c35667a7f7c45a02",
        "mydb": "1f2058e76f30783c",
        "sqlquery": "msg.topic",
        "sql": "SELECT * FROM Record\nWHERE Name = device;",
        "name": "Read",
        "x": 450,
        "y": 340,
        "wires": [
            [
                "5d61fd21606aff3d",
                "a603f96ecf2e6ce2"
            ]
        ]
    },
    {
        "id": "1f2058e76f30783c",
        "type": "sqlitedb",
        "db": "mydatabasepath.db",
        "mode": "RWC"
    }
]

When submitting a query, the response from the function node "Request" is as follows:

{
payload: ["E00321BA","2024-48"]
topic: 
    SELECT * 
    FROM Record 
    WHERE Name = $device 
    AND strftime('%Y-%W', Time) = $time;
}

Passing this to the SQL node produces an empty array in the payload, which shouldn't happen since, if I try this with a manual request using a fixed statement in the same SQLite node:

SELECT * FROM Record
WHERE Name = 'E00321BA'
AND strftime('%Y-%W', Time) = '2024-48';

I'm getting what I expect:
payload: [{"Name":"E00321BA","Count":24,"Time":"2024-11-25 10:46:59"},{"Name":"E00321BA","Count":5,"Time":"2024-11-25 11:49:47"}]
Which is correct.

What's the difference between my flow and the manual "Fixed Statement" setup? And why are the results not the same?

2

Please connect a debug node to the output of the function, set it to Output Complete Message and show us what it gives.

3

Add a debug node (set to display the complete msg object) to the output of the function node and show what it displays.

4 
{
	"_msgid": "e61f1f854ffd14b8",
	"payload": [
		"E00321BA",
		"2024-W48"
	],
	"topic": "\n    SELECT *\n    FROM Record \n    WHERE Name = $device \n    AND strftime('%Y-%W', Time) = $week\n",
	"req": {
		"_events": {},
		"_readableState": {
			"highWaterMark": 65536,
			"buffer": [],
			"bufferIndex": 0,
			"length": 0,
			"pipes": [],
			"awaitDrainWriters": null
		},
		"socket": "[internal]",
		"httpVersionMajor": 1,
		"httpVersionMinor": 1,
		"httpVersion": "1.1",
		"complete": true,
		"rawHeaders": [
			"Host",
			"redacted:1880",
			"Connection",
			"keep-alive",
			"User-Agent",
			"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36 Edg/131.0.0.0",
			"Accept",
			"*/*",
			"Referer",
			"http://redacted:1880/table",
			"Accept-Encoding",
			"gzip, deflate",
			"Accept-Language",
			"en-US,en;q=0.9,fi;q=0.8",
			"If-None-Match",
			"W/\"d2-r0an0hrMYPMe5vRBcpbvOKUBpH4\""
		],
		"rawTrailers": [],
		"joinDuplicateHeaders": null,
		"aborted": false,
		"upgrade": false,
		"url": "/submit-week?device=E00321BA&week=2024-W48",
		"method": "GET",
		"statusCode": null,
		"statusMessage": null,
		"client": "[internal]",
		"_consuming": false,
		"_dumped": false,
		"baseUrl": "",
		"originalUrl": "/submit-week?device=E00321BA&week=2024-W48",
		"_parsedUrl": {
			"protocol": null,
			"slashes": null,
			"auth": null,
			"host": null,
			"port": null,
			"hostname": null,
			"hash": null,
			"search": "?device=E00321BA&week=2024-W48",
			"query": "device=E00321BA&week=2024-W48",
			"pathname": "/submit-week",
			"path": "/submit-week?device=E00321BA&week=2024-W48",
			"href": "/submit-week?device=E00321BA&week=2024-W48",
			"_raw": "/submit-week?device=E00321BA&week=2024-W48"
		},
		"params": {},
		"query": {
			"device": "E00321BA",
			"week": "2024-W48"
		},
		"res": "[internal]",
		"body": {},
		"_sessionManager": {
			"_key": "passport"
		},
		"_passport": {
			"instance": {
				"_key": "passport",
				"_strategies": {
					"session": {
						"name": "session",
						"_key": "passport"
					},
					"bearer": {
						"name": "bearer",
						"_realm": "Users"
					},
					"oauth2-client-password": {
						"name": "oauth2-client-password"
					},
					"anon": {
						"name": "anon"
					},
					"tokens": {
						"name": "tokens"
					}
				},
				"_serializers": [],
				"_deserializers": [],
				"_infoTransformers": [],
				"_framework": {},
				"_sm": {
					"_key": "passport"
				},
				"strategies": {},
				"_userProperty": "user"
			}
		},
		"_parsedOriginalUrl": {
			"protocol": null,
			"slashes": null,
			"auth": null,
			"host": null,
			"port": null,
			"hostname": null,
			"hash": null,
			"search": "?device=E00321BA&week=2024-W48",
			"query": "device=E00321BA&week=2024-W48",
			"pathname": "/submit-week",
			"path": "/submit-week?device=E00321BA&week=2024-W48",
			"href": "/submit-week?device=E00321BA&week=2024-W48",
			"_raw": "/submit-week?device=E00321BA&week=2024-W48"
		},
		"route": {
			"path": "/submit-week",
			"stack": [
				{
					"name": "cookieParser",
					"keys": [],
					"regexp": {
						"__enc__": true,
						"type": "regexp",
						"data": "/^\\/?$/i"
					},
					"method": "get"
				},
				{
					"name": "httpMiddleware",
					"keys": [],
					"regexp": {
						"__enc__": true,
						"type": "regexp",
						"data": "/^\\/?$/i"
					},
					"method": "get"
				},
				{
					"name": "corsHandler",
					"keys": [],
					"regexp": {
						"__enc__": true,
						"type": "regexp",
						"data": "/^\\/?$/i"
					},
					"method": "get"
				},
				{
					"name": "metricsHandler",
					"keys": [],
					"regexp": {
						"__enc__": true,
						"type": "regexp",
						"data": "/^\\/?$/i"
					},
					"method": "get"
				},
				{
					"name": "<anonymous>",
					"keys": [],
					"regexp": {
						"__enc__": true,
						"type": "regexp",
						"data": "/^\\/?$/i"
					},
					"method": "get"
				},
				{
					"name": "<anonymous>",
					"keys": [],
					"regexp": {
						"__enc__": true,
						"type": "regexp",
						"data": "/^\\/?$/i"
					},
					"method": "get"
				}
			],
			"methods": {
				"get": true
			}
		},
		"cookies": {},
		"signedCookies": {},
		"headers": {
			"host": "redacted:1880",
			"connection": "keep-alive",
			"user-agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/131.0.0.0 Safari/537.36 Edg/131.0.0.0",
			"accept": "*/*",
			"referer": "http://redacted:1880/table",
			"accept-encoding": "gzip, deflate",
			"accept-language": "en-US,en;q=0.9,fi;q=0.8",
			"if-none-match": "W/\"d2-r0an0hrMYPMe5vRBcpbvOKUBpH4\""
		}
	},
	"res": {}
}

Is the raw output of the function node

5

Can you add a debug node (set to display the complete msg object) to the output of the http-in node and show what that is.

6

You are missing the quotes round $device and $week. Try this in the function node

msg.topic = `
    SELECT * 
    FROM Record 
    WHERE Name = '$device' 
    AND strftime('%Y-%W', Time) = '$week'
`;
msg.payload = [msg.req.query.device, msg.req.query.week];
return msg;
7

Try this in the function node:

msg.device = msg.req.query.device
msg.week = msg.req.query.week;
msg.topic = `SELECT * FROM Record WHERE Name = ${msg.device} AND strftime('%Y-%W', Time) = ${msg.week}
`;

return msg;

this is what I got when playing with it:
SELECT * FROM Record WHERE Name = E00321BA AND strftime('%Y-%W', Time) = 2024-W48

8

That is also missing the quotes round the device and the time. Compare your output with the working query the OP posted.

SELECT * FROM Record
WHERE Name = 'E00321BA'
AND strftime('%Y-%W', Time) = '2024-48';
9

Just adding the single quotes
msg.topic = `SELECT * FROM Record WHERE Name = '${msg.device}' AND strftime('%Y-%W', Time) = '${msg.week}'
takes care of that and the results look like this:
SELECT * FROM Record WHERE Name = 'E00321BA' AND strftime('%Y-%W', Time) = '2024-W48'

10

The problem with that is that it is open to sql injection, it is generally much better to use parameterised queries, as the OP has attempted, where the db will sanitise the data. I think it likely that the original code posted, with the addition of the quotes, will work.

11

@Colin
@zenofmud
Oddly enough the quotes weren't the problem, but the line breaks in the javascript query string.
All I did was define the string in a single line without other modifications and it started working. Thank you for your input and suggestions anyways.