×
Kunena 5.1.14 Released - Security release (13 Aug 2019)

The Kunena team is proud to announce the arrival of Kunena 5.1.14 [K5.1.14] which is now available for download as a native Joomla extension for Joomla 3.9.x. This version addresses most of the issues that were discovered in K 5.1 and issues discovered during the development stages of K 5.1.14. This update fixed 1 security issue.

× Topics must relate to a currently supported version of Kunena. If you are unsure what is the current supported version of Kunena, please go to the download page.

If you are having problems then, for your own benefit, it would save us all a lot of time if you would kindly post your configuration report when you ask for help from this forum. If you do not post your configuration report we will not ask you for it but you will probably not get your problem solved, either.

Question Deleting orphaned attachments

More
6 months 2 weeks ago #1 by tcn
I went through the diagnostics and encountered a few errors. Not wanting to just delete items, that had no option to fix it, I dove into the code (diagnostics.php) and looked at the queries behind the diagnostic items that showed a failed status.

I executed the queries on the database to get a better insight. Most items related to the welcome topic, which we somehow managed to abandon during migration. These I deleted, not being worried.

However, we also have five orphaned attachments. I dug into these as well. The files were still present on the file system by their hashed (?: fb75b6038ea1318f5b5adf436fed9a55) name. For three of the files, all from the same user, I found corresponding error messages (FATAL ERROR, no further details) in the log. For the other two, by another user, there were no error messages. I assume the user aborted his post.

I'm fine with deleting the entries using the diagnostics screen. However, If my understanding of the code is correct, it will only delete the orphaned entries from the database. Shouldn't the files also be deleted? After all, they are orphaned. Over time this could accumulate and it would be very hard to determine is these files are still relevant with the database references deleted.

On a site note, but this probably belongs in a feature request, wouldn't it be helpful, if the content of the results of the diagnostic queries for failed items could be inspected? Show the user what has been found, especially if deletion is the only available option. For attachments, this should allow the user to view or download them and show information about the file type.

My modified query to get the information I needed, looked like:
SELECT a.id, a.mesid, a.userid, u.name, a.folder, a.filename, a.filetype FROM jml_kunena_attachments AS a
    LEFT JOIN jml_kunena_messages AS m ON a.mesid=m.id
    INNER JOIN jml_users AS u ON a.userid=u.id
WHERE m.id IS NULL
Tells me who's attachments have been orphaned and what the file type is. I know I took a shortcut, knowing that our user information is stored in jml_users.
The following user(s) said Thank You: xillibit

Please Log in or Create an account to join the conversation.

More
6 months 2 weeks ago #2 by rich
Replied by rich on topic Deleting orphaned attachments

Tells me who's attachments have been orphaned and what the file type is.

Maybe I understand this question not correct. With your database query you get all this informations. User ID, File type, name and path to the files.
A orphaned attachment may arise when a user creates a post and abort the action without click on the Cancel button.

Please Log in or Create an account to join the conversation.

More
6 months 2 weeks ago #3 by tcn
Replied by tcn on topic Deleting orphaned attachments
I think we are on the same page. ;) I just overloaded you with information. Sorry.

The query executed by the diagnostics for attachmentOrphaned is:
SELECT a.id, a.mesid, a.userid, a.folder, a.filename FROM jml_kunena_attachments AS a
    LEFT JOIN jml_kunena_messages AS m ON a.mesid=m.id
WHERE m.id IS NULL
That doesn't tell me what file type the attachment is nor which user it belongs to. The user id is not very helpful. That's why I modified the query to get that information as well.

Moreover, it appears the attachments are uploaded as soon as the user selects them from local storage. Otherwise Kunena wouldn't know what ID to assign to the attachment. Since the file is already on the server, it needs to be cleaned up together with the database entries referring to the file. Otherwise, over time, the file system on the server will fill up with orphaned files. Once the database entries are cleaned, which is what you can do from the diagnostics, it's hard to tell if the file is still needed looking just at the file system. You get files named like e.g. fb75b6038ea1318f5b5adf436fed9a55. With the Linux file command you will still be able to tell what the file type is, but not if it belongs to a post.

Long story short, cleaning up the database, in case of orphaned files, is not enough. You need to clean up the files themselves as well.

But, before I'm inclined to do so (I'm always hesitant deleting stuff), I need more information. Having the username allows me to skim through the logs. Being able to view the file, allows me to determine the importance of the file to some extent.

For all the diagnostic options, the user should be allowed to examine the query results, if any. I gave the example of the welcome post, which also showed up in diagnostics. From the diagnostics interface, I'm unable to determine any information about what I'm about to delete. That's hazardous, I dare say.

With the query results available, I would have seen instantly that the broken item was about the welcome post.

Sorry, I did again. Information overload. ;)

Please Log in or Create an account to join the conversation.

More
6 months 2 weeks ago - 6 months 2 weeks ago #4 by rich
Replied by rich on topic Deleting orphaned attachments
Since 2016 Google is giving warnings/bann’s for outdated information like missing topics/categories/attachments. Thats why we have the Diagnostics page.
When you remove correctly any attachment, or delete a forum message permanently, it will be removed also on the server. Before only the database item was deleted. So far so good.

The problem is now, when a user abort such as with the browser back and forward button, or not saves the message, then the uploaded file is orphaned.
It's very hard to find this posts, provided that they exist at all. As you can see with your database query, it shows message id 0 at all rows with orphaned files.

Once the database entries are cleaned, which is what you can do from the diagnostics, it's hard to tell if the file is still needed looking just at the file system. You get files named like e.g. fb75b6038ea1318f5b5adf436fed9a55.

Yourself want it so. You have chosen "Protect Attachments = Yes" in the configuration. This feature protect attachment files from direct access by entering a URL such as websiteaddress/media/kunena/attachments/image.png, therefore are the file types and names encrypted.
In my opinion is it not needed, the user have access to the files anyway via the forum.

Long story short, cleaning up the database, in case of orphaned files, is not enough. You need to clean up the files themselves as well.

Yes, I agree with you, but how that should work it's a mystery to me.
Last edit: 6 months 2 weeks ago by rich.

Please Log in or Create an account to join the conversation.

More
6 months 1 week ago #5 by tcn
Replied by tcn on topic Deleting orphaned attachments
First of all, thank you for you reply, Rich. I appreciate your support.

I think the diagnostics is a great thing. It gives you insight into any issues and helps you tackle them. We wouldn't have this discussion without it. I would probably not even know about orphaned attachments. Yet, I think the diagnostics can be improved further.

You have chosen "Protect Attachments = Yes"

Absolutely! Choices will differ from use case to use case. Protected for me means, protected from unauthorized access. Think about scripted access, for example. Once you know where to look, it would not be too hard to pull the attachments from your server using a script. We also have restricted areas in our forum. Attachments uploaded there, are definitely off limits for the general public.

Yes, I agree with you, but how that should work it's a mystery to me.

Well, all the information is there, right in front of you. In pseudo code it goes something like:
get orphanedAttachments($DB)
for each orphanedAttachment do
  FILE=${KUNENA_ROOT_DIR}/${DB_FOLDER}/DB_FILENAME
  if exist ${FILE}
    delete ${FILE}
  fi
end for
clean orphanedAttachments($DB)
That's it! All done. Database cleaned, orphaned files removed from storage.

Please Log in or Create an account to join the conversation.

More
6 months 1 week ago #6 by rich
Replied by rich on topic Deleting orphaned attachments

Absolutely! Choices will differ from use case to use case. Protected for me means, protected from unauthorized access.

Yes I agree, the choices will differ from use case to use case. But many users choose this option because this is available and the word Protect contain.
And if you choose to do so, you have encrypted files on your server.
There arealso problems with pdf files in messages, so it better if you do not allow pdfs, when you use the protect option.

Well, all the information is there, right in front of you. In pseudo code it goes something like:
code ....

Now I have to say thank you.

Please Log in or Create an account to join the conversation.

More
6 months 1 week ago - 6 months 1 week ago #7 by tcn
Replied by tcn on topic Deleting orphaned attachments

And if you choose to do so, you have encrypted files on your server.

Encrypted? I guess you meant obfuscated. I pulled the orphaned files from the server. Linux recognized them for what they are. The file command looks at the MIME type of the file. I renamed them and had no problem opening the files. Something I wouldn't be able to do with encrypted files without the encryption key.

There arealso problems with pdf files in messages, so it better if you do not allow pdfs, when you use the protect option.

Really? :huh: Well, good to know. PDF files aren't uploaded very often on our forum, but they are permitted. It would be helpful if that would be mentioned somewhere. Maybe the description of that option should be changed, then. It does not mention any encryption, either. It was the description that made me enable it.

Now I have to say thank you.

You are welcome! Glad I could help.
Last edit: 6 months 1 week ago by tcn.

Please Log in or Create an account to join the conversation.

More
6 months 1 week ago #8 by rich
Replied by rich on topic Deleting orphaned attachments

I guess you meant obfuscated.

Sorry, yes I meant the same.

Linux recognized them for what they are.

Yes I know it, I'm a Ubuntu user. Linux has no problem with this files.

Well, good to know. PDF files aren't uploaded very often on our forum, but they are permitted. It would be helpful if that would be mentioned somewhere.

It's a bug, please see here: github.com/Kunena/Kunena-Forum/issues/6171

Please Log in or Create an account to join the conversation.

More
6 months 1 week ago #9 by tcn
Replied by tcn on topic Deleting orphaned attachments
Thanks again for the heads up. I turned off protected attachments for now. Will keep an eye on the bug.

Please Log in or Create an account to join the conversation.

  • Not Allowed: to create new topic.
  • Not Allowed: to reply.
  • Not Allowed: to edit your message.
Time to create page: 0.133 seconds