Node-red-node-sqlite using "Via msg.topic" reports ""Error: SQLITE_RANGE: column index out of range""

Core Development
1

My node-red-node-sqlite which is configured as

image

reported error:

Error: SQLITE_RANGE: column index out of range

when I am passing as input:
image

WORKAROUND:
I managed to fix this by deleting the msg.payload from my input and then it is working.
So it works properly when I pass exactly the same input without msg.payload.

image

In that case I didn't get the error and FYI the query returned the following:

image

It took me quite some time to figure out that the msg.payload was causing this.
Maybe it would be good to update the documentation or make the implementation independent from the msg.payload passed as input when "Via msg.topic" is used.

2

Out of curiosity, did you try it with an empty msg.payload?

3

With null payload it is also working.

More precisely it worked also for following input:
image

4

I just did some tests and so far, it works fine if msg.payload is a
string,
number,
boolean,
timestamp,
json,
but does fail if it is an array

1 Like
5

I think that is a bug. The best thing to do is submit an issue then it will get looked at, at some point.

6

Digging into this more, the code that I believe causes this issue was added back in January 2015 (see Redirecting to Google Groups) to deal with binding.

Binding parameters is important with any database. You can use your fav. search engine to lookup why - in short SQL injection, on purpose or accidental. It could be as simple as generating a query with "Today's bug" in a string, and now you have broken SQL because of the single quote - sure you can put in a regex that says change all single quotes to double - but that is SQLite specific, move to MySLQ and you have to change all your code.

@dceejay the question is how to determine if the sql is in msg.topic but not using a binding?

1 Like
7

Submitted issue:

1 Like
8

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