U8: SQL

Anleitung für das Wiki

In diesem Wiki könnt ihr gemeinsam Lerninhalte erstellen. Jedes Wiki ist direkt und für jeden angemeldeten User editierbar. Wenn bereits bei vorherigen Prüfungs-Wikis zum Thema (manchmal auch nur Teilgebiete streifend) erstellt wurden, so werden sie unten verlinkt. Ansonsten einfach hier diesen Eintrag Überschreiben und mit entsprechenden Inhalten füllen.

Um Beispielaufgaben etc. zu bearbeiten haben wir extra eine Lerngruppe (https://mediencommunity.de/lerngruppe-mediengestalter-ap-sommer-2021) eingerichtet, bitte diese für das gemeinsame Lösen von Aufgaben nutzen.

 

Viel Erfolg beim Lernen.

Das Team der mediencommunity

Bewertung: 
0
Bisher keine Bewertung

SQL

SQL

SQL (Structured Query Language) hat sich als Abfragesprache für relationale Datenbanken durchgesetzt.
Zu den bekanntesten freien SQL-Datenbanken zählen MySQL, PostgreSQL und MaxDB.
Zu den größten kommerziellen Datenbankherstellern zählen Oracle und IBM.

Statisch/dynamisches SQL

  1. Bei statischem SQL ist die SQL-Anweisung dem Datenbanksystem zum Zeitpunkt der Programmübersetzung bekannt und festgelegt (z. B. wenn die Abfrage eines Kontos vorformuliert ist und zur Laufzeit nur die Kontonummer eingesetzt wird).

  2. Bei dynamischem SQL ist die SQL-Anweisung dem Datenbanksystem erst zum Zeitpunkt der Programmausführung bekannt (z. B. weil der Benutzer die komplette Abfrage eingibt). So sind z. B. alle SQL-Anweisungen, die mittels SQL/CLI oder JDBC ausgeführt werden grundsätzlich dynamisch. Ausgeführt werden dynamische SQL-Anweisungen im Allgemeinen mit execute immediate (SQL-String).

 

MySQL-Abfragen

CRUD-Prinzip: Das Akronym "CRUD" beschreibt die Datenbankoperationen, auf denen jegliche Kommunikation mit der Datenbank beruht.

Alle Datensätze anzeigen

MySQL-Code

  SELECT * FROM tabellenname  

zeigt alle Datensätze der Tabelle tabellennamean.

Einzelne Felder aller Datensätze anzeigen

MySQL-Code

  SELECT kunde_name FROM tabellenname  

zeigt alle Felder kunde_namealler Datensätze der Tabelle tabellennamean.

MySQL-Code

  SELECT kunde_name, kunde_vorname FROM tabellenname  

zeigt alle Felder kunde_nameund kunde_vornamealler Datensätze der Tabelle tabellennamean.

Einschränkungen mit WHERE

MySQL-Code

  SELECT kunde_name, kunde_vorname FROM tabellenname WHERE kunde_ort='Hamburg'

Schränkt die Ausgabe auf alle Kunden aus Hamburg ein. Groß- und Kleinschreibung ist hier nicht relevant (Hamburg, hamburg).

Platzhalter mit LIKE %

MySQL-Code

  SELECT * FROM tabellenname WHERE kundeOrt LIKE '%freiburg%'

findet die Orte “Ostfreiburg”, “Freiburg West” und “Freiburg-Süd”. Die Prozentzeichen sind Platzhalter für eine beliebige (oder keine) Zeichenfolge.

Verknüpfung von Abfragen mit OR und AND

MySQL-Code

  SELECT * FROM tabellenname WHERE kunde_ort=freiburg' AND kunde_name='müller'   SELECT * FROM tabellenname WHERE kunde_name='mayer' OR kunde_name='maier' OR kunde_name='meyer' OR kunde_name='meier'

Ausgabe sortieren mit ORDER BY

MySQL-Code

  SELECT * FROM tabellenname ORDER BY kunde_id  

sortiert nach Kunde-ID aufsteigend (bei Text: alphabetisch aufsteigend).

MySQL-Code

  SELECT * FROM tabellenname ORDER BY kunde_ort, kunde_name  

sortiert nach Ort und dann nach Name (d.h. innerhalb eines Ortes nach Name, alphabetisch aufsteigend).

Um nicht aufsteigend, sondern absteigend zu sortieren, hängt man ein DESC an:

MySQL-Code

  SELECT * FROM tabellenname ORDER BY kunde_name DESC

Aktualisierung eines Datensatzes durch UPDATE:

MySQL-Code

  UPDATE tabellenname SET variablenname='neuer Inhalt' WHERE id=3 UPDATE tabellenname SET telefonnummer='neuer Inhalt', postleitzahl='neuer Inhalt' WHERE kunde_id=7

Statt id=3 kann auch ein anderes eindeutig identifizierendes Feld benutzt werden.

Ändern der Tabellenstruktur mit ALTER TABLE:

Mit ALTER TABLE tabellenname [Spezifikation]wird eine Tabelle verändert. Mögliche Spezifikationen:

RENAME– benennt die Tabelle um, z.B.

MySQL-Code

  ALTER TABLE kunden RENAME bestandskunden  

ADD COLUMN– fügt ein Feld hinzu, z.B.

MySQL-Code

  ALTER TABLE kunden ADD COLUMN registrierungsdatum TIMESTAMP

Die Position kann mit FIRST und AFTER angegeben werden (FIRST = neues Feld wird an den Anfang gesetzt, AFTER feldname= neues Feld wird nach feldnameeingefügt).

DROP COLUMN– löscht ein Feld, z.B.

MySQL-Code

  ALTER TABLE kunden DROP COLUMN registrierungsdatum

CHANGE– Änderung eines Feldes, wobei die Attribute des Feldes neu angegeben werden müssen, z.B.

MySQL-Code

  ALTER TABLE kunden CHANGE altername neuername VARCHAR(255) NOT NULL

oder um nur die Attribute zu ändern

MySQL-Code

  ALTER TABLE search CHANGE altername altername VARCHAR(200) NOT NULL

Löschen von Daten mit DELETE

DELETElöscht einen/mehrere Datensätze aus einer Tabelle. Mit

MySQL-Code

  DELETE FROM tabellenname  

würden sämtliche Datensätze einer Tabelle gelöscht werden. Durch die Einschränkung WHERE

MySQL-Code

  DELETE FROM tabellenname WHERE id=8

… würden im Beispiel alle Datensätze gelöscht werden, in denen das Feld idden Wert 8hat.

Löschen einer ganzen Tabelle mit DROP TABLE

MySQL-Code

  DROP TABLE tabellenname 

 

Quelle: http://www.informatikzentrale.de/mysql-abfragen.html

 

Nützliche Videos zu SQL:
• https://www.youtube.com/watch?v=E4uDCxUF45E&index=4&list=PLPjKx80JC4CCT8Mbazn4uKaplp-v3Jwz_
• https://www.youtube.com/watch?v=hAxnxdCj4KQ&list=PLPjKx80JC4CCT8Mbazn4uKaplp-v3Jwz_&index=2
• https://www.youtube.com/watch?v=c0RGHsrb-5A&list=PLPjKx80JC4CCT8Mbazn4uKaplp-v3Jwz_&index=3
• https://www.youtube.com/watch?v=r0Eyhl7AQrM&list=PLPjKx80JC4CCT8Mbazn4uKaplp-v3Jwz_&index=1

 
Bewertung: 
5
Durchschnitt: 5 (4 Stimmen)

SQL-Injections

a. Allgemeines

• SQL-Injection (dt. SQL-Einschleusung) bezeichnet das Ausnutzen einer Sicherheitslücke in Zusammenhang mit SQL-Datenbanken, die durch mangelnde Maskierung oder Überprüfung von Metazeichen in Benutzereingaben entsteht.

• Der Angreifer versucht dabei, über die Anwendung, die den Zugriff auf die Datenbank bereitstellt, eigene Datenbankbefehle einzuschleusen. Sein Ziel ist es, Daten auszuspähen, in seinem Sinne zu verändern, die Kontrolle über den Server zu erhalten oder einfach größtmöglichen Schaden anzurichten.

• Das heißt, dass SQL Abfragen Zugriffskontrollen hinters Licht führen, und dadurch Standard Authentifizierungs- und Authorisationschecks umgehen können, und manchmal können SQL Abfragen sogar Zugriff zu Kommandos auf Betriebssystemebene erlauben.

• Sie sind möglich, wenn Daten wie Benutzereingaben in Web-Formularen in den SQL-Interpreter gelangen und Zeichen enthalten, die für den Interpreter Sonderfunktionen besitzen:

  • ==> Backslash \
  • ==> Anführungszeichen „“
  • ==> Apostroph ’
  • ==> Semikolon ; etc.

b. Vorgang der SQL-Injection

• Der „Hacker“ benutzt eine Standard-Datenbankanfrage oder ein Formular und merkt sich dabei den übergebenen Syntax

==> http://webserver/cgi-bin/find.cgi?ID=42

• Dieser wird im SQL-Interpreter als entsprechende Befehlskette umgewandelt

==> SELECT author, subject, text FROM artikel WHERE ID=42;

• Der „Hacker“ muss sich in der SQL-Syntax Schreibweise auskennen um dem Befehl eigene Syntax-Befehle anhängen zu können.

• Um bei der oben angesprochenen Datenbank Daten ausspähen zu können, könnte der „Hacker“ folgendes schreiben:

==> http://webserver/cgi-bin/find.cgi?ID=42+UNION+SELECT+'Datenbank',+'Tabelle',+'Spalte'+ UNION+SELECT+table_schema,+table_name,+column_name+FROM+information_schema.columns+ WHERE+NOT+table_schema='information_schema';#%20

• Das Beispiel zeigt, wie bei einer Abfrage mit 3 Ergebnisspalten die Struktur sämtlicher zugreifbarer Datenbanken in Erfahrung gebracht werden kann.

• Mit zeitbasierten Angriffen (also jene, die im richtigen Moment passieren), ist es dem Angreifer auch möglich sich für die Datenbank einen Administratorenstatus anzulegen. Hierzu muss er während der eigentliche SuperUser eingelogged ist eine SQL-Injection mit einem „root“ Befehl einschleusen. Da der SuperUser vom System immer mit der Hierarchie → „Root“ versehen wird.

==> Wurde ein System infiltriert, kann man sich über den Server-Traffic die Seitenzugriffe ansehen. Ist ein Traffic sehr auffällig (Normale Monatszugriffe im Schnitt 10.000 und plötzlich sieht man in einem Monat 100.000 oder 1.000.000 Zugriffe) ist es anzunehmen, das ein Zugriffs-Algorithmus in Verbindung mit einer Root-SQL-Injection genutzt wurde um im richtigen Moment (wenn der SuperUser eingelogged ist) das System mit Administratorenrechten zu infiltrieren.

c. Gegenmaßnahmen

• Bei der Programmierung von HTML/PHP Formularen sollten zwingend Inhaltsabfragen eingebaut werden, bei denen aus der Benutzereingabe die SQL-Syntax-Befehlszeichen ( \ “ ’ + ; etc.) entfernt bzw. maskiert werden.

• Bei den Inhaltsübergaben sollten Datentypabfragen eingebaut werden, damit wenn möglich der Input auch immer dem erwarteten Datentyp entspricht.

• Bauen Sie Codefragmente in den PHP Code ein, der verhindert, dass Fehlermeldungen beim Client angezeigt werden, diese geben dem Hacker Informationen über das Datenbankschema.

• Verwendung von verschlüsselten Passwortfeldern für alle Nutzer der Datenbank und akribisches Achten auf die Nutzerverwaltung

• Legen sie einen individualisierten „SuperUser“ an und loggen sie sich ausschließlich mit diesem „Account“ ein, damit der „Root“-SU nie genutzt wird.

• Verwendung von WAF (Web-Application-Firewall)

==> Eine Web Application Firewall (WAF) oder Web Shield ist ein Verfahren, das Webanwendungen vor Angriffen über das Hypertext Transfer Protocol (HTTP) schützen soll. Es stellt damit einen Spezialfall einer Application-Level-Firewall (ALF) oder eines Application-Level-Gateways (ALG) dar. • Verwendung von „stored procedures“

==> Es ist eine Funktion in Datenbankmanagementsystemen, mit der ganze Abläufe von Anweisungen vom Datenbank-Client aufgerufen werden können. ==> Sie ist somit ein eigenständiger Befehl, der eine Abfolge gespeicherter Befehle ausführt.

==> Gespeicherte Prozeduren tragen dazu bei, die Sicherheit einer Anwendung stark zu erhöhen. Da der Client in der Regel keine DELETE-, UPDATE- oder INSERT-Zugriffsrechte mehr benötigt, ist es Angreifern nicht möglich, selbst Datenbanken zu manipulieren (z. B. durch SQL-Injection).

• Verwendung von „Prepared Statements“ mit gebundenen Variabeln

==> Dabei werden die Daten als Parameter an einen bereits kompilierten Befehl übergeben. Die Daten werden somit nicht interpretiert und eine SQL-Injection verhindert.

Bewertung: 
5
Durchschnitt: 5 (1 Stimme)

SQL

SQL

Scriptsprache

Structured Query Language (Strukturierte Abfrage-Sprache)

Datenbanksprache zur Definition von Datenstrukturen in relationalen Datenbanken

zum Bearbeiten (Einfügen, Verändern, Löschen) und Abfragen von darauf basierenden Datenbeständen

SQL dient quasi als Mittel zum Zweck, um sämtliche Datenbankoptionen durchzuführen

 

3 DATENBANKSYSTEME DIE MIT SQL ARBEITEN

MySQL 

PostgreSQL

MaxDB

 

SQL-Anweisungen

DDL(DATA DESCRIPTION LANGUAGE)

Datendefinition

—> Definition des Aufbaus und der Struktur der Datenbank

 

DML(DATA MANIPULATION LANGUAGE)

Datenmanipulation 

—> Abfrage und Veränderung von Daten

 

DCL(DATA CONTROL LANGUAGE)

Datenkontrolle

—> Berechtigung erteilen und verwalten

 

Datentypen

INT(Integer)
—> Ganze Zahlen (positiv oder negativ)

Beispiel: 10 —> INT (2)

DEC(Decimal)
—> Festkommazahl(positiv oder negativ) mit insgesamt n Ziffern, davon m Nachkomma stellen

Beispiel: 4859,27 —> DEC (6,2)

CHAR(Character)
—> Zeichenkette mit fester Länge (max.255)

Beispiel: Vierbuchstaben-Abkürzung z.B. MiMa —> CHAR (4)

VARCHAR(n)
—> Zeichenkette mit variabler Länge

Beispiel: Vornamen —> VARCHAR(30) (maximal benötigte Länge an Buchstaben)

DATE
—> Datum

Beispiel: Geburtsdatum 24.06.1992

YEAR
—> Jahr

Beispiel: 1992

TIME
—> Uhrzeit

Beispiel: 10:53

DATETIME
—> Datum und Uhrzeit zusammengefasst

Beispiel: 24.06.1992, 10:53

TIMESTAMP

—> Zeitstempel mit Datum und Uhrzeit; wird für die aktuelle Zeit  verwendet

Beispiel: 24.06.2018, 10:54

 

DDL-Anweisung

Weitere SQL Anweisungen

UNSIGNED —> Nur positive Zahlen

NOT NULL —>  Dieses Feld darf nicht leer bleiben

PRIMARY KEY—> Angabe welches Element der Primärschlüssel sein soll

AUTO_INCREMENT—> Automatisch ansteigend

 

Quelltext einer erstellten MySQL/SQL

Datenbank mit dem Namen „literatursammlung“

CREATE DATABASE literatursammlung;
USE literatursammlung;

CREATE TABLE buecher(
Titel_Nr INT(3) UNSIGNED NOT NULL;
Titel VARCHAR(30) UNSIGNED NOT NULL;
Verlag_Nr INT(3) UNSIGNED NOT NULL;
Abt_Nr INT(3) UNSIGNED NOT NULL;
PRIMARY KEY(Titel_Nr)
);

CREATE TABLE buecher_autoren(
Titel_Nr INT(3) UNSIGNED NOT NULL;
Autor_Nr INT(4) UNSIGNED NOT NULL;
PRIMARY KEY (Titel_Nr, Autor_Nr)
);

CREATE TABLE autoren(
Autor_Nr INT(4) UNSIGNED NOT NULL;
A_Name VARCHAR (30) UNSIGNED NOT NULL;
A_Vorname VARCHAR(30) UNSIGNED NOT NULL;
PRIMARY KEY (Autor_Nr)
);

CREATE TABLE verlage(
Verlag_Nr INT(3) UNSIGNED NOT NULL;
Verlag VARCHAR(30) UNSIGNED NOT NULL;
PRIMARY KEY (Verlag_Nr)
);

SQL-Befehle zur

Abfrage und Veränderung der Daten

 

SELECT

—> Datensätze aus Datenbank abfragen

Beispiel:

SELECT * FROM buecher;

—> vollständige Tabelle „buecher“ wird aufgelistet, das * steht für alle

 

SELECT Titel FROM „buecher“;

—> alle Buechertitel aus der Tabelle werden aufgelistet

 

SELECT * FROM buecher WHERE Titel=“Dateiformate“;

—> liefert alle Datensätze der Tabelle „buecher“ mit dem Titel Dateiformate

 

SELECT Titel FROM buecher WHERE Verlag_Nr = „103“ AND Abt_Nr= „3“;

—> liefert alle Titel, die im Verlag Springer erschienen sind und in der Abteilung Print stehen

 

ORDER BY

—>Datensätze sortiert ausgeben;

ASC= aufsteigende Sortierung

DESC= absteigende Sortierung

Beispiel:

SELECT * FROM autoren

ORDER BY A_Name ASC;

—>wählt alle Inhalte aus der Tabelle „autoren“ aus

und sortiert sie alphabetisch aufsteigend nach dem Nachnamen

 

DELETE

—>Datensätze löschen

Beispiel:

DELETE FROM buecher

WHERE Titel=“HTML Handbuch“

—> löscht alle Datensätze mit dem Titel HTML Handbuch

aus der Tabelle „buecher“

 

UPDATE

—>Einträge verändern

Beispiel:

UPDATE buecher SET Abt_Nr=“3″

WHERE Titel_Nr=“203″

—>ändert die Abteilungsnummer des Buches

mit der Titel_Nr 203 in der Tabelle „buecher“

 

 

Bewertung: 
4
Durchschnitt: 4 (1 Stimme)