The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?

asked 01 May '12, 13:31

Erik%20Anderson's gravatar image

Erik Anderson
accept rate: 15%

edited 01 May '12, 14:58

Mark%20Culp's gravatar image

Mark Culp

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?

(01 May '12, 14:11) Mark Culp
Replies hidden

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
      return 'digest:' || "login" || ':' || base64_encode(HEX2BIN(hash("login" || ':' || pass,'sha1')))


    create function dba.hmac(mkey long binary, msg long binary, algorithm varchar(100), blocksize integer default 64) returns long varchar
      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);
(01 May '12, 14:27) Erik Anderson

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:)

(01 May '12, 16:07) Volker Barth
Replies hidden

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, Input Validation & More.

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

(02 May '12, 03:20) Volker Barth

@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?

(02 May '12, 03:22) Volker Barth

Try defining:

   create function hextobin( in @hex long varchar )
   returns long binary
       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;

and then just use hextobin as you would normally.


permanent link

answered 01 May '12, 14:33

Mark%20Culp's gravatar image

Mark Culp
accept rate: 40%

edited 01 May '12, 14:57

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?

(01 May '12, 14:37) Erik Anderson
Replies hidden

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

(01 May '12, 14:37) Mark Culp
Replies hidden

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.

(01 May '12, 14:41) Mark Culp

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

(01 May '12, 16:22) Erik Anderson

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.

(01 May '12, 16:23) Erik Anderson 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...)

(01 May '12, 16:41) Volker Barth

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.

(01 May '12, 19:49) Erik Anderson

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

(02 May '12, 03:09) Volker Barth
Comment Text Removed

FWIW, Mark has not promised too much:

SA 16 has introduced both HEXTOBIN and BINTOHEX...

(21 Aug '13, 07:13) Volker Barth
showing 3 of 9 show all flat view

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
    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;
select 0x616263, hextobin( '616263'), hextobin( '0x616263');

The algorithm validates the string.

permanent link

answered 14 Jun '12, 19:36

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
accept rate: 39%

edited 15 Jun '12, 03:24

Volker%20Barth's gravatar image

Volker Barth

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 01 May '12, 13:31

question was seen: 1,614 times

last updated: 21 Aug '13, 07:13