Intelligente Suche

[Lupus]

Hallo,

schon seit mehreren Stunden probiere ich schon an einem "intelligenten Suchscript" mit PHP und MySQL rum, aber ich komme nicht zu dem Resultat das ich gerne hätte: eine kombination aus Sting- und Volltextsuche.
Man hat - ähnlich wie bei FileMaker - eine Suchmaske mit mehreren Textfeldern, in die Suchebegriffe eingegeben werden.
Wenn man also in einem Datensatz in der Spalte 'sonstiges' "Ich habe keine Haustiere" stehen hat, soll man den Datensatz z.B mit folgenden Eingaben (im Textfeld mit name="sonstiges") finden: "Ich habe Haustiere"(Volltextsuche), "Tier"(Stringsuche),...

Hier meine Ansätze:
 - [...] WHERE sonstiges LIKE '%".$_POST['sonstiges']."%' AND [...]
   Problem: "Ich habe Haustiere" kann nicht gefunden werden,
   da nur Strings gesucht werden.
 - [...] WHERE MATCH sonstiges AGAINST '".$_POST['sonstiges']."' AND [...]
   Problem: wegen dem AND müssen alle Felder ausgefüllt werden,
   "Tier" kann nicht gefunden werden wegen der Volltext suche.

Die Tabelle hat am Ende 27 Spalten und man soll auch Datensätze finden, wenn nicht alle 27 Textfelder ausgefüllt werden.

Kann mir jemand Tipps geben, wie ich den MySQL-Query formulieren muss?

Danke,
Lupus

[Tom]

Hello,

ich habe das jetzt noch nicht ganz verstanden.

Angenommen, Du hast die Spalten:

Tierart
  Gewicht
  Eigentümer

Und ein passendes Suchformular dazu

Suchbegriff          Wolf
  Tierart              Hund
  Gewicht              18,5kg
  Eigentümer           Wolf

Soll jetzt der Suchbegriff in allen Feldern gesucht werden?
Was ist, wenn im Suchbegriff

Suchbegriff          Wolf Katze Hund

drinsteht?
Soll dann trotzdem jedes Wort in jedem Feld gesucht werden?

Ich bin ein bisschen blöd und deshalb frage ich immer lieber dreimal. *)

*) bevor überhaupt keiner fragt...

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom

[Lupus]

Der Witz ist, dass man nicht nur ein Feld hat, in das man einen oder mehrere Suchbegriffe eingibt, sondern man hat für jede Spalte ein eigenes Suchfeld(in einem Formular).
Es soll dann nach einem Datensatz suchen, der alle "kriterien" der augefüllten Felder einhält - und die nicht ausgefüllten ingnoriert.

Möglich wäre das schon so:
$sql = "SELECT id, tierart, eigentuemer,... FROM animals WHERE ";
if (trim($_POST['gewicht'])!='') {
  $sql .= "MATCH gewicht AGAINST '".$_POST['gewicht']."'";
  // Abfragen ob weitere Felder ausgefüllt sind, Wenn ja: $sql .= " AND ";
}
if (trim($_POST['eigentuemer'])!='') {
  $sql .= "MATCH eigentuemer AGAINST '".$_POST['eigentuemer']."'";
  // Abfragen ob weitere Felder ausgefüllt sind, Wenn ja: $sql .= " AND ";
}
//... die if abfrage für alle Spalten wiederhohlen.

Aber das Problem ist, dass die Tabelle 27 spalten hat und der Code dann extrem unübersichtlich wird...
Und so kann immernoch nicht nach Teilen aus Wörtern gesucht werden, also wenn die nach der Tierart Haustier gesucht, soll man diesen Datensatz auch finden denn man nur Haus[...], [...]tier, oder [...]ustie[...] in <input name="haustier" .../> eingibt.

Jetzt suche ich nach einem guten Query, der all das kann/erfüllt,...

Ich hoffe das mein Problem so einigermassen klar geschildert ist.

[Sven Rautenberg]

Moin!

... nicht nur ein Feld ... 27 spalten ... Code dann extrem unübersichtlich ...
Jetzt suche ich nach einem guten Query, der all das kann/erfüllt,...

Du hast ein komplexes Problem, eine komplexe Tabelle, und erwartest eine EINFACHE Lösung?

Das, was du willst, schätze ich ungefähr als so komplex ein, wie die Suchmaschine Google. Ist Google einfach? Sicher nur in der Benutzung, nicht in der Programmierung.

- Sven Rautenberg

[Lupus]

Mittlerweile konnte ich das Problem Lösen.
Hier ein Codeauszug:

<?php
  if(!isset($_POST['search'])) {
?>
<form action="index.php?content=search" method="post">
  Tierart <input type="text" name="tierart" />
  Gewicht <input type="text" name="gewicht" />
  Name <input type="text" name="name" />
  Beschreibung <input type="text" name="beschreibung" />
  <br><br>
  <input type="hidden" name="search" value="seach" />
  <input type="submit" value="Suchen" />
</form>
<?php
  }
  else {
      $i = 0;
      $a = 0;
      $sql = "SELECT
                  name, gewicht
              FROM
                  datenfelder
              WHERE ";

foreach($_POST as $key2 => $value2) {
          if(trim($value2)!='' AND $key2 != 'search') $a++;
      }

foreach($_POST as $key => $value) {
          if(trim($value)!='' AND $key != 'search') {
              $i++;
              $sql .= "(MATCH ".$key." AGAINST ('".$_POST[$key]."') OR ";
              $sql .= $key." LIKE '%".$_POST[$key]."%')";
              if($i<$a) $sql .= " AND ";
          }
      }
      if($a == 0) $sql .= "id = 0"; //id ist wegen auto_increment nie 0
      $result = mysql_query($sql) OR die(mysql_error());
      if (mysql_num_rows($result)) {
          while ($row = mysql_fetch_assoc($result)) {
              echo $row['name'] . " | " . $row['gewicht'] . "<br>\n";
          }
      }
      else {
          echo "Es konnten keine Datensätze gefunden werden";
          // evt. Eingabeformular erneut anzeigen
      }

}
?>

Voraussetzung von diesem Script ist, dass die Eingabefelder den selben Namen wie die Tabellen Spalten der Datenbank haben.

Diese suche kann nun Folgendes:
  - Gross- und Kleinschreibung wird nicht beachtet
  - Man kann nach Teilwörtern suchen. (So kann ein Datensatz auch gefunden werden,
    wenn man nach der Tierart "Fuchs" sucht und in der Datenbank "Wüstenfuchs" steht.
  - Man kann die Suchwörter in belibiger Reihenfolge eingeben
    (Wenn die Beschreibung "grosse Ohren, rotes Fell, lebt in der Wüste" ist,
    wird der Datensatz auch gefunden wenn mann "Wüste, Fell rot" eingibt.
  - Wenn man mehrere Suchbegriffe in ein Feld eintippt und einen Shcreibfehler macht,
    wird der Datensatz - sofern das andere Wort gefunden wird - trotzdem ausgegeben.
  - Eine kombination dieser Sucharten ist auch möglich.

Grüsse Lupus

[Sven Rautenberg]

Moin!

Mittlerweile konnte ich das Problem Lösen.

Hurra, wir bauen uns eine SQL-Injection und schießen uns damit selbst ins Knie.

$sql .= "(MATCH ".$key." AGAINST ('".$_POST[$key]."') OR ";
$sql .= $key." LIKE '%".$_POST[$key]."%')";

Ein Angreifer kann $key und $_POST[$key] frei bestimmen - und ich sehe hier nirgendwo auch nur den Ansatz eines Hauchs einer Kontrolle oder Escaping.

- Sven Rautenberg

[Lupus]

Ein Angreifer kann $key und $_POST[$key] frei bestimmen -
und ich sehe hier nirgendwo auch nur den Ansatz eines Hauchs
einer Kontrolle oder Escaping.

Ich habe hier alles möglichst eifach halten wollen. Aber für das Escapen habe ich eine eigene function geschrieben:

function input($var) {
    $var = addslashes(htmlentities(trim($var), ENT_QUOTES, 'UTF-8'));
    return $var;
}

[...]
$sql .= "(MATCH ".$key." AGAINST ('".input($_POST[$key])."' IN BOOLEAN MODE) OR ";
$sql .= $key." LIKE '%".input($_POST[$key])."%')";
[...]

$key kann man nur frei bestimmen, wenn man z.B Firebug hat.
Aber auch das Problem lässt sich lösen: mann kann z.B. alle Spalten
in einem Array Speichern und dann eine weitere Abfrage einbauen:

$spalten = array('tierart', 'gewicht', 'name', 'beschreibung',...);
foreach($_POST as $key => $value) {
    if(!in_array($key, $spalten) AND $key != 'search') {
        die('Bitte benutzen Sie die Aingabefelder aus dem Formular...');
    }
}

So sollten doch eigentlich alle Sicherheitslücken geschlossen ein,...
Hoffe ich zumindest...

Grüsse,
Lupus

[dedlfix]

echo $begrüßung;

Ich habe hier alles möglichst eifach halten wollen. Aber für das Escapen habe ich eine eigene function geschrieben:
    $var = addslashes(htmlentities(trim($var), ENT_QUOTES, 'UTF-8'));

addslashes() berücksichtigt zu wenig Zeichen wenn es sich um eine MySQL-Datenbank handelt. Dafür ist mysql_real_escape_string() vorgesehen. (Für andere Datenbanksysteme sind Maskierungen gemäß deren Regeln zu verwenden.)

HTML-Entities bzw. jegliche andere für ein bestimmtes Ausgabemedium vorgesehe Kodierung hat in einer Datenbank nichts verloren, weil du dann die datenbankeigenen Stringfunktionen nicht mehr oder nur noch eingeschränkt nutzen kannst. 'Hühner' sind bei dir 'H&uuml;hner' und werden beim Sortieren vor den 'Hasen' ausgegeben, weil ein & kleiner als ein a ist. Und was machst du mit den Entities, wenn du später mal die Daten in eine Textdatei ausgeben oder per E-Mail versenden willst?

htmlentities() sind für eine durchgehend auf UTF-8 basierenden Verarbeitung auch nicht mehr nötig, da praktisch sämtliche Zeichen direkt gemäß UTF-8 kodiert werden können. Einzig die htmlspecialchars() werden in HTML noch benötigt.

Zusammenfassend:

• addslashes() zugunsten von mysql_real_escape_string() aus dem Script und dem Gedächtnis verdrängen.
• htmlentities() zugunsten von htmlspecialchars() aus dem Script und dem Gedächtnis verdrängen.
• htmlspecialchars() erst zur Ausgabe in einen HTML-Kontext anwenden.

$sql .= "(MATCH ".$key." AGAINST ('".input($_POST[$key])."' IN BOOLEAN MODE) OR ";
$key kann man nur frei bestimmen, wenn man z.B Firebug hat.

Für die Maskierung von Spaltennamen gibt es für MySQL keine vorgefertigte Funktion. Du müsstest das selbst gemäß den unter Database, Table, Index, Column, and Alias Names (ab Absatz beginnend mit "Identifier quote characters can be included ...") aufgeführten Regeln bewerkstelligen.

echo "$verabschiedung $name";

[Lupus]

Danke für deine Zusammenfassung, dedlfix!

Wenn ich dich richtig verstanden habe, sollte die input funtction also so aussehen:

$var = mysql_real_escape_string(htmlspecialchars(trim($var), ENT_QUOTES, 'UTF-8'));

Also immernoch mit ENT_QUOTES Doppelte- und Einfachanführungszeichen in HTMLZeichen umwandeln.
Es sollen unter Umständen auch solche Zeichen einfefügt werden: ø æ å,...
Deshalb verwende ich überall das UTF-8 charset(Datenbank und im <head>)
Aber trotzdem nicht in HTML Zeichen umwandeln? Oder nur für die Ausgabe umwandeln lassen(z.B. eine output function).
Das charset möchte ich sicherheitshalber in htmlspecialchars stehen lassen.
Was ist den die Gefahr wenn ich addslashes statt mysql_real_escape_string verwende?
Am ende soll diese Datenbank mit MSS (Microsoft SQL Server) laufen. Damit kenni ich micht nocht nicht sehr gut au und weiss nicht was es für befehle gibt...

[Johannes Zeller]

Hallo Lupus,

$var = mysql_real_escape_string(htmlspecialchars(trim($var), ENT_QUOTES, 'UTF-8'));

Nein, so geht das nicht. Lies dir nochmal die Beschreibung zu mysql_real_escape_string() im Handbuch durch. Du musst da ansetzen, wo die Daten in dein SQL-Query eingefügt werden:

        foreach($_POST as $key => $value) {  
          if(trim($value)!='' AND $key != 'search') {  
              $i++;  
              $sql .= "(MATCH ".$key." AGAINST ('".mysql_real_escape_string($value)."') OR ";  
              $sql .= $key." LIKE '%".mysql_real_escape_string($value)."%')";  
              if($i<$a) $sql .= " AND ";  
          }  
      }

Da $value gleich $_POST['key'] ist, kann man das hier stattdessen verwenden.

Das Problem ist aber, dass mysql_real_escape_string nicht zum Maskieren von Spaltennamen verwendet werden kann und es in PHP auch keine vorgefertige Funktion dafür gibt. Eine gute Lösung dafür ist es, ein Array mit allen möglichen Spaltennamen (in deinem Falle wohl ohne 'search' zu haben. Wenn wir davon ausgehen, dass du irgendwo weiter oben ein solches Array mit dem namen $spalten angelegt hast, kannst du nun einfach überprüfen ob es eine Spalte mit dem Namen $key gibt:

        foreach($_POST as $key => $value) {  
          if(trim($value)!='' AND [link:http://www.php.net/in-array@title=in_array]($key, $spalten)) {  
              $i++;  
              $sql .= "(MATCH ".$key." AGAINST ('".mysql_real_escape_string($value)."') OR ";  
              $sql .= $key." LIKE '%".mysql_real_escape_string($value)."%')";  
              if($i<$a) $sql .= " AND ";  
          }  
      }

Also immernoch mit ENT_QUOTES Doppelte- und Einfachanführungszeichen in HTMLZeichen umwandeln.
Es sollen unter Umständen auch solche Zeichen einfefügt werden: ø æ å,...
Deshalb verwende ich überall das UTF-8 charset(Datenbank und im <head>)

Du solltest noch kontrollieren, dass dein Server auch im HTTP-Header Content-Type UTF-8 oder zumindest nichts Anderes, als du mittels er Meta-Angabe angegeben hast, sendet. Die Angabe im HTTP-Header überschreibt nämlich die Angabe im HTML-Quelltext.

Aber trotzdem nicht in HTML Zeichen umwandeln? Oder nur für die Ausgabe umwandeln lassen(z.B. eine output function).

Grunsätzlich gilt, immer nur für den entsprechenden Kontext maskieren. Also für die Kommunikation mit der Datenbank mysql_real_escape_string() verwenden, für die Ausgabe als HTML htmlspecialchars().

Was ist den die Gefahr wenn ich addslashes statt mysql_real_escape_string verwende?

Addslashes ist zu generisch und berücksichtigt nicht alle Fälle, die mysql_real_escape_string berücksichtigt.

Am ende soll diese Datenbank mit MSS (Microsoft SQL Server) laufen. Damit kenni ich micht nocht nicht sehr gut au und weiss nicht was es für befehle gibt...

Dann solltest du dein Script so programmieren, dass die Kommunikation zur Datenbank vernünftig gekapselt ist, und sich der entsprechende Teil leicht austauschen lässt.

Im PHP-Handbuch findest du die Microsoft-SQL-Server-Funktionen. Allerdings gibt es für MS-SQL keine vordefinierte Escape-Funktion. Laut den Kommentaren im Handbuch reicht es allerdings aus, ' durch '' zu ersetzen, beispielsweise mittels str_replace().

Schöne Grüße,

Johannes

[Sven Rautenberg]

Moin!

Am ende soll diese Datenbank mit MSS (Microsoft SQL Server) laufen. Damit kenni ich micht nocht nicht sehr gut au und weiss nicht was es für befehle gibt...

Dann kannst du deine wunderschöne Lösung jetzt direkt wieder in die Tonne treten, denn du verwendest SQL-Befehle, die nur mit MySQL laufen.

Entwickle immer mit der Datenbank, die am Ende auch eingesetzt werden soll!

Oder verzichte auf die Möglichkeit, die speziellen Features einer Datenbank einzusetzen, und verwende dann einen Datenbank-Abstraktionslayer, der nur allgemeine, in allen damit nutzbaren Datenbanken vorkommende Features unterstützt.

- Sven Rautenberg

[Tom]

Hello,

Zusammenfassend:

macic_quotes ausschalten oder wieder entfernen _vor_ dem mysql_real_escape_string()
http://de.php.net/manual/en/ref.info.php#ini.magic-quotes-gpc
http://de.php.net/manual/en/function.get-magic-quotes-gpc.php

• addslashes() zugunsten von mysql_real_escape_string() aus dem Script und dem Gedächtnis verdrängen.
• htmlentities() zugunsten von htmlspecialchars() aus dem Script und dem Gedächtnis verdrängen.
• htmlspecialchars() erst zur Ausgabe in einen HTML-Kontext anwenden.

$sql .= "(MATCH ".$key." AGAINST ('".input($_POST[$key])."' IN BOOLEAN MODE) OR ";
$key kann man nur frei bestimmen, wenn man z.B Firebug hat.

Für die Maskierung von Spaltennamen gibt es für MySQL keine vorgefertigte Funktion. Du müsstest das selbst gemäß den unter Database, Table, Index, Column, and Alias Names (ab Absatz beginnend mit "Identifier quote characters can be included ...") aufgeführten Regeln bewerkstelligen.

Harzliche Grüße vom Berg
http://bergpost.annerschbarrich.de

Tom