SQLite

      *(z++) = hexdigits[(c>>4)&0xf];
      *(z++) = hexdigits[c&0xf];
    }
    *z = 0;
    sqlite3_result_text(context, zHex, n*2, sqlite3_free);
  }
}

/*
** Buffer zStr contains nStr bytes of utf-8 encoded text. Return 1 if zStr
** contains character ch, or 0 if it does not.
*/
static int strContainsChar(const u8 *zStr, int nStr, u32 ch){
  const u8 *zEnd = &zStr[nStr];
  const u8 *z = zStr;
  while( z<zEnd ){
    u32 tst = Utf8Read(z);
    if( tst==ch ) return 1;
  }
  return 0;
}

/*
** The unhex() function. This function may be invoked with either one or
** two arguments. In both cases the first argument is interpreted as text
** a text value containing a set of pairs of hexadecimal digits which are
** decoded and returned as a blob.
**
** If there is only a single argument, then it must consist only of an
** even number of hexadeximal digits. Otherwise, return NULL.
**
** Or, if there is a second argument, then any character that appears in
** the second argument is also allowed to appear between pairs of hexadecimal
** digits in the first argument. If any other character appears in the
** first argument, or if one of the allowed characters appears between 
** two hexadecimal digits that make up a single byte, NULL is returned.
**
** The following expressions are all true:
**
**     unhex('ABCD')       IS x'ABCD'
**     unhex('AB CD')      IS NULL
**     unhex('AB CD', ' ') IS x'ABCD'
**     unhex('A BCD', ' ') IS NULL
*/
static void unhexFunc(
  sqlite3_context *pCtx,
  int argc,
  sqlite3_value **argv
){
  const u8 *zPass = (const u8*)"";
  int nPass = 0;
  const u8 *zHex = sqlite3_value_text(argv[0]);
  int nHex = sqlite3_value_bytes(argv[0]);
#ifdef SQLITE_DEBUG
  const u8 *zEnd = &zHex[nHex];
#endif
  u8 *pBlob = 0;
  u8 *p = 0;

  assert( argc==1 || argc==2 );
  if( argc==2 ){
    zPass = sqlite3_value_text(argv[1]);
    nPass = sqlite3_value_bytes(argv[1]);
  }
  if( !zHex || !zPass ) return;

  p = pBlob = contextMalloc(pCtx, (nHex/2)+1);
  if( pBlob ){
    u8 c;                         /* Most significant digit of next byte */
    u8 d;                         /* Least significant digit of next byte */

    while( (c = *zHex)!=0x00 ){
      while( !sqlite3Isxdigit(c) ){
        u32 ch = Utf8Read(zHex);
        assert( zHex<=zEnd );
        if( !strContainsChar(zPass, nPass, ch) ) goto unhex_null;
        c = *zHex;
        if( c==0x00 ) goto unhex_done;
      }
      zHex++;
      assert( *zEnd==0x00 );
      assert( zHex<=zEnd );
      d = *(zHex++);
      if( !sqlite3Isxdigit(d) ) goto unhex_null;
      *(p++) = (sqlite3HexToInt(c)<<4) | sqlite3HexToInt(d);
    }
  }

 unhex_done:
  sqlite3_result_blob(pCtx, pBlob, (p - pBlob), sqlite3_free);
  return;

 unhex_null:
  sqlite3_free(pBlob);
  return;
}


/*
** The zeroblob(N) function returns a zero-filled blob of size N bytes.
*/
static void zeroblobFunc(
  sqlite3_context *context,
  int argc,

#ifndef SQLITE_OMIT_FLOATING_POINT
    FUNCTION(round,              1, 0, 0, roundFunc        ),
    FUNCTION(round,              2, 0, 0, roundFunc        ),
#endif
    FUNCTION(upper,              1, 0, 0, upperFunc        ),
    FUNCTION(lower,              1, 0, 0, lowerFunc        ),
    FUNCTION(hex,                1, 0, 0, hexFunc          ),
    FUNCTION(unhex,              1, 0, 0, unhexFunc        ),
    FUNCTION(unhex,              2, 0, 0, unhexFunc        ),
    INLINE_FUNC(ifnull,          2, INLINEFUNC_coalesce, 0 ),
    VFUNCTION(random,            0, 0, 0, randomFunc       ),
    VFUNCTION(randomblob,        1, 0, 0, randomBlob       ),
    FUNCTION(nullif,             2, 0, 1, nullifFunc       ),
    DFUNCTION(sqlite_version,    0, 0, 0, versionFunc      ),
    DFUNCTION(sqlite_source_id,  0, 0, 0, sourceidFunc     ),
    FUNCTION(sqlite_log,         2, 0, 0, errlogFunc       ),

} -body {
  execsql {
    SELECT a = ( SELECT d FROM (SELECT d FROM t2) ) FROM t1 
  }
} -test {
  faultsim_test_result {0 {}}
}

do_faultsim_test 2 -faults oom* -prep {
  faultsim_restore_and_reopen
} -body {
  execsql {
    SELECT hex ( unhex('ABCDEF') );
  }
} -test {
  faultsim_test_result {0 ABCDEF}
}


finish_test

# 2023 January 23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#

set testdir [file dirname $argv0]
source [file join $testdir tester.tcl]

set testprefix unhex


foreach {tn hex} {
  1  0000
  2  FFFF
  3  0123456789ABCDEF
} {
  do_execsql_test 1.$tn.1 {
    SELECT hex( unhex( $hex ) );
  } $hex

  do_execsql_test 1.$tn.2 {
    SELECT hex( unhex( lower( $hex ) ) );
  } $hex
}

do_execsql_test 2.0 {
  SELECT typeof( unhex('') ), length( unhex('') );
} {blob 0}

foreach {tn hex} {
  1  ABC
  2  hello
  3  123456x7
  4  0xff
} {
  do_execsql_test 2.$tn {
    SELECT unhex( $hex ) IS NULL;
  } 1
}

do_catchsql_test 3.0 {
  SELECT unhex();
} {1 {wrong number of arguments to function unhex()}}
do_catchsql_test 3.1 {
  SELECT unhex('ABCD', '1234', '');
} {1 {wrong number of arguments to function unhex()}}

#--------------------------------------------------------------------------
# Test the 2-argument version.
#
foreach {tn hex} {
  1 "FFFF  ABCD"
  2 "FFFF ABCD"
  3 "FFFFABCD "
  4 " FFFFABCD"
  5 "--FFFF AB- -CD- "
  6 "--"
  7 " --"
} {
  set out ""
  foreach x [split $hex ""] {
    if {[string is xdigit $x]} { append out $x }
  }

  do_execsql_test 5.$tn.1 {
    SELECT hex( unhex($hex, ' -') );
  } [list $out]
}

do_execsql_test 6.0 {
  SELECT typeof( unhex(' ', ' -') ), length( unhex('-', ' -') );
} {blob 0}


do_execsql_test 6.1 "
  SELECT hex( unhex('\u0E01ABCD\u0E02', '\uE01\uE02') )
" {ABCD}
do_execsql_test 6.2 "
  SELECT typeof( unhex('\u0E01ABCD\u0E02', '\uE03\uE02') )
" {null}
do_execsql_test 6.3 "
  SELECT hex( unhex('\u0E01AB CD\uE02\uE01', '\uE01 \uE02') )
" {ABCD}

#--------------------------------------------------------------------------
# Test that if either argument is NULL, the returned value is also NULL.
#
do_execsql_test 6.4.1 { SELECT typeof(unhex(NULL)) } {null}
do_execsql_test 6.4.2 { SELECT typeof(unhex(NULL, ' ')) } {null}
do_execsql_test 6.4.3 { SELECT typeof(unhex('1234', NULL)) } {null}


finish_test