crypto functions and hex strings - am I working too hard?

[Former Member]

This has been a long-term annoyance for me and I would very much like to know if I'm working too hard. It appears as though the hash functions in ASA (mostly HASH and BASE64_*) accept varbinary parameters but return hex strings as their result. This gets really annoying when trying to use the results of a HASH (for instance, the next step after calling HASH is normally to BASE64-encode the result) and the only function ASA seems to provide to translate from one to the other is HEX2INT. As one example, I just finished writing a HMAC function and I ran into this at least a couple of times (very grateful that XOR handles varbinary though).

I ended up writing a function that would take the results of these functions and translate it back into a varbinary using HEX2INT byte-by-byte, but this is starting to feel rediculously over-complicated. I'd rather not write my own DLL plugin to do this stuff (reimplementing crypto without a doctorate is usually a really bad idea) but is there something obvious that I'm not seeing here?

[MarkCulp]

I think you are working too hard ... Please clarify exactly what you are trying to accomplish? I.e. what are your inputs and what outputs are you wanting?

[Former Member]

Here's some functions I've written, can they be done without calling nonexistent function HEX2BIN after every call to HASH?

Zookeeper login utilty:

    create function zookeeper.constructDigest(in "login" long varchar, in pass long varchar) returns long varchar
    begin
      return 'digest:' || "login" || ':' || base64_encode(HEX2BIN(hash("login" || ':' || pass,'sha1')))
    end;

HMAC:

    create function dba.hmac(mkey long binary, msg long binary, algorithm varchar(100), blocksize integer default 64) returns long varchar
    begin
      declare opad long binary;
      declare ipad long binary;
      if length(mkey) > blocksize then
        set mkey = HEX2BIN(hash(mkey, algorithm));
      end if;
      if length(mkey) < blocksize then
        set mkey = mkey || repeat(char(0), blocksize - length(mkey));
      end if;
      set ipad = cast(repeat('6', blocksize) as long binary) ^ mkey;
      set opad = cast(repeat('\\\\', blocksize) as long binary) ^ mkey;
      return hash(opad || HEX2BIN(hash(ipad || msg, algorithm)), algorithm);
    end;

[VolkerBarth]

Very interesting question - I remember I have struggled with the output from these crypto functions as well a while ago (and not really followed that route) ... and I surely agree on that "reimplementing crypto without a doctorate ..." lesson:)

[VolkerBarth]

FWIW, as to the "reimplementing crypto ..." issue: I have found the following book a great help to not stumble on every error one can make by combining security primitives like hashes and ciphers:

Viega/Messier: Secure Programming Cookbook for C and C++: Recipes for Cryptography, Authentication, ....

For example, it tells how to use HMAC in a fashion to improve its resistance against birthday attacks and to capture replay attacks.

[VolkerBarth]

@Mark: Sometimes I wish more documentation on how to use these crypto functions with SQL Anywhere - is there anything in your toolset to tell about?

[MarkCulp · Accepted solution]

Try defining:

   create function hextobin( in @hex long varchar )
   returns long binary
   begin
       declare @bin long binary;
       if patindex( '%[^0-9a-fA-F]%', @hex ) = 0 then  -- check for invalid hex string
           execute immediate( 'set @bin = cast( 0x' || @hex || ' as long binary )' );
       end if;
       return @bin;
   end;

and then just use hextobin as you would normally.

HTH

[Former Member · Accepted solution]

That's... slightly better than my WHILE loop repeatedly calling HEXTOINT. I'm not sure what the tradeoff is between execute immediate vs trying to have asa do string operations. Am i right that this workaround is necessary and there is no facility that handles hex strings of arbitrary lengths nor one to prevent them from being generated?

[MarkCulp · Accepted solution]

Note that native support for hextobin() is on the enhancement list for the next major release of SQL Anywhere.

[MarkCulp · Accepted solution]

I think the above is the best you can do for now. See my other comment for the future. i.e. in the future you will not need to define hextobin() since it will be natively supported as a built-in function.

Re. which is better: execute immediate or a big loop that manipulates strings, the answer is that execute immediate will run much faster than a loop.

[Former Member · Accepted solution]

lol, wonder what that would do to a database with a naming collision. Maybe it would only show up when you rebuild, like sa_external_library_unload

[Former Member · Accepted solution]

Okay, I think I have my answer. Workaround for now (with improved performance in the proposed algorithm) and wait for a native version in ASA13 to come out.

[VolkerBarth · Accepted solution]

...so you have used your own sa_external_library_unload() function? (Then I'm glad my functions commonly use German language tokens and prefixes so any identifier collisions with SQL Anywhere are not expected at all...)

[Former Member · Accepted solution]

No, but I managed to find a database that was native-rebuilt under ASA12 so that I could unload a DLL that I had used under a different database on the same engine. I'm tempted to explicitly define it so I can use it in more places without having to rebuild though.

Functions like HEXTOBIN would likely be more of a reserved keyword though, I'd be curious as to how an engine implementing that as a keyword would handle a validly-rebuilt database with a function defining the same term.

[VolkerBarth · Accepted solution]

Well, for collisions with newly introduced reserved words, the rebuild will usually fail - cited from the v12.0.1 docs:

If an object name (such as a table, column, variable, or parameter name) corresponds to a reserved word introduced in a later version of SQL Anywhere (such as NCHAR), then the reload fails. [...]

Change all references to the reserved word to use a different name. For variable names, prefixing the name with @ is a common convention that prevents naming conflicts.

Howver, the names of builtin functions usually aren't reserved words, so I'm not sure whether a rebuild would fail when they get non-unique...

[VolkerBarth · Accepted solution]

FWIW, Mark has not promised too much:

SA 16 has introduced both HEXTOBIN and BINTOHEX...

[Former Member]

If you wish to avoid EXECUTE IMMEDIATE, you can also (ab)use the openstring algorithm to load any string in LOAD table format:

create or replace function hextobin( in @hex long varchar )
returns long binary
begin
    declare @bin long binary;
    if locate(@hex,'0x') <> 1 then
        set @hex='0x'||@hex;
    end if;
    select bin
    into @bin
    from openstring( value @hex )
    with( bin long binary ) B;
    return @bin;
end
go
select 0x616263, hextobin( '616263'), hextobin( '0x616263');

The algorithm validates the string.