# Node-red-node-sqlite using "Via msg.topic" reports ""Error: SQLITE\_RANGE: column index out of range""

**URL:** <https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462>\
**Category:** Core Development\
**Created:** [12 August 2020 08:45 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462 "2020-08-12T08:45:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [12 August 2020 08:45 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/1 "2020-08-12T08:45:20Z")

</div>

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/d/3/d365bb56d62e2dcfd3151aff5db01b2d3c7b6dd7.png)

reported error:

```auto
Error: SQLITE_RANGE: column index out of range

```

when I am passing as input:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/9/590807c81eed38b30e8a3417bd3feaabd575a564.png)

**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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/3/a369aa36e1a2940bd2c85307499931697628e12b.png)

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

![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/0/a0ee9dfe74da6fba5ee34b06a10aa7a5b58e2ccb.png)

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.

---

<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:** [12 August 2020 09:04 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/2 "2020-08-12T09:04:09Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [12 August 2020 09:08 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/3 "2020-08-12T09:08:24Z")

</div>

With `null` payload it is also working.

More precisely it worked also for following input:  
 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/4/74316087f429d58f0234085143d2c70358eff71c.png)

---

<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:** [12 August 2020 09:51 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/4 "2020-08-12T09:51:02Z")

</div>

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

---

<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:** [12 August 2020 10:33 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/5 "2020-08-12T10:33:12Z")

</div>

> [@janvda](#):
>
> Maybe it would be good to update the documentation

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

---

<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:** [12 August 2020 10:47 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/6 "2020-08-12T10:47:07Z")

</div>

Digging into this more, the code that I believe causes this issue was added back in January 2015 (see [Redirecting to Google Groups](https://groups.google.com/forum/#!topic/node-red/hO-SnMWcKUE)) 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?

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [12 August 2020 11:13 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/7 "2020-08-12T11:13:49Z")

</div>

> [@Colin](#):
>
> The best thing to do is submit an issue

Submitted issue:

- [node-red-node-sqlite using msg.topic reports "Error: SQLITE\_RANGE: column index out of range" when msg.payload is an array. · Issue #670 · node-red/node-red-nodes · GitHub](https://github.com/node-red/node-red-nodes/issues/670)

---

<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:** [11 October 2020 11:13 UTC](https://discourse.nodered.org/t/node-red-node-sqlite-using-via-msg-topic-reports-error-sqlite-range-column-index-out-of-range/31462/8 "2020-10-11T11:13:49Z")

</div>

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