/*************************************************************************************
 * 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,
   
 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 der Trigger (samt zugehöriger Prozedur) schon existiert: Lösche ihn.
DROP TRIGGER  IF EXISTS encrpyt_password_trigger ON haendler;
DROP FUNCTION IF EXISTS encrpyt_password_function();

CREATE FUNCTION encrpyt_password_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    -- Wenn ein neues Passwort eingegeben wird, 
    -- speichere das Passwort gezalzen und verschlüsselt.
    IF (TG_OP = 'INSERT' OR 
        (TG_OP = 'UPDATE' AND NEW.h_password <> OLD.h_password)
       ) 
      THEN NEW.h_password = crypt(NEW.h_password, gen_salt('bf',8));  
    END IF;

    -- Gibt das modifizierte NEW-Tupel zurück.
    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_password wird gesalzen und verschlüsselt.
CREATE TRIGGER encrpyt_password_trigger 
BEFORE INSERT OR UPDATE
ON haendler
FOR EACH ROW 
  EXECUTE PROCEDURE encrpyt_password_function()
;

-- Erstelle Funktion zum Testen, ob das Passwort korrekt eingegeben wurde.
DROP FUNCTION IF EXISTS check_password(VARCHAR, VARCHAR);

-- usr: Benutzername oder E-Mail-Adresse
CREATE FUNCTION check_password(usr VARCHAR, pw VARCHAR) RETURNS BOOLEAN 
RETURNS NULL ON NULL INPUT
LANGUAGE SQL
IMMUTABLE
AS
$$
  SELECT h.h_password = crypt(pw, h.h_password)
  FROM   haendler h
  WHERE  usr IN (h.h_user_name, h.h_email);
$$  
;


/* 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 = crypt('geheim', h_password) AS password_correct
FROM   haendler
WHERE  h_id = 1;

SELECT check_password('hans@maier.de', 'geheim');
SELECT check_password('müller', 'geheim');
SELECT check_password('maier2', 'geheim');


SELECT check_password('maier2', '123456'), check_password('maier2', '654321');

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

SELECT check_password('maier2', '123456'), check_password('maier2', '654321');


SELECT check_password(h_user_name, 'geheim'), h_email, h_password
FROM   haendler
WHERE  h_user_name = 'maier1';

UPDATE haendler
SET    h_email = 'hannes@maier.de'
WHERE  h_id = 1;

SELECT check_password(h_user_name, 'geheim'), h_email, h_password
FROM   haendler
WHERE  h_user_name = 'maier1';