pythonpython-3.xencryptionsmime

Extract Excel file from a multipart/signed email attachment


I need to extract an Excel email attachment from a signed email that I fetch via Microsoft Graph.

Postman shows the contentType as multipart/signed and the name as smime.p7m so I guess I somehow need to unpack this attachment first.

Can you help me figure out what the general procedure in such cases would be and maybe what python packages can deal with it and turn it into an Excel file?


Solution

  • You know an email has been digitally signed and content has been encrypted for security when you receive it with a multipart/signed content type. You must first validate the digital signature on the email before extracting and decrypting the encrypted content in order to extract the Excel email attachment from the signed email.

    The general procedures are as follows:

    Get the signed email using the Microsoft Graph API.

    Extract the email's MIME content. To parse the MIME content and extract the attachment, use a Python MIME library like the email package.

    Verify the digital signature and decrypt the content using a Python library like M2Crypto or OpenSSL.

    The decrypted data should be saved as an Excel file.

    import base64, email
    from M2Crypto import SMIME, X509, BIO
    
    
    msg1 = email.message_from_string(mime_content)
    
    # extract the attachment
    attachment = None
    for part in msg1.walk():
        if part.get_content_type() == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet':
            attachment = part.get_payload(decode=True)
            break
    
    if attachment is None:
        print("Attachment not found")
    else:
        # verify the digital signature and decrypt the content
        p7 = SMIME.load_pkcs7_bio(BIO.MemoryBuffer(mime_content))
        signers = p7.get0_signers(X509.X509_Stack())
        if len(signers) == 0:
            print("No signers found Pass.")
        else:
            signer = signers[0]
            bio1 = BIO.MemoryBuffer(str(attachment))
            smime = SMIME.SMIME()
            smime.set_x509_store(SMIME.X509_Store())
            smime.set_x509_stack(X509.X509_Stack())
            smime.load_key('path/to/private_key.pem', 'path/to/certificate.pem')
            dec = smime.decrypt(bio1, signer)
            excel_content = base64.b64decode(dec)
            # save the decrypted content as an Excel file
            with open('path/to/excel_file.xlsx', 'wb') as f:
                f.write(excel_content)
            print("Excel file saved successfully")