I cant understand why they don't just store the XML in the ntext column directly in the first place... maybe someone was hoping that gzip run can save some size?
Re Davy: >> There are also performance implications with binary data, >I think its quite the contrary, there is an extra step involved going to & from base64 and binary. Database performance, high critical databases it's faster to write text than binary, this might have changed with the last version of SQL but it was true up until '2008 >> it's usually more secure to store as base64 >oh god not again. base64 is just a representation of the data in printable ASCII characters. - think binary (base2), hex (base8) :) >base64 has absolutely nothing to do with security. >if security is what you are after, use proper encryption such as AES on the original data, then base64 the output if you need >to send/store as text Completely missed my point, I'm not talking about encryption at all, it's more secure to store as base64, as you don't get problems trying to get the data back out. Just that what you put in is what you are getting out, without strange driver problems or Collation issues. Davy. *Si hoc legere scis nimium eruditionis habes*. On Thu, Sep 10, 2015 at 12:00 PM, Nelson <nelson.honey...@gmail.com> wrote: > I cant understand why they don't just store the XML in the ntext column > directly in the first place... > maybe someone was hoping that gzip run can save some size? > > Re Davy: > >> There are also performance implications with binary data, > I think its quite the contrary, there is an extra step involved going to & > from base64 and binary. > > >> it's usually more secure to store as base64 > oh god not again. base64 is just a representation of the data in printable > ASCII characters. - think binary (base2), hex (base8) :) > base64 has absolutely nothing to do with security. > > if security is what you are after, use proper encryption such as AES on > the original data, then base64 the output if you need to send/store as text > > > > Nelson Chan > > On 10 September 2015 at 19:50, Davy Jones <djones...@gmail.com> wrote: > >> Probably because binary data was easy to put in and easy to get out, but >> what you put in is not what you get out. >> There are also performance implications with binary data, it's usually >> more secure to store as base64 in a varchar(max) or text. >> Absolutely no reason to store in an unicode column. >> Davy >> >> Sent from my iPhone >> >> > On 10 Sep 2015, at 09:47, Greg Low (罗格雷格博士) <g...@greglow.com> wrote: >> > >> > The data was stored by Biztalk, and of course it shoved binary data >> into an ntext data type column. The next two questions are: >> > >> > 1. Why put base 64 encoded data into an ntext (unicode) column when >> such a limited range of values can be generated? >> > 2. Why use a deprecated data type (ntext) in the first place? >> > >> > I'm sure that both questions are above my paygrade :-) >> > >> > Regards >> > >> > Greg >> > >> > Dr Greg Low >> > SQL Down Under >> > +61 419201410 >> > 1300SQLSQL (1300775775) >> > >> >> On 10 Sep 2015, at 5:10 pm, Thomas Koster <tkos...@gmail.com> wrote: >> >> >> >> It is strange that base-64 encoding is even used here at all. Surely >> >> proper binary data types have been available in relational databases >> >> since the dark ages? >> >> -- >> >> Thomas Koster >> >> >> >> >> >>> On 10 September 2015 at 16:40, Greg Low (罗格雷格博士) <g...@greglow.com> >> wrote: >> >>> That was my first reaction too. Haven't spent time staring at base64 >> >>> encoding for a long time. Knew someone would recognise it though. The >> brains >> >>> trust comes through again! >> >>> >> >>> Regards >> >>> >> >>> Greg >> >>> >> >>> Dr Greg Low >> >>> SQL Down Under >> >>> +61 419201410 >> >>> 1300SQLSQL (1300775775) >> >>> >> >>> On 10 Sep 2015, at 3:55 pm, Stephen Price <step...@perthprojects.com> >> wrote: >> >>> >> >>> How did you get my Azure certificate? wtf?? >> >>> >> >>> Seriously though, the trailing == on the end (plus the overall look) >> makes >> >>> it look exactly like an Azure publish certificate. >> >>> >> >>>> On Thu, 10 Sep 2015 at 08:39 Greg Low (罗格雷格博士) <g...@greglow.com> >> wrote: >> >>>> >> >>>> Perfect thanks Thomas. >> >>>> >> >>>> I'll just have to add a base64 decode function and I should be fine. >> >>>> >> >>>> Regards, >> >>>> >> >>>> Greg >> >>>> >> >>>> Dr Greg Low >> >>>> >> >>>> 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 >> 4913 >> >>>> fax >> >>>> SQL Down Under | Web: www.sqldownunder.com >> >>>> >> >>>> -----Original Message----- >> >>>> From: ozdotnet-boun...@ozdotnet.com [mailto: >> ozdotnet-boun...@ozdotnet.com] >> >>>> On Behalf Of Thomas Koster >> >>>> Sent: Thursday, 10 September 2015 10:33 AM >> >>>> To: ozDotNet <ozdotnet@ozdotnet.com> >> >>>> Subject: Re: Odd text encoding >> >>>> >> >>>>> On 10 September 2015 at 10:21, Greg Low (罗格雷格博士) <g...@greglow.com> >> wrote: >> >>>>> This one’s driving me crazy and I thought the brains trust might >> have >> >>>>> an idea. >> >>>>> >> >>>>> Here’s a value that’s stored in an ntext column in a SQL Server DB: >> >>>>> >> H4sIAAAAAAAEALVW0W7aMBT9lanvre0wBkNtJEo3DWkFBGGvyDiXYi22M9vpYL/Wh33Sfm >> >>>>> >> GGJASatKOS95KH3HvPyTk+tvPn6fe1NLg3Bas5PMIsS0H3GVOZtJGm0lBmuZJfuLFKb99t >> >>>>> >> RCJNzw3cXKytTXsIGbYGQc2V4Ewro1b2iimBZj8SFGDcRbiNom0K8UQrBnGmwaB4qS4OQI >> >>>>> >> S8AakCmYLJEjsDu4dD5dffg1iC/sZjUF+5/F7RdP2zTEAbJWlyB5byxFRc7/1zFQsyjEFa >> >>>>> >> vuKM7takYmz/N8ZZJgTV24rqo3+qfeSG8hGMFU7fON2JO/KTeDX09YBXrB3/QgdKWsdWCw >> >>>>> >> zxwjVPY2qhH8euZOocH3xwmHTxAHaRgjTOswXNbVwsaUIlg6CiC7xs61zSZK0k1AX9g8CB >> >>>>> >> txDBaAaa04T/2m8ZdDTvJcn5FxYHAjXmp9JxxdHymaFyR6bAnCCXpZg/3yhvOZXPzGxEN3 >> >>>>> >> vnav57ydMp1y01nNUX2quLkzy6ZxwAgRc3TwLy0o1BvB7gcwNaUgH1PBIv12Au6ZNwGkol >> >>>>> >> 4f4fIl3kOkfZ7hm2MOm0OteooVS0F6swcHAPzvuwPxjM70k58bxaDB2t2aE0GI+i6fB2Hg >> >>>>> >> 3Ho3K8qa8OcedKn7USYYBJ+xJ3LjFpADh0NQNEqhjvOoQXxl1PaRLd5DaMV0c9IcH44FVz >> >>>>> x6lrjS6f1vK359184V9pkluuCgoAAA== >> >>>>> >> >>>>> Somehow, that’s apparently meant to be either a) an XML file, or b) >> a >> >>>>> GZipped XML file. >> >>>> >> >>>> echo "H4s...." | base64 -d | gunzip >> >>>> >> >>>> <snip> >> > >
