Decode a text string marked as RFC822_msg

Question

I have had a request from management to pick out emails with a very long conversation in them. The goal being to get staff to "Just ring the customer" instead of endlessly emailing them.

I have a system (Policy Patrol) that saves incoming and outgoing emails to a SQL database as a backup before they are passed on to my Exchange server. I figured I can just query that database and parse the messages within to determine which ones have more than the specified number of replies in the conversation.

Ok that is the background, Here is my problem.

The database only has two tables and one obviously contains the message. The first 2048 characters of the message are in one column as plain readable text. However I need to read the full message which is encoded. I turned off the Zip compression feature to make sure this was not part of the mix.

The column name that contains the full message is "RFC822_msg" I sent myself a simple test message that contains the following text

Subject: "This is a test message"

Body: "Now is the time for all good students to come to the aid of their teachers."

Here is the actual string of characters in the RFC822_msg column

0x52656365697665643A2066726F6D206D61696C2D7061302D6636382E676F6F676C652E636F6D20283230392E38352E3232302E3638292062792063616D707369652E766A7261792E636F6D2E61750D0A202831302E322E322E33292077697468204D6963726F736F667420534D5450205365727665722028544C53292069642031342E332E3237392E323B205468752C2032382041707220323031360D0A2031313A33343A3339202B313030300D0A52656365697665643A206279206D61696C2D7061302D6636382E676F6F676C652E636F6D207769746820534D5450206964207A7932736F373936313631387061632E322020202020202020666F720D0A203C64706F6C6C61726440766A7261792E636F6D2E61753E3B205765642C2032372041707220323031362031383A33343A3338202D303730302028504454290D0A444B494D2D5369676E61747572653A20763D313B20613D7273612D7368613235363B20633D72656C617865642F72656C617865643B0D0A2020202020202020643D676D61696C2E636F6D3B20733D32303132303131333B0D0A2020202020202020683D66726F6D3A746F3A7375626A6563743A646174653A6D6573736167652D69643A6D696D652D76657273696F6E3A7468726561642D696E6465780D0A2020202020202020203A636F6E74656E742D6C616E67756167653B0D0A202020202020202062683D363965457A3937476254694D36517548785847474C5354304E66774C7241686B43766B655067596D4170453D3B0D0A2020202020202020623D6A623555546C45684A64456E42384D6F4F4454456D37533350435755584B6E5338337A6B796D6A516C346F4D32465235347559624E487A544750427452384F792F670D0A202020202020202020336C545A5A394E736E2F7A676F2B7245445832533477523878344A4655547841576F6238546E7253383968796D51516772496E6553484C5139436C6F70526851513757570D0A202020202020202020744770473744366D57564F4936317662736E5342376F386C7A304E5A464C5958333272554D347A43456E466F494D6E78346A7A53416F777A35316E6C37526672573238780D0A202020202020202020576D416C734D6D7A75655A4E715A4F354779454D36546D50714A2F32776654704236373178344231457A723043486C45727A57575778495855586E65534E38514E5A7A370D0A2020202020202020204D6565794F774962684579632F556249713334483379706546704832444D784F7459506E4E6F34414A4A6A6D72727466525A5258365A3635526F672F32643835584965730D0A202020202020202020374559413D3D0D0A582D476F6F676C652D444B494D2D5369676E61747572653A20763D313B20613D7273612D7368613235363B20633D72656C617865642F72656C617865643B0D0A2020202020202020643D31653130302E6E65743B20733D32303133303832303B0D0A2020202020202020683D782D676D2D6D6573736167652D73746174653A66726F6D3A746F3A7375626A6563743A646174653A6D6573736167652D69643A6D696D652D76657273696F6E0D0A2020202020202020203A7468726561642D696E6465783A636F6E74656E742D6C616E67756167653B0D0A202020202020202062683D363965457A3937476254694D36517548785847474C5354304E66774C7241686B43766B655067596D4170453D3B0D0A2020202020202020623D4D4859374346586355435279416B6C3459434D4B57722F6130774D34677641354B5168415A332F446C7A694B595A5A4F474A574C32654134786F7244412F6A4853630D0A2020202020202020204A6E704B507855796259724D4E4277773448476E346D474133494B4A467A55412F324471756D4F4857546233446E53314251556351377A3733466B356532797062634B700D0A20202020202020202044355365754A41697A33634E59555471694C646548445955694C726F4D5471466F734B465A2F7863442B5538546D5531597357736A466D4C59685550425278306E3633450D0A202020202020202020654E55364A673145617452455273564146315A51753377484139757A384D69643853326735526C424A7437597A72334235776B4E55796B4570614362745768704A4151500D0A202020202020202020367474494E6A6C554B6377467755547935634F36416E762F32774A346C775458756C723165597249512F3179416B73535048686645544A3563354F4F4261512B534E67490D0A202020202020202020735641673D3D0D0A582D476D2D4D6573736167652D53746174653A20414F50723446574C324C3872434A7238455367706F6B4E462B4A657A697774357178356B51356D576B415655463578766D5768707430315065572B36374D506B4C5536674A773D3D0D0A582D52656365697665643A2062792031302E36362E3233312E3733207769746820534D5450206964207465396D7231363830343237317061632E36322E313436313830373237363837323B0D0A20202020202020205765642C2032372041707220323031362031383A33343A3336202D303730302028504454290D0A52657475726E2D506174683A203C706F6C6C6172646440676D61696C2E636F6D3E0D0A52656365697665643A2066726F6D20564A524159574B53313720285B312E3132392E32362E3230315D292020202020202020627920736D74702E676D61696C2E636F6D20776974680D0A2045534D545053412069642038736D3937353834383870666B2E36392E323031362E30342E32372E31382E33342E33342020202020202020666F720D0A203C64706F6C6C61726440766A7261792E636F6D2E61753E20202020202020202876657273696F6E3D544C53315F32206369706865723D45434448452D5253412D4145533132382D5348410D0A20626974733D3132382F313238293B20202020202020205765642C2032372041707220323031362031383A33343A3336202D303730302028504454290D0A46726F6D3A20446176696420506F6C6C617264203C706F6C6C6172646440676D61696C2E636F6D3E0D0A546F3A20446176696420506F6C6C617264203C64706F6C6C61726440766A7261792E636F6D2E61753E0D0A5375626A6563743A205468697320697320612074657374206D6573736167650D0A446174653A205468752C2032382041707220323031362031313A33343A3331202B313030300D0A4D6573736167652D49443A203C3030346630316431613065652431663465323239302435646561363762302440676D61696C2E636F6D3E0D0A4D494D452D56657273696F6E3A20312E300D0A436F6E74656E742D547970653A206D756C7469706172742F6D697865643B0D0A09626F756E646172793D222D2D2D2D3D5F4E657874506172745F3030305F303035305F30314431413134312E4631314239443430220D0A582D4D61696C65723A204D6963726F736F6674204F75746C6F6F6B2031352E300D0A5468726561642D496E6465783A2041644767376639694F43392B596271675335576B6541384F634C4B596E513D3D0D0A436F6E74656E742D4C616E67756167653A20656E2D61750D0A582D4D532D544E45462D436F7272656C61746F723A2030303030303030303841464635303246414237414439343741413037313934434546334445334235303730304333423638453130463737353131434542344344303041413030424242364536303030303030303030303044303030303442433932313937383445303835343541434646313933373338324231333741303030303030303030423035303030300D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D4F726967696E616C4172726976616C54696D653A2032382041707220323031362030313A33343A33392E313939300D0A2028555443290D0A582D4D532D45786368616E67652D466F726573742D4172726976616C4875625365727665723A20434F4D50414E595352562E63616D707369652E6C6F63616C0D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D4F726967696E616C436C69656E744950416464726573733A203230392E38352E3232302E36380D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D4F726967696E616C5365727665724950416464726573733A2031302E322E322E330D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D41757468536F757263653A20434F4D50414E595352562E63616D707369652E6C6F63616C0D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D4175746841733A20416E6F6E796D6F75730D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D4D657373616765446972656374696F6E616C6974793A20496E636F6D696E670D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D43726F73732D5072656D697365732D486561646572732D50726F6365737365643A0D0A20434F4D50414E595352562E63616D707369652E6C6F63616C0D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D5052443A20676D61696C2E636F6D0D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D53656E6465724964526573756C743A20506173730D0A52656365697665642D5350463A20506173732028434F4D50414E595352562E63616D707369652E6C6F63616C3A20646F6D61696E206F6620706F6C6C6172646440676D61696C2E636F6D0D0A2064657369676E61746573203230392E38352E3232302E3638206173207065726D69747465642073656E646572290D0A2072656365697665723D434F4D50414E595352562E63616D707369652E6C6F63616C3B20636C69656E742D69703D3230392E38352E3232302E36383B0D0A2068656C6F3D6D61696C2D7061302D6636382E676F6F676C652E636F6D3B0D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D41565374616D702D4156473A20323031332E302E33353536205B343437372E302E302F31323037365D3B303B0D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D41565374616D702D4D61696C626F783A204156474553453B31363535333B303B0D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D53434C3A20310D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D50434C3A20320D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D416E74697370616D2D5265706F72743A0D0A2044563A332E332E31353932382E3838353B5349443A53656E646572494453746174757320506173733B4F72696749503A3230392E38352E3232302E36380D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D4F726967696E616C53697A653A20363438340D0A582D4D532D45786368616E67652D466F726573742D4D65737361676553636F70653A2030303030303030302D303030302D303030302D303030302D3030303030303030303030300D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D4D65737361676553636F70653A2030303030303030302D303030302D303030302D303030302D3030303030303030303030300D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D48796769656E65506F6C6963793A205374616E646172640D0A582D4D532D45786368616E67652D4F7267616E697A6174696F6E2D526563697069656E742D4C696D69742D56657269666965643A20547275650D0A0D0A2D2D2D2D2D2D3D5F4E657874506172745F3030305F303035305F30314431413134312E46313142394434300D0A436F6E74656E742D547970653A20746578742F706C61696E3B20636861727365743D2275732D6173636969220D0A436F6E74656E742D5472616E736665722D456E636F64696E673A20376269740D0A0D0A4E6F77206973207468652074696D6520666F7220616C6C20676F6F642073747564656E747320746F20636F6D6520746F2074686520616964206F662074686569722074656163686572732E0D0A0D0A0D0A2D2D2D2D2D2D3D5F4E657874506172745F3030305F303035305F30314431413134312E46313142394434300D0A436F6E74656E742D547970653A206170706C69636174696F6E2F6D732D746E65663B206E616D653D2277696E6D61696C2E646174220D0A436F6E74656E742D5472616E736665722D456E636F64696E673A206261736536340D0A436F6E74656E742D446973706F736974696F6E3A206174746163686D656E743B2066696C656E616D653D2277696E6D61696C2E646174220D0A0D0A654A382B49695142415161514341414541414141414141424141454141516551426741494141414135415141414141414141446F41414549674163414741414141456C515453354E61574E790D0A62334E765A6E5167545746706243354F6233526C4144454941514F5142674245435141414B5141414141734141674142414141414177416D414141414141414C41436B4141414141414234410D0A6341414241414141467741414146526F61584D6761584D67595342305A584E304947316C63334E685A32554141414942635141424141414146674141414148526F4F332F596A6776666D47360D0A6F45755670486750446E43796D4A30414141734141513441414141414167454B446745414141417541414141414141414149722F55432B7265746C487167635A544F383934375542414D4F320D0A6A6844336452484F744D304171674337747559414141414141413441414141414177415544674541414141654143674F4151414141433841414141774D4441774D4441774D774677623278730D0A59584A6B5A45426E625746706243356A6232304263473973624746795A4752415A32316861577775593239744141416541436B4F4151414141433841414141774D4441774D4441774D7746770D0A6232787359584A6B5A45426E625746706243356A6232304263473973624746795A4752415A32316861577775593239744141414C414259774151414141414D41336A2B6654674141417744780D0A50776B4D4141414441414A5A4141415741414D4143566B434141414141774142614145414141414441416C6F4541414141414D414C4941494941594141414141414D414141414141414142470D0A41414141414243464141414141414141417741386741676742674141414141417741414141414141414559414141414141595541414141414141414C414875414343414741414141414144410D0A41414141414141415267414141414147685141414141414141417341663441494941594141414141414D414141414141414142474141414141413646414141414141414141774343674167670D0A42674141414141417741414141414141414559414141414147495541414141414141414C414A65414343414741414141414144414141414141414141526741414141434368514141414141410D0A41414D41796F41494941594141414141414D4141414141414141424741414141414F75464141414A44414141437741664467454141414143416667504151414141424141414144556C6636630D0A615871465334366D44302F736331464541674836447745414141415141414141697639514C3674363255657142786C4D377A336A74514D412F673846414141414167454A45414541414141630D0A425141414741554141466B4B4141424D576B5A314576383574514D41436742795933426E4D544931676A49445132683062577778417A4834596D6C6B424141444D4145444166634B675038430D0A7041506B42784D436742434441464145566768566E7765794562554F55514D424167426A6141724163484E6C64444947414162444562557A65775247464563774572775277776A76436663370D0A4A5269694E516D304F546B4B497A4535646A4D52736778675977425143776B425A444D6D4E68626743365530494243434B6C7848447249426B4134514F53413844724967686E674F304143410D0A4F6E5939496768774A4734364250426F5A5144416379326D625133674132427A627747414C515767316D3066514137514968376C6278397649486D31494B426D4465426C49355568566E63680D0A373963692F7954674262426B49565A744A51414F734B423063446F764C79566C4C6942484B6934673453386A70433842304441307469384F49436E67625345334B4164334C4341514C6E637A0D0A4C6757775A79395551464976556B56444C5136794E4E6777496A3452777833584D783177487042495A57466B4C6230784E67377750434D48674147514947356842344139522F4D4A384153510D0A5958514673515767416A414A384570304A51424E4946596756796442495041784E53416F493841654941535143594447494165414431423162536B74726831677754417863335235624755300D0A667A4178434345744C51716A4C796F6752686B43495342454152414C67476C3061564D43494151674B69384B6F304143456932365A6744515A51716B415A45444D48733568554567514778350D0A4F694A444D4D4269357763684264417855476769476341364A777177776D3467674755744D546F553042317737544D774D7A3368485841325067453973526E416E6E30354C7A6F2F4F304D370D0A6F4778704F39482F50483839677A4D684D7A415530454F694858412B6D78303377564D31776A672F437346774C6B324E494A424F4262414177477773494547672B30624B44314232527368410D0A436744414C4B414C674C41364D474E7451666C4A39433047344D7343514344784C6970774D4445464D454835797A397A414A4236492F41784D55776754482F6A4F786C426F794973637742780D0A54594147636E394B6D6943515037455973435777494D414C5947344D5A3355655143507752553474565A4A5450755A684F6B4767626D7448594D5A7A50534647776B683563415351552B4C6E0D0A535474527354577A4C584146454157774F4A41504F3341616745483547434D36497A41317344597A517A46422B54485165434441787757444D5641347354703162675342552B48365A564E490D0A646751414F4A414A67465176564144314E2F4273474542334359425658315A7656332F774F54553052674841574F395A2F3173496E5678545251444141784246497A45335865393358764D310D0A774654674F6C5468494A417773477A39494E4A775056464F5430396655473958357750774831716758614269496A376D52734A446148443552594668645234675A55396D574749775A3744390D0A41434174416941375947662F615139714831462F76314B50507A494B73464C414D734D4759474D346F6938626B304247545A4D7749444A4F41694133396A6C33343071664F674841642F52350D0A37303469337A384553474A312F3042566461493666456F2B3572383355445950486E456F674458504E7468624270414D4947637830484E6949446C6450736F384876452B43714D38627956510D0A46484476564F414241573179424342326231457941545142586E516855467851443041417748676C4144476F4D444932495641766737557667344C4E4E7A42624366415055475A6466312B430D0A442F65444834516C4332423543474146514955716737665668684A776852786B4D564268686D474779642B4C65347177683238747A4456794C793776486B444B4E526267504162675A486C480D0A63485242386A315338554656523346554143554157485839495642326C5A56684643326848654D4149514D77385851794D7A41344476475870526267485A74364F543577504568684D5A414C0D0A59415151506439385370643541414359337834544E6936786A65442F6D725247313576666E4F4E48414166674432453851483946594469674234424E4D564F685859434B41472F7A424842630D0A51485231415141434D4B4542696E442F494F4768515970776F53494C63444D41494B43684572357042634178304144514A5941556B4336496A6673757359516763496875446B4350773661680D0A6C35622F43714B58687770796C36634B73616D346D553042774F2B416B616175686F417573533949595A643571526935485977314F4943526C4A4B49626A65533867734F7753327766624B410D0A4177414E4E50302F72513444414138302F542B7444674942464451424141414145414141414F6B7636335757554553476737683935534B715355676541506F2F4151414141424D41414142770D0A6232787359584A6B5A45426E625746706243356A6232304141414942346D554241414141464141414144336D5669662F652F5241737543455063364F5A7A7741432B78784167486A5A5145410D0A41414156414141414644336D5669662F652F5241737543455063364F5A7A7741432B7878414141414167462F414145414141434E414141414D4441774D4441774D44413451555A474E5441790D0A526B46434E3046454F545133515545774E7A45354E454E46526A4E4552544E434E5441334D4442444D3049324F4555784D4559334E7A55784D554E46516A524452444177515545774D454A430D0A516A5A464E6A41774D4441774D4441774D444177524441774D444130516B4D354D6A45354E7A6730525441344E54513151554E47526A45354D7A637A4F444A434D544D33515441774D4441770D0A4D4441774D4549774E5441774D44414141414141417741474544633569524D44414163514F77414141414D4145424141414141414177415245414541414141654141675141514141414477410D0A4141424F5431644A553152495256524A5455564754314A425445784854303945553152565245564F56464E5554304E5054555655543152495255464A524539475645684653564A55525546440D0A534556535577433970513D3D0D0A0D0A2D2D2D2D2D2D3D5F4E657874506172745F3030305F303035305F30314431413134312E46313142394434302D2D0D0A

I have tried several things to decode it but nothing seem to recognise the format. Base64 seems to include + and / characters so it isn't that.

Any ideas what this encoding is and how to decode it?

Thanks in advance David

Answer 1

Looks like I have answered my own question.

I found an online tool that decodes it here http://tomeko.net/online_tools/hex_to_ascii.php?lang=en

It is called Hexadecimal -> ASCII string decoder. Now I need to figure out how to do the decode with a command line utility.

However this does answer my original question. David

Update

Just for completeness I went on to create a console application based on code I found here

https://codereview.stackexchange.com/questions/97950/conversion-of-hexadecimal-string-to-string

c# Source Code (Compile using Visual Studio 2008)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace HexToText
{
    class Program
    {
        static void Main(string[] args)
        {
            TextReader textReader = Console.In;
            string input = textReader.ReadToEnd();
            string result = hexStringToString(input);
            Console.WriteLine(result);
        }

        static string hexStringToString(string hexString)
        {
            if (hexString == null || (hexString.Length & 1) == 1)
            {
                throw new ArgumentException();
            }
            var sb = new StringBuilder();
            for (var i = 0; i < hexString.Length; i += 2)
            {
                var hexChar = hexString.Substring(i, 2);
                if (hexChar != "0x")
                {
                    sb.Append((char)Convert.ToByte(hexChar, 16));
                }
            }
            return sb.ToString();
        }
    }
}

Usage: HexToText < Input.txt > Output.txt

or

Type Input.txt | HexToText > Output.txt

The leading 0x will be ignored.

I hope this will help someone else facing a similar problem.