Remote Datenbank Management

Provider, die dem Webmaster einen Datenbankzugriff (MySQL) erlauben, bieten dazu auch ein Administrationsfrontend an. An einem Solchen muss sich der Webmaster per Name und Passwort anmelden, bevor die meisten administrativen Operationen per Mausklick erledigt werden können. Der vorliegende Artikel beschreibt eine Eigenentwicklung, mit welcher die Administration auf eigene Bedürfnisse zugeschnitten werden kann womit der administrative Arbeitsaufwand auch weitgehend automatisiert werden kann.

Für eine Remote-Datenbank-Administration via HTTP (oder HTTPS) wird ein CGI-Script als Server benötigt, sowie ein oder mehrere für den Bedarf zugeschnittene Clients, UserAgent oder UA genannt. Der vorliegende Artikel beschreibt ein voll funktionsfähiges und universelles MasterScript (Server) sowie UA's in Perl. Beginnen wir mit der Sicherheit.

Zufriffskontrolle mit .htaccess

Es versteht sich von selbst, dass das Herzstück, ein CGI-Script auf dem Webserver, welches den uneingeschränkten Datenbankzugriff lesend und schreibend ermöglicht, nicht für jedermann zugänglich sein darf. Bei einem Apache-Webserver (wird von den meisten Providern eingesetzt) funktioniert das über eine Datei .htaccess, welche in die Serverkonfiguration eingreift:

AuthUserFile /home/user/html/.htpassfile
AuthName "Secret Realm"
AuthType Basic
order deny,allow
<Files ~ "master.cgi">
        require valid-user
</Files>

Diese .htaccess, im Verzeichnis der CGI-Scripts angelegt, wird bewirken, dass für unser MasterScript master.cgi ein Benutzername und ein Passwort verlangt wird. Dieses Passwort muss in der Datei AuthUserFile /home/user/html/.htpassfile hinterlegt sein. Die Passwortdatei für einen Benutzer 'name' mit dem Passwort 'password' kann mit Perl erzeugt werden, 'IN' ist hierbei das 'Salz in der Suppe' und das kann auch mit anderen (zwei) Zeichen notiert sein:

Kommandozeile
perl -e "print 'name:',crypt 'password', 'IN'" > .htpassfile

Dateiinhalt .htpassfile:
name:INVVE0vlCEq5I

Außer mit Perl, kann die Passwortdatei auch mit dem Apache-Hilfsprogramm htpasswd erzeugt werden, es sind auch mehrere Benutzer möglich, jeweils Einer je Zeile in der Passwortdatei.

Das Master-Script master.cgi

Es bekommt ein SQL-Statement per POST übergeben und gibt die Antwort des Datenbankservers (MySQL) als text/plain zurück in der Response an den UA:

#!/usr/bin/perl
###########################################################################
# Rolf Rost 08.01.2008
# mySQL-Master, 15.4.2009
###########################################################################
# VARs zu MySQL
my $base = 'datenbank_name';
my $host = 'datenbank_hostname'; # lt. DNS oder eine IP-Adresse
my $port = '3306';
my $user = 'users_name';
my $pass = 'users_password';
###########################################################################
use strict;
use CGI 'param';
use DBI;

print "Content-type: text/plain\n\n";

my $dbh = connMySQL();

if(param()){
	if(my $q = param('q')){
		# $q beinhaltet ein vollständiges SQL-Statement
		execute($q);
	}
	else{
		print "Unbekannter Parameter";
	}
}
else{
	browse();
}
exit;
###########################################################################
# Gibt die Create-Statements aller Tabellen aus
sub browse{
	my $aref  = $dbh->selectall_arrayref("show tables");
	my %tabs = map{$_->[0], 1}@$aref;
	
	print "Tabellen in DB myweb\n\n";
	foreach my $tab(keys %tabs){
		print "$tab\n";
		my $statement = qq(SHOW CREATE TABLE $tab);
		my @row  = $dbh->selectrow_array($statement);
		print "$row[1]\n\n";
	}
	
	return;
}
###########################################################################
# Zeilentrenner:  "\n" NewLine
# Spaltentrenner: "\t" Tabulator
sub execute{
	my $q = shift;
	my $ref  = $dbh->selectall_arrayref($q) or printf("%s\n", $dbh->errstr);
	if(not scalar @$ref){
		print "Empty Set/Anfrage fehlerfrei\n";
		return;
	}

	foreach my $row(@$ref){
		my $li = join("\t", @$row);
		print "$li\n";
	}
	return;
}
###########################################################################
# Gibt einen Database-Handle zurück
sub connMySQL{
	my $dsn = "DBI:mysql:database=$base;host=$host;port=$port";    
	my $dbh = DBI->connect($dsn, $user, $pass) or return;
	return $dbh;        	
}

Zum Testen laden wir das Script per FTP auf den Webserver, setzen es ausführbar (chmod 755) und rufen es im Browser auf ohne Parameter. Es sollte nun die Abfrage des Benutzername/Passwort erfolgen und bei einer erfolgreichen Autentifizierung die Auflistung der Create-Statements aller Tabellen im Browserfenster zu sehen sein.

Ein UserAgent zum Testen

Untenstehendes Script zeigt den grundsätzlichen Aufbau eines UA zum Managen der Remote-Datenbank wobei das SQL-Statement als CGI-Parameter mit der Method POST an das MasterScript gesendet wird:

#!/usr/bin/perl
###########################################################################
# VARs
my $proxy = 'http://217.237.149.142:80'; # Bei Bedarf
###########################################################################
use strict;
require LWP::UserAgent;

printf("%s\n", myWrapper(query => "SHOW TABLES"), proxy => $proxy);
 
exit;
###########################################################################
# Sendet das SQL-Statement per POST an das MasterScript auf dem Server
sub myWrapper{
	my %p = (
		url => 'http://example.com/cgi-bin/master.cgi',
		user => 'standardbenutzer',
		pass => 'standardpasswort',
	@_);

	my $ua = LWP::UserAgent->new;
	$ua->proxy('http', $p{proxy}) if exists $p{proxy};

	my $req = HTTP::Request->new(POST => $p{url});
	$req->content("q=$p{query}");
	$req->authorization_basic($p{user}, $p{pass});
	return $ua->request($req)->content;
}
# Rückgabe ist die Antwort vom MySQL-Server
# Benutzername und Passwort beziehen sich hier auf .htaccess
###########################################################################

Nach dem Absenden von SQL-Statements an das MasterScript mit obenstehenden Test-UserAgent erscheint die Antwort vom Server augenblicklich so, als würde der OP direkt vor dem MySQL-Server sitzen. Die Funktion myWrapper() wird in den nächsten Beispielen unverändert übernommen. Mögliche Parameter:

Es empfiehlt sich, die Funktion myWrapper() und weitere Funktionen, die öfter gebraucht werden, in einem lokalen Perl-Modul zusammenzufassen.

Datensicherung Gästebuch als praktisches Beispiel

Es folgt ein Beispielscript für den UA, der das Gästebuch auf die lokale Festplatte sichert. Zunächst das Script, Erläuterungen dann weiter unten im Script:

# Die Gästebuchtabelle hat zwei Felder:
# ts: Zeitstempel als UNIX timestamp (Primärschlüssel)
# msg: für die eingetragene Nachricht

# Es werden ersteinmal alle Primärschlüssel ermittelt
my $x = myWrapper(query => "SELECT ts FROM book");

if($x =~ /Empty/){
	print "Keine Nachrichten im Gaestebuch\n";
	sleep 3;
	exit;
} # in diesem Fall ist das Gästebuch leer und es gibt da nichts zu sichern

# falls Einträge vorhanden sind, erzeugt split() ein Array mit den Schlüsseln
my @nrs = split "\n", $x;

# Die Einträge downloaden und im lokalen Verzeichnis als Textdatei schreiben
foreach my $ts(@nrs){
	my $msg = myWrapper(query => "SELECT msg FROM book WHERE ts=$ts");
	$msg =~ s/\r//g;   # siehe Anmerkungen
	print "Schreibe $ts\n";
	open OUT, ">$dir/$ts" || die $!;
	print OUT $msg;
	close OUT;
}

print "Taste....\n";
my $c = <STDIN>;

Anmerkungen: Das Webfrontend für das Gästebuch hat eine textarea für den Eintrag. Jeder darin eingefügte Zeilenumbruch erzeugt nicht nur ein NewLine "\n" sondern auch das Zeichen Carriage Return "\r" (Wagenrücklauf, bekannt aus der Zeit der Schreibmaschine). Dieses Zeichen ist kodiert mit der Bitfolge 1101, dezimal 13, hexadezimal 0xD. Je nach Texteditor könnte dieses Zeichen beim Lesen stören, in obenstehenden Script werden daher alle diese Zeichen entfernt.

Eine Tabelle anlegen und Datensätze einfügen

Legen wir nun eine Tabelle an auf dem Server, hier das CREATE-Statement und die Anwendung der Funktion myWrapper():

my $query = qq(
	CREATE TABLE `mbox` (
	  `lfdnr` int(1) NOT NULL auto_increment,
	  `msg` varchar(255) NOT NULL,
	  PRIMARY KEY  (`lfdnr`)
	)
);

printf("%s\n", myWrapper(query => $query));
# Wenn alles geklappt hat, erhalten wir: Empty Set/Anfrage fehlerfrei

# Ein Weiteres
printf("%s\n", myWrapper(query => "SHOW CREATE TABLE mbox"));
# zeigt:
mbox	CREATE TABLE `mbox` (
  `lfdnr` int(1) NOT NULL auto_increment,
  `msg` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`lfdnr`)
) TYPE=MyISAM

Sofern sichergestellt ist, dass es mit den Feldnamen keine Konflikte mit MySQL-spezifischen/reservierten Namen gibt, können die Backticks (`mbox`) auch weggelassen werden. Testen wir nun, wie das Einfügen von Datensätzen funktioniert:

printf("%s\n", myWrapper(query => "INSERT INTO mbox VALUES('', 'Das ist ein Text')"));
printf("%s\n", myWrapper(query => "INSERT INTO mbox VALUES('', 'Das ist noch ein Text')"));

# Empty Set/Anfrage fehlerfrei

Eine Tabelle ausgeben

printf("%s\n", myWrapper(query => "SELECT lfdnr, msg FROM mbox"));

1	Das ist ein Text
2	Das ist noch ein Text

Inhalte aller HTML-Dokumente in eine Tabelle auf dem Server einfügen

In meinem Artikel Die lokale Suchmaschine für die WebSite habe ich beschrieben, wie ein Abbild der HTML-Inhalte aller Seiten für die lokale Suchmaschine erzeugt wird. Hier nun die Besonderheiten beim Upload des Index über HTTP an das Masterscript master.cgi. Ausgangsbasis ist ein hash aller Dokumente und auch CGI-Scripts in der Form:

my %url = (
	'/document_url' => 'Document Title',
);

Damit auch die HTML-Ausgabe von CGI-Scripts für den Suchindex erfasst werden, wird der Inhalt per HTTP auf dem lokalen Webserver ermittelt mit untenstehende Funktion:

###############################################################################
# den Content der Dokumente vom Webserver ermitteln
sub getContent{
	my $fqdoc = shift;  # http://example.com/document.html
	my $ua = LWP::UserAgent->new;
	my $req = HTTP::Request->new(GET => $fqdoc);
	return $ua->request($req)->content;
}
###############################################################################

Nachdem mit dem Modul HTML::TagParser der Inhalt des Body ermittelt und die Tags entfernt wurden, liegt der nackte Content einer jeden Webseite vor, wobei dieser immer noch Sonderzeichen enthalten kann, die für die Übertragung mit HTTP entsprechend maskiert werden müssen. Das kann mit dem Modul URI::Escape erfolgen:

$bodyText = uri_escape($bodyText);

Schließlich ist der Inhalt noch zu quoten:

use DBI;
$bodyText = DBD::_::db::quote(undef, $bodyText);
# quoten ohne dass ein DatabaseHandle erstellt werden musste

Nun kann der Request an der MasterScript gemacht werden:

my $query = qq(INSERT INTO suchindex VALUES('$url','$url{$url}', $bodyText));
print myWrapper(query => $query, url => $master);
# Empty Set/Anfrage fehlerfrei

Zusammenfassung

Die hier vorgeschlagenen Perl-Scripts erlauben ein automatisiertes Management der MySQL-Datenbank auf dem Webserver remote per HTTP und haben sich bereits in meiner Praxis bewährt. Die Scripts für die UserAgents sind recht einfach und für spezielle Anwendungsfälle zugeschnitten. Damit können alle anfallenden administrativen Aufgaben gelöst werden, wie das Anlegen/Löschen von Tabellen sowie eine allgemeine Datenpflege.

Last-Modified: Tue, 22 Jun 2010 19:50:31 GMT