# Inconsistent SQLite node query behaviour

**URL:** <https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445>\
**Category:** General\
**Tags:** database, sqlite\
**Created:** [25 November 2024 12:06 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445 "2024-11-25T12:06:46Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![JesFo](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jesfo/32/50735_2.png) [@JesFo](https://discourse.nodered.org/u/JesFo)\
**Post date:** [25 November 2024 12:06 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/1 "2024-11-25T12:06:46Z")

</div>

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

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

```auto
{
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:

```auto
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?

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 November 2024 12:24 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/2 "2024-11-25T12:24:55Z")

</div>

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

---

<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:** [25 November 2024 12:26 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/3 "2024-11-25T12:26:03Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![JesFo](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jesfo/32/50735_2.png) [@JesFo](https://discourse.nodered.org/u/JesFo)\
**Post date:** [25 November 2024 12:33 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/4 "2024-11-25T12:33:07Z")

</div>

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

---

<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:** [25 November 2024 15:07 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/5 "2024-11-25T15:07:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 November 2024 15:39 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/6 "2024-11-25T15:39:41Z")

</div>

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

```auto
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;

```

---

<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:** [25 November 2024 15:49 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/7 "2024-11-25T15:49:29Z")

</div>

Try this in the function node:

```auto
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 `

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 November 2024 16:09 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/8 "2024-11-25T16:09:52Z")

</div>

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

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

```

---

<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:** [25 November 2024 16:34 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/9 "2024-11-25T16:34:13Z")

</div>

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'`

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [25 November 2024 16:37 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/10 "2024-11-25T16:37:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![JesFo](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/jesfo/32/50735_2.png) [@JesFo](https://discourse.nodered.org/u/JesFo)\
**Post date:** [26 November 2024 06:30 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/11 "2024-11-26T06:30:43Z")

</div>

@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.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [10 December 2024 06:31 UTC](https://discourse.nodered.org/t/inconsistent-sqlite-node-query-behaviour/93445/12 "2024-12-10T06:31:35Z")

</div>

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.