Converting varbinary to text

[Dalton Calford]

Hey all,

I am looking for a way to convert  a hex_encoded string to a varchar ie

select 'test-string' as text,

           hex_encode('test-string') as hex_encoded_string,

           hex_decode(hex_encode('test-string')) as reverse_of_above_binary,

           ???(hex_decode(hex_encode('test-string')) as should_be_original_text

from RDB$DATABASE

I have tried cast but it gave me the hex_encoded value.   

I am looking to have a value XOR'd against a hash and the encrypted value stored, to be reversed by certain functions.   

Has anyone done anything like this?

  

[Norbert Saint Georges]

Dalton Calford a écrit :

> Hey all,
>
> I am looking for a way to convert a hex_encoded string to a varchar ie
>
> select 'test-string' as text,
> hex_encode('test-string') as hex_encoded_string,
> hex_decode(hex_encode('test-string')) as reverse_of_above_binary,
> ???(hex_decode(hex_encode('test-string')) as
> should_be_original_text
> from RDB$DATABASE

select 'test-string' as text,
hex_encode('test-string') as hex_encoded_string,

cast(hex_decode( hex_encode( 'test-string')) as varchar(12)
)

as reverse_of_above_binary
from RDB$DATABASE

--
Norbert Saint Georges
http://tetrasys.fi

[Mark Rotteveel]

HEX_ENCODE effectively only accepts (VAR)BINARY or BLOB SUB_TYPE BINARY
values, and HEX_DECODE produces binary values. Non-binary values like
CHAR, VARCHAR or BLOB SUB_TYPE TEXT are coerced to their binary counterpart.

When reversing it, you need to cast to the appropriate data-type (and
possibly character set). The literal 'test-string' is a CHAR(11), so use
`CAST(... as CHAR(11))` or `CAST(... as CHAR(11) CHARACTER SET UTF8)`;
you can also declare a longer type, but then you may want to consider
using VARCHAR instead.

Mark
--
Mark Rotteveel

[Dalton Calford]

Thanks, it was the character set option that worked for me.   I had to do extra testing using isql/command line as the UI I am using was being "helpful" and I was getting strange results with the stored procs vs what the ui was showing me.

Does anyone know where I can find a bin_xor function that works on binary data vs integers?

--
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/01f3615a-d17c-4183-8b18-3a4706fcf942%40lawinegevaar.nl.

[Mark Rotteveel]

On 09/10/2024 16:23, Dalton Calford wrote:
> Thanks, it was the character set option that worked for me.   I had to
> do extra testing using isql/command line as the UI I am using was being
> "helpful" and I was getting strange results with the stored procs vs
> what the ui was showing me.
>
> Does anyone know where I can find a bin_xor function that works on
> binary data vs integers?

I'm not sure there are ready made solutions for this. You probably need
to implement a UDR for it yourself.

If it is not performance sensitive, you could try to write a PSQL
function to use ASCII_VAL and ASCII_CHAR per byte to be able to BIN_XOR
them and concatenate them back into a (var)binary string.

Mark
--
Mark Rotteveel

[Dimitry Sibiryakov]

'Mark Rotteveel' via firebird-support wrote 09.10.2024 16:43:
> I'm not sure there are ready made solutions for this. You probably need to
> implement a UDR for it yourself.

May be XOR is not a goal but they needs an encryption. In this case functions
ENCRYPT/DECRYPT is exactly what is needed.

--
WBR, SD.