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

SQL Server 2012 Binary Text Field - convert to human readable HTML text

Expand Messages
  • Charles Carroll
    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)?
    Message 1 of 8 , Feb 27, 2014
    • 0 Attachment
      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...
    • Paul Livengood
      have you tried casting it? declare@b varbinary(max) set@b =0x5468697320697320612074657374 selectcast(@b asvarchar(max)) ________________________________ From:
      Message 2 of 8 , Feb 27, 2014
      • 0 Attachment
        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...


      • Charles Carroll
        How would I cast inline if it is one of the select fields select a,b,c,d ... assuming it field c for example?
        Message 3 of 8 , Feb 27, 2014
        • 0 Attachment
          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...



        • Paul Livengood
          SELECT   A, B, CAST (C AS VARCHAR(MAX)), D      From: Charles Carroll To: SQLQueriesNoCode@yahoogroups.com Sent:
          Message 4 of 8 , Feb 27, 2014
          • 0 Attachment
            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...





          • Charles Carroll
            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
            Message 5 of 8 , Feb 27, 2014
            • 0 Attachment
              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...






            • Eric Kestler
              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
              Message 6 of 8 , Feb 27, 2014
              • 0 Attachment
                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...






              • Charles Carroll
                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
                Message 7 of 8 , Feb 27, 2014
                • 0 Attachment
                  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...








                • Eric Kestler
                  You’re welcome. --  Eric Kestler Sent with Airmail From: Charles Carroll seniorprogrammerguru@gmail.com Reply: sqlqueriesnocode@yahoogroups.com
                  Message 8 of 8 , Feb 27, 2014
                  • 0 Attachment
                    You’re welcome.
                    -- 
                    Eric Kestler
                    Sent with Airmail

                    From: Charles Carroll seniorprogrammerguru@...
                    Reply: sqlqueriesnocode@yahoogroups.com sqlqueriesnocode@yahoogroups.com
                    Date: February 27, 2014 at 2:21:36 PM
                    To: sqlqueriesnocode@yahoogroups.com sqlqueriesnocode@yahoogroups.com
                    Subject:  Re: [SQLQueriesNoCode] SQL Server 2012 Binary Text Field - convert to human readable HTML text

                     
                    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...








                  Your message has been successfully submitted and would be delivered to recipients shortly.