[utils/fetch_reports.php] Invalid datetime format

[williamdes]

utils/fetch_reports.php
Error: Failed to insert the report (-1)
dmarc-srg [error]: PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '53552-03-06 13:43:36' for column `dmarc-srg`.`dmarc-srg_reports`.`begin_time` at row 1 in /var/www/html/classes/Database/Mariadb/ReportMapper.php:190
Stack trace:
#0 /var/www/html/classes/Database/Mariadb/ReportMapper.php(190): PDOStatement->execute()
#1 /var/www/html/classes/Report/Report.php(72): Liuch\DmarcSrg\Database\Mariadb\ReportMapper->save(Array)
#2 /var/www/html/classes/Report/ReportFetcher.php(110): Liuch\DmarcSrg\Report\Report->save('sfr.fr!<domain>...')
#3 /var/www/html/utils/fetch_reports.php(150): Liuch\DmarcSrg\Report\ReportFetcher->fetch()
#4 {main}

Sent by a French operator (SFR) from the top 10 operators in France.

<feedback 
xmlns:tns="http://dmarc.org/dmarc-xml/0.1" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <version>1</version>
    <report_metadata>
        <org_name>sfr.fr</org_name>
        <email>dmarc-reporting@sfr.fr</email>
        <report_id>5d724848-4dd4-4466-a614-71cd081442ed</report_id>
        <date_range>
            <begin>1627776308616</begin>
            <end>1627776308616</end>
        </date_range>
    </report_metadata>
    <policy_published>
        <domain>domain.tld</domain>
        <adkim>r</adkim>
        <aspf>r</aspf>
        <p>none</p>
        <fo>0:1:d:s</fo>
    </policy_published>
    <record>
        <row>
            <source_ip>54.240.7.38</source_ip>
            <count>1</count>
            <policy_evaluated>
                <disposition>none</disposition>
                <dkim>pass</dkim>
                <spf>pass</spf>
            </policy_evaluated>
        </row>
        <identifiers>
            <envelope_from>mail.domain.tld</envelope_from>
            <header_from>domain.tld</header_from>
        </identifiers>
        <auth_results>
            <dkim>
                <domain>domain.tld</domain>
                <selector>wdbu6yuuwpqajqxubnutceaf27g2kmsa</selector>
                <result>pass</result>
                <human_result>good signature</human_result>
            </dkim>
            <spf>
                <domain>mail.domain.tld</domain>
                <result>pass</result>
            </spf>
        </auth_results>
    </record>
</feedback>

Did the code get it wrong or did they get the timestamp handing wrong ? Report-ID: 2021-08-04-03-01-14-9329 Some years ago, but still we could handle this nicely ?

[liuch]

It looks like a timestamp with milliseconds, what uses in JavaScript for instance. But RFC says it must be: "DIGIT seconds since 00:00:00 UTC January 1, 1970 indicating start of the time range contained in the report". When I drop the last three digits, I get August 2021. The report is clearly wrong. I don't think such a report should be accepted. But my code reacts inadequately, I agree with you. I will add handling for this error. Thank you.

[williamdes]

They probably fixed it since then or stopped the service, strangely I can not find any other reports. I bet my client still has sfr emails as users.

Thank you in advance for handling this error

[williamdes]

The fix works perfectly 🚀

Failed to get incoming report:
  Error message:
    - Failed to add an incoming report: wrong date value
  Report ID: 5d724848-4dd4-4466-a614-71cd081442ed
  Email message metadata:
    - From:    dmarc-reporting@sfr.fr
    - Date:    Wed, 4 Aug 2021 03:01:14 +0200 (CEST)
    - Mailbox: DMARC (DMARC-SRG)

[williamdes]

That said, the error would not help me a lot if I had to find the corresponding email :) Could you change the output ?

- Report ID: 5d724848-4dd4-4466-a614-71cd081442ed
+ Report ID: 5d724848-4dd4-4466-a614-71cd081442ed (2021-08-04-03-01-14-9329)

Or maybe including the email subject line is easier for you since fetching the report Id requires some parsing

Sorry for this feature request out of an issue

Edit: nevermind, the report Id is from the XML file.. 😄

[liuch]

That said, the error would not help me a lot if I had to find the corresponding email :)

Date: Wed, 4 Aug 2021 03:01:14 +0200 (CEST) Isn't that not enough to find the corresponding email?

• Report ID: 5d724848-4dd4-4466-a614-71cd081442ed (2021-08-04-03-01-14-9329)

Where should the script take this date from if the report contains incorrect dates?

Maybe I didn't get your idea, sorry then.

[williamdes]

Isn't that not enough to find the corresponding email?

Hmm, yes, Indeed 🤦🏻

Where should the script take this date from if the report contains incorrect dates? Maybe I didn't get your idea, sorry then.

My mistake, it's in the email header but I did not provide it until then: Report Domain: domain.tld Submitter: sfr.fr Report-ID: 2021-08-04-03-01-14-9329