Loading ...
Sorry, an error occurred while loading the content.

4884Re: [SQLQueriesNoCode] SQL Server 2012 Binary Text Field - convert to human readable HTML text

Expand Messages
  • Charles Carroll
    Feb 27, 2014
      Its actually Base64 encoded inconsistently.

      Some are some are not. I now have a pointer on what casts to do now.

      Here is what the second hand info I just got is I need to now turn it into the right cast syntax.


      Here is the CF function to encode and decode the Page content:

      Encode: <cfset getpage.docpage = ToBinary(getpage.docpage)>

                      Decode: <cfset junk = Tobase64(junk)>

       

       

      You can also use the following SQL function to encode and decode the content (DocPages..DocPage)

      -- Encode the string "TestData" in Base64 to get "VGVzdERhdGE="

      SELECT

          CAST(N'' AS XML).value(

                'xs:base64Binary(xs:hexBinary(sql:column("bin")))'

              , 'VARCHAR(MAX)'

          )   Base64Encoding

      FROM (

          SELECT CAST('TestData' AS VARBINARY(MAX)) AS bin

      ) AS bin_sql_server_temp;

       

      -- Decode the Base64-encoded string "VGVzdERhdGE=" to get back "TestData"

      SELECT

          CAST(

              CAST(N'' AS XML).value(

                  'xs:base64Binary("VGVzdERhdGE=")'

                , 'VARBINARY(MAX)'

              )

              AS VARCHAR(MAX)

          )   ASCIIEncoding

      ;




      On Thu, Feb 27, 2014 at 1:51 PM, Charles Carroll <seniorprogrammerguru@...> wrote:
      This app is a cold fusion app so I need to see how CF encrypts it and decrypts it to see if we can use a .NET equivalent to decrypt,

      None of this data should be encrypted so I want to decrypt and normalize.




      On Thu, Feb 27, 2014 at 1:43 PM, Eric Kestler <ekestler@...> wrote:
      Charles,

      It looks like an encrypted password. I’ve used the .Net Crypt class, which generates base64 bytes, and this looks like them.

      Just a thought. You could try running that string through Decrypt….

      ..e
      -- 
      Eric Kestler
      Sent with Airmail

      From: Charles Carroll seniorprogrammerguru@...
      Reply: sqlqueriesnocode@yahoogroups.com sqlqueriesnocode@yahoogroups.com
      Date: February 27, 2014 at 1:12:40 PM
      To: sqlqueriesnocode@yahoogroups.com sqlqueriesnocode@yahoogroups.com

      Subject:  Re: [SQLQueriesNoCode] SQL Server 2012 Binary Text Field - convert to human readable HTML text

       
      It comes out the same so that data must not be binary :(

      I am leaning towards the idea the data is decrypted and encrypted by Cold Fusion and the person who said it was binary is just wrong.

      However some row data is HTML and some is this nonsense so that is a different sort of technical debt entirely.





      On Thu, Feb 27, 2014 at 12:49 PM, Paul Livengood <p_livengood@...> wrote:
       
      SELECT   A, B, CAST (C AS VARCHAR(MAX)), D
         



       From: Charles Carroll <seniorprogrammerguru@...>
      To: SQLQueriesNoCode@yahoogroups.com
      Sent: Thursday, February 27, 2014 10:37 AM
      Subject: Re: [SQLQueriesNoCode] SQL Server 2012 Binary Text Field - convert to human readable HTML text

       
      How would I cast inline if it is one of the select fields

      select
          a,b,c,d
      ...

      assuming it field c for example?



      On Thu, Feb 27, 2014 at 12:18 PM, Paul Livengood <p_livengood@...> wrote:
       
      have you tried casting it?

      declare @b varbinary(max)
      set @b = 0x5468697320697320612074657374

      select cast(@b as varchar(max))



      From: Charles Carroll <seniorprogrammerguru@...>
      To: sqlqueriesnocode@yahoogroups.com
      Sent: Thursday, February 27, 2014 10:12 AM
      Subject: [SQLQueriesNoCode] SQL Server 2012 Binary Text Field - convert to human readable HTML text

       
      I inherited a lousy database - 2nd normal form max, no documentation, most things done wrong.

      HTML in the DB is stored in a text field

      a. why not varchar(max)? That is what I will change it to.

      The text field appears to be binary i.e.
      PEZPTlQgc2l6ZT0zPiAKPFA+VG9 ...

      What is easiest way to convert it to text again?

      I am in SQL Server 2012 who knows what DB version they made it in 12 years ago...








    • Show all 8 messages in this topic