1 files changed, 85 insertions, 2 deletions

diff --git a/src/sisudoc/spine_search.d b/src/sisudoc/spine_search.d
index e551cc8..599f3c7 100755
--- a/src/sisudoc/spine_search.d
+++ b/src/sisudoc/spine_search.d
@@ -62,6 +62,35 @@ import arsd.core;
 import arsd.cgi;
 import d2sqlite3;
 import std.process : environment;
+import std.file : exists, isFile;
+/+ ↓ sqlite db schema version this search form expects, as stamped by spine in
+     PRAGMA user_version; keep in step with sqlite_db_schema_version in
+     sisudoc-spine src/sisudoc/outputs/io_out/sqlite.d
++/
+enum sqlite_db_schema_version_expected = 1;
+/+ ↓ the db to search is named by a request parameter, so it must be a plain
+     filename within the configured db directory: no directory separators, no
+     parent references, no leading dot; anything else falls back to the default
++/
+string db_filename_sanitised(string _requested, string _default) {
+  import std.algorithm : canFind;
+  if (_requested.length == 0
+  || _requested.canFind("/")
+  || _requested.canFind("\\")
+  || _requested.canFind("..")
+  || _requested[0] == '.') {
+    return _default;
+  }
+  foreach (_c; _requested) {
+    if (!((_c >= 'a' && _c <= 'z')
+    || (_c >= 'A' && _c <= 'Z')
+    || (_c >= '0' && _c <= '9')
+    || _c == '.' || _c == '_' || _c == '-')) {
+      return _default;
+    }
+  }
+  return _requested;
+}
 void spine_cgi_search_function(Cgi cgi) {
   mixin(import("configuration.txt"));
   string header;
@@ -99,7 +128,7 @@ void spine_cgi_search_function(Cgi cgi) {
   conf.query_string            = environment.get("QUERY_STRING",          "");
   conf.http_url                = environment.get("HTTP_REFERER",          conf.query_base_url ~ conf.query_string);
   conf.request_method          = environment.get("REQUEST_METHOD",        "POST");
-  conf.db_path                 = _cfg.db_sqlite_path;
+  conf.db_path                 = environment.get("SPINE_SEARCH_DB_PATH", _cfg.db_sqlite_path);
   struct CGI_val {
     string db_selected      = "";
     string sql_match_limit  = "";     // radio: ( 1000 | 2500 )
@@ -1590,6 +1619,11 @@ void spine_cgi_search_function(Cgi cgi) {
         return val;
       }
       cv.db_selected      = set_value("selected_db", _cfg.db_sqlite_filename); // selected_db_name == db (spine.search.db or whatever)
+      /+ ↓ selected_db names a file in the configured db directory, never a path
+           a value carrying a directory separator, a parent reference or a leading
+           dot is discarded in favour of the configured default
+      +/
+      cv.db_selected      = db_filename_sanitised(cv.db_selected, _cfg.db_sqlite_filename);
       cv.sql_match_limit  = set_value("sml",         "1000");
       cv.sql_match_offset = set_value("smo",         "0");
       cv.search_text      = set_value("sf",          "");
@@ -1630,7 +1664,56 @@ void spine_cgi_search_function(Cgi cgi) {
       // cgi.write("cv.checked_sql: "      ~ (cv.checked_sql ? "checked" : "off") ~ "<br>\n");
     }
   }
-  auto db = Database(conf.db_path ~ "/" ~ cv.db_selected);
+  string db_file_with_path = conf.db_path ~ "/" ~ cv.db_selected;
+  /+ ↓ report a missing db rather than let sqlite create an empty one +/
+  if (!(db_file_with_path.exists && db_file_with_path.isFile)) {
+    cgi.write("<hr><p class=\"publication\">search database not found: "
+      ~ cv.db_selected
+      ~ "</p>\n</body>\n");
+    return;
+  }
+  /+ ↓ the search form only ever reads, open read only +/
+  auto db = Database(db_file_with_path, SQLITE_OPEN_READONLY);
+  { /+ ↓ a db that carries neither of the tables searched is not a spine search db +/
+    long _tables_found = 0;
+    try {
+      _tables_found = db.execute(
+        "SELECT count(*) FROM sqlite_master WHERE type = 'table'"
+        ~ " AND name IN ('metadata_and_text', 'doc_objects');"
+      ).oneValue!long;
+    } catch (Exception) {
+      _tables_found = 0;
+    }
+    if (_tables_found < 2) {
+      cgi.write("<hr><p class=\"publication\">not a spine search database: "
+        ~ cv.db_selected
+        ~ "</p><p>expected tables metadata_and_text and doc_objects;"
+        ~ " build the database with: spine --sqlite-db-recreate ... then"
+        ~ " spine --sqlite-update ... </p>\n</body>\n");
+      db.close;
+      return;
+    }
+  }
+  { /+ ↓ db schema version, as stamped by spine in PRAGMA user_version +/
+    int _schema_version = 0;
+    try {
+      _schema_version = db.execute("PRAGMA user_version;").oneValue!int;
+    } catch (Exception) {
+      _schema_version = 0;
+    }
+    if (_schema_version != 0
+    && _schema_version != sqlite_db_schema_version_expected) {
+      cgi.write("<hr><p class=\"publication\">search database schema version "
+        ~ _schema_version.to!string
+        ~ ", this search form expects version "
+        ~ sqlite_db_schema_version_expected.to!string
+        ~ "</p><p>the database needs to be rebuilt by a matching spine:<br>"
+        ~ "spine --sqlite-db-recreate --sqlite-db-path=... --sqlite-db-filename=...<br>"
+        ~ "spine --sqlite-update ... </p>\n</body>\n");
+      db.close;
+      return;
+    }
+  }
   {
     uint sql_match_offset_counter(T)(T cv) {
       sql_match_offset_count += cv.sql_match_limit.to!uint;