From cd8a98300440418d31ac9222a53c25602cc100de Mon Sep 17 00:00:00 2001 From: Ralph Amissah Date: Mon, 14 Sep 2026 10:44:22 -0400 Subject: search: allow a db path from the environment Allow a db path from the environment, and refuse what it cannot read Three changes related to opening the database: - provide an environment override for the db path: SPINE_SEARCH_DB_PATH overrides the compiled-in db_sqlite_path, beside the other environment.get calls. Path was the one setting without an override, (SERVER_NAME, REQUEST_SCHEME, SCRIPT_NAME, QUERY_STRING could come from the environment). This adds consistency and makes testing the search form database builds easier (as it no longer requires rebuilding the binary with a patched configuration for readily accessible db path) - selected_db is required to be a plain filename within the configured directory, the database is opened read only, and a database that is not there is named rather than created. - The form now reads PRAGMA user_version, as stamped by spine, and refuses a database built to another schema version, naming both versions and the two commands that rebuild it. A database carrying neither of the tables it joins is refused the same way. sqlite_db_schema_version_- is expected and has to be in sync/step with the sqlite_db_schema_version in sisudoc-spine src/sisudoc/outputs/io_out/sqlite.d; the two tests added there under test/ check that they agree. (assisted by Claude-Code) --- ..._doc_header_including_copyright_and_license.org | 30 +++++- org/spine_search_cgi.org | 101 ++++++++++++++++++++- src/sisudoc/spine_search.d | 87 +++++++++++++++++- 3 files changed, 213 insertions(+), 5 deletions(-) diff --git a/org/sisudoc_spine_version_info_and_doc_header_including_copyright_and_license.org b/org/sisudoc_spine_version_info_and_doc_header_including_copyright_and_license.org index b2a6fec..4e5b2f2 100644 --- a/org/sisudoc_spine_version_info_and_doc_header_including_copyright_and_license.org +++ b/org/sisudoc_spine_version_info_and_doc_header_including_copyright_and_license.org @@ -98,7 +98,7 @@ #+NAME: spine_project_version_part_minor #+BEGIN_SRC org -18 +24 #+END_SRC *** patch @@ -137,3 +137,31 @@ git://git.sisudoc.org/package/dlang-nix-overlays-and-flake #+BEGIN_SRC org git+file:///home/ralph/grotto/repo/git.repo/projects/doc-reform/packages/nix-flakes/dlang/dlang-nix-flakes" #+END_SRC + +* ocda ssp version format + +#+NAME: ocda_ssp_format_version +#+BEGIN_SRC org +<>.<> +#+END_SRC + +*** major + +#+NAME: ocda_ssp_format_version_part_major +#+BEGIN_SRC org +1 +#+END_SRC + +*** minor + +#+NAME: ocda_ssp_format_version_part_minor +#+BEGIN_SRC org +1 +#+END_SRC + +* sqlite collection search db schema version + +#+NAME: sqlite_collection_search_db_schema_version +#+BEGIN_SRC org +1 +#+END_SRC diff --git a/org/spine_search_cgi.org b/org/spine_search_cgi.org index e160c2b..b32277f 100644 --- a/org/spine_search_cgi.org +++ b/org/spine_search_cgi.org @@ -82,6 +82,7 @@ version (Posix) { module spine_search; @system: <> +<> void spine_cgi_search_function(Cgi cgi) { mixin(import("configuration.txt")); <> @@ -184,7 +185,56 @@ void spine_cgi_search_function(Cgi cgi) { // <> } } - 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("

search database not found: " + ~ cv.db_selected + ~ "

\n\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("

not a spine search database: " + ~ cv.db_selected + ~ "

expected tables metadata_and_text and doc_objects;" + ~ " build the database with: spine --sqlite-db-recreate ... then" + ~ " spine --sqlite-update ...

\n\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("

search database schema version " + ~ _schema_version.to!string + ~ ", this search form expects version " + ~ sqlite_db_schema_version_expected.to!string + ~ "

the database needs to be rebuilt by a matching spine:
" + ~ "spine --sqlite-db-recreate --sqlite-db-path=... --sqlite-db-filename=...
" + ~ "spine --sqlite-update ...

\n\n"); + db.close; + return; + } + } { uint sql_match_offset_counter(T)(T cv) { sql_match_offset_count += cv.sql_match_limit.to!uint; @@ -232,6 +282,41 @@ import d2sqlite3; import std.process : environment; #+END_SRC +**** schema version + +#+NAME: spine_search_schema_version +#+BEGIN_SRC d +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 = <>; +/+ ↓ 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; +} +#+END_SRC + **** inits #+NAME: spine_search_init_1 @@ -295,7 +380,7 @@ conf.query_base_url = conf.http_request_type ~ "://" ~ conf.http_host ~ 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); #+END_SRC **** cgi val @@ -1001,6 +1086,11 @@ return val; #+NAME: spine_search_set_selections #+BEGIN_SRC d 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", ""); @@ -2207,6 +2297,13 @@ _cfg.doc_root_db /var/www/sqlite/ #+END_SRC +*** sqlite collection search db schema version + +#+NAME: sqlite_collection_search_db_schema_version +#+BEGIN_SRC text +<<./sisudoc_spine_version_info_and_doc_header_including_copyright_and_license.org:sqlite_collection_search_db_schema_version()>> +#+END_SRC + *** spine_search configuration.txt see configuration_cfte.org 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") ~ "
\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("

search database not found: " + ~ cv.db_selected + ~ "

\n\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("

not a spine search database: " + ~ cv.db_selected + ~ "

expected tables metadata_and_text and doc_objects;" + ~ " build the database with: spine --sqlite-db-recreate ... then" + ~ " spine --sqlite-update ...

\n\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("

search database schema version " + ~ _schema_version.to!string + ~ ", this search form expects version " + ~ sqlite_db_schema_version_expected.to!string + ~ "

the database needs to be rebuilt by a matching spine:
" + ~ "spine --sqlite-db-recreate --sqlite-db-path=... --sqlite-db-filename=...
" + ~ "spine --sqlite-update ...

\n\n"); + db.close; + return; + } + } { uint sql_match_offset_counter(T)(T cv) { sql_match_offset_count += cv.sql_match_limit.to!uint; -- cgit v1.2.3