Mit SQL ist es auch möglich, eigene Funktionen zu erstellen. In diesem und dem danach folgenden Artikel erstelle ich so eine Funktion unter Verwendung von verschiedenen bereits vorhandenen SQL-Funktionen und SQL-Tools. Im aktuellen Artikel werden die Vorarbeiten für die Funktion erstellt und im nächsten Artikel werden diese dann zu einer eigenen Funktion (UDF = User Defined Function) zusammengeführt.
Was ist für die Prüfung zu beachten?
Eine E-Mail-Adresse besteht aus einem @-Zeichen, sowie einem lokalen Teil davor und einem Domänenteil dahinter wie in meinem Beispiel lokaler-teil@edv-baer.com. Der Domänenteil richtet sich nach der Domain, unter dem die E-Mail-Adresse angelegt ist – in meinem Beispiel ist dies: edv-baer.com.
Laut Wikipedia ist das At-Zeichen oder kurz At, auch Adresszeichen, ist das Schriftzeichen @. Umgangssprachliche Bezeichnungen sind Affenschwanz, Affenohr, Affenschaukel, Klammeraffe. Das At-Zeichen ist grundlegender Bestandteil von E-Mail-Adressen, es steht dort zwischen Benutzername und Domain.
Um zu ermitteln, ob eine E-Mail-Adresse gültig ist, sind folgende Schritte erforderlich:
- Ist der Inhalt des Feldes E-Mail leer oder NULL? Sollen diese Werte zulässig ein?
- Ist das ‘@‘-Zeichen vorhanden?
- Überprüfen der Syntax
- Aufbau der E-Mail-Adresse nach RFC 5321 und RFC 5322
- Der lokale Teil der Adresse darf aus bis zu 64 Teichen bestehen (vor ‘@‘)
- Der Domänen-Teil darf aus bis zu 253 Zeichen bestehen (nach ‘@‘)
- Der erste Teil darf aus lateinischen Buchstaben, Zahlen und einigen Sonderzeichen bestehen
- Überprüfen der Domain
- Beim Domänen-Teil muss es sich um eine gültige Domäne handeln
Für die einzelnen Prüfungen und der zum Abschluss zu erstellenden Funktion soll nur SQL verwendet werden.
Für die Erstellung der Funktion werden mehrere eingebaute SQL-Funktionen wie LENGTH, POSITION, SUBSTRING, Prüfung auf gültigen Ausdruck mit REGEXP_LIKE und Konstrukte wie die CASE-Anweisung, SQL-Variable, das COMPOUND STATEMENT und die Tabellenfunktion DNS_LOOKUP verwendet.
Hinweis: Die hier verwendeten Prüfungen erheben keinen Anspruch auf Vollständigkeit und Korrektheit. Anpassungen können von Ihnen selbst durchgeführt werden.
Hier folgen nun die einzelnen Schritte zur Prüfung der E-Mail auf Korrektheit.
Meine Testumgebung
Zum Testen habe ich mir folgende E-Mail-Adressen in SQL-Variablen abgespeichert, um die Beispiele übersichtlich zu halten:
- create or replace variable qgpl.email0 varchar(400) default(‘ ‘); — leer ist gültige E-Mail
- create or replace variable qgpl.email varchar(400) default(‘info@edv-baer.com’); — gültige E-Mail
- create or replace variable qgpl.email2 varchar(400) default(‘info&edv-baer.com’); — ungültige E-Mail, @ fehlt
- create or replace variable qgpl.email3 varchar(400) default(‘1234567890123456789012345 67890123456789012345678901234567890123456789@edv-baer.com’); — ungültige E-Mail, 1.Teil > 64t
- create or replace variable qgpl.email5 varchar(400) default(‘info#@edv-baer.com’); — ungültige E-Mail, # vor @
- select qgpl.email5 from sysibm.sysdummy1;
- create or replace variable qgpl.email6 varchar(400) default(‘info@edv-baer.Xom’); — ungültige Domain
- select qgpl.email6 from sysibm.sysdummy1;
Für die einzelnen Prüfungen verwende ich die folgenden Stati:
Bedeutung der Prüf-Stati:
- 0 = kein Fehler
- 1 = Länge E-Mail > 318
- 2 = kein @-Zeichen vorhanden
- 3 = Länge 1.Teil > 64
- 4 = Länge 2.Teil > 253
- 5 = ungültige Syntax
- 6 = Domain existiert nicht
0) Prüfen, ob E-Mail = Blank oder IS NULL à soll zulässig sein
if qgpl.email = ‘ ‘ or qgpl.email IS NULL
THEN SET status = 0;
— end if;
else — nur dann sollen auch die folgenden Prüfungen durchgeführt werden
….
END IF;
1) Maximale Länge <= 318
Für das Ermitteln der Länge eines Feldes wird die skalare Funktion length verwendet:
— prüfen max. Länge <=318
select case
when length(qgpl.email) <= 318
Then 0
else 1
end
from sysibm.sysdummy1;
2) Ist das Zeichen ‘@‘ vorhanden?
Für diese Prüfung verwende ich die skalare Funktion position:
— prüfen, ob @-Zeichen vorhanden
select case
when position(‘@’, qgpl.email) > 0 — kein Fehler
Then 0
else 2
end
from sysibm.sysdummy1;
select case
when position(‘@’, qgpl.email2) > 0 — Fehler
Then 0
else 2
end
from sysibm.sysdummy1;
3) Prüfen der Länge des ersten Teils <=64
Der erste Teil der E-Mail wird mit den Funktionen position und substring ermittelt. Zur Ermittlung der Länge für diesen Teilstring wird die Funktion length verwendet:
— gültig
select case
when length(substring(qgpl.email, 1, position(‘@’, qgpl.email )-1)) <= 64
Then 0
else 3
end
from sysibm.sysdummy1;
— ungültig
select case
when length(substring(qgpl.email3, 1, position(‘@’, qgpl.email3 )-1)) <= 64
Then 0
else 3
end
from sysibm.sysdummy1;
4) Prüfen der Länge des zweiten Teils (der Domain) <=253
Hier werden die gleichen Funktionen wie zuvor verwendet:
— prüfen, Länge 2. Teil > 253
select case
when length(trim(substring(qgpl.email, position(‘@’, qgpl.email )+1))) <= 253
Then 0
else 4
end
from sysibm.sysdummy1;
5) Prüfen gültige Syntax
In einer E-Mail-Adresse sind folgende Zeichen erlaubt:
- Alphabetische [a-z] [A-Z]
- Ziffern [0-9]
- Unterstrich (_)
- Das Et-Zeichen (@)
- Der Punkt (.)
- Der Bindestrich (-)
Für diese Prüfung bietet sich die Verwendung von Funktionen für reguläre Ausdrücke an. Mit dem TR2 (Technology Refresh) wurden zahlreiche Funktionen für die Verarbeitung von regulären Ausdrücken der Db2 hinzugefügt. Hier verwende ich die Funktion Regexp_Like.
— valid
SELECT
CASE
WHEN REGEXP_like(‘info@edv-baer.com’, ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’)
THEN ‘0’
ELSE ‘5’
END AS email_status
FROM sysibm.sysdummy1;
— invalid
SELECT
CASE
WHEN REGEXP_like(‘info#@edv-baer.com’, ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’)
THEN ‘0’
ELSE ‘5’
END AS email_status
FROM sysibm.sysdummy1;
6) Prüfen, ob Domain vorhanden
Die Prüfung, ob eine in der E-Mail-Adresse angegebene Domain gültig und auch vorhanden ist, kann mit Hilfe einer DNS-Suche durchgeführt werden. Eine DNS-Suche ist der Prozess, bei dem Domain-Namen wie beispielsweise edv-baer.com in IP-Adressen umgesetzt wird. Für diesen Prozess gibt es eine SQL-Funktion. Mit dem PTF Level 28 für IBM i 7.3 und für die PTF-Gruppe SF99703 DB2 FOR IBM I wurde die SQL-Tabellenfunktion QSYS2.DNS_LOOKUP verfügbar gemacht.
Bei Aufruf der Funktion können zwei Parameter übergeben werden:
- SEARCH_NAME: Der Name der Domäne, die aufgelöst werden soll
- DOMAIN_SERVER: Der Name oder die IP-Adresse des Domain Name Servers. Dieser Parameter ist nicht erforderlich.
Die Tabellen-Funktion gibt insgesamt vier Spalten zurück. Für unseren Zweck sind die beiden ersten Spalten ausreichend:
- ADDRESS_SPACE_TYPE: Art der IP-Adresse IPV4 oder IPV6
- IP_ADDRESS: IPV4 oder IPV6 Adresse
Beachten Sie, dass die Funktion nur dann ausgeführt werden kann, wenn Ihre IBM i-Partition sich mit dem Internet verbinden kann.
Weitere Informationen zu QSYS2.DNS_LOOKUP finden Sie unter https://www.ibm.com/docs/en/i/7.3?topic=services-dns-lookup-table-function
Ein Beispiel für die Verwendung von QSYS2.DNS_LOOKUP
Quelle: Bär
Für meine Prüfung verwende ich folgende Syntax:
SELECT ‘1’
FROM TABLE(QSYS2.DNS_LOOKUP(RTRIM(‘edv-baer.com’))) — gültig
LIMIT 1 ;
SELECT ‘1’
FROM TABLE(QSYS2.DNS_LOOKUP(RTRIM(‘edv-baer.Xom’))) — ungültig
LIMIT 1 ;
SELECT ‘1’
FROM TABLE(QSYS2.DNS_LOOKUP(RTRIM(‘ids-edv.de’))) — gültig
LIMIT 1 ;
Dafür muss aus der E-Mail-Adresse die Domain extrahiert werden. Dies geschieht mit der folgenden Anweisung:
values rtrim(substr(‘info@edv-baer.com’, position(‘@’, ‘info@edv-baer.com’) + 1));
— Ergebnis: edv-baer.com
Beides zusammen führt zu folgender SQL-Anweisung:
— gültig
SELECT ‘1’
FROM TABLE(QSYS2.DNS_LOOKUP(
rtrim(substr(‘info@edv-baer.com’, position(‘@’, ‘info@edv-baer.com’) + 1))))
LIMIT 1 ;
Beispiel für ungültig:
— ungültig
SELECT ‘1’
FROM TABLE(QSYS2.DNS_LOOKUP(
rtrim(substr(‘info@edv-baer.Xom’, position(‘@’, ‘info@edv-baer.Xom’) + 1))))
LIMIT 1 ;
Für die Erarbeitung dieses Themas habe ich u.a. die folgenden Quellen verwendet:
- https://www.pcwelt.de/article/1110128/e-mail-e-mail-adresse-vorab-auf-gueltigkeit-und-erreichbarkeit-ueberpruefen.html
- https://centralops.net/co/
- https://www.experte.de/email-pruefen
- https://www.ibm.com/docs/en/host-on-demand/12.0?topic=SSS9FA_12.0.0/com.ibm.hod.doc/help/hotspots.htm
- https://www.rpgpgm.com/2022/09/using-sql-to-perform-domain-lookup.html
- SQL Referenzhandbuch 7-3.pdf
- https://www.rpgpgm.com/2017/08/regexp-like-added-to-sql.html
- https://www.complexsql.com/regexp_like-examples/
- https://www.rpgpgm.com/2023/02/finding-all-fields-that-contain-certain.html
- https://www.ibm.com/docs/en/i/7.3?topic=functions-regexp-count
Vorschau:
Im nächsten Artikel werden die hier behandelten einzelnen Prüfschritte erst zu einem Compound Statement und dann zu einer Prüffunktion (UDF) zusammengeführt.
Bis dahin wünsche ich Ihnen weiterhin viel Spaß beim Vermehren Ihrer Fertigkeiten.
Der Autor Theo Bär schreibt regelmäßig für den TechKnowLetter.
Sie erreichen ihn unter
EDV-Beratung Theo Bär
Ringmauerweg 1
69250 Schönau
Tel.: (+49) 6228 912 630
E-Mail: [info@edv-baer.com]
Sechs Ausgaben des TechKnowLetters erhalten Sie hier für 88 Euro.