Verschlüsselte Daten in MySQL Datenbank

Ich musste für ein Projekt verschlüsselte Daten in einer MySQL Datenbank ablegen. Es gibt dazu zwei Möglichkeiten, dies zu tun. Zum einen verschlüsselt man die Daten auf der Seite des Web Servers. In meinem Fall wäre dies mittels PHP zu realisieren gewesen. Zum anderen verschlüsselt man die Daten auf dem Datenbankserver, also in diesem Fall in MySQL. Ich werde in diesem Artikel beide Wege beschreiben.

Datenverschlüsselung mit PHP

Ich arbeite mit PHP 7 und die Funktion mcrypt_encrypt() hat ab der Version 7.1 den Status „überholt“ (engl. DEPRECATED) erhalten. Deswegen verwende ich für die Verschlüsselung die OpenSSL Funktionen. Um eine gute Verschlüsselung zu erreichen, verwende ich einen 256 Bit AES Schlüssel. Das Ganze verpacke ich in eine kleine Klasse, um den Code wiederverwendbar zu machen. Und so sieht der Source Code für diese Aufgabe aus:


class Encryption
{
  // Konstante für Verschlüsselungsmethode
  const AES_256_CBC = 'aes-256-cbc';

  private $_secret_key = 'secret key'; // hier einen sicheren Schlüssel einsetzen
  private $_secret_iv  = 'secret iv'; // hier einen weiteren sicheren Schlüssel einsetzen
  private $_encryption_key;
  private $_iv;

  // im Konstruktor werden die Instanzvariablen initialisiert
  public function __construct()
  {
    $this->_encryption_key = hash('sha256', $this->_secret_key);
    $this->_iv             = substr(hash('sha256', $this->_secret_iv), 0, 16);
  }

  public function encryptString($data)
  {
    return base64_encode(openssl_encrypt($data, self::AES_256_CBC, $this->_encryption_key, 0, $this->_iv));
  }

  public function decryptString($data)
  {
    return openssl_decrypt(base64_decode($data), self::AES_256_CBC, $this->_encryption_key, 0, $this->_iv);
  }

  public function setEncryptionKey($key)
  {
    $this->_encryption_key = $key;
  }

  public function setInitVector($iv)
  {
    $this->_iv = $iv;
  }
}

In den beiden Instanzvariablen $_secret_key und $_secret_iv habe ich mit Hilfe eines Passwort Generators verschlüsselte Zeichenketten eingetragen. Ich würde eine Zeichenlänge von mindestens 16 Zeichen empfehlen (je länger, desto besser). Im Konstruktor wird mit diesen Schlüsseln dann ein Hash gebildet, der für die eigentliche OpenSSL Verschlüsselung verwendet wird. AES verwendet 16 Byte Blöcke, so daß der Initialisierungsvektor (iv) mit der SUBSTR() Funktion auf 16 Zeichen gekürzt wurde. Für eine detailliertere Beschreibung der genauen Vorgehensweise der OpenSSL Verschlüsselung habe ich am Ende dieses Artikels einige weiterführende Links vorbereitet. Die OpenSSL Bibliothek enthält weitere Funktionen, die man hier hätte einsetzen können. Aber ich habe mich für den einfachsten Weg entschieden, ohne die Zuhilfenahme irgendwelcher Black Boxes. Somit sind die einzelnen Schritte später leichter nachvollziehbar.

Die Anwendung dieser Klasse ist dann denkbar einfach. Man referenziert die Klasse, nimmt eine oder mehrere Felder aus einem Formular, verschlüsselt sie und schreibt das Ergebnis in die Datenbank.


$encryption_class = new Encryption();
// Formularfeld verschlüsseln
$encrypted = $encryption_class->encryptString($raw_string);

// Verschlüsselten String in die Datenbank schreiben
...

// Verschlüsselten Wert aus der Datenbank lesen und entschlüsseln
...
$decrypted = $encryption_class->decryptString($encrypted);

Auf das Lesen aus und dem Schreiben in die Datenbank habe ich an dieser Stelle verzichtet. Dies ist hier nicht mehr das Problem, nachdem die Daten bereits verschlüsselt vorliegen.

Datenverschlüsselung mit MySQL

Die zweite Methode basiert auf der Nutzung von SQL Statements, die ebenfalls eine Verschlüsselung von Daten ermöglichen. In MySQL gibt es hierzu die beiden Funktionen AES_ENCRYPT() und AES_DECRYPT(). Als Parameter benötigt man neben dem zu verschlüsselnden Wert noch einen Salt. Auch hierzu habe ich mich eines Passwort Generators bedient und einen 30 Stellen langen Schlüssel generiert, der zusammen mit der AES Verschlüsselung einem Hacker das Leben recht schwer machen dürfte.

Zu beachten ist in diesem Zusammenhang, daß der MySQL Server standardmäßig AES mit einem 128 Bit Schlüssel verschlüsselt. Man kann über eine Einstellung am Server auf eine höhere Verschlüsselung (192 oder 256 Bit) umschalten.

Für eine Verschlüsselung mit AES 256 Bit braucht man folgende Einstellungen am MySQL Server (diese Verschlüsselung benötigt ebenfalls einen Initialisierungsvektor):


SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = SHA2('My secret passphrase',512);
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = AES_ENCRYPT('text',@key_str,@init_vector);
SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);

Da ich unter PHP für die Datenbankabstraktion Zend_Db verwende, zeige ich zunächst, wie man den SQL String für die Nutzung mit Zend_Db_Table zusammenstellt.


// zentral abgelegten Salt abrufen
$salt = Zend_Registry::get['AES_SALT'];
// Verschlüsselung für das INSERT Statement definieren
$user['username'] = new Zend_Db_Expr("AES_ENCRYPT('" . $data[username] . "','" . $salt . "')");
// Neuen Datensatz mit verschlüsseltem Wert in die Datenbank schreiben
$db->insert($user);

// SELECT Statement zum Auslesen eines verschlüsselten Wertes aus der Datenbank
$select = $db->select()
             ->from($db->_name,
                    array(
                      'id',
                      'username' => new Zend_Db_Expr("AES_DECRYPT(username,'" . $salt . "')")
                    ));
$record = $db->fetchAll($select);

In reinem SQL würde die Abfrage so aussehen:


# Daten AES verschlüsselt schreiben
INSERT INTO user (id, username) VALUES
(1, AES_ENCRYPT('Hugo', 'SALT');

# AES verschlüsselte Daten lesen
SELECT id, AES_DECRYPT(username, 'SALT') FROM user;

Weiterführende Links

Ähnliche Artikel:

Kommentare(10)

EinsteinMar 2020

Lieber Adrian

vielen Dank für deine rasche Antwort. Mit deinem VARCHAR Tipp hat die Speicherung der verschlüsselten Strings geklappt. Mir ist daraufhin jedoch aufgefallen, dass ich so die Werte logischerweise nicht mehr alphabetisch direkt aus der DB ziehen kann. Insofern werde ich auf deine zweite Variante umsteigen und die Verschlüsselung MySQL überlassen.

Ich arbeite mit dem PDO-Driver und hab versucht, die SET’s in meinem Config File einzufügen. Doch irgendwie bockt der rum. Ich kopiere dir mein Code mal rein, damit du dir das anschauen kannst.

$pdo = new PDO( „mysql:host=“ . $DB_SERVER . „;dbname=“ . $DB_NAME . „“, $DB_USER, $DB_PW, array(PDO::MYSQL_ATTR_INIT_COMMAND => „SET NAMES utf8″ ));
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$sql = “
SET block_encryption_mode = ‚aes-256-cbc‘;
SET @key_str = SHA2(‚Geheim‘,512);
SET @init_vector = RANDOM_BYTES(16);
SET @crypt_str = AES_ENCRYPT(‚text‘,@key_str,@init_vector);
SELECT AES_DECRYPT(@crypt_str,@key_str,@init_vector);
„;
$sth = $dbh->prepare($sql);
$sth->execute();

Ich möchte nicht jedes Mal vor jedem INPUT in die Datenbank die SET’s machen müssen. Weisst du, ob ich die in meiner config.php einmal machen kann und dann hats das System gefressen? Siehst du irgendwo einen Fehler, warum das nicht läuft?

Vielen Dank für dein Hilfe und einen schönen Tag.
Einstein

    adminMar 2020

    Hallo Einstein,
    ich würde es anders machen. Lies die Datensätze zuerst verschlüsselt aus der Datenbank und schreibe sie in ein Array. Dabei entschlüsselst Du die verschlüsselten Felder. Im Anschluss kannst Du das Array nach dem Feld – mit den nunmehr entschlüsselten Daten – sortieren.

    Bei dem Verfahren in MySQL gehören die SET Anweisungen nicht in den SQL-String, sondern oben in die Initialisierung hinein. Ich müsste jetzt aber nachschauen, wo genau man dies hinzufügt. Das weiß ich gerade nicht auswendig. Solltest Du aber in der MySQL Dokumentation finden.
    Viele Grüße
    Adrian

EinsteinMar 2020

Lieber Admin

nach langer Recherche bezüglich sinnvoller Ver- und Entschlüsslung von Daten mittels PHP, bin ich auf deinen Blog gestossen. Mit grossem Interesse habe ich deinen Beitrag gelesen.

Die Ver- und Entschlüsselung mittels PHP funktioniert einwandfrei. Kannst du noch spezifizieren, welchen Type du in der MySQL gewählt hast, um den verschlüsselten String zu speichern? Wäre Binary oder Varbinary die richtige Wahl? Wenn ja, mit welcher Länge?

Ich habe das Problem, dass ich manchmal sehr lange Sätze verschlüsseln muss und dann die entschlüsselung nicht mehr sauber funktioniert, weil die Länge zu kurz ist.

Kannst du mir helfen?
Danke dir & lieber Gruss
Einstein

    adminMar 2020

    Hallo Einstein,
    ich habe mir das Leben leicht gemacht und Felder vom Typ VARCHAR verwendet. Damit gibt es keinerlei Probleme, wenn man diese Felder groß genug dimensioniert und erzeugt auch kein Overload, wenn manche Datensätze kleinere Datenmengen enthalten als andere. Und dennoch ist die Sicherheit gegeben durch die Verschlüsselung.
    Viele Grüße
    Adrian

NguyenAug 2019

Ich verwendet die Variante „Datenverschlüsselung mit PHP“. Es ist wunderbar und funktioniert gut. Vielen Dank!

tomMay 2019

Ich möchte mich hier ganz herzlich bedanken, denn das war genau das, was ich schon lange gesucht habe. Daten verschlüsselt in eine Datenbank speichern und für den administrativen Bereich wieder im Klartext anzeigen.

SUPER!!! Vielen Dank !

LG
-= tom =-

    adminJun 2019

    Es freut mich, wenn ich anderen mit meinen Erfahrungen helfen kann. Genau deswegen führe ich diesen Blog.

DieterNov 2018

Hallo,
Datenverschlüsselung mit PHP

Wenn ich einen String zum verschlüsseln übergebe, bekomme ich auch etwas zurück. Input „Haus1“ Output „M3A5aWdobU4wY2ZpdmtzUktEV0s0dz09“.
Beim Entschlüsseln kommt eine leerer String zurück.

Die Version auf dem Webserver ist „OpenSSL/1.0.2k“ und PHP Version 5.6.31 .

Mache ich hier grundsätzlich etwas falsch ?

    adminNov 2018

    Hallo Dieter,
    ich müsste schon den verwendeten Quellcode sehen, um die Frage zu beantworten. Einfach hier reinposten. Danke.

AndreaMay 2018

Tolle Vorlage! Nutze ich zur verschlüsselten Speicherung von Mail-Adressen. Vielen Dank :-)

Fragen oder Feedback zu diesem Artikel

Deine E-Mail-Adresse wird nicht veröffentlicht.