Skip to content

Instantly share code, notes, and snippets.

@pyramation

pyramation/a.md Secret

Last active November 18, 2020 22:45
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pyramation/15e2b531ea973fccd0011ce334030adc to your computer and use it in GitHub Desktop.
Save pyramation/15e2b531ea973fccd0011ce334030adc to your computer and use it in GitHub Desktop.
Download ZIP
TOTP
Raw
base32.sql
CREATE SCHEMA base32;
CREATE FUNCTION base32.string_to_bytea ( input text ) RETURNS bytea AS $EOFCODE$
DECLARE
i int;
codes int[];
output bytea;
len int = character_length(input);
BEGIN
RETURN input::bytea;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.bytea_to_ascii ( input bytea ) RETURNS int[] AS $EOFCODE$
DECLARE
i int;
codes int[];
len int = octet_length(input);
BEGIN
FOR i IN 0 .. len-1 LOOP
codes = array_append(codes, get_byte( input, i ));
END LOOP;
RETURN codes;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.bytea_to_string ( input bytea ) RETURNS text AS $EOFCODE$
SELECT encode(input, 'escape');
$EOFCODE$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION base32.bytea_to_str ( input bytea ) RETURNS text AS $EOFCODE$
DECLARE
i int;
codes int[];
output text;
len int = octet_length(input);
BEGIN
-- get ascii codes
codes = base32.bytea_to_ascii(input);
-- convert to string
FOR i IN 1 .. len LOOP
output = concat(output, chr(codes[i]) );
END LOOP;
RETURN output;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.to_groups ( input bytea ) RETURNS text AS $EOFCODE$
DECLARE
i int;
output bytea;
bytes bytea;
len int = octet_length(input);
nlen int;
bitlength int;
nbitlength int;
byte_to_write int;
byte_to_read int;
bit_to_write int;
bit_to_read int;
bits_left int;
mod5 int;
starting_bit int;
out int[];
BEGIN
IF ( len % 5 = 0 ) THEN
RETURN input;
END IF;
nlen = len + 5 - (len % 5);
-- create blank bytea size of new length
output = lpad('', nlen, 'x')::bytea;
FOR i IN 0 .. len-1 LOOP
output = set_byte(output, i, get_byte(input, i));
END LOOP;
FOR i IN 1 .. 5 - (len % 5) LOOP
output = set_byte(output, i+len-1, 255);
END LOOP;
-- 2 chars required to represent each byte ( 2 * nlen)
-- '\x0000000000'::bytea
-- bytes = ('\x' || lpad('', nlen*2, '0'))::bytea;
bitlength = len * 8;
nbitlength = nlen * 8;
-- 2 chars for each byte needed
-- num bytes = nbitlength / 5
bits_left = 8;
byte_to_write = -1;
bit_to_write = 0;
bit_to_read = 4;
starting_bit = 4;
bytes = ('\x' || lpad('', (nbitlength/5)*2, '0'))::bytea;
FOR i IN 0 .. nbitlength-1 LOOP
mod5 = i % 5;
IF (mod5 = 0) THEN
byte_to_write = byte_to_write + 1;
IF (i != 0) THEN
starting_bit = starting_bit + 5;
bit_to_read = starting_bit;
END IF;
END IF;
bit_to_write = (byte_to_write * 8) + mod5;
IF (bit_to_read < bitlength) THEN
bytes = set_bit(bytes, bit_to_write, get_bit(input, bit_to_read));
ELSE
bytes = set_bit(bytes, bit_to_write, 0);
END IF;
bit_to_read = bit_to_read - 1;
bits_left = bits_left - 1;
IF (bits_left < 0) THEN
bits_left = 8;
END IF;
END LOOP;
FOR i IN 0 .. octet_length(bytes)-1 LOOP
out = array_append(out, get_byte(bytes, i));
END LOOP;
RETURN array_to_string(out, '-');
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.binary_to_int ( input text ) RETURNS int AS $EOFCODE$
DECLARE
i int;
buf text;
BEGIN
buf = 'SELECT B''' || input || '''::int';
EXECUTE buf INTO i;
RETURN i;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.to_ascii ( input text ) RETURNS int[] AS $EOFCODE$
DECLARE
i int;
output int[];
BEGIN
FOR i IN 1 .. character_length(input) LOOP
output = array_append(output, ascii(substring(input from i for 1)));
END LOOP;
RETURN output;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.to_binary ( input int ) RETURNS text AS $EOFCODE$
DECLARE
i int = 1;
j int = 0;
output char[] = ARRAY['x', 'x', 'x', 'x', 'x', 'x', 'x', 'x'];
BEGIN
WHILE i < 256 LOOP
output[8-j] = (CASE WHEN (input & i) > 0 THEN '1' ELSE '0' END)::char;
i = i << 1;
j = j + 1;
END LOOP;
RETURN array_to_string(output, '');
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.to_binary ( input int[] ) RETURNS text[] AS $EOFCODE$
DECLARE
i int;
output text[];
BEGIN
FOR i IN 1 .. cardinality(input) LOOP
output = array_append(output, base32.to_binary(input[i]));
END LOOP;
RETURN output;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.to_groups ( input text[] ) RETURNS text[] AS $EOFCODE$
DECLARE
i int;
output text[];
len int = cardinality(input);
BEGIN
IF ( len % 5 = 0 ) THEN
RETURN input;
END IF;
FOR i IN 1 .. 5 - (len % 5) LOOP
input = array_append(input, 'xxxxxxxx');
END LOOP;
RETURN input;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.string_nchars ( text, int ) RETURNS text[] AS $EOFCODE$
SELECT ARRAY(SELECT substring($1 from n for $2)
FROM generate_series(1, length($1), $2) n);
$EOFCODE$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION base32.to_chunks ( input text[] ) RETURNS text[] AS $EOFCODE$
DECLARE
i int;
output text[];
str text;
len int = cardinality(input);
BEGIN
RETURN base32.string_nchars(array_to_string(input, ''), 5);
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.fill_chunks ( input text[] ) RETURNS text[] AS $EOFCODE$
DECLARE
i int;
output text[];
chunk text;
len int = cardinality(input);
BEGIN
FOR i IN 1 .. len LOOP
chunk = input[i];
IF (chunk ~* '[0-1]+') THEN
chunk = replace(chunk, 'x', '0');
END IF;
output = array_append(output, chunk);
END LOOP;
RETURN output;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.to_decimal ( input text[] ) RETURNS text[] AS $EOFCODE$
DECLARE
i int;
output text[];
chunk text;
buf text;
len int = cardinality(input);
BEGIN
FOR i IN 1 .. len LOOP
chunk = input[i];
IF (chunk ~* '[x]+') THEN
chunk = '=';
ELSE
chunk = base32.binary_to_int(input[i])::text;
END IF;
output = array_append(output, chunk);
END LOOP;
RETURN output;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.base32_alphabet ( input int ) RETURNS char(1) AS $EOFCODE$
DECLARE
alphabet text[] = ARRAY[
'A', 'B', 'C', 'D', 'E', 'F',
'G', 'H', 'I', 'J', 'K', 'L',
'M', 'N', 'O', 'P', 'Q', 'R',
'S', 'T', 'U', 'V', 'W', 'X',
'Y', 'Z', '2', '3', '4', '5',
'6', '7'
]::text;
BEGIN
RETURN alphabet[input+1];
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.to_base32 ( input text[] ) RETURNS text AS $EOFCODE$
DECLARE
i int;
output text[];
chunk text;
buf text;
len int = cardinality(input);
BEGIN
FOR i IN 1 .. len LOOP
chunk = input[i];
IF (chunk = '=') THEN
chunk = '=';
ELSE
chunk = base32.base32_alphabet(chunk::int);
END IF;
output = array_append(output, chunk);
END LOOP;
RETURN array_to_string(output, '');
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.encode ( input text ) RETURNS text AS $EOFCODE$
BEGIN
IF (character_length(input) = 0) THEN
RETURN '';
END IF;
RETURN
base32.to_base32(
base32.to_decimal(
base32.fill_chunks(
base32.to_chunks(
base32.to_groups(
base32.to_binary(
base32.to_ascii(
input
)
)
)
)
)
)
);
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.base32_alphabet_to_decimal ( input text ) RETURNS text AS $EOFCODE$
DECLARE
alphabet text = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
alpha int;
BEGIN
alpha = position(input in alphabet) - 1;
IF (alpha < 0) THEN
RETURN '=';
END IF;
RETURN alpha::text;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.base32_to_decimal ( input text ) RETURNS text[] AS $EOFCODE$
DECLARE
i int;
output text[];
BEGIN
input = upper(input);
FOR i IN 1 .. character_length(input) LOOP
output = array_append(output, base32.base32_alphabet_to_decimal(substring(input from i for 1)));
END LOOP;
RETURN output;
END;
$EOFCODE$ LANGUAGE plpgsql STABLE;
CREATE FUNCTION base32.decimal_to_chunks ( input text[] ) RETURNS text[] AS $EOFCODE$
DECLARE
i int;
part text;
output text[];
BEGIN
FOR i IN 1 .. cardinality(input) LOOP
part = input[i];
IF (part = '=') THEN
output = array_append(output, 'xxxxx');
ELSE
output = array_append(output, right(base32.to_binary(part::int), 5));
END IF;
END LOOP;
RETURN output;
END;
$EOFCODE$ LANGUAGE plpgsql STABLE;
CREATE FUNCTION base32.base32_alphabet_to_decimal_int ( input text ) RETURNS int AS $EOFCODE$
DECLARE
alphabet text = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
alpha int;
BEGIN
alpha = position(input in alphabet) - 1;
RETURN alpha;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.zero_fill ( a int, b int ) RETURNS bigint AS $EOFCODE$
DECLARE
bin text;
m int;
BEGIN
IF (b >= 32 OR b < -32) THEN
m = b/32;
b = b-(m*32);
END IF;
IF (b < 0) THEN
b = 32 + b;
END IF;
IF (b = 0) THEN
return ((a>>1)&2147483647)*2::bigint+((a>>b)&1);
END IF;
IF (a < 0) THEN
a = (a >> 1);
a = a & 2147483647; -- 0x7fffffff
a = a | 1073741824; -- 0x40000000
a = (a >> (b - 1));
ELSE
a = (a >> b);
END IF;
RETURN a;
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION base32.decode ( input text ) RETURNS text AS $EOFCODE$
DECLARE
i int;
arr int[];
output text[];
len int;
num int;
value int = 0;
index int = 0;
bits int = 0;
BEGIN
input = replace(input, '=', '');
input = upper(input);
len = character_length(input);
num = len * 5 / 8;
IF (len = 0) THEN
RETURN '';
END IF;
select array(select * from generate_series(1,num))
INTO arr;
FOR i IN 1 .. len LOOP
value = (value << 5) | base32.base32_alphabet_to_decimal_int(substring(input from i for 1));
bits = bits + 5;
IF (bits >= 8) THEN
-- arr[index] = (value >>> (bits - 8)) & 255;
arr[index] = base32.zero_fill(value, (bits - 8)) & 255;
index = index + 1;
bits = bits - 8;
END IF;
END LOOP;
len = cardinality(arr);
FOR i IN 0 .. len-2 LOOP
output = array_append(output, chr(arr[i]));
END LOOP;
RETURN array_to_string(output, '');
END;
$EOFCODE$ LANGUAGE plpgsql STABLE;
Raw
totp.sql
CREATE SCHEMA totp;
CREATE FUNCTION totp.urlencode ( in_str text ) RETURNS text AS $EOFCODE$
DECLARE
_i int4;
_temp varchar;
_ascii int4;
_result text := '';
BEGIN
FOR _i IN 1..length(in_str)
LOOP
_temp := substr(in_str, _i, 1);
IF _temp ~ '[0-9a-zA-Z:/@._?#-]+' THEN
_result := _result || _temp;
ELSE
_ascii := ascii(_temp);
IF _ascii > x'07ff'::int4 THEN
RAISE exception 'won''t deal with 3 (or more) byte sequences.';
END IF;
IF _ascii <= x'07f'::int4 THEN
_temp := '%' || to_hex(_ascii);
ELSE
_temp := '%' || to_hex((_ascii & x'03f'::int4) + x'80'::int4);
_ascii := _ascii >> 6;
_temp := '%' || to_hex((_ascii & x'01f'::int4) + x'c0'::int4) || _temp;
END IF;
_result := _result || upper(_temp);
END IF;
END LOOP;
RETURN _result;
END;
$EOFCODE$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION totp.base32_to_hex ( input text ) RETURNS text AS $EOFCODE$
DECLARE
output text[];
decoded text = base32.decode(input);
len int = character_length(decoded);
hx text;
BEGIN
FOR i IN 1 .. len LOOP
hx = to_hex(ascii(substring(decoded from i for 1)))::text;
IF (character_length(hx) = 1) THEN
-- if it is odd number of digits, pad a 0 so it can later
hx = '0' || hx;
END IF;
output = array_append(output, hx);
END LOOP;
RETURN array_to_string(output, '');
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION totp.hotp ( key bytea, c int, digits int DEFAULT 6, hash text DEFAULT 'sha1' ) RETURNS text AS $EOFCODE$
DECLARE
c BYTEA := '\x' || LPAD(TO_HEX(c), 16, '0');
mac BYTEA := HMAC(c, key, hash);
trunc_offset INT := GET_BYTE(mac, length(mac) - 1) % 16;
result TEXT := SUBSTRING(SET_BIT(SUBSTRING(mac FROM 1 + trunc_offset FOR 4), 7, 0)::TEXT, 2)::BIT(32)::INT % (10 ^ digits)::INT;
BEGIN
RETURN LPAD(result, digits, '0');
END;
$EOFCODE$ LANGUAGE plpgsql IMMUTABLE;
CREATE FUNCTION totp.generate ( secret text, period int DEFAULT 30, digits int DEFAULT 6, time_from timestamptz DEFAULT now(), hash text DEFAULT 'sha1', encoding text DEFAULT 'base32', clock_offset int DEFAULT 0 ) RETURNS text AS $EOFCODE$
DECLARE
c INT := FLOOR(EXTRACT(EPOCH FROM time_from) / period)::INT + clock_offset;
key bytea;
BEGIN
IF (encoding = 'base32') THEN
key = ( '\x' || totp.base32_to_hex(secret) )::bytea;
ELSE
key = secret::bytea;
END IF;
RETURN totp.hotp(key, c, digits, hash);
END;
$EOFCODE$ LANGUAGE plpgsql STABLE;
CREATE FUNCTION totp.generate_secret ( hash text DEFAULT 'sha1' ) RETURNS bytea AS $EOFCODE$
BEGIN
-- See https://tools.ietf.org/html/rfc4868#section-2.1.2
-- The optimal key length for HMAC is the block size of the algorithm
CASE
WHEN hash = 'sha1' THEN RETURN gen_random_bytes(20); -- = 160 bits
WHEN hash = 'sha256' THEN RETURN gen_random_bytes(32); -- = 256 bits
WHEN hash = 'sha512' THEN RETURN gen_random_bytes(64); -- = 512 bits
ELSE
RAISE EXCEPTION 'Unsupported hash algorithm for OTP (see RFC6238/4226).';
RETURN NULL;
END CASE;
END;
$EOFCODE$ LANGUAGE plpgsql VOLATILE;
CREATE FUNCTION totp.verify ( secret text, check_totp text, period int DEFAULT 30, digits int DEFAULT 6, time_from timestamptz DEFAULT now(), hash text DEFAULT 'sha1', encoding text DEFAULT NULL, clock_offset int DEFAULT 0 ) RETURNS boolean AS $EOFCODE$
SELECT totp.generate (
secret,
period,
digits,
time_from,
hash,
encoding,
clock_offset) = check_totp;
$EOFCODE$ LANGUAGE sql;
CREATE FUNCTION totp.url ( email text, totp_secret text, totp_interval int, totp_issuer text ) RETURNS text AS $EOFCODE$
SELECT
concat('otpauth://totp/', totp.urlencode (email), '?secret=', totp.urlencode (totp_secret), '&period=', totp.urlencode (totp_interval::text), '&issuer=', totp.urlencode (totp_issuer));
$EOFCODE$ LANGUAGE sql STRICT IMMUTABLE;
CREATE FUNCTION totp.random_base32 ( _length int DEFAULT 16 ) RETURNS text LANGUAGE sql AS $EOFCODE$
SELECT
string_agg(('{a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z,2,3,4,5,6,7}'::text[])[ceil(random() * 32)], '')
FROM
generate_series(1, _length);
$EOFCODE$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment