Csv data into sqlite

[SplitSecComp]

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.

[Colin]

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.

[jbudd]

@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

[Colin]

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.

[jbudd]

Sorry for the topic hijack @SplitSecComp

OK thanks @Colin !
So this will drop the table - unsafe

This will not - safe

[SplitSecComp]

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

[SplitSecComp]

I get these errors

[Colin]

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().

[SplitSecComp]

[Steve-Mcl]

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

[SplitSecComp]

This is what comes out of the Get CSV file node

[SplitSecComp]

i am new to sql please show me an example what you mean with "prepared staements"

[Colin]

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.

[Colin]

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.

[SplitSecComp]

Here what comes out of the debug node

[SplitSecComp]

expanded view

[SplitSecComp]

[Colin]

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.

[SplitSecComp]

now i get this error

Error: SQLITE_ERROR: near ":35": syntax error

[SplitSecComp]