Posts: 3
Threads: 1
Joined: Jan 2022
Hi All
I am a old fella in need of some help,
can I put some background first.
We have a bespoke support ticketing system, that uses a Python Script to pick up mail from a mailbox, it was written years ago by our then eccentric dev guy, back in about 2000 ish, been updated once since then to Python 2.7,
We have decided to move all our systems to AWS including a move to Amazon Workmail, this is when the fun starts, the old 2.7 stuff was all written for POP3 as the mailbox system, at this point I would like to point out that our dev guy has since become a covid victim, and is no longer with us, and all this was in his head, no documentation.
so I have very little coding experience, a couple of Pi projects, so the boss has decided its my problem to fix!
I have managed to get the script collecting from Amazon IMAP and posting to the Support system database, but I am having a huge problem with Base64 encoded emails, they all end up as a page of rubbish, it's at this point I am lost.
can anyone help me fix the decode part of the script so we can process these insane HTML emails that are a security risk anyway, but we can't tell every one text only emails
I can provide samples of the old 2.7 pop script, and where I have got to with the newer 3.8 script for IMAP, I decided it was time to update at the same time, we also have a load of old PHP5.3 stuff that I have to upgrade too,
thanks very much in advance,
Posts: 1,822
Threads: 2
Joined: Apr 2017
Since this is something your business needs, it would be in your interests to actually hire someone that could give it the attention it deserves. It's one thing for some kind volunteers to help out where they can, but they likely won't have the time to take this on as a full-time project.
Posts: 3
Threads: 1
Joined: Jan 2022
HI
the last time this code was changed was 13 years ago, so not really a full time project, it just fixing the base64 decode of emails,
but never mind I will continue on without help, I will fix it in the end, and it wont get touched again till long after I retire, in 2 years time
Posts: 8,090
Threads: 154
Joined: Sep 2016
(Jan-14-2022, 04:21 PM)Waylander Wrote: but never mind I will continue on without help, I will fix it in the end I think you misunderstood @ ndc85430 [well-intended] advice. That said - you didn't show anything specific you need help with, just background information and general overview of the problem you have. If you really want help - show some code (not too much, not too little, just enough to demonstrate the problem), ask about specific questions.
BashBedlam likes this post
Posts: 3
Threads: 1
Joined: Jan 2022
Jan-14-2022, 05:38 PM
(This post was last modified: Jan-14-2022, 05:42 PM by Yoriz.)
Hi
Ok I can post all the code with the users and passwords removed, i got the impression from the reply that employ someone as you dont help with business questions
the Current Script
#! /usr/bin/python3
# eSecuure Reponse Ticketing Backend
import hashlib
import email
import mimetypes
import os
import poplib
import re
import smtplib
import time
import sys
import base64
import imaplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from time import strftime
import datetime
now = datetime.datetime.now()
import pymssql
#from pymssql import _mssql
from configobj import ConfigObj
from email.utils import formatdate
from imaplib import IMAP4_SSL
from hashlib import sha256
ini = ConfigObj("E:\Python39\checkMail-imap.ini")
# setup config vars
mailini = ini["Mail Server"]
mailPOPServer = mailini['mailPOPServer']
mailSMTPServer = mailini['mailSMTPServer']
mailIMAPServer = mailini['mailIMAPServer']
mailUser = mailini['mailUser']
mailPass = mailini['mailPass']
global mailFromAddress
mailFromAddress = mailini['mailFromAddress']
dbini = ini['Database Server']
dbServer = dbini['dbServer']
dbUser = dbini['dbUser']
dbPass = dbini['dbPass']
dbName = dbini['responseDB']
portaldbServer = dbini['portaldbServer']
portaldbUser = dbini['portaldbUser']
portaldbPass = dbini['portaldbPass']
portaldbName = dbini['portaldbName']
dbPort = ":1433"
fileini = ini['File Locations']
global fileStore
fileStore = fileini['saveFiles']
mimeini = ini['Mime Types']
allowedMime = mimeini['permittedFiles']
t1 = time.time()
# quick test to see what os we are running on
if os.name == "posix":
dbServer = dbServer + dbPort
con = pymssql.connect(host=dbServer, user=dbUser, password=dbPass, database=dbName)
cur = con.cursor()
con2 = pymssql.connect(host=portaldbServer, user=portaldbUser, password=portaldbPass, database=portaldbName)
cur2 = con2.cursor()
imapi = imaplib.IMAP4_SSL(mailIMAPServer)
imapi_user = 'xxxxxxxxxxxxxx'
imapi_pass = 'xxxxxxxxx'
## login to server
print("Logging into mailbox...")
resp_code, response = imapi.login(imapi_user, imapi_pass)
print("Response Code : {}".format(resp_code))
print("Response : {}\n".format(response[0].decode()))
### Completed Config ... Now skip to the end where we do stuff
def sumfile(fobj):
'''Returns an md5 hash for an object with read() method.'''
m = hashlib.new()
while True:
d = fobj.read(4096)
if not d:
break
m.update(d)
return m.hexdigest()
def hashlibsum(fname):
'''Returns an md5 hash for file fname, or stdin if fname is "-".'''
if fname == '-':
ret = sumfile(sys.stdin)
else:
try:
f = file(fname, 'rb')
except:
return 'Failed to open file'
ret = sumfile(f)
f.close()
return ret
def storeFile(part, type, jobNumber, inboxid):
# stores the files that match our allowed mime types list in the folder set in the .ini file
# starts out by grabbing the file and matching the declared mime type to the file extension's mime type
# if it matches then it checks against a list of permitted mime types before:
# storing the file and inserting a record into the database
targetDirName = strftime("%Y%m%d")
createTargetDir = os.path.join(fileStore, targetDirName)
if not os.path.exists(createTargetDir):
os.makedirs(createTargetDir)
typ = part.get_content_type()
maintype = part.get_content_maintype()
if (maintype != "text" and maintype != "multipart" and maintype != "message"):
# we have an attachment!
fileName = part.get_filename()
print
"Filename:"
print
fileName
if fileName == None:
fileName = "Unknown.bad"
fileName = fileName.replace('\\', '')
try:
actualExt = fileName.rsplit(".", 1)
except (AttributeError):
actualExt = ["none", "bad"]
guessExt = mimetypes.guess_extension(part.get_content_type())
print
guessExt
if (guessExt != None):
guessExt = guessExt.replace('.', '')
try:
extFound = actualExt[1] in allowedMime
except (IndexError):
extFound = guessExt
print
guessExt
allowedFile = 0
if (extFound == True): # message is ok - store it
allowedFile = 1
print
"inboxid: %s end\n" % (inboxid)
inboxid = '%d' % (inboxid)
filePath = "%s\\" % (createTargetDir)
try:
fp = open(filePath + fileName + "_" + inboxid, "wb")
except IOError:
try:
print
fileName
fileNameParts = fileName.split("?")
fileName = fileNameParts[3]
fp = open(filePath + fileName + "_" + inboxid, "wb")
except (AttributeError):
print
"Attachment Error"
fileName = "unknown.bad"
fp = open(filePath + fileName + "_" + inboxid, "wb")
fp.write(part.get_payload(decode=1))
fp.close()
fileName = (filePath + fileName + "_" + inboxid)
fileHash = hashlibsum(fileName)
cur.execute("INSERT INTO attachments VALUES ('%s','%s','%s','%s','%s',%d,'%s');" % (
inboxid, jobNumber, filePath, fileName, typ, allowedFile, fileHash))
con.commit()
print
"Attachment %s stored" % (fileName)
return
def sendEmail(emailTo, emailSubj, emailMessage):
emailMsg = MIMEMultipart()
emailFrom = mailFromAddress
emailMsg['From'] = mailFromAddress
emailMsg['To'] = emailTo
emailMsg['Date'] = formatdate(localtime=True)
emailMsg['Subject'] = emailSubj
emailMsg.attach(MIMEText(emailMessage))
smtp = smtplib.SMTP(mailSMTPServer)
if emailTo == None:
return
else:
smtpresult = smtp.sendmail(emailFrom, emailTo, emailMsg.as_string())
smtp.close()
print
"Email sent to %s" % (emailTo)
return
return
def createTicket(fromAddress, msgSubj, msgBody, mailParseable):
# creates a new email
msgType = "mail"
msgNew = 1
jobNumber = ""
print
len(msgBody)
dievar = 0
# if len(msgBody) > 15000:
# msgBody = msgBody[0:15000]
cur.execute("INSERT INTO inbox VALUES (%s,%s,%s,%s,getDate(),%s,%d,%d,%d);",
(jobNumber, fromAddress, msgSubj, msgBody, msgType, msgNew, 0, 0))
inboxid = cur.lastrowid
# print "lastrow (crticket): %d end\n" % (inboxid)
con.commit()
# grab the ID inserted so we can log against the inbox record ... which sucks
# query = "SELECT @@IDENTITY As 'Identity'"
# cur.execute(query)
# con.commit()
# inboxid = (cur.fetchone())[0]
print
"Message added to inbox"
return inboxid
def addTicket(fromAddress, msgSubj, msgBody, jobNumber, mailParseable):
# adds an email to a job
msgType = "mail"
msgNew = 3
# need to check that job number exists and if not then BREAK!!!!
query = "SELECT * FROM jobs WHERE id = %s" % (jobNumber)
# print query
cur.execute(query)
cur.fetchall()
print
"rowcount = %d aargh" % (cur.rowcount)
if cur.rowcount == 0:
con.commit()
print
"Unknown - creating new message"
inboxid = createTicket(fromAddress, msgSubj, msgBody, mailParseable)
return inboxid
else:
con.commit()
cur.execute("INSERT INTO inbox VALUES (%s,%s,%s,%s,getDate(),%s,%d,%d,%d);",
(jobNumber, fromAddress, msgSubj, msgBody, msgType, msgNew, 2, 0))
# print "INSERT INTO inbox VALUES (%s,%s,%s,%s,getDate(),%s,%d,%d,%d);",(jobNumber,fromAddress,msgSubj,msgBody,msgType,msgNew,2,0)
inboxid = cur.lastrowid
# print "lastrow (addticket): %d end\n" % (inboxid)
con.commit()
# grab the ID inserted so we can log against the inbox record ... which sucks
# query = "SELECT @@IDENTITY As 'Identity'"
# cur.execute(query)
# con.commit()
# inboxid = (cur.fetchone())[0]
# ok so the message is logged against the job - now I need to send a mail to the enginner in charge
# sendEmail(emailTo,emailFrom,emailSubj,emailMessage)
query = "SELECT assigned_to FROM jobs WHERE ID='%s'" % (jobNumber)
print
query
cur.execute(query)
# con.commit()
engID = (cur.fetchone())[0]
query = "SELECT email FROM responseusers WHERE ID = %d;" % (engID)
cur2.execute(query)
# con2.commit()
rows = cur2.fetchall()
engEmail = None
for row in rows:
engEmail = row[0]
if engEmail != None:
break
print
engEmail
emailSubj = "An update to job [e%s] has been received" % (jobNumber)
emailMessage = "The call log system has been updated with a new client email\nMessage is:\n%s\n\nGo to http://esecure.evolve-online.com/response/jobdetails.php?id=%s to view this message" % (
msgBody, jobNumber)
if engEmail != None:
sendEmail(engEmail, emailSubj, emailMessage)
print
"Message added to job %s" % (jobNumber)
query = "UPDATE jobs SET state = 1 WHERE ID = %s;" % (jobNumber)
cur.execute(query)
con.commit()
return inboxid
def getMail():
resp_code, mail_count = imapi.select(mailbox="INBOX", readonly=False)
result, data = imapi.uid('search', None, "SEEN")
uidList = data[0].split()
resp_code, mails = imapi.search(None, 'UNSEEN')
mail_ids = mails[0].decode().split()
print("Total Mail IDs : {}\n".format(len(mail_ids)))
for mail_id in mail_ids[:2]:
resp_code, mail_data = imapi.fetch(mail_id, '(RFC822)') ## Fetch mail data.
#if the value mail_data is blank then jump to the end
try:
msg = email.message_from_bytes(mail_data[0][1]) ## Construct Message from mail data
except (UnboundLocalError):
#print("No Data to Contsruct Message")
return
#return cleanup()
#print ("Message %s\n%s\n" % (mail_ids, data[0][1]))
numMessages = len(mail_count) # sets the number of messages to play with
#print ("Found %s new messages" % (numMessages))
# print numMessages
# let's build us some regexes
re_from = re.compile(r"^(From)")
re_to = re.compile(r"^(To)")
re_subj = re.compile(r"^(Subject)")
re_jobNumber = re.compile(r"[\[]+E+[\d{6}]+[\]]",
re.IGNORECASE) # this one is eeeeevil but grabs the job number from the subject
re_fromAddress = re.compile(r"\W[A-Z0-9._%-\'\+]+@[A-Z0-9.-]+\.[A-Z]{2,6}\W",
re.IGNORECASE) # tries to get the pure email address from the From header
# start to loop through each message returned
if numMessages > 0:
print
("Processing emails")
for msg in mails:
print
("-" * 40)
#msgNum = int(msg.split(" ")[0])
#msgSize = int(msg.split(" ")[1])
#mailRaw = string.join(pop.retr(msgNum)[1], "\n")
mailParseable = email.message_from_bytes(mail_data[0][1])
msgFrom = mailParseable.__getitem__('From')
msgRecv = mailParseable.__getitem__('Received')
msgSubj = mailParseable.__getitem__('Subject')
if msgSubj == None:
msgSubj = "No Subject"
msgSubj = msgSubj[0:254]
msgTo = mailParseable.__getitem__('To')
#print("Message: %d, Size: %d bytes" % (msgNum, msgSize))
print("From: %s" % msgFrom)
print("Subj: %s" % msgSubj)
msgBody = ""
if mailParseable.is_multipart():
print ("Mail is Multipart")
for part in mailParseable.walk():
typ = part.get_content_type()
if typ and typ.lower() == "text/plain":
# Found the first text/plain part
msgBody = part.get_payload()
break
else:
msgBody = mailParseable.get_payload()
# jobNumber
tmp_jobNumber = re_jobNumber.search(msgSubj) # attempts to match the job number from the subject
# pass through a quick try block as this will throw an exception if the match does't work
# it took me the best part of an hour to work that out :-)
try:
jobNumber = tmp_jobNumber.group() # uses the group operator to extract the contents of the regex result
jobNumber = jobNumber.replace('[', '')
jobNumber = jobNumber.replace(']', '')
jobNumber = jobNumber.replace('e', '')
jobNumber = jobNumber.replace('E', '')
isNew = 0 # YES!!! You have got it right and this is a reply to an existing job ticket
except (AttributeError): # only handles AttributeErrors - anything else will blow up
isNew = 1 # DOH! Computer says no ... no match on the regex so we're dealing with a new job
jobNumber = ""
# INSERT CLOSED JOB DETECTION HERE
if isNew == 0:
query = "SELECT engclose FROM jobs WHERE ID = %s;" % (jobNumber)
cur.execute(query)
con.commit()
closed = 0
try:
if cur.rowcount > 0:
closed = (cur.fetchone())[0]
except TypeError:
closed = 1
if closed == 1:
isNew = 1
# let's check the from address against our list of permitted senders
# first I need to get the address properly
try:
tmp_fromAddress = re_fromAddress.search(msgFrom)
fromAddress = tmp_fromAddress.group()
fromAddress = fromAddress.replace('<', '')
fromAddress = fromAddress.replace('>', '')
except (AttributeError):
fromAddress = msgFrom
fromAddress = fromAddress.lower()
# got it - now I can build the query string and test if it exists in the DB
insertStatus = False
inboxid = 0
if isNew == 0:
inboxid = addTicket(fromAddress, msgSubj, msgBody, jobNumber, mailParseable)
elif isNew == 1:
print("New message")
inboxid = createTicket(fromAddress, msgSubj, msgBody, mailParseable)
else:
print
"ERROR. is new value not set"
return
if mailParseable.is_multipart():
print
"Processing Attachments"
for part in mailParseable.walk():
typ = part.get_content_type()
storeFile(part, typ, jobNumber, inboxid)
print ("Current date and time : ")
print (now.strftime("%Y-%m-%d %H:%M:%S"))
print("-" * 40)
return
def cleanup():
resp_code, mails = imapi.search(None, "SEEN")
mail_ids = mails[0].decode().split()
print("Total Mail IDs : {}\n".format(len(mail_ids)))
print("Deleting Mails...")
for mail_id in mail_ids[:2]:
resp_code, mail_data = imapi.fetch(mail_id, '(RFC822)') ## Fetch mail data.
message = email.message_from_bytes(mail_data[0][1]) ## Construct Message from mail data
print("Mail ID : {}, Date : {}, Subject : {}".format(mail_id, message.get("Date"), message.get("Subject")))
resp_code, response = imapi.copy(mail_id, 'Deleted Items')
resp_code, response = imapi.store(mail_id, '+FLAGS', '\\Deleted')
print("Response Code : {}".format(resp_code))
print("Response : {}\n".format(response[0].decode()))
resp_code, response = imapi.expunge()
print("Response Code : {}".format(resp_code))
print("Response : {}\n".format(response[0].decode()))
return
getMail() # calls the inital getMail function probably needs wrapping in a better constructor
elapsed = time.time() - t1
print ("Run completed in %d seconds" % (elapsed))
print ("-" * 40)
cur.execute("UPDATE mailGrabberStatus SET Status=1,LastUpdate=GETDATE();")
con.commit()
con.close()
con2.close()So I believe the part that needs fixing is in the def getmail() section
this is the part I have modified to get the mail from Amazon Workmail,
you can tell from some of the comments in the code, the original guy was a bit quirky
Thanks for your Help
P.S.
this is the log, the script runs every 3 mins, this is no new messages
Output: ----------------------------------------
Logging into mailbox...
Response Code : OK
Response : Logged in
Total Mail IDs : 0
Run completed in 0 seconds
----------------------------------------
Logging into mailbox...
Response Code : OK
Response : Logged in
Total Mail IDs : 0
Run completed in 0 seconds
----------------------------------------
this is with a new message
----------------------------------------
Logging into mailbox...
Response Code : OK
Response : Logged in
Total Mail IDs : 1
From: Forcepoint <[email protected]>
Subj: Webinar: Best practices for deploying DLP for the first time
Mail is Multipart
New message
Current date and time :
2022-01-14 15:10:47
----------------------------------------
Run completed in 1 seconds
----------------------------------------
Posts: 1,822
Threads: 2
Joined: Apr 2017
Nope, I didn't imply that business requests aren't allowed here. I simply suggest that employing someone is better because they can focus on it and make sure they make decisions that make sense for your business.
That being said, are there any tests? I'm guessing not, but that's one thing that should be improved at least. Michael Feathers' "Working Effectively with Legacy Code" gives good advice.
|
