DBI und Platzhalter

[hotti]

hi,

mein schönes Statement

  
	my $st = qq(  
		INSERT INTO bucket VALUES(?,?,?, NOW())  
		ON DUPLICATE KEY UPDATE anz = $anz, datime = NOW()  
	);  

gefällt mir noch nicht so richtig, weil im Fall ON DUPLICATE kein Platzhalter sondern direkt der Wert $anz verwendet wird. Zum Testen, ob eine Exception auftritt, benutze ich eval like this:

eval { $sth->execute($self->{SID}, $self->{URL}, $anz) };

Wobei execute() mit 3 Params aufgerufen wird. Mich beunruhigt die Frage, was im Fall ON DUPLICATE passiert, wenn eine Exception auftritt: Wirft die dann auch einen $EVAL_ERROR? Oder nibbelt das Script einfach nur ab?

Hotti

[hotti]

hi,

die Exception tritt auf und $@ ist befüllt, hier mal die komplette Method

  
# Shop::Warenkorb methods  
# Artikel in den Warenkorb  
sub insert{  
	my $self = shift;  
	my $anz = shift or return;  
  
	$anz = 'otto'; # provoziere Fehler, $anz muss integer sein  
  
	my $st = qq(  
		INSERT INTO warenkorb VALUES(?,?,?, NOW())  
		ON DUPLICATE KEY UPDATE anz = $anz, datime = NOW()  
	);  
	  
	my $sth = $self->{DBH}->prepare($st);  
	eval { $sth->execute($self->{SID}, $self->{URL}, $anz) };  
	  
	if($self->{EVE} = $@){  
		$self->{ERR} = $DBI::errstr;  
		return;  
	}  
	else { return 1 }  
}  

Allerdings ist die Fehlermeldung unverständlich:

DBD::mysql::st execute failed: Unknown column 'otto' in 'field list'

Was zum Teufel passiert hier?

Setze ich direkt
eval { $sth->execute($self->{SID}, $self->{URL}, 'otto') };

erhalte ich wie erwartet ein
DBD::mysql::st execute failed: Incorrect integer value: 'otto' for column 'anz'

???

[Alexander (HH)]

Moin Moin!

$anz = 'otto'; # provoziere Fehler, $anz muss integer sein

my $st = qq(
INSERT INTO warenkorb VALUES(?,?,?, NOW())
ON DUPLICATE KEY UPDATE anz = $anz, datime = NOW()
);

füg hier mal print $st; ein.

my $sth = $self->{DBH}->prepare($st);
eval { $sth->execute($self->{SID}, $self->{URL}, $anz) };

if($self->{EVE} = $@){
$self->{ERR} = $DBI::errstr;
return;
}
else { return 1 }
}
[/code]

Allerdings ist die Fehlermeldung unverständlich:

DBD::mysql::st execute failed: Unknown column 'otto' in 'field list'

Nein, sie ist komplett richtig.

Was zum Teufel passiert hier?

Genau das, was Du der Datenbank-Karrikatur befiehlst: Sollte das Insert scheitern, weise der Spalte anz den Wert der Spalte otto zu.

Setze ich direkt
eval { $sth->execute($self->{SID}, $self->{URL}, 'otto') };

erhalte ich wie erwartet ein
DBD::mysql::st execute failed: Incorrect integer value: 'otto' for column 'anz'

Was hast Du zwischen den tests noch am Code geändert? Laß mich raten: Du hast $anz='otto'; wieder rausgeschmissen, wodurch $anz wieder numerisch wurde.

Es gibt gute Gründe, Werte nicht direkt in SQL-Statements zu frickeln. Das Hauptargument heißt SQL Injection, und genau daran leidest Du gerade. Ein anderer Grund ist, dass Du damit das Caching von prepared Statements massiv sabotierst.

Schreib SQL in Single Quotes bzw. q() bzw. <<'__marker__', damit kommst Du gar nicht erst in Versuchung, Variablen in den String zu interpolieren.

Oh, und noch was: In welche Spalten schreibt Dein INSERT-Statement? Du hoffst auf die Default-Reihenfolge der Tabelle, und genau darauf würde ich mich niemals verlassen. Schon allein, weil diese Information an einer völlig anderen Stelle im Programm steht, wenn überhaupt. So ist das nicht wartbar. Benutze die Langform des INSERT-Statements, mit den Spaltennamen in Klammern und mit Kommas getrennt zwischen dem Tabellennamen und dem VALUES-Keyword. So funktioniert Dein Code auch noch, wenn irgendwann (z.B. Dein zukünftiges Ich) mal jemand die Tabellenstruktur ändert. Ich finde es ziemlich übel bis kaputt, dass SQL die Angabe der Spalten als optional definiert.

Alexander

[hotti]

Moin Moin!

Genau das, was Du der Datenbank-Karrikatur befiehlst: Sollte das Insert scheitern, weise der Spalte anz den Wert der Spalte otto zu.

Achso, ja, klar.

Schreib SQL in Single Quotes bzw. q() bzw. <<'__marker__', damit kommst Du gar nicht erst in Versuchung, Variablen in den String zu interpolieren.

Ok, Single Quotes. Das Caching prepared St. lese ich nach.

Oh, und noch was: In welche Spalten schreibt Dein INSERT-Statement? Du hoffst auf die Default-Reihenfolge der Tabelle, und genau darauf würde ich mich niemals verlassen. Schon allein, weil diese Information an einer völlig anderen Stelle im Programm steht, wenn überhaupt. So ist das nicht wartbar. Benutze die Langform des INSERT-Statements, mit den Spaltennamen in Klammern und mit Kommas getrennt zwischen dem Tabellennamen und dem VALUES-Keyword.

Absolut empfehlenswert, hab schon damit angefangen, das so zu machen.

Danke Dir!!!
Hotti

[Alexander (HH)]

Moin Moin!

Genau das, was Du der Datenbank-Karrikatur befiehlst: Sollte das Insert scheitern, weise der Spalte anz den Wert der Spalte otto zu.

Achso, ja, klar.

Jaja, Wald und Bäume ...

Genau mit so einer Aktion baut man sich scheunentorgroße Einfallstore. Deswegen immer mit Platzhaltern, selbst für Konstanten. Dazu Taint Mode in Perl (#!perl -T) und im DBI (Taint oder wenigstens TaintIn), und die gröbsten Dämlichkeiten sind abgewürgt. Dann noch vergessen, dass es *zu* leichte Wege gibt, um das Taint-Flag loszuwerden, und stattdessen immer schön alles, was reinkommt, mit einer möglichst restriktiven RegExp testen. Diverse Module auf CPAN (mit Validate bzw. Validator im Namen) helfen dabei.

Oh, und NIE versuchen, Falscheingaben zu reparieren. Damit öffnet man neue Tore.

Schreib SQL in Single Quotes bzw. q() bzw. <<'__marker__', damit kommst Du gar nicht erst in Versuchung, Variablen in den String zu interpolieren.

Ok, Single Quotes. Das Caching prepared St. lese ich nach.

prepare_cached

Alexander

[hotti]

Moin Moin!

Genau das, was Du der Datenbank-Karrikatur befiehlst: Sollte das Insert scheitern, weise der Spalte anz den Wert der Spalte otto zu.

Achso, ja, klar.

Jaja, Wald und Bäume ...

Is manchmal so ;)

[..]

Deinen Input werde ich verarbeiten, vielen Dank!

Wo ich gerade dranbin, ist ein modular und objektorientiert aufgebautes CMS mit integrertem Warenkorb, der so integriert ist, dass Verkaufsangebote an beliebiger Stelle eingebaut werden können (u.a. wird es auch eine Rest-Schnittstelle geben).

Viele Grüße!

[hotti]

hi again,

Ok, Single Quotes. Das Caching prepared St. lese ich nach.

prepare_cached

Wow, und noch ein Cache: connect_cached

mit merklichen Effekt auf meiner alten Kiste ;)

(ungefähr so wie eine Kettensäge im Vergleich mit Fuchsschwanz, aber die Kettensäge hat keinen Schwanz sondern ein Schwert)

Schönes Wochenende,
Hotti

[Matti Mäkitalo]

Hi,

Benutze die Langform des INSERT-Statements, mit den Spaltennamen in Klammern und mit Kommas getrennt zwischen dem Tabellennamen und dem VALUES-Keyword. So funktioniert Dein Code auch noch, wenn irgendwann (z.B. Dein zukünftiges Ich) mal jemand die Tabellenstruktur ändert. Ich finde es ziemlich übel bis kaputt, dass SQL die Angabe der Spalten als optional definiert.

Ich würde weitergehen und die "UPDATE"-ähnliche Syntax verwenden (wobei ich mir nicht sicher bin, ob sie Standard-SQL ist oder rein MySQL-spezifisch.

  
INSERT INTO `bla`  
 SET column1 = 'blub',  
     column2 = 'bli',  
     column3 = 'bla';  

Das kann man dann nämlich ganz schnell in ein UPDATE verwandeln (da spar ich mir beim C&P bissl Arbeit :)) und Werte und Spaltennamen sind noch näher beisammen.
Die Methode mit Angabe der Spaltennamen nutze ich nur noch, wenn ich gleichzeitig mehrere Werte einfüge (was aber nicht häufig passiert) oder in Verbindung als "INSERT INTO bla (...) SELECT ...".

Bis die Tage,
Matti

[Alexander (HH)]

Moin Moin!

Ich würde weitergehen und die "UPDATE"-ähnliche Syntax verwenden (wobei ich mir nicht sicher bin, ob sie Standard-SQL ist oder rein MySQL-spezifisch.

Nö, MySQL-Spezifisch und damit raus.

INSERT INTO bla
SET column1 = 'blub',
     column2 = 'bli',
     column3 = 'bla';

>   
> Das kann man dann nämlich ganz schnell in ein UPDATE verwandeln (da spar ich mir beim C&P bissl Arbeit :)) und Werte und Spaltennamen sind noch näher beisammen.  
  
Sicher, das dürfte auch die Intention hinter dieser Insert-Variante sein, vollkommen nachvollziehbar, aber eben nicht Standard und damit vorprogrammierter Ärger, wenn man MySQL endlich hinter sich lassen will.  
  
Alexander

-- 
Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so".

[hotti]

hi,

Ich würde weitergehen und die "UPDATE"-ähnliche Syntax verwenden (wobei ich mir nicht sicher bin, ob sie Standard-SQL ist oder rein MySQL-spezifisch.

MySQL-spezifisch gibt es da noch 'replace'.

qq(
REPLACE funktioniert auf exakt gleiche Weise wie INSERT. Der Unterschied besteht darin, dass, wenn ein alter Datensatz denselben Wert wie ein neuer Datensatz für einen Primärschlüssel oder einen eindeutigen Index hat, der alte Datensatz gelöscht wird, bevor der neue eingefügt wird);

Hotti

[Alexander (HH)]

Moin Moin!

MySQL-spezifisch gibt es da noch 'replace'.

qq(
REPLACE funktioniert auf exakt gleiche Weise wie INSERT. Der Unterschied besteht darin, dass, wenn ein alter Datensatz denselben Wert wie ein neuer Datensatz für einen Primärschlüssel oder einen eindeutigen Index hat, der alte Datensatz gelöscht wird, bevor der neue eingefügt wird);

Auch sehr schön, aber Du solltest Dir bei der Verwendung solcher Konstrukte bewußt sein, dass Du dich damit in eine Ecke einmauerst.

In https://forum.selfhtml.org/?t=204225&m=1382771 schriebst Du:

Wo ich gerade dranbin, ist ein modular und objektorientiert aufgebautes CMS mit integrertem Warenkorb, der so integriert ist, dass Verkaufsangebote an beliebiger Stelle eingebaut werden können ...

Ich sehe keinen Grund, das Projekt auf MySQL zu beschränken. Sicher, PHP und MySQL ist die übliche Kombination bei Massenhostern, und deswegen wäre es sicherlich sinnvoll, *auch* mit MySQL zurecht zu kommen. Aber gerade mit DBI bist Du sehr unabhängig von der DB, und warum sollte als DB nicht auch etwas vernünftiges wie PostgreSQL zum Einsatz kommen? Oder SQLite (netterweise gleich in DBD::SQLite verpackt) für kleinere Installationen ohne DB-Admin? Oder Oracle für Leute, die schon reichlich Kohle in Oracle inverstiert haben? Oder MS SQL Server für Microsoft-Buden? DB2 für IBM-Buden?

Klar, die Systeme haben alle sehr unterschiedliche SQL-Dialekte, aber der kleinste gemeinsame Nenner ist trotzdem recht groß. Der größte Unterschied ist sicherlich das automatische Generieren von IDs und anschließend das (transaktionssichere und race-freie) Ermitteln der frisch generierten ID.

Ich hab das vor etwa 10 Jahren für Oracle, SQL Server und PostgreSQL durch einen sehr dünnen "Database Abstraction Layer" erschlagen, der im wesentlichen eine DB-spezifische Sonderbehandlung für Insert-Statements gemacht hat. Der Rest war im Wesentlichen das Vermeiden von DB-spezifischer SQL-Syntax, Umschreiben von meinen abstrakten Typnamen auf DB-spezifische Typnamen[1], und die Möglichkeit, nach dem connect() der DB erst einmal ein (oder mehrere) SQL-Statement zu senden, mit dem der DB Manieren beigebracht werden (z.B. ALTER SESSION SET NLS_NUMERIC_CHARACTERS=".," für eingedeutschte Oracles).

[1] Wie heißt ein 255 Zeichen langer String, der beliebige Unicode-Zeichen aufnehmen kann? VARCHAR2(255 CHAR) bei Oracle, NVARCHAR(255) bei MS SQL Server, CHARACTER VARYING(255) oder VARCHAR(255) bei PostgreSQL -- und STRING:255 in der Anwendung, egal für welche DB. Von anderen Typen will ich gar nicht anfangen. Über diesen Mechanismus wurden übrigens auch automatisch generierte IDs behandelt, der Typ hieß unabhängig von der DB AUTONUM und wurde je nach DB auf NUMBER(10) plus Trigger plus Sequence, INTEGER IDENTITY(1,1) oder SERIAL umgeschrieben.

Das größte Problem war aber folgendes: Das Projekt fing auf Oracle an, etwas anderes war gar nicht vorgesehen, später kamen dann aber Kunden, die unbedingt den MS SQL Server einsetzen wollten (Klarer Fall von Millionen-Fliegen-Prinzip, und außerdem hatte man MS ja schon so viel gutes Geld in den Rachen geworfen ...). Oracle erlaubt pro Connection (pro $dbh) beliebig viele (oder jedenfalls deutlich mehr als eines) Statements ($sth) im aktiven Zustand zwischen execute() und finish(), der MS SQL Server nicht mehr als eines. Und das ist noch heute so, auch wenn man sich da mittlerweile mit Tricks um die Beschränkung herummogeln kann. Leider mogelt man sich dabei zwangsläufig auch gleich um große Teile des DBI herum.

Deshalb habe ich eine Zwei-Klassen-Gesellschaft eingeführt: Eine Haupt-Verbindung, auf der man Daten per DELETE, INSERT und UPDATE verändern und Transaktionen nutzen und natürlich lesen durfte, und beliebig viele Hilfsverbindungen, auf denen man nur lesen durfte. Der "Database Abstraction Layer" hat dann zwei Methoden gehabt, mit denen man sich die Haupt-Verbindung oder eine alte oder neue, benannte Hilfsverbindung holen konnte. Für alle Datenbanken außer MS SQL Server haben beide Methoden immer stumpf die selbe Verbindung geliefert.

Alexander

[hotti]

Moin,

Ich sehe keinen Grund, das Projekt auf MySQL zu beschränken. Sicher, PHP und MySQL ist die übliche Kombination bei Massenhostern, und deswegen wäre es sicherlich sinnvoll, *auch* mit MySQL zurecht zu kommen. Aber gerade mit DBI bist Du sehr unabhängig von der DB, und warum sollte als DB nicht auch etwas vernünftiges wie PostgreSQL zum Einsatz kommen?

Stimmt. Das 'I' steht für Unabhängigkeit und ein klarer Punkt für Perl::DBI. Du weckst Erinnerungen: Replace haben 'wir' (alte Firma) früher gerne verwendet und jetzt wo Du das hier [..] postest, erinnere ich mich, dass der Umstieg zu PostgreSQL mit 'etwas' Arbeit verbunden war, es stand die Frage, entweder die Scripts zu ändern oder eine Prozedur für PostgreSQL zu schreiben. Wir sind damals jedoch nach kurzer Zeit wieder zu MySQL zuückgekehrt....

Nagut, für mein CMS habe ich gestern abend 'replace' getestet und den Code soweit sicher bekommen, die Unabhängigkeit von MySQL ist jedoch jetzt auch ein erklärtes Ziel und wird noch in der Entwicklungsphase umgesetzt ;)

Vielen Dank und schönes Wochenende,
Hotti

[hotti]

hi Matti,

Das kann man dann nämlich ganz schnell in ein UPDATE verwandeln (da spar ich mir beim C&P bissl Arbeit :)) und Werte und Spaltennamen sind noch näher beisammen.

Die Sache mit ON DUPLICATE ist eigentlich ganz einfach und von der engine unabhängig:

  
 my $st = q(  
   INSERT INTO bucket(sid, url, anz, datime) VALUES (?,?,?,NOW())  
   ON DUPLICATE KEY UPDATE datime=NOW(), anz=?  
 );  

Zu beachten ist hierbei, dass vier Platzhalter gebunden sind, ergo muss execute mit vier Parametern aufgerufen werden:

  
  eval { $sth->execute($self->{SID}, $self->{URL}, $anz, $anz) };  
  # caught exceptions  

Hotti