# Csv data into sqlite
**URL:** https://discourse.nodered.org/t/csv-data-into-sqlite/60405
**Category:** General
**Tags:** database
**Created:** [27 March 2022 14:21 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405 "2022-03-27T14:21:58Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [27 March 2022 14:21 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/1 "2022-03-27T14:21:58Z")
Hi.
I have a device that sends me data in a csv format. How can i move/import the csv data into sqlite. I have set up the database and tables and can read the csv data but cant get it into sql. I dont want to do it manually it must be an automated process every time a file arrives in the directory.
Can someone kindly help.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [27 March 2022 14:41 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/2 "2022-03-27T14:41:24Z")
Assuming that you are trying to write the individual values in the csv to fields in the database then have a look at the csv node. You should be able to use that to convert the csv to a javascript object, then you just need to build the SQL query to insert it into the database using the sqlite node.
**[Edit]** Or maybe just use the string directly in a query built, for example, using a Template node (not ui\_template). What does the csv look like and what SQL query are you trying to build?
Though it is generally safer to use parameters in the query in order to avoid SQL injection.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [27 March 2022 17:10 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/3 "2022-03-27T17:10:44Z")
> [@SplitSecComp](#):
>
> I dont want to do it manually it must be an automated process every time a file arrives in the directory.
A Watch node will trigger a message whenever a new file is created in the watched directory.
> [@Colin](#):
>
> Though it is generally safer to use parameters in the query in order to avoid SQL injection.
Sorry, totally off topic but why is that?
I _think_ an SQL injection might be something like `" 'Smith'; SELECT secret from secrets"`
Can you give examples using / not using parameters and how an attempted injection would be affected?
Thanks 🙂
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [27 March 2022 20:52 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/4 "2022-03-27T20:52:18Z")
A more critical embedded command might be a DROP TABLE command. When you use parameters the code that expands the parameters checks for SQL injection and stops it. In fact in sqlite I think they are called Prepared Queries. It tells you how to do that in the node's help text.
An example of injection in node-red might be a query generated in a Template node like this.
`SELECT * from sometable WHERE name = '{{msg.payload}}';`
If msg.payload came from user input in some way then a malicious user might provide a name like `fred'; DROP TABLE sometable;`
So the query now reads
`SELECT * from sometable WHERE name = 'fred'; DROP TABLE sometable; ';`
Using a prepared query prevents the DROP TABLE statement from getting through.
---
### Author:  [@jbudd](https://discourse.nodered.org/u/jbudd)
#### Post date: [27 March 2022 22:35 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/5 "2022-03-27T22:35:59Z")
Sorry for the topic hijack @SplitSecComp
OK thanks @Colin !
So this will drop the table - unsafe
This will not - safe
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 06:47 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/6 "2022-03-28T06:47:12Z")
This is what the csv file look like
This is my database structure
This is my flow
This is my function node that i am trying to write to the db
var sql = "";
var d = new Date();
var epoch = d.getTime();
var outputs = ;
sql = "INSERT INTO gpstrack (Time) " +
"VALUES ("+msg.payload.Time+")";
outputs.push({topic:sql});
return [outputs];
But cant get i to work 🙂
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 06:49 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/7 "2022-03-28T06:49:29Z")
I get these errors
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [28 March 2022 08:01 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/8 "2022-03-28T08:01:13Z")
The one thing you have not shown us is what is in the message going into your function node. Specifically what is in msg.payload.Time. It should be an integer, if it is a Date object then you should use `msg.payload.Time.getTime()`.
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:06 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/9 "2022-03-28T08:06:25Z")
---
### Author:  [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)
#### Post date: [28 March 2022 08:08 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/10 "2022-03-28T08:08:15Z")
You are using standard SQL queries and the date is not surrounded by quotes - that's why it fails. As explained earlier in the thread, you should really use prepared statements to avoid worrying about things like quotations and sequel injection attacks.
Sorry, I've just looked at your table design. You want time stamp not a string time. You will have to convert the time column in your data to an epoch before generating the sql
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:08 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/11 "2022-03-28T08:08:55Z")
This is what comes out of the Get CSV file node
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:17 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/13 "2022-03-28T08:17:55Z")
i am new to sql please show me an example what you mean with "prepared staements" 🙂 🙂
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [28 March 2022 08:22 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/14 "2022-03-28T08:22:06Z")
That does not show us what is in the message going into the function. Feed it into a debug node and check that Time is an integer.
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [28 March 2022 08:23 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/15 "2022-03-28T08:23:17Z")
I suggest you get what you have going first, then change the sql to use prepared statements. It shows how to do that in the help text for the sqlite node.
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:33 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/16 "2022-03-28T08:33:15Z")
Here what comes out of the debug node
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:34 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/17 "2022-03-28T08:34:07Z")
expanded view
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:34 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/18 "2022-03-28T08:34:56Z")
---
### Author:  [@Colin](https://discourse.nodered.org/u/Colin)
#### Post date: [28 March 2022 08:38 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/19 "2022-03-28T08:38:03Z")
You have not selected First Row contains column names in the csv node, and the Time value is a string. If you want to have it as an integer then you will have to convert it. Alternatively change the column type to string.
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:49 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/20 "2022-03-28T08:49:05Z")
now i get this error
Error: SQLITE\_ERROR: near ":35": syntax error
---
### Author:  [@SplitSecComp](https://discourse.nodered.org/u/SplitSecComp)
#### Post date: [28 March 2022 08:52 UTC](https://discourse.nodered.org/t/csv-data-into-sqlite/60405/21 "2022-03-28T08:52:28Z")
[Next page](https://discourse.nodered.org/t/csv-data-into-sqlite/60405.md?page=2)