Zeronet: [ NewsfeedPlugin ] Doesn't check if time is unix timestamp for 'date_added'

[rllola]

Step 1: Please describe your environment

• ZeroNet version: 0.6.2 (rev3467)
• Operating system: Ubuntu 16.04
• Web browser: Firefox
• Tor status: disabled
• Opened port: yes
• Special configuration: None

Step 2: Describe the problem:

I am trying to add a news feed for my site but I have an error when I am in my home page and no news feed showing.

Steps to reproduce:

1. Create a followFeed query with date_added in the Date format and not Unix tomestamp
2. Go back to main page to see your news feed
3. See error message

Observed Results:

I have printed my row["date_added"]

From the log :

Expected Results:

We should see the newsfeed and not the error.

Fix

We can either verify the date format inside the Newsfeed Plugin or add to the documentation that the date format has to be unix.

First fix would be better because the developer would not have to think about it making development easier.

[rllola]

For future information if like me in your db you are not using unix timestamp you can convert it by using doing this : strftime('%s', date_added) AS date_added,

See : https://sqlite.org/lang_datefunc.html

[rllola]

Actually doing what I said before doesn't work because it is going to break the parser...

Look like the parser doesn't like to be given a function with several parameters... The ',' inside the strftime break it and give you the date_added)

[rllola]

I gave up...

I just modified my site so it will register new date at the unix timestamp format...

[EibrielInv]

Hi, some questions:

• Can you give an example of Date format?
• Could be the best solution to test the value type here https://github.com/HelloZeroNet/ZeroNet/blob/master/plugins/Newsfeed/NewsfeedPlugin.py#L81 and if it is not int skip with a warning?

[imachug]

@EibrielInv That is (new Date).toString()

[rllola]

The date format is something like this : 2017-11-26T12:16:57.714Z you can see it in the database under the column date_added in mixtape database.

@EibrielInv Yes, we need to check that the type is an integer or you will need to convert it to unix timestamp.

I also tried to convert it in the sql query doing this strftime('%s', date_added) AS date_added but there is a parser which failed to take the all strftime('%s', date_added) value and only return , date_added) precesely this line https://github.com/HelloZeroNet/ZeroNet/blob/master/src/Db/DbQuery.py#L18
The regexp doesn't undertand that we are in a function and will cut it at the first coma returning , date_added)

I think checking the time format and converting it is actually a good solution for that.

[rllola]

Ok I found a workaround until we do a patch for this :
"CAST(round((julianday(date_added)- 2440587.5)*86400.0) AS integer) AS date_added" <-- for all the people who have done the same mistake as me we can do that.

Still need a patch

[rllola]

Just a quick note on it.

I believe that just fixing this issue by doing a conversion from date format to int won't be enough here : https://github.com/HelloZeroNet/ZeroNet/blob/master/plugins/Newsfeed/NewsfeedPlugin.py#L81

In the SQL query we have a WHEN condition which does date_added < strftime('%s', 'now') or something similar or if date_added is not a timestamp we can have an unexpected result...

@shortcutme Do you want me to work on it or you got it ?

[HelloZeroNet]

I'm on it :)

[HelloZeroNet]

I have added a change that will ignore these items: https://github.com/HelloZeroNet/ZeroNet/commit/b4eb09d5e9f2de69c416dce5f3171989231c9a99

Unfortunately the parsing with sqlite using stftime won't work reliably as it disables sqlite indexes on that field and results 100x slowdown for bigger databases