/*************************************************************************************
 * Beispiel aus der Vorlesung "Multimedia-Datenbanken" von Wolfgang Kowarschick
 *
 * Siehe auch: https://crackstation.net/hashing-security.htm
 *************************************************************************************/

/* Alte Tabellen etc. (in der richtigen Reihenfolge) löschen. */

DROP TABLE  IF EXISTS haendler CASCADE;
DROP DOMAIN IF EXISTS D_EMAIL  CASCADE;


/* Domains definieren */

-- CREATE DOMAIN IF NOT EXISTS D_UNTAINTED
-- AS
-- VARCHAR CHECK (value !~ '[<>"'';]|--|/\*');
DO 
$$ 
  BEGIN
    CREATE DOMAIN D_UNTAINTED
    AS
    VARCHAR CHECK (value !~ '[<>"'';]|--|/\*');
  EXCEPTION
    WHEN duplicate_object THEN null;
  END 
$$;

CREATE DOMAIN D_EMAIL
AS
VARCHAR
-- https://stackoverflow.com/questions/201323/how-to-validate-an-email-address-using-a-regular-expression
CHECK (value ~* '\A(?:[a-z0-9!#$%&''*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&''*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])\Z');


/* Tabellen definieren */

CREATE TABLE haendler
(h_id        INTEGER     NOT NULL,
 h_name      D_UNTAINTED NOT NULL,
 h_ortschaft D_UNTAINTED,
   
 h_user_name D_UNTAINTED,
 h_email     D_EMAIL     NOT NULL,
 h_password  VARCHAR,
 h_salt      VARCHAR,
   
 CONSTRAINT pk_haendler
   PRIMARY KEY (h_id),

 CONSTRAINT unique_haendler_user_name
   UNIQUE (h_user_name), 
 
 CONSTRAINT unique_haendler_email
   UNIQUE (h_email)
);

  
/* Trigger zur Passwort-Speicherung */

-- Falls die Trigger (samt zugehörigen Prozeduren) schon existieren: Lösche sie.
DROP TRIGGER  IF EXISTS encrpyt_password_trigger_insert ON haendler;
DROP FUNCTION IF EXISTS encrpyt_password_function_insert();

DROP TRIGGER  IF EXISTS encrpyt_password_trigger_update ON haendler;
DROP FUNCTION IF EXISTS encrpyt_password_function_update();

CREATE FUNCTION encrpyt_password_function_insert() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    -- Erzeuge einen zufälligen Salt-String.
    NEW.h_salt     = encode(gen_random_bytes(258), 'base64');

    -- Salze das Passwort und speichere es als Hashwert.
    NEW.h_password = encode(digest(NEW.h_password || NEW.h_salt, 'sha512'), 'hex');  

    -- Gibt das modifizierte NEW-Tupel zurück.
    RETURN NEW;
  END
$$
;

CREATE FUNCTION encrpyt_password_function_update() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    -- Ändere ddas Salz und das Passwort nur, wenn ein neues Passwort
    -- eingegeben wurde. Falls nur andere Attribute geändert werden,
    -- dürfen das Salz und das Passwort nicht veraändert werden.
    IF (NEW.h_password <> OLD.h_password) THEN
      NEW.h_salt     = encode(gen_random_bytes(256), 'base64');
      NEW.h_password = encode(digest(NEW.h_password || NEW.h_salt, 'sha512'), 'hex');  
    ELSE
      NEW.h_salt = OLD.h_salt;  -- Dder Salzwert darf unter keinen Umsänden 
                                -- vom Benutzer verändert werden.
    END IF;

    RETURN NEW;
  END
$$
;

-- Jedes Mal BEVOR ein neues Tupel in haendler eingefügt wird bzw. den alten Wert ersetzt,
-- wird es modifiziert. Der Wert des Attributs h_salt wird automatisch ermittelt,
-- der Wert des Attributs h_password wird verschlüsselt.

CREATE TRIGGER encrpyt_password_trigger_insert 
BEFORE INSERT 
ON haendler
FOR EACH ROW 
  EXECUTE PROCEDURE encrpyt_password_function_insert()
;

CREATE TRIGGER encrpyt_password_trigger_update 
BEFORE UPDATE 
ON haendler
FOR EACH ROW 
  EXECUTE PROCEDURE encrpyt_password_function_update()
;


-- Daten einfügen
 
INSERT INTO haendler(h_id, h_name, h_ortschaft, h_user_name, h_email, h_password)
VALUES 
  (1, 'Maier',   'Königsbrunn', 'maier1',  'hans@maier.de',          'geheim'),
  (2, 'Müller',  'Königsbrunn', 'müller',  'gerd@mueller.de',        'geheim'),
  (3, 'Maier',   'Augsburg',    'maier2',  'hans@wrzlbrmpft.de',     '123456'),
  (4, 'Huber',   NULL,          'huber',   'huber@huber-gmbh.com',   '"Iw!.d%6z*"'),
  (5, 'Schmidt', 'Hamburg',     'schmidt', 'schmiddy@schmidt.firma', 'g3h31m')
;

/* Passwort-Abfrage */

SELECT * FROM haendler;

SELECT h_password = encode(digest('geheim' || h_salt, 'sha512'), 'hex') AS password_correct
FROM   haendler
WHERE  h_id = 1;


/* Vereinfachung mit Hilfe einer Passwort-Abfrage-Funktion */

DROP FUNCTION IF EXISTS check_password(pw VARCHAR, h haendler);

CREATE FUNCTION check_password(pw VARCHAR, h haendler) RETURNS BOOLEAN
RETURNS NULL ON NULL INPUT
LANGUAGE SQL
IMMUTABLE
AS
$$
  SELECT h.h_password = encode(digest(pw || h.h_salt, 'sha512'), 'hex');
$$  
;

/* Ein paar weitere Tests */

-- Die obige Passwort-Überprüfung kann nun einfacher erfolgen:

SELECT check_password('geheim', haendler)
FROM   haendler
WHERE  h_id = 1;

SELECT check_password('geheim', h)
FROM   haendler h
WHERE  h_id = 2;

SELECT check_password('geheim', h), h.*
FROM   haendler h;



SELECT check_password('123456', h), check_password('654321', h), h_salt
FROM   haendler h
WHERE  h_id = 3;

UPDATE haendler
SET    h_password = '654321'
WHERE  h_id = 3;

SELECT check_password('123456', h), check_password('654321', h), h_salt
FROM   haendler h
WHERE  h_id = 3;


SELECT check_password('geheim', h), h_email, h_salt
FROM   haendler h
WHERE  h_id = 1;

UPDATE haendler
SET    h_email = 'hannes@maier.de',
       h_salt  = 'DIES HAT KEINEN EFFEKT'
WHERE  h_id = 1;

SELECT check_password('geheim', h), h_email, h_salt
FROM   haendler h
WHERE  h_id = 1;