Search Request with empty Search Term causes Internal Server Error 500 with SQLite Backend

[bodo-hugo-barwich]

Working on the Docker Deployment #26 I tried to reproduce different URLs from the Production Site like https://perldoc.pl/5.28.1/search
But the empty Search Term produces a SQL Error in the SQLite Backend which leads to an Internal Server Error 500:

$ wget -S -O search_noresult.html --max-redirect=0 "http://localhost:3000/5.28.1/search"
--2020-11-24 16:32:06--  http://localhost:3000/5.28.1/search
Resolviendo localhost (localhost)... ::1, 127.0.0.1
Conectando con localhost (localhost)[::1]:3000... conectado.
Petición HTTP enviada, esperando respuesta... 
  HTTP/1.1 500 Internal Server Error
  Content-Type: text/html;charset=UTF-8
  Date: Tue, 24 Nov 2020 16:32:06 GMT
  Content-Security-Policy: default-src 'self'; connect-src 'self' www.google-analytics.com; img-src 'self' data: www.google-analytics.com www.googletagmanager.com; script-src 'self' 'unsafe-inline' cdnjs.cloudflare.com code.jquery.com stackpath.bootstrapcdn.com www.google-analytics.com www.googletagmanager.com; style-src 'self' 'unsafe-inline' cdnjs.cloudflare.com stackpath.bootstrapcdn.com; report-uri /csp-reports
  Server: Mojolicious (Perl)
  Content-Length: 30901
2020-11-24 16:32:06 ERROR 500: Internal Server Error.

which is documented in the Web Service Log like this:

[2020-11-24 16:32:06.23318] [22] [debug] [GcRSLW_i] GET "/5.28.1/search"
[2020-11-24 16:32:06.23391] [22] [debug] [GcRSLW_i] Routing to a callback
[2020-11-24 16:32:06.26225] [22] [error] [GcRSLW_i] DBD::SQLite::st execute failed: fts5: syntax error near "" at /home/perldoc-browser/lib/PerldocBrowser/Plugin/PerldocSearch/SQLite.pm line 87.
 at /home/perldoc-browser/lib/PerldocBrowser/Plugin/PerldocSearch/SQLite.pm line 87.

[2020-11-24 16:32:06.26287] [22] [debug] [GcRSLW_i] Template "exception.development.html.ep" not found
[2020-11-24 16:32:06.26315] [22] [debug] [GcRSLW_i] Template "exception.html.ep" not found
[2020-11-24 16:32:06.26343] [22] [debug] [GcRSLW_i] Rendering template "mojo/debug.html.ep"
[2020-11-24 16:32:06.30368] [22] [debug] Your secret passphrase needs to be changed
[2020-11-24 16:32:06.30888] [22] [debug] [GcRSLW_i] 500 Internal Server Error (0.07567s, 13.215/s)

Enabling the DBI SQL Trace in /lib/PerldocBrowser/Plugin/PerldocSearch/SQLite.pm :

sub _function_search ($c, $perl_version, $query, $limit = undef) {
  my $limit_str = defined $limit ? ' LIMIT ?' : '';
  my @limit_param = defined $limit ? $limit : ();
  $query =~ s/"/""/g;
  $query = join ' ', map { qq{"$_"} } split ' ', $query;

  $c->app->log->debug("_function_search - query dmp:\n"
    . dump $query);

  $c->sqlite->db->{TraceLevel} = "3|SQL";
  $c->sqlite->db->dbh->trace('3|SQL', 'log/development.log');

  my $res = undef;

  eval
  {
    $res = $c->sqlite->db->query(q{SELECT "name",
    snippet("functions_index", 1, '__HEADLINE_START__', '__HEADLINE_STOP__', ' ... ', 36) AS "headline"
    FROM "functions_index" WHERE "rowid" IN (SELECT "id" FROM "functions" WHERE "perl_version" = ? AND "description" != '')
    AND "functions_index" MATCH ? ORDER BY "rank"} . $limit_str,
    $perl_version, $query, @limit_param);

    $res = $res->hashes;
  };

  if($@)
  {
    $c->app->log->debug("_function_search - query failed!");

    die($@);
  }

  return $res;
}

documents the following Error Report:

[2020-11-24 17:24:45.85865] [20] [debug] [MhTNx5Ng] GET "/5.28.1/search"
[2020-11-24 17:24:45.86123] [20] [debug] [MhTNx5Ng] Routing to a callback
[2020-11-24 17:24:45.86733] [20] [debug] _function_search - query dmp:
""
    DBI::db=HASH(0x558fd9edc5c8) trace level set to 0x100/3 (DBI @ 0x0/0) in DBI 1.642-ithread (pid 20)
    -> FETCH for DBD::SQLite::db (DBI::db=HASH(0x558fd9edc5c8)~INNER 'Active') thr#558fd77e5260
    .. FETCH DBI::db=HASH(0x558fd9edc5c8) 'Active' = 1
    <- FETCH= ( 1 ) [1 items] at SQLite.pm line 106 via  at Database.pm line 32
    -> ping for DBD::SQLite::db (DBI::db=HASH(0x558fd9edc520)~0x558fd9edc5c8) thr#558fd77e5260
1   <- sqlite_db_filename= ( '/home/perldoc-browser/./perldoc-browser.sqlite' ) [1 items] at SQLite.pm line 248 via  at SQLite.pm line 78
    .. FETCH DBI::db=HASH(0x558fd9edc5c8) 'Active' = 1
1   <- FETCH= ( 1 ) [1 items] at SQLite.pm line 252 via  at SQLite.pm line 78
    <- ping= ( 1 ) [1 items] at SQLite.pm line 78 via  at SQLite.pm line 118
    <> FETCH= ( CODE(0x558fd9a5e258) ) [1 items] ('HandleError' from cache) at Database.pm line 53 via  at SQLite.pm line 98
    -> EXISTS in DBD::_::common for DBD::SQLite::db (DBI::db=HASH(0x558fd9edc5c8)~INNER 'HandleError') thr#558fd77e5260
1   <> FETCH= ( CODE(0x558fd9a5e258) ) [1 items] ('HandleError' from cache) at DBI.pm line 1371 via  at Database.pm line 55
    <- EXISTS= ( 1 ) [1 items] at Database.pm line 55 via  at SQLite.pm line 98
    <> FETCH= ( CODE(0x558fd9a5e258) ) [1 items] ('HandleError' from cache) at Database.pm line 55 via  at SQLite.pm line 98
    -> STORE for DBD::SQLite::db (DBI::db=HASH(0x558fd9edc5c8)~INNER 'HandleError' CODE(0x558fda641218)) thr#558fd77e5260
    STORE DBI::db=HASH(0x558fd9edc5c8) 'HandleError' => CODE(0x558fda641218)
    <- STORE= ( 1 ) [1 items] at Database.pm line 55 via  at SQLite.pm line 98
    -> prepare_cached in DBD::_::db for DBD::SQLite::db (DBI::db=HASH(0x558fd9edc520)~0x558fd9edc5c8 'SELECT "name",
    snippet("functions_index", 1, '__HEADLINE_START__', '__HEADLINE_STOP__', ' ... ', 36) AS "headline"
    FROM "functions_index" WHERE "rowid" IN (SELECT "id" FROM "functions" WHERE "perl_version" = ? AND "description" != '')
    AND "functions_index" MATCH ? ORDER BY "rank" LIMIT ?' undef 3) thr#558fd77e5260
sqlite trace: prepare statement: SELECT "name",
    snippet("functions_index", 1, '__HEADLINE_START__', '__HEADLINE_STOP__', ' ... ', 36) AS "headline"
    FROM "functions_index" WHERE "rowid" IN (SELECT "id" FROM "functions" WHERE "perl_version" = ? AND "description" != '')
    AND "functions_index" MATCH ? ORDER BY "rank" LIMIT ? at dbdimp.c line 903
1   <- prepare= ( DBI::st=HASH(0x558fda675ac8) ) [1 items] at DBI.pm line 1733 via  at Database.pm line 62
    <- prepare_cached= ( DBI::st=HASH(0x558fda675ac8) ) [1 items] at Database.pm line 62 via  at Database.pm line 60
    -> bind_param for DBD::SQLite::st (DBI::st=HASH(0x558fda675ac8)~0x558fda675a80 1 '5.28.1') thr#558fd77e5260
sqlite trace: bind into 0x558fda675ae0: 1 => 5.28.1 (0) pos 0 at dbdimp.c line 1515
    <- bind_param= ( 1 ) [1 items] at Database.pm line 111 via  at Database.pm line 63
    -> bind_param for DBD::SQLite::st (DBI::st=HASH(0x558fda675ac8)~0x558fda675a80 2 '') thr#558fd77e5260
sqlite trace: bind into 0x558fda675ae0: 2 =>  (0) pos 2 at dbdimp.c line 1515
    <- bind_param= ( 1 ) [1 items] at Database.pm line 111 via  at Database.pm line 63
    -> bind_param for DBD::SQLite::st (DBI::st=HASH(0x558fda675ac8)~0x558fda675a80 3 21) thr#558fd77e5260
sqlite trace: bind into 0x558fda675ae0: 3 => 21 (0) pos 4 at dbdimp.c line 1515
    <- bind_param= ( 1 ) [1 items] at Database.pm line 111 via  at Database.pm line 63
    -> execute for DBD::SQLite::st (DBI::st=HASH(0x558fda675ac8)~0x558fda675a80) thr#558fd77e5260
sqlite trace: executing SELECT "name",
    snippet("functions_index", 1, '__HEADLINE_START__', '__HEADLINE_STOP__', ' ... ', 36) AS "headline"
    FROM "functions_index" WHERE "rowid" IN (SELECT "id" FROM "functions" WHERE "perl_version" = ? AND "description" != '')
    AND "functions_index" MATCH ? ORDER BY "rank" LIMIT ? at dbdimp.c line 973
sqlite trace: Execute returned 2 cols at dbdimp.c line 1086
    -- HandleSetErr err=1, errstr='fts5: syntax error near ""', state=undef, undef
sqlite error 1 recorded: fts5: syntax error near "" at dbdimp.c line 1132
    -- HandleSetErr err=1, errstr='fts5: syntax error near ""', state=undef, undef
sqlite error 1 recorded: fts5: syntax error near "" at dbdimp.c line 1134
    !! ERROR: 1 'fts5: syntax error near ""' (err#2)
    <- execute= ( undef ) [1 items] at Database.pm line 64 via  at Database.pm line 60
    -> HandleError on DBI::st=HASH(0x558fda675a80) via CODE(0x558fda641218) (undef)
    <- HandleError= 0 (undef)
    -> STORE for DBD::SQLite::db (DBI::db=HASH(0x558fd9edc5c8)~INNER 'HandleError' CODE(0x558fd9a5e258)) thr#558fd77e5260
    STORE DBI::db=HASH(0x558fd9edc5c8) 'HandleError' => CODE(0x558fd9a5e258)
       ERROR: 1 'fts5: syntax error near ""' (err#0)
    <- STORE= ( 1 ) [1 items] at Database.pm line 70 via  at SQLite.pm line 98
    -> FETCH for DBD::SQLite::db (DBI::db=HASH(0x558fd9edc5c8)~INNER 'Active') thr#558fd77e5260
    .. FETCH DBI::db=HASH(0x558fd9edc5c8) 'Active' = 1
       ERROR: 1 'fts5: syntax error near ""' (err#0)
    <- FETCH= ( 1 ) [1 items] at SQLite.pm line 106 via  at Database.pm line 32
[2020-11-24 17:24:45.87228] [20] [debug] _function_search - query failed!
[2020-11-24 17:24:45.87366] [20] [error] [MhTNx5Ng] DBD::SQLite::st execute failed: fts5: syntax error near "" at /home/perldoc-browser/lib/PerldocBrowser/Plugin/PerldocSearch/SQLite.pm line 98.
 at /home/perldoc-browser/lib/PerldocBrowser/Plugin/PerldocSearch/SQLite.pm line 98.

[2020-11-24 17:24:45.87547] [20] [debug] [MhTNx5Ng] Template "exception.development.html.ep" not found
[2020-11-24 17:24:45.87578] [20] [debug] [MhTNx5Ng] Template "exception.html.ep" not found
[2020-11-24 17:24:45.87607] [20] [debug] [MhTNx5Ng] Rendering template "mojo/debug.html.ep"
[2020-11-24 17:24:45.90578] [20] [debug] Your secret passphrase needs to be changed
[2020-11-24 17:24:45.91161] [20] [debug] [MhTNx5Ng] 500 Internal Server Error (0.052952s, 18.885/s)

[Grinnz]

Thanks, should be fixed now.