📜 Revelio Pages: Paginated searchcmd Results and MariaDB-Safe LIKE Matching

This Mediabot v3 update improves an existing user-facing command:

searchcmd

The goal was to make command search output more readable and to fix a real MariaDB SQL escaping issue discovered during live testing.

---

Context

searchcmd <keyword> searches registered public commands by keyword.

Before this pass, the command could return up to 50 matches but tried to display them on a single IRC line.

That had two problems:

large result sets became unreadable
long output was truncated and information was lost

During the pagination work, a MariaDB compatibility bug was also exposed in the SQL LIKE ... ESCAPE clause.

---

Bug fix: MariaDB-safe LIKE ESCAPE

The broken SQL looked like this:

WHERE action LIKE ? ESCAPE '\'

On MariaDB/MySQL, this is fragile because the backslash can escape the SQL quote and break the string.

The command now uses a safer explicit escape character:

WHERE action LIKE ? ESCAPE '!'

User input is escaped before being used in the LIKE pattern:

$like =~ s/!/!!/g;
$like =~ s/%/!%/g;
$like =~ s/_/!_/g;

This means:

% is treated literally
_ is treated literally
! is escaped as the escape character itself

So searches like these no longer break SQL and no longer behave as unwanted wildcards:

searchcmd %
searchcmd _
searchcmd check

---

Improvement: paginated output

The old behavior tried to build one long line such as:

Commands containing 'ban': ban unban bans kickban ...

That does not scale well.

The new behavior keeps the SQL limit at 50 results, but displays details by NOTICE in chunks of 5 commands per line.

Example channel output:

Commands containing 'check': 12 result(s), showing max 50 - details sent by notice to Te[u]K

Then the user receives notices such as:

searchcmd[01]: checkauth checkhost checklevel checkban checktimer
searchcmd[02]: checkseen checkquote checkuser ...

This avoids flooding the channel while still giving the user the full useful result list.

---

Channel vs private behavior

If searchcmd is called from a channel:

the channel receives a short summary
the detailed result list is sent by NOTICE to the requesting nick

If it is called privately:

the summary and detail lines are sent privately/notice-style to the user

This keeps channel output clean.

---

SQL safety preserved

The command still uses a prepared statement with a placeholder:

WHERE action LIKE ? ESCAPE '!'

The keyword becomes a bound value, not string-concatenated SQL.

This keeps SQL injection protection intact while adding literal wildcard escaping.

---

Regression test added

A new test file covers the behavior:

t/cases/18_searchcmd_pagination.t

It checks that:

searchcmd exists
SQL LIMIT 50 is preserved
MariaDB-safe ESCAPE '!' is used
!, %, and _ are escaped correctly
results are paginated 5 commands per line
detail lines are numbered
channel flood is avoided by sending details by notice
the old single-line truncation path is gone

The test was also adjusted after discovering that the initial negative assertion was too fragile.

---

Files changed

Mediabot/DBCommands.pm
t/cases/18_searchcmd_pagination.t

---

Suggested validation

Syntax checks:

perl -I. -c Mediabot/DBCommands.pm
perl -I. -c t/cases/18_searchcmd_pagination.t
perl -I. -c mediabot.pl

Focused tests:

env LANG=C.UTF-8 LC_ALL=C.UTF-8 perl t/test_commands.pl --filter 'searchcmd_pagination|dispatch|timer_validation' --verbose

Full test suite:

env LANG=C.UTF-8 LC_ALL=C.UTF-8 perl t/test_commands.pl --verbose

Runtime checks:

m searchcmd check
m searchcmd %
m searchcmd _

Expected result:

no SQL error
summary in channel
details by notice
literal % and _ search behavior

---

Suggested commit

📜 Revelio Pages: paginate searchcmd results by notice

---

Closing note

This is a practical user-facing improvement.

searchcmd is now easier to read, safer with MariaDB string escaping, and cleaner in public channels.

Small command, real polish.