Beispiele der Vorlesung 10

SQL-Beispiele aus dem Miro-Board (08 Benutzerverwaltung)

SQL-Dateien

haendler1.sql	(CREATE-TABLE- und INSERT-Befeh­le: Prak­ti­kums­ver­si­on, etwas mehr Daten)
haendler1_uuid.sql	(Eine Händ­ler-Daten­bank id vom Typ UUID)
update-01.sql	(Bei­spie­le zur Update-Operation)

Postgres: Kryptographische Befehle (random, encoding)

/* Verschlüsselung */

SELECT gen_random_uuid();

SELECT gen_random_bytes(256);

SELECT encode(gen_random_bytes(256), 'hex');
SELECT encode(gen_random_bytes(256), 'base64');

SELECT digest('geheim', 'md5');
SELECT encode(digest('geheim', 'md5'), 'hex');
-- http://md5decrypt.net/en/
-- https://crackstation.net/
-- e8636ea013e682faf61f56ce1cb1ab5c

SELECT digest('geheim', 'sha256');
SELECT encode(digest('geheim', 'sha256'), 'hex');
-- http://md5decrypt.net/en/Sha256/
-- https://crackstation.net/
-- addb0f5e7826c857d7376d1bd9bc33c0c544790a2eac96144a8af22b1298c940

SELECT digest('geheim', 'sha512');
SELECT encode(digest('geheim', 'sha512'), 'hex');
-- http://md5decrypt.net/en/Sha512/
-- https://crackstation.net/
-- 8d847e01d22baa969f71fa362b4de21c9e13c7882bcea13ba5c6a8ae0d71fc8c9700c82e0087a65c8b37bd29f536747f28c9672bec1cae7762d2c9f36b6013f2

SELECT encode(gen_random_bytes(32), 'base64');
-- SKKO6bHd8U+IQ/7TR5IQQXV+4JYhTIhi9eXMGTIDmog=
SELECT encode(digest('geheim' || 'SKKO6bHd8U+IQ/7TR5IQQXV+4JYhTIhi9eXMGTIDmog=', 'sha512'), 'hex');
-- http://md5decrypt.net/en/Sha512/ liefert das Passwort nicht mehr im Klartext
-- https://crackstation.net/
-- 9750c58338678863cd90751495ef293d8824d8acbe43172c2b4d8b100156fc82f0b63970a7ad19fecbded2e61b955c12dc8bc41b91a34044d393da7c52401f7c

Postgres: Kryptographische Befehle (crypt)

-- https://www.postgresql.org/docs/current/static/pgcrypto.html
SELECT gen_salt('bf'), gen_salt('bf'), gen_salt('bf');

SELECT crypt('geheim', gen_salt('bf')), crypt('geheim', gen_salt('bf'));

SELECT crypt('geheim', '$2a$06$pKJmBXm9ihFbTBCl3/wBM.'), '$2a$06$pKJmBXm9ihFbTBCl3/wBM.';

SELECT crypt('geheim', '§"$%fdg43TREGD54ü3245ete§$%§$%'), crypt('password', '§"$%fdg43TREGD54ü3245ete§$%§$%');
SELECT crypt('geheim', '$2a$06$pKJmBXm9ihFbTBCl3/wBM.'),  crypt('password', '$2a$06$pKJmBXm9ihFbTBCl3/wBM.');

SELECT gen_salt('bf');
SELECT gen_salt('bf',8);
SELECT gen_salt('bf',14);

SELECT crypt('geheim', gen_salt('bf'));
SELECT crypt('geheim', gen_salt('bf',8));
SELECT crypt('geheim', gen_salt('bf',14));


-- '$2a$14$5PEKjgHVsugXNVXHWbZmt.9F4ieQw1iVHKrYngZFKIeGEB2/E6GLG'

SELECT
  ( '$2a$14$5PEKjgHVsugXNVXHWbZmt.9F4ieQw1iVHKrYngZFKIeGEB2/E6GLG'
  =
    crypt
    ( 'geheim',
      '$2a$14$5PEKjgHVsugXNVXHWbZmt.9F4ieQw1iVHKrYngZFKIeGEB2/E6GLG'
    )
  ) AS password_correct;
  
SELECT
  ( '$2a$14$5PEKjgHVsugXNVXHWbZmt.9F4ieQw1iVHKrYngZFKIeGEB2/E6GLG'
  =
    crypt
    ( 'passwort',
      '$2a$14$5PEKjgHVsugXNVXHWbZmt.9F4ieQw1iVHKrYngZFKIeGEB2/E6GLG'
    )
  ) AS password_correct;

Before-Trigger

- Falls der Trigger (samt zugehöriger Prozedur) schon existiert: Lösche ihn.
DROP TRIGGER  IF EXISTS h_name_mit_ortschaft_trigger ON haendler;
DROP FUNCTION IF EXISTS h_name_mit_ortschaft_function() CASCADE;

-- Erzeuge die Trigger-Prozedur. Diese berechnet den Wert von
-- h_name_mit_ortschaft aus den neuen Werten von h_name und h_ortschaft.
CREATE FUNCTION h_name_mit_ortschaft_function() RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
  BEGIN
    NEW.h_name_mit_ortschaft = COALESCE(NEW.h_name || ' in ' || NEW.h_ortschaft, NEW.h_name);
    -- 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_name_mit_ortschaft wird automatisch berechnet.
CREATE TRIGGER h_name_mit_ortschaft_trigger
BEFORE INSERT OR UPDATE
ON haendler
FOR EACH ROW
  EXECUTE PROCEDURE h_name_mit_ortschaft_function()
;

Untainted Strings

CREATE DOMAIN IF NOT EXISTS D_UNTAINTED
AS
VARCHAR CHECK (value !~ '[<>"'';]|--|/\*')
;

CREATE TABLE haendler
(id   UUID        NOT NULL gen_random_uuid(),
 name D_UNTAINTED NOT NULL,
 ...
);

Händler: Sichere Domains

/* 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
CHECK (value ~* '\A[a-z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-z0-9-]+(?:\.[a-z0-9-]+)*\Z')
;


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)
);

Händler: Hash and Salt

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
$$
;

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()
;

Händler: Hash-Tests

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;

Händler ohne ’salt”

DROP TABLE IF EXISTS haendler CASCADE;

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)
);

Händler: Crypt

-- 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);
$$  
;

Händler: Crypt-Tests

/* 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';