Post reply

How to overcome missing 0x for varbinary?

  • November 8, 2018 at 5:26 am

    #416475

    I have a virbinary(MAX)  column which contains an encrypted data. We use standard encryption method EncryptByKey(Key_GUID.... to decrypt the data. All works well, but for some of the records the content of that column misses leading 0x. Can one advise how to address the problem, please? I appreciate I can go back to someone who wrote the data in, and I would indeed if I could.
    I am not placing the whole string here as a sample, as they are over 40K length, but to clarify, some data starts with 0066C5DD48CFFB4FAC instead of 0x0066C5DD48CFFB4FAC. Obviously if I manually take the whole 0x0066C5DD48CFFB4FAC and decrypt, it produces the correct result.

  • November 8, 2018 at 5:43 am

    #2012797

    So, you're saying that you have a varbinary value without 0x at the start? I didn't even think that was possible. As an example the following code doesn't work:
    DECLARE @b-2 varbinary = 0066C5DD48CFFB4FAC;
    The     binary data has to start with 0x:
    DECLARE @b-2 varbinary = 0x0066C5DD48CFFB4FAC;
    Changing the value to a string, and converting it, works but that changes the value:
    DECLARE @b-2 varbinary(10) = CONVERT(varbinary(10),'0066C5DD48CFFB4FAC');
    PRINT @b-2;
    That will output 0x30303636433544443438.
    Are you sure your column is a varbinary? (sounds like it's a varchar)

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 8, 2018 at 5:54 am

    #2012798

    BOR15K - Thursday, November 8, 2018 5:26 AM

    I have a virbinary(MAX)  column which contains an encrypted data. We use standard encryption method EncryptByKey(Key_GUID.... to decrypt the data. All works well, but for some of the records the content of that column misses leading 0x. Can one advise how to address the problem, please? I appreciate I can go back to someone who wrote the data in, and I would indeed if I could.
    I am not placing the whole string here as a sample, as they are over 40K length, but to clarify, some data starts with 0066C5DD48CFFB4FAC instead of 0x0066C5DD48CFFB4FAC. Obviously if I manually take the whole 0x0066C5DD48CFFB4FAC and decrypt, it produces the correct result.

    Are you certain that the column's data type is varbinary?
    😎

  • November 8, 2018 at 7:48 am

    #2012813

    BOR15K - Thursday, November 8, 2018 5:26 AM

    I have a virbinary(MAX)  column which contains an encrypted data. We use standard encryption method EncryptByKey(Key_GUID.... to decrypt the data. All works well, but for some of the records the content of that column misses leading 0x. Can one advise how to address the problem, please? I appreciate I can go back to someone who wrote the data in, and I would indeed if I could.
    I am not placing the whole string here as a sample, as they are over 40K length, but to clarify, some data starts with 0066C5DD48CFFB4FAC instead of 0x0066C5DD48CFFB4FAC. Obviously if I manually take the whole 0x0066C5DD48CFFB4FAC and decrypt, it produces the correct result.

    If you're looking at things on the screen during a simple SELECT and the column is actually a VARBINARY, then it's not only possible for it to sometimes omit the 0X on the return to the screen, but it frequently makes it look like there's no data in the column on the return to the screen.  This occurs because VARBINARY contains atypical data (like CHAR(0) and other characters) that interfere with the way SSMS displays them.  The underlying VARBINARY is still in good shape and you should have no problem decrypting it so long as you don't try to display it or use the return from the display.

    I know this because we store WAV files in our telephone system database and the only way you can sometimes verify that there's a WAV file present in the column is to check it the DATALENGTH() function.

    If the underlying data isn't VARBINARY, post back.  We can fix that.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • November 8, 2018 at 7:55 am

    #2012814

    Jeff Moden - Thursday, November 8, 2018 7:48 AM

    BOR15K - Thursday, November 8, 2018 5:26 AM

    I have a virbinary(MAX)  column which contains an encrypted data. We use standard encryption method EncryptByKey(Key_GUID.... to decrypt the data. All works well, but for some of the records the content of that column misses leading 0x. Can one advise how to address the problem, please? I appreciate I can go back to someone who wrote the data in, and I would indeed if I could.
    I am not placing the whole string here as a sample, as they are over 40K length, but to clarify, some data starts with 0066C5DD48CFFB4FAC instead of 0x0066C5DD48CFFB4FAC. Obviously if I manually take the whole 0x0066C5DD48CFFB4FAC and decrypt, it produces the correct result.

    If you're looking at things on the screen during a simple SELECT and the column is actually a VARBINARY, then it's not only possible for it to sometimes omit the 0X on the return to the screen, but it frequently makes it look like there's no data in the column on the return to the screen.  This occurs because VARBINARY contains atypical data (like CHAR(0) and other characters) that interfere with the way SSMS displays them.  The underlying VARBINARY is still in good shape and you should have no problem decrypting it so long as you don't try to display it or use the return from the display.

    I know this because we store WAV files in our telephone system database and the only way you can sometimes verify that there's a WAV file present in the column is to check it the DATALENGTH() function.

    If the underlying data isn't VARBINARY, post back.  We can fix that.

    My thoughts 0x'actly!
    😎

  • November 8, 2018 at 11:59 am

    #2012885

    Jeff Moden - Thursday, November 8, 2018 7:48 AM

    BOR15K - Thursday, November 8, 2018 5:26 AM

    I have a virbinary(MAX)  column which contains an encrypted data. We use standard encryption method EncryptByKey(Key_GUID.... to decrypt the data. All works well, but for some of the records the content of that column misses leading 0x. Can one advise how to address the problem, please? I appreciate I can go back to someone who wrote the data in, and I would indeed if I could.
    I am not placing the whole string here as a sample, as they are over 40K length, but to clarify, some data starts with 0066C5DD48CFFB4FAC instead of 0x0066C5DD48CFFB4FAC. Obviously if I manually take the whole 0x0066C5DD48CFFB4FAC and decrypt, it produces the correct result.

    If you're looking at things on the screen during a simple SELECT and the column is actually a VARBINARY, then it's not only possible for it to sometimes omit the 0X on the return to the screen, but it frequently makes it look like there's no data in the column on the return to the screen.  This occurs because VARBINARY contains atypical data (like CHAR(0) and other characters) that interfere with the way SSMS displays them.  The underlying VARBINARY is still in good shape and you should have no problem decrypting it so long as you don't try to display it or use the return from the display.

    I know this because we store WAV files in our telephone system database and the only way you can sometimes verify that there's a WAV file present in the column is to check it the DATALENGTH() function.

    If the underlying data isn't VARBINARY, post back.  We can fix that.

    Just curious, when would varbinary not display the leading 0x as the result of a select in ssms (obviously I know that null will display null). I thought varbinary would always display the 0x unless null.

  • November 8, 2018 at 9:09 pm

    #2012924

    patrickmcginnis59 10839 - Thursday, November 8, 2018 11:59 AM

    Jeff Moden - Thursday, November 8, 2018 7:48 AM

    BOR15K - Thursday, November 8, 2018 5:26 AM

    I have a virbinary(MAX)  column which contains an encrypted data. We use standard encryption method EncryptByKey(Key_GUID.... to decrypt the data. All works well, but for some of the records the content of that column misses leading 0x. Can one advise how to address the problem, please? I appreciate I can go back to someone who wrote the data in, and I would indeed if I could.
    I am not placing the whole string here as a sample, as they are over 40K length, but to clarify, some data starts with 0066C5DD48CFFB4FAC instead of 0x0066C5DD48CFFB4FAC. Obviously if I manually take the whole 0x0066C5DD48CFFB4FAC and decrypt, it produces the correct result.

    If you're looking at things on the screen during a simple SELECT and the column is actually a VARBINARY, then it's not only possible for it to sometimes omit the 0X on the return to the screen, but it frequently makes it look like there's no data in the column on the return to the screen.  This occurs because VARBINARY contains atypical data (like CHAR(0) and other characters) that interfere with the way SSMS displays them.  The underlying VARBINARY is still in good shape and you should have no problem decrypting it so long as you don't try to display it or use the return from the display.

    I know this because we store WAV files in our telephone system database and the only way you can sometimes verify that there's a WAV file present in the column is to check it the DATALENGTH() function.

    If the underlying data isn't VARBINARY, post back.  We can fix that.

    Just curious, when would varbinary not display the leading 0x as the result of a select in ssms (obviously I know that null will display null). I thought varbinary would always display the 0x unless null.

    I don't know what would cause it to display some data without the "0x".  I've only heard of it before and have not seen it myself.  I do see it everyday where there is no data in the full display column when there actually is data in the column.  Here's a capture of the grid output from one of our tables on our phone system...

    The "Call_Binary" column is where the WAV file is stored.  The "First100" column is the result of a SUBSTRING of that column from character 1 to 100.  Call_Size is the result of a DATALENGTH().  Notice that there is no "0x" in the "Call_Binary" column and it's not actually NULL, which would show a NULL if it were null.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply