Hashids for TSQL (project no longer maintained)

A small set of TSQL functions to generate YouTube-like hashes from one or many numbers. Use hashids when you do not want to expose your database ids to the user.

http://www.hashids.org/

This repository contains a port to TSQL of the other projects found at hashids.org. The Javascript and .NET versions of Hashids are the primary reference projects for this port.

The included hashids-tsql generator creates a custom set of TSQL functions to encode values with your chosen salt and other options.

Status

This project is no longer maintained and will likely be archived soon.

The SQL functions generated by hashids-tsql can currently encode numbers, ~~but cannot decode them yet.~~ You can decode them with this function

Quick Start
What is it?
TSQL Functions
Use Cases
Generator Usage
TODO

Quick Start

The command below will generate a set of TSQL encode functions and test objects into the test.sql file.

npm install -g hashids-tsql

hashids-tsql -t test.sql

Results

What is it?

hashids (Hash ID's) creates short, unique, decryptable hashes from unsigned integers.

(NOTE: This is NOT a true cryptographic hash, since it is reversible.)

It was designed for websites to use in URL shortening, tracking stuff, or making pages private (or at least unguessable).

This algorithm tries to satisfy the following requirements:

Hashes must be unique and decryptable.
They should be able to contain more than one integer (so you can use them in complex or clustered systems).
You should be able to specify minimum hash length.
Hashes should not contain basic English curse words (since they are meant to appear in public places - like the URL).

Instead of showing items as 1, 2, or 3, you could show them as U6dc, u87U, and HMou. You can choose to store these hashes in the database or encrypt + decrypt on the fly. If storing them in the database, then hashids-tsql will let you encode hashids in a TSQL stored procedure, trigger or computed column.

All integers need to be greater than or equal to zero.

See hashids.org for more information on this technique.

TSQL Functions

TSQL does not have function overloading, so the single encode function that is common in other hashids.org libraries is instead represented here as a set of encode functions with slight variations in name and declaration.

The basic forms of encode for TSQL are:

encode1(int) string
encode2(int, int) string
encodeList(table) string
encodeSplit(string, string) string

In TSQL, the encode functions that take 1 or 2 integers will be much more useful than the one that takes a table because typically, you don't want to construct a table variable just to pass 1 or 2 integers into a function.

There are multiple variations on each basic form, in order to return different int and string types (varchar/nvarchar, int/bigint). encode1A returns a varchar (ASCII) value. encode1B accepts a bigint. encode1BA accepts a bigint and returns a varchar (and so on).

The SQL Server database project HashidsTsql in this repository contains a full set of pre-generated functions to test with.

Use Cases

The primary use case for a TSQL hashid encoding function can be summarized in the ComputedTest table where the table's HashId uses encode1 to hash the Id column as part of the atomic INSERT of a record.

However, calling encode1 or encode2 as part of a stored procedure that INSERTs might be preferable in order to avoid costly recomputes.

As a persisted computed column, HashId can be indexed. BEWARE that some situations can result in extremely expensive queries if you add a hashid column so work must be done in order to avoid recomputes.

If every table were to use the same encode1 function, then any row in any table with Id = 1 would have the same hash. Therefore, encode2 is provided, which takes 2 numbers. So, TableX can call encode2(1, [Id]) and TableY can call encode2(2, [Id]) and so on...guaranteeing that the hash for each table's [Id] column don't collide.

Two more TSQL encode functions are currently included which create a hashid from a list of numbers in a table or delimited string. They are encodeList and encodeSplit.

Generator Usage

Usage: hashids-tsql [options] [file or directory/ path]

Options:

  -h, --help               output usage information
  -V, --version            output the version number
  -d, --database [name]    Database name. [HashidsTsql]
  -m, --schema [name]      Database schema. [hashids]
  -a, --ascii              Generate ASCII/varchar compatible function(s).
  -b, --bigint             Generate BIGINT compatible function(s).
  -e, --encodeOnly         Generate encode function(s) only.
  -s, --salt [value]       Salt. [random]
  -n, --minHashLength [n]  Minimum hash length. [0]
  -l, --alphabet [value]   Alphabet. [a-z,A-Z,1-9,0]
  -x, --fileExt [value]    Extension for output files. [sql]
  -t, --test               Generate test procedureds and tables.

TODO

Find a better way to detect if a directory path is provided than by looking at the last character.
Replace No-UTF8-BOM-in-templates.txt note with usage of something like "strip-bom" in app.js
Create all manner of automated tests, primarily to test against other implementations.
Create TSQL functions for decoding and integrate them into the hashids-tsql generator.

brutaldev/hashids-tsql