1. Janos SUTO Avatar Janos SUTO
  2. Piler
  3. piler
  4. Issues

IMAP users cannot view messages missing an email address in "To:" field

Issue #698 closed
Jason Mulholland created an issue

piler 1.1.1, build 904 fresh install, no upgrades

We have some email that we imported using pilerimport that unfortunately did not have proper email addresses in the "To:" fields, but rather the personal names only.

For example, "From: foo@example.com" sent to "To: John Doe"

When user foo@example.com logs into Piler using his IMAP credentials, such messages do show up in a search result (though with an empty "To:" field), but he cannot view the actual message and only sees "no permission for XXXX". Auditor accounts are still able to see these messages though.

Given that foo@example.com was the sender of the message, why can't he view it?

Comments (17)

  1. eXtremeSHOK

    Can you provide a header of one of the emails you tried to import ?

    If the emails are from an ms exchange server, you will need to configure the exchange server and re-import.

  2. Jason Mulholland reporter

    I am importing emails into Piler coming from an Exchange server, but the problem messages seem to be earlier messages that were originally imported to our Exchange server from Google Apps mail when we migrated from that over a year ago. Otherwise, there seem to be no problems with later messages that were originally received by the Exchange server.

    I am doing PST exports from Outlook and then using readpst to convert to .eml as shown in the Piler wiki.

    I will send you an example message header privately.

    We do not host our own Exchange server, so we have limited options for any configuration.

  4. Janos SUTO repo owner

    This is not a bug, at least not in piler. The piler GUI applies a filter based on the email addresses of the given user logged in (unless he's an auditor), and compares the sender and recipient addresses of the message to the email filter mentioned. The problem is that the message in question has no To: address, just a name which piler just can't use, so the gui thinks that this message doesn't belong to this user.

    You may try to fix this particular message by adding an entry to the rcpt table, eg.

    insert into rcpt (id, to,todomain) values(123, 'email@address', 'address'). The id field should be the same as in the metadata table for this message. Let me know if it fixes the problem.

  5. Jason Mulholland reporter

    I still don't understand why "foo@example.com" should not be allowed to view a message that he sent (i.e. "From:" is "foo@example.com"), regardless of what the recipients are (or not). I don't think the recipients, or lack thereof should matter here. He should be entitled to see it.

    That's fine with me if this isn't considered a bug, but I don't agree that this is how this should work. Design flaw IMHO.

    I have many messages that are like this and possibly more in the future. I don't think manually fixing all such messages in the database as suggested is very practical.

  6. Jason Mulholland reporter

    FYI, I've done what you asked by adding the missing recipient email address in the 'rcpt' table for an affected message. Adding it does allow that message to then be viewed by the sender user.

    Looking into the code shows me that permission to view a message by a non-auditor user is determined by checking for its existence in the 'v_messages' view, which is an inner join of the 'metadata' and 'rcpt' tables. Without an corresponding entry in the 'rcpt' table, the message will appear not to exist when it actually does (as far as the view is concerned).

  8. Janos SUTO repo owner

    Yes, that's it exactly how it works. Instead of sending 2 queries (metadata for sender, and rcpt for recipients) I use a single query to get both. Valid messages have both from and to addresses.

  9. Jason Mulholland reporter

    Then I think Piler needs a way to reimport messages (overwriting), especially over messages considered 'invalid'. I've looked, but I don't see a way. I tried reimporting from a different export (not using PST), but the messages get discarded as duplicates. Otherwise I'd have to fiddle around in the database trying to fix or purge several thousand messages.

  10. eXtremeSHOK

    You have a case of JIJO (junk-in junk-out) with your data.

    The headers are missing and the to: field has been obfuscated to just a display name. These emails are actually invalid.

    I offered to help you with your issues with a custom importer to fix/reconstruct your mails.

    This is not a piler issue, its purely an issue with your data.

  11. Jason Mulholland reporter

    Yeah, I got it. Data's fault.

    Yes, I got your PMs. You asked me to send a sample message header. I sent it to you. Then you sent me some Youtube links to videos set to techno music supposedly showcasing your own software to fix my current woes, with all kinds of lines flying past in a command shell. Sorry, I really didn't get it. There is also no reference to your software within any Piler material. I've no reason to trust it.

    Maybe it's not clear, but I've been attempting to make constructive suggestions to help improve the usability of Piler in light of my little dilemma. Such as, at least allowing the sender to see his own 'invalid' messages, or to allow for reimporting messages. I could also add that the importer should probably reject or indicate 'invalid' messages instead of quietly accepting them. But nobody seems very interested in really discussing anything. So, if we'd rather just defend software and blame the user then I'll just consider this issue closed.

    Thanks.

  12. eXtremeSHOK

    Everything you require is actually in the MySQL database.

    As per Junos reply, It is simply a case of replacing the "Some Name" records in the rcpt table with "user@email.com".

    ` UPDATE rcpt SET to = 'john@doe.com' WHERE to = 'John Doe' COLLATE utf8mb4_bin;

    As to my credintials.. 30seconds on github and bitbucket.

  13. Jason Mulholland reporter

    That is simply not how Piler works.

    The "rcpt" table contains strictly email addresses. In the event of missing or malformed email recipients, Piler does not instead create entries with the personal names. No corresponding entry will be added to "rcpt" whatsoever. If there is not one single proper email address recipient for a message, then there will be zero "rcpt" entries for it.

    In fact, the personal names are nowhere to be found in the MySQL database. Those are only located within the message files. So, to perform a quick and tidy UPDATE in the database like you're suggesting is not even possible.

    Janos did not suggest doing an UPDATE, but rather an INSERT. That's fine for just a handful of messages, but not for a few thousand. They'd have to be done by cross-referencing messages in the GUI and then fixing them in the DB one at a time.

  14. Janos SUTO repo owner

    We don't have to be difficult on this issue. The problem origins from the invalid To header, and I still haven't decided how to fix it. The best fix could be if it were possible to regenerate the message with the correct headers. Otherwise not a reimport needed, rather some fix for the rcpt table. Eg. fixmissingrecipient -d /path/to/emlfiles -r usethis@address.for.missing.to

    However, this may work if you run this utility on a single mailbox's emails only, otherwise you may give access to others' emails. Let me know if this would work for your environment, and if so, we may come up with a tool to fix it. Btw. Adrian is my associate in the piler project. I assume he tried to fix the problem for you.

  18. Log in to comment
Assignee
Type
proposal
Priority
minor
Status
closed
Votes
0
Watchers
2
Jira: the preferred issue tracker for Bitbucket. Join the team!