[Meta] FreeGameDev changes NEW: https://freegamedev.net

[Julius]

I just added a draft Privacy Policy that asks users for consent in regards to storage of IP, but I will consider patching it out as you described. Thanks for investigating that.

I don't think running the rss feed as a live process with no caching is a good idea. RSS feeds get abused a lot and it could easily result in a denial of service attack kind of situation, even if maybe unintentional. I also don't really see the problem in having the rss feed a few minutes out of date by caching it in a .xml file. But maybe I am missing something in regards to how these relative time-stamps work in RSS feeds?

Edit: and from the public directory it would not have access to the config file with the database credentials anyways I guess.

[bzt]

I just added a draft Privacy Policy that asks users for consent in regards to storage of IP, but I will consider patching it out as you described. Thanks for investigating that.

You're welcome! Hope you patch it.

I don't think running the rss feed as a live process with no caching is a good idea. RSS feeds get abused a lot and it could easily result in a denial of service attack kind of situation, even if maybe unintentional.

If you worry about that, then you shouldn't use Flarum in the first place. Downloading its frontpage makes hell a lot more (and lot worse, unoptimized) db queries than this little one. But, you can lower the limit to - let's say - 20 records. And I can add client-side cache management to it if you'd like.

I also don't really see the problem in having the rss feed a few minutes out of date by caching it in a .xml file. But maybe I am missing something in regards to how these relative time-stamps work in RSS feeds?

Yes, you miss the point that time differences are relative to the http query time.

Let's say, there's a forum post at 8:00am. If it were a cron job running at - let's say - 8:05am, then it would cache "5 minutes ago" after that if someone downloads the cached xml at 8:07am or at 8:09am, in both case it would say "5 minutes ago", however correctly it should say "7 minutes ago" and "9 minutes ago" respectively. The issue here is, you cannot rely on the pubDate field in the xml and that your database server, webserver and the client all have their clocked synced. I tell you from experience, they never will be. That's why the code calculates the elapsed time using only the database server's time (no sync assumed, the difference is calculated using the same, only one clock source.)

Edit: and from the public directory it would not have access to the config file with the database credentials anyways I guess.

Now you're bluffing. If that were true, then Flarum couldn't read it's own configuration either.

I don't understand why are you so afraid to put this very minimal and little rss.php in the public directory when you have already put that horrible and badly written Flarum there... You see, you can verify each and every single line in this rss script, make sure it doesn't do anything harmful, but you can't say that about Flarum, yet you're not afraid of that code.

Cheers,
bzt

[bzt]

Here's the modified code:
- it only returns 20 records tops
- it tells the clients not to query the feed more than once per minute
- it tells the clients to close the connection (so that your server can free the tcp session)

{l Code}: {l Select All Code}

<?php
/* ------------------ set this to the Flarum config file -------------------- */
$config = @include("path/to/flarum/config.php");
$limit = 20; /* return that many records at most */
/* -------------------------- configuration ends ---------------------------- */

if(!is_array($config) || !is_array($config['database']))
    die("Configuration not found.\n");

/***** connect to the database using the configuration in Flarum's config *****/
try {
    $pdo = new \PDO(
        $config['database']['driver'].":host=".$config['database']['host'].
        ";dbname=".$config['database']['database'].
        (!empty($config['database']['charset']) ?
            ";charset=".$config['database']['charset'] : ""),
        $config['database']['username'],
        @$config['database']['password'],
        [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_EMULATE_PREPARES => 0
        ]);
} catch(\PDOException $e) {
    die("Unable to connect to database: ".$e->getMessage()."\n");
}
if(!empty($config['database']['charset']))
    $pdo->exec("set names '".$config['database']['charset']."';");

/********************** get some data from the database ***********************/
try {
    $sql = "SELECT a.id, a.title, a.slug, a.last_posted_at, b.username, ".
        " TIMESTAMPDIFF(SECOND, a.last_posted_at, NOW()) as t, ".
        " DATE_FORMAT(a.last_posted_at, '%a, %d %b %Y %T') as d FROM ".
        $config["database"]["prefix"]."discussions as a LEFT JOIN ".
        $config["database"]["prefix"]."users as b ON a.last_posted_user_id = b.id ".
        "WHERE b.id IS NOT NULL AND a.is_private != 1 ".
        "ORDER BY a.last_posted_at DESC LIMIT ".$limit.";";
    $res = $pdo->query($sql);
    $rows = $res->fetchAll(\PDO::FETCH_ASSOC);
    $pdo->close();
} catch(\PDOException $e) {
    die("Unable to query the database: ".$e->getMessage()."\n");
}

/*************************** output what we've got ****************************/
/* tell the clients not to query this url more than once per minute */
header("Pragma:cache");
header("Cache-Control:cache,public,max-age=60");
header("Connection:close");
echo("<"."?xml version=\"1.0\" encoding=\"utf-8\"?".">\r\n");
?>
<rss version="2.0">
<channel>
 <title>FreeGameDev</title>
 <link><![CDATA[<?php echo($config["url"]); ?>]]</link>
 <description>Free Game Development</description>
<?php foreach($rows as $row) { ?>
  <item>
    <title><![CDATA[<?php echo(htmlspecialchars($row["title"])); ?>]]></title>
    <description><![CDATA[<?php
        echo("by ".htmlspecialchars($row["username"])." ");
        $t = $row["t"] + 0;
        if($t < 60)
            echo("just now");
        else if($t < 60*60)
            echo(($t/60)." minute".($t/60 > 1 ? "s" : "")." ago");
        else if($t < 60*60*24)
            echo(($t/60/60)." hour".($t/60/60 > 1 ? "s" : "")." ago");
        else
            echo(($t/60/60/24)." day".($t/60/60/24 > 1 ? "s" : "")." ago");
        ?>]]></description>
    <guid><![CDATA[<?php echo(
        /* create a link to the discussion. Slug might contain the id already */
        $config["url"]."/d/".$row["id"]."-".$row["id"]."-".
        urlencode($row["slug"]));
        ?>]]</guid>
    <pubDate><![CDATA[<?php
        echo(htmlspecialchars($row["d"]." ".date("T"))); ?>]]></pubDate>
  </item>
<?php } ?>
</channel>
</rss>


But as I've said, this is nothing, unmeasurable almost, downloading the Flarum frontpage makes a lot worse things to your server's load and puts uncomparably more work to the database server.

Cheers,
bzt

[Julius]

I just now implemented a way to stop the IP logging that also survives forum upgrades, but your idea was a good hint on what to do. I just need to clean up the database of those IPs already logged and update the PP again. Edit: done.

I don't think loading the Flarum main page is quite comparable as it is heavily cached and people don't constantly spam the refresh button in their browser like some RSS clients seem to do by default. Edit: of course individually it is nothing much, but RSS feeds tend to end up in a lot of places that just 24/7 request updates (like server side feed readers) and thus this would just add load to the DB for what likely is never even read.

So this RSS client side access limts that you added now... do you know how widely such client recommendations are supported by RSS feed readers?

I am not afraid of your code, it seems perfectly fine for what it attempts to do. I am just not sure if this specific implementation is a good idea for the reasons outlined above. As for the "public" directory... yeah I am not 100% sure what and what not that restricts I guess I would need to try it. But at the very least your script would leak the server internal path of the config file which is not ideal either. Edit: I guess something like "../config.php" could be used?

[bzt]

I just now implemented a way to stop the IP logging that also survives forum upgrades, but your idea was a good hint on what to do. I just need to clean up the database of those IPs already logged and update the PP again.

Great!

I don't think loading the Flarum main page is quite comparable as it is heavily cached and people don't constantly spam the refresh button in their browser like some RSS clients seem to do by default.

Yeah it is, because Flarum does a lot more checks to access the cache and to see if cache needs update than what this little script does altogether. You see, it is so brainf*ck simple, that adding any caching and checking if that cache needs an update would actually increase it's overhead. (One possible way to implement a viable cache for it is to unconditionally update the rss cache when people post something, but that would require modifying Flarum's code here, which I'd like to avoid.)

But you should not believe what I say, do your own performance measurements. You can use xdebug to do the profiling, it works great (it can do a lot more than that). That's what I have used all the time when I was developing for the web.

So this RSS client side access limts that you added now... do you know how widely such client recommendations are supported by RSS feed readers?

It is RFC. All feed readers must accept it. While true, there can be badly written feed readers sure, assuming that a feed reader isn't RFC compliant is like assuming the users won't spam the refresh button (I would say the chances are much higher for the latter).

I am not afraid of your code, it seems perfectly fine for what it attempts to do. I am just not sure if this specific implementation is a good idea for the reasons outlined above. As for the "public" directory... yeah I am not 100% sure what and what not that restricts I guess I would need to try it. But at the very least your script would leak the server internal path of the config file which is not ideal either.

Now why would it leak the server internal path? First, you can use a relative path. Second, it never outputs that path to the user, so no leak possible. I've even added a surpress operator to the include so that if you specify an incorrect path, php error message wouldn't leak that incorrect path either. I've also payed attention not to print any sensitive information in the error messages. I have about 20 years of experience with high-security and high-available, load-balanced web-clusters you know :-D But it's okay that you don't trust my code, you don't know me, after all. Do your tests, check whatever you want to check. Let me know if you find anything.

Cheers,
bzt

[Julius]

I'll play around with it and see if I like the results But for sure your efforts are appreciated!

[Julius]

Hmm, so that doesn't work very well tbh.

I got it to a semi-functional state by fixing line 40 as

{l Code}: {l Select All Code}

$pdo->db = null;

and removing all the CDATA parts (not exactly sure why if fails completely with those), and at least now I get something that some feed-readers interpret as a RSS feed (but not a very useful one).

However it is still far from a valid rss feed: https://validator.w3.org/feed/check.cgi ... %2Frss.php

[bzt]

Thanks for the feedback, I've fixed all the issues. Here comes an updated version.

Hmm, so that doesn't work very well tbh.

I got it to a semi-functional state by fixing line 40 as

{l Code}: {l Select All Code}

$pdo->db = null;

That's strange, the PDO driver should have a close method. Anyway, I made it optional.

and removing all the CDATA parts (not exactly sure why if fails completely with those)

Uppps, I forgot to properly close one of them, mea culpa. Fixed.

and at least now I get something that some feed-readers interpret as a RSS feed (but not a very useful one).

What do you mean by "not very useful"? I deliberately not cited the post's content to spare resources, only the topic's title and the post time and poster user. I can add more fields, no probs, but that would increase the script's load.

However it is still far from a valid rss feed: https://validator.w3.org/feed/check.cgi ... %2Frss.php

Okay, I've fixed all of these.
- pubDate: believe it or not, this is an error in the validator. The timezone you have set up on the server ("CEST") is not listed as a valid timezone in the validator... I've replaced the timezone with a numerical form, as RFC822 allows that too. Should fix that warning.
- served as text/html: fair enough, I've added content type to fix this.
- missing atom:link: well, this is an rss feed, not an atom feed, but why not, I've added these too. Should pass the validation now.

Please give it a try, and let me know if you find any other problems with it, I'll fix those too.

{l Code}: {l Select All Code}

<?php
/* ------------------ set this to the Flarum config file -------------------- */
$config = @include("path/to/flarum/config.php");
$limit = 20; /* return that many records at most */
/* -------------------------- configuration ends ---------------------------- */

if(!is_array($config) || !is_array($config['database']))
    die("Configuration not found.\n");

/***** connect to the database using the configuration in Flarum's config *****/
try {
    $pdo = new \PDO(
        $config['database']['driver'].":host=".$config['database']['host'].
        ";dbname=".$config['database']['database'].
        (!empty($config['database']['charset']) ?
            ";charset=".$config['database']['charset'] : ""),
        $config['database']['username'],
        @$config['database']['password'],
        [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_EMULATE_PREPARES => 0
        ]);
} catch(\PDOException $e) {
    die("Unable to connect to database: ".$e->getMessage()."\n");
}
if(!empty($config['database']['charset']))
    $pdo->exec("set names '".$config['database']['charset']."';");

/********************** get some data from the database ***********************/
try {
    $sql = "SELECT a.id, a.title, a.slug, a.last_posted_at, b.username, ".
        " TIMESTAMPDIFF(SECOND, a.last_posted_at, NOW()) as t, ".
        " DATE_FORMAT(a.last_posted_at, '%a, %d %b %Y %T') as d FROM ".
        $config["database"]["prefix"]."discussions as a LEFT JOIN ".
        $config["database"]["prefix"]."users as b ON a.last_posted_user_id = b.id ".
        "WHERE b.id IS NOT NULL AND a.is_private != 1 ".
        "ORDER BY a.last_posted_at DESC LIMIT ".$limit.";";
    $res = $pdo->query($sql);
    $rows = $res->fetchAll(\PDO::FETCH_ASSOC);
    if(method_exists($pdo, "close"))
      $pdo->close();
    else
       $pdo->db = null;
} catch(\PDOException $e) {
    die("Unable to query the database: ".$e->getMessage()."\n");
}

/*************************** output what we've got ****************************/
/* tell the clients not to query this url more than once per minute */
header("Pragma:cache");
header("Cache-Control:cache,public,max-age=60");
header("Connection:close");
header("Content-Type: application/rss+xml");
echo("<"."?xml version=\"1.0\" encoding=\"utf-8\"?".">\r\n");
?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<channel>
 <title>FreeGameDev</title>
 <link><![CDATA[<?php echo($config["url"]); ?>]]></link>
 <description>Free Game Development</description>
 <atom:link href="<?php echo($config["url"]); ?>/rss.php" rel="self" type="application/rss+xml" />
<?php foreach($rows as $row) { ?>
  <item>
    <title><![CDATA[<?php echo(htmlspecialchars($row["title"])); ?>]]></title>
    <description><![CDATA[<?php
        echo("by ".htmlspecialchars($row["username"])." ");
        $t = $row["t"] + 0;
        if($t < 60)
            echo("just now");
        else if($t < 60*60)
            echo(floor($t/60)." minute".($t/60 > 1 ? "s" : "")." ago");
        else if($t < 60*60*24)
            echo(floor($t/60/60)." hour".($t/60/60 > 1 ? "s" : "")." ago");
        else
            echo(floor($t/60/60/24)." day".($t/60/60/24 > 1 ? "s" : "")." ago");
        ?>]]></description>
    <guid><![CDATA[<?php echo(
        $config["url"]."/d/".$row["id"]."-".$row["id"]."-".
        urlencode($row["slug"]));
        ?>]]></guid>
    <pubDate><![CDATA[<?php
        echo(htmlspecialchars($row["d"]." ".date("O"))); ?>]]></pubDate>
  </item>
<?php } ?>
</channel>
</rss>


Cheers,
bzt

[Julius]

Yes that looks significantly better now that the time-stamp actually works. Thanks!

I will test it a bit more and see if it can be further improved. Maybe including the post content or at least clearly differentiating between new topics and replies would make sense though.

[Julius]

There is a problem with timezones it seems. I post that I just now made shows up as some hours ago in the feed due to difference in timezone to the server in Germany?

[bzt]

Yes that looks significantly better now that the time-stamp actually works. Thanks!

Good to hear and you're welcome!

I will test it a bit more and see if it can be further improved. Maybe including the post content or at least clearly differentiating between new topics and replies would make sense though.

I've added the "new topic" / "reply", that was easy. On the other hand, I would not recommend post contents. First, they would require much more resource, both on the database side on the network traffic. Second, if Flarum allows formatting, then posts would be shown with non-interpreted html tags (not good), and it would require further resources to parse and remove those tags. But can be done, let me know if despite this you want post content in the feed.

There is a problem with timezones it seems. I post that I just now made shows up as some hours ago in the feed due to difference in timezone to the server in Germany?

Haha, told you. That's why I calculate the elapsed time from strictly the database time.

Assuming the database server is set to UTC, we could try to replace the "date("O")" with "+0000" and see if post time displays correctly for you. I've modified this, but if timezone still doesn't work, just put "date("O")" back.

{l Code}: {l Select All Code}

-      echo(htmlspecialchars($row["d"]." +0000")); ?>]]></pubDate>
+      echo(htmlspecialchars($row["d"]." ".date("O"))); ?>]]></pubDate>


I've found another issue, printing "minute" / "minutes" wasn't always correct, I've fixed that too. Here's the updated version:

{l Code}: {l Select All Code}

<?php
/* ------------------ set this to the Flarum config file -------------------- */
$config = @include("path/to/flarum/config.php");
$limit = 20; /* return that many records at most */
/* -------------------------- configuration ends ---------------------------- */

if(!is_array($config) || !is_array($config['database']))
   die("Configuration not found.\n");

/***** connect to the database using the configuration in Flarum's config *****/
try {
   $pdo = new \PDO(
      $config['database']['driver'].":host=".$config['database']['host'].
      ";dbname=".$config['database']['database'].
      (!empty($config['database']['charset']) ?
         ";charset=".$config['database']['charset'] : ""),
      $config['database']['username'],
      @$config['database']['password'],
      [
         \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
         \PDO::ATTR_EMULATE_PREPARES => 0
      ]);
} catch(\PDOException $e) {
   die("Unable to connect to database: ".$e->getMessage()."\n");
}
if(!empty($config['database']['charset']))
   $pdo->exec("set names '".$config['database']['charset']."';");

/********************** get some data from the database ***********************/
try {
   $sql = "SELECT a.id, a.title, a.slug, a.last_posted_at, a.comment_count, b.username, ".
      " TIMESTAMPDIFF(SECOND, a.last_posted_at, NOW()) as t, ".
      " DATE_FORMAT(a.last_posted_at, '%a, %d %b %Y %T') as d FROM ".
      $config["database"]["prefix"]."discussions as a LEFT JOIN ".
      $config["database"]["prefix"]."users as b ON a.last_posted_user_id = b.id ".
      "WHERE b.id IS NOT NULL AND a.is_private != 1 ".
      "ORDER BY a.last_posted_at DESC LIMIT ".$limit.";";
   $res = $pdo->query($sql);
   $rows = $res->fetchAll(\PDO::FETCH_ASSOC);
   if(method_exists($pdo, "close"))
     $pdo->close();
   else
     $pdo->db = null;
} catch(\PDOException $e) {
   die("Unable to query the database: ".$e->getMessage()."\n");
}

/*************************** output what we've got ****************************/
/* tell the clients not to query this url more than once per minute */
header("Pragma:cache");
header("Cache-Control:cache,public,max-age=60");
header("Connection:close");
header("Content-Type: application/rss+xml");
echo("<"."?xml version=\"1.0\" encoding=\"utf-8\"?".">\r\n");
?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<channel>
 <title>FreeGameDev</title>
 <link><![CDATA[<?php echo($config["url"]); ?>]]></link>
 <description>Free Game Development</description>
 <atom:link href="<?php echo($config["url"]); ?>/rss.php" rel="self" type="application/rss+xml" />
<?php foreach($rows as $row) { ?>
  <item>
   <title><![CDATA[<?php echo(htmlspecialchars($row["title"])); ?>]]></title>
   <description><![CDATA[<?php
      echo(($row["comment_count"] < 2 ? "new topic" : "reply").
          " by ".htmlspecialchars($row["username"])." ");
      $t = $row["t"] + 0; $m = floor($t/60); $h = floor($t/60/60); $d = floor($t/60/60/24);
      if($t < 60)
         echo("just now");
      else if($t < 60*60)
         echo($m." minute".($m > 1 ? "s" : "")." ago");
      else if($t < 60*60*24)
         echo($h." hour".($h > 1 ? "s" : "")." ago");
      else
         echo($d." day".($d > 1 ? "s" : "")." ago");
      ?>]]></description>
   <guid><![CDATA[<?php echo(
      $config["url"]."/d/".$row["id"]."-".$row["id"]."-".
      urlencode($row["slug"]));
      ?>]]></guid>
   <pubDate><![CDATA[<?php
      echo(htmlspecialchars($row["d"]." +0000")); ?>]]></pubDate>
  </item>
<?php } ?>
</channel>
</rss>


Cheers,
bzt

[Julius]

Thanks for looking into it another time.

The time in the feed reader seems to be correct now, but the one that is mentioned in the message body itself is still incorrect.

[bzt]

Thanks for looking into it another time.

That's nothing, it is in my best interest to get this working :-)

The time in the feed reader seems to be correct now, but the one that is mentioned in the message body itself is still incorrect.

Then your RSS reader is buggy, and can't convert from server's timezone to yours, but it can convert from UTC to yours.

You have two options:
1. put "date("O")" back and use an rss feed reader that interprets timezone correctly (then all date times will be in server's local time)
2. leave pubDate as-is and replace "NOW()" in the sql with "CURRENT_TIMESTAMP()". (and all date times will be in UTC)

I'd say it worth to give a try to option 2., but chances are good there are still some readers that can't convert UTC into local time on the client's side. But I think the chance of failing with that is less than with option 1. (TBH I use Want My RSS firfox addon, and with that times are correct either way, doesn't matter if dates are in server local time or in UTC.) Anyway I say let's give a try to full UTC, replace

{l Code}: {l Select All Code}

-      " TIMESTAMPDIFF(SECOND, a.last_posted_at, NOW()) as t, ".
+      " TIMESTAMPDIFF(SECOND, a.last_posted_at, CURRENT_TIMESTAMP()) as t, ".


This should do the trick.

Cheers,
bzt

[Julius]

Hmm, ok. I am just using Thunderbird to test the feed, so maybe that isn't the best feed reader.

But what I actually meant is the time mentioned in the message body itself, for example when it says: "reply by user 2 hours ago". That time has both an timezone issue and also never seems to update (maybe due to client side caching?).

[bzt]

Hmm, ok. I am just using Thunderbird to test the feed, so maybe that isn't the best feed reader.

It will be fine when all date times will be in UTC.

But what I actually meant is the time mentioned in the message body itself, for example when it says: "reply by user 2 hours ago". That time has both an timezone issue and also never seems to update (maybe due to client side caching?).

Yes, that's a timezone issue, that using CURRENT_TIMESTAMP will solve. About not updating I don't know, could be a caching issue? Let's solve timezone first, and then I'll investigate that.

(This is always like this with date times and timezones... don't you worry, we'll get to the end pretty soon)

Cheers,
bzt

[Julius]

I did the CURRENT_TIMESTAMP change now, and completely removed all entries in the thunderbird feed-reader, but the wrong time in the message body remains. Otherwise it seems unchanged.

P.S.: Any good suggestion how to not query tags that are hidden? Right now we don't use any, but in theory your script would also query those and thus expose such posts to non-authorized users.

[bzt]

Ok, I've found the cause of the caching problem.

{l Code}: {l Select All Code}

$ curl -I https://freegamedev.net/rss.php
HTTP/2 200
server: nginx
date: Wed, 09 Jun 2021 14:54:30 GMT
content-type: application/rss+xml
pragma: cache
cache-control: cache,public,max-age=60
cache-control: max-age=3600
expires: Wed, 09 Jun 2021 15:54:30 GMT


As you can see, my script sets the cache time to 60 secs (1 minute), but then something overrides that by adding a header line with 3600 secs (1 hour). You should figure out what's adding that, but I'm assuming could be a bad nginx config maybe? I could help you with that, but I'm not sure you want to trust me with the nginx config... In blind I can only say that Flarum isn't cached, so there must be a "location" and an "expire" config option somewhere in the config file.

See here:

{l Code}: {l Select All Code}

$ curl -I https://freegamedev.net/
HTTP/2 200
server: nginx
date: Wed, 09 Jun 2021 15:05:10 GMT
content-type: text/html; charset=utf-8
vary: Accept-Encoding
x-content-type-options: nosniff
referrer-policy: same-origin
x-powered-by: Flarum
x-csrf-token: fBfViSOoOEoSIPPzaZWmQOVmntjLsjhbqvPOz3BN
set-cookie: flarum_session=LKyeud1qJXC3wkV9n8s4IiWiU4xAydfcAJMwR3Bq; Path=/; Expires=Wed, 09 Jun 2021 17:05:10 GMT; Max-Age=7200; Secure; HttpOnly; SameSite=Lax
cache-control: max-age=0
expires: Wed, 09 Jun 2021 15:05:10 GMT


As you can see, here "max-age=0" (meaning no client side caching at all) and not "max-age=3600", so there must be some difference how nginx handles those urls.

Should be something like

{l Code}: {l Select All Code}

location ~ rss\.php {
  expire 1m;
}


Cheers,
bzt

[bzt]

P.S.: Any good suggestion how to not query tags that are hidden? Right now we don't use any, but in theory your script would also query those and thus expose such posts to non-authorized users.

That's already taken care of. If you take a closer look at the sql, it has "a.is_private != 1".

Cheers,
bzt

[bzt]

Just for the records, with "date{"O")" and "NOW()", it was ok for me. With "+0000" and "CURRENT_TIMESTAMP" it is now off by timezone hours.

In description: "reply by poVoq 4 hours ago"
In the pubDate: "Wed, 09 Jun 2021 13:01:14 +0000"
In feed reader: "6/9/2021, 3:01:14 PM" with abs. time and "2 hours ago" in relative time.

I've cleared the cache and also downloaded the feed with curl, so I'm sure I get the latest uncached version. It would be great to know the exact mysql / mariadb configuration, what timezone it is configured for. Should be UTC, but maybe it is CEST?

Edit I've found a solution that makes it irrelevant what the database's timezone is. Please try this:

{l Code}: {l Select All Code}

-      " TIMESTAMPDIFF(SECOND, a.last_posted_at, CURRENT_TIMESTAMP()) as t, ".
-      " DATE_FORMAT(a.last_posted_at, '%a, %d %b %Y %T') as d FROM ".
+      " TIMESTAMPDIFF(SECOND, a.last_posted_at, NOW()) as t, ".
+      " DATE_FORMAT(CONVERT_TZ(a.last_posted_at, 'SYSTEM', 'UTC'), '%a, %d %b %Y %T') as d FROM ".


This way the TIMESTAMPDIFF are using the same zone for the field and the current time, so with NOW() the difference must be correct. If you see a different "ago" in your feed reader than what's in the description, then it is an issue with your reader.
And for the displayed date it is converted to UTC no matter what the configuration is, so it should be fine in your reader too.

Cheers,
bzt

[Julius]

Database is probably CEST, but didn't check.

I made the two changes as you outlined, but now the actual date in the feed reader is broken again as you can likely see.

Edit: and it doesn't validate: https://validator.w3.org/feed/check.cgi ... %2Frss.php

[Julius]

I just tested the private tags and they do show up in the rss feed. I guess another bugfix is needed for that. Thanks for looking into that as well

[Julius]

Looks like the originally proposed Flarum extenson got an update after all: https://discuss.flarum.org/d/27687-synd ... atom-feeds

I'll try it as an alternative later today.

[bzt]

Database is probably CEST, but didn't check.

Then the original code was correct.

I made the two changes as you outlined, but now the actual date in the feed reader is broken again as you can likely see.

Edit: and it doesn't validate: https://validator.w3.org/feed/check.cgi ... %2Frss.php

This is more than strange, CONVERT_TZ not working, are you kidding? Then the only solution is to go back to the original code (with "date("O")" and "NOW"), and for you to use a feed reader that can actually understand timezones in pubDate.

I just tested the private tags and they do show up in the rss feed. I guess another bugfix is needed for that. Thanks for looking into that as well

What do you mean by "tag"? There's no such thing in Flarum, there are discussions and posts. Both can be made hidden, is that what you mean by private tag? Private discussions are taken care of, I'm 100% sure of that. Anyway I've added more checks for the hidden fields too.

Here's the latest (using server local time and filtering out hidden records):

{l Code}: {l Select All Code}

<?php
/* ------------------ set this to the Flarum config file -------------------- */
$config = @include("path/to/flarum/config.php");
$limit = 20; /* return that many records at most */
/* -------------------------- configuration ends ---------------------------- */

if(!is_array($config) || !is_array($config['database']))
   die("Configuration not found.\n");

/***** connect to the database using the configuration in Flarum's config *****/
try {
   $pdo = new \PDO(
      $config['database']['driver'].":host=".$config['database']['host'].
      ";dbname=".$config['database']['database'].
      (!empty($config['database']['charset']) ?
         ";charset=".$config['database']['charset'] : ""),
      $config['database']['username'],
      @$config['database']['password'],
      [
         \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
         \PDO::ATTR_EMULATE_PREPARES => 0
      ]);
} catch(\PDOException $e) {
   die("Unable to connect to database: ".$e->getMessage()."\n");
}
if(!empty($config['database']['charset']))
   $pdo->exec("set names '".$config['database']['charset']."';");

/********************** get some data from the database ***********************/
try {
   $sql = "SELECT a.id, a.title, a.slug, a.last_posted_at, a.comment_count, b.username, ".
      " TIMESTAMPDIFF(SECOND, a.last_posted_at, NOW()) as t, ".
      " DATE_FORMAT(a.last_posted_at, '%a, %d %b %Y %T') as d FROM ".
      $config["database"]["prefix"]."discussions as a LEFT JOIN ".
      $config["database"]["prefix"]."users as b ON a.last_posted_user_id = b.id ".
      "WHERE b.id IS NOT NULL AND b.hidden_at IS NULL AND a.hidden_at IS NULL".
      " AND a.is_private != 1 AND b.is_private != 1 ".
      "ORDER BY a.last_posted_at DESC LIMIT ".$limit.";";
   $res = $pdo->query($sql);
   $rows = $res->fetchAll(\PDO::FETCH_ASSOC);
   if(method_exists($pdo, "close"))
     $pdo->close();
   else
     $pdo->db = null;
} catch(\PDOException $e) {
   die("Unable to query the database: ".$e->getMessage()."\n");
}

/*************************** output what we've got ****************************/
/* tell the clients not to query this url more than once per minute */
header("Pragma:cache");
header("Cache-Control:cache,public,max-age=60");
header("Connection:close");
header("Content-Type: application/rss+xml");
echo("<"."?xml version=\"1.0\" encoding=\"utf-8\"?".">\r\n");
?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom">
<channel>
 <title>FreeGameDev</title>
 <link><![CDATA[<?php echo($config["url"]); ?>]]></link>
 <description>Free Game Development</description>
 <atom:link href="<?php echo($config["url"]); ?>/rss.php" rel="self" type="application/rss+xml" />
<?php foreach($rows as $row) { ?>
  <item>
   <title><![CDATA[<?php echo(htmlspecialchars($row["title"])); ?>]]></title>
   <description><![CDATA[<?php
      echo(($row["comment_count"] < 2 ? "new topic" : "reply").
          " by ".htmlspecialchars($row["username"])." ");
      $t = $row["t"] + 0; $m = floor($t/60); $h = floor($t/60/60); $d = floor($t/60/60/24);
      if($t < 60)
         echo("just now");
      else if($t < 60*60)
         echo($m." minute".($m > 1 ? "s" : "")." ago");
      else if($t < 60*60*24)
         echo($h." hour".($h > 1 ? "s" : "")." ago");
      else
         echo($d." day".($d > 1 ? "s" : "")." ago");
      ?>]]></description>
   <guid><![CDATA[<?php echo(
      $config["url"]."/d/".$row["id"]."-".$row["id"]."-".
      urlencode($row["slug"]));
      ?>]]></guid>
   <pubDate><![CDATA[<?php
      echo(htmlspecialchars($row["d"]." ".date("O")); ?>]]></pubDate>
  </item>
<?php } ?>
</channel>
</rss>


Looks like the originally proposed Flarum extenson got an update after all: https://discuss.flarum.org/d/27687-synd ... atom-feeds

I'll try it as an alternative later today.

Whatever sails your boat that makes it possible to list new posts without enabling JS.

Cheers,
bzt

[Julius]

Your new version fails completely. Don't have time right now to try and find the error though. I left it up in the broken state for now.

[bzt]

Your new version fails completely. Don't have time right now to try and find the error though. I left it up in the broken state for now.

Aaaaargggh, copy'n'paste bug... One less closing parenthesis.

{l Code}: {l Select All Code}

-      echo(htmlspecialchars($row["d"]." ".date("O")); ?>]]></pubDate>
+      echo(htmlspecialchars($row["d"]." ".date("O"))); ?>]]></pubDate>


Cheers,
bzt