sloop query opens a database for reading. There are two ways in: a builder that asks questions, and --sql for one statement when you already know what you want. Neither can change anything, and the second half of this page is about why that is a guarantee rather than a promise.

The builder

sloop query shop with no other flags. Six questions, and the only thing you type is a value — every table, every column and every test is picked from a list the database itself supplied.

Which database?
Skipped when you named one on the command line. Otherwise every database this registry knows, as a list.
Which table?
The tables that are actually there, read from the catalogue. Not typed, so it cannot be mistyped.
Which columns?
A tick list of that table’s columns. Tick none and you get all of them.
Narrow it down?
No, and every row comes back. Yes, and it asks the next three — then offers another condition.
Which column? / And it…
The test, from a list of eleven: is, is not, contains, starts with, ends with, is more than, is less than, is at least, is at most, is empty, is not empty.
What are you looking for?
The only free text in the whole builder, and it is a value rather than a fragment of a statement. The tests that need no value — is empty, is not empty — skip it.

There is no DROP on any of those lists, so there is no DROP to catch. The builder is safe because of what it can produce, not because of what it screens out — and that is a different kind of safe from a filter.

The builder ends in a scrollable grid, two hundred rows at a time. The page is a LIMIT in the statement rather than a window over everything the server sent, so a table nobody could print is a table you can open and page through.

--sql, for one statement

The flag form exists for two reasons: somebody who knows SQL should not have to click through a builder to run one statement, and a session done by hand should become a line you can schedule.

one statement

$ sloop query shop --sql "select city, count(*) as customers,$     round(avg(spend),2) as avg_spend from customers group by city"Reading: shop  project city    customers  avg_spendLisbon  321        237.54Dhaka   321        238.28Berlin  321        237.17Osaka   321        237.91 Rows: 4

--sqlprints rather than opening the grid, and the choice is made by which form you used rather than by guessing at what is attached — a flag form that opened a full-screen grid could not go in a script at all.

a bigger result

$ sloop query shop --sql "select id, name, city, spend from customers"Reading: shop  project id    name           city    spend1     Customer 1     Berlin  0.372     Customer 2     Lisbon  0.743     Customer 3     Osaka   1.11…1283  Customer 1283  Osaka   474.711284  Customer 1284  Dhaka   475.08 Rows: 1284

And because the builder is a screen of questions, it refuses to start where there is nobody to answer them — naming the flag that does the job instead:

exit 2

$ sloop query shop   # from a scriptReading: shop  projecterror: building a query is a screen of questions, and there is no terminal to ask at  hint: give the statement instead: `sloop query <name> --sql "select ..."`

Nothing it runs can write, and the server is what says so

--sql takes any statement you give it. It is not checked against a list of forbidden words. Instead, every statement either form produces runs inside a transaction that sloop has told the server is read-only — and from there it is the engine's rule, not sloop's.

PostgreSQL refusing, three ways

$ sloop query shop --sql "update customers set city = 'Nowhere'"Reading: shop  projecterror: psql.exe failed against postgres://app@127.0.0.1:5446/shop:  ERROR:  cannot execute UPDATE in a read-only transaction $ sloop query shop --sql "delete from orders"  ERROR:  cannot execute DELETE in a read-only transaction $ sloop query shop --sql "create table sneaky (id int)"  ERROR:  cannot execute CREATE TABLE in a read-only transaction

Read the error text: cannot execute UPDATE in a read-only transaction is PostgreSQL talking, not sloop. sloop did not inspect that statement, decide it was a write and block it. It handed it to the server inside a read-only transaction and the server refused it.

Why that is not the same as a word list

A blacklist checks the first word. So here is a statement whose first word is WITH — allowed by any such list — which deletes every row in a table:

both servers, both refused

# it begins with WITH, so no list of forbidden first words sees it$ sloop query shop --sql "with doomed as (delete from customers returning id)$     select count(*) from doomed"Reading: shop  projecterror: psql.exe failed against postgres://app@127.0.0.1:5446/shop:  ERROR:  cannot execute SELECT in a read-only transaction # the MySQL shape of the same trick, on MySQL 8.4.11$ sloop query mshop --sql "with doomed as (select id from customers)$     delete customers from customers join doomed on doomed.id = customers.id"error: mysql.exe failed against mysql://app@127.0.0.1:3312/shop:  ERROR 1792 (25006) at line 1: Cannot execute statement in a READ ONLY transaction.

Neither engine let it through. That is the whole argument for the approach: a filter has to imagine every shape a write can take, and a read-only transaction does not have to imagine anything. The engine knows what writes because the engine does the writing.

Where MySQL and MariaDB need one more thing

On PostgreSQL the guarantee is complete and it is entirely the server's. On the MySQL family it is not, and sloop is explicit about the difference rather than quietly papering over it.

A READ ONLY transaction on MySQL refuses every data change — including the hidden one above. It does not refuse DDL, because CREATE TABLE performs an implicit commit that ends the transaction before it runs. There is no session-level setting that closes that gap; read_only is a server-wide switch and belongs to the administrator. So sloop adds a fence of its own there, and says which of the two refused you:

sloop, not the server

$ sloop query mshop --sql "create table sneaky (id int)"Reading: mshop  projecterror: sloop only reads, and CREATE is not a statement that reads  hint: this engine commits a transaction before it runs a statement like that, so its  own read-only transaction cannot refuse it and sloop does. Statements that read:  SELECT, WITH, SHOW, EXPLAIN, DESCRIBE, DESC, TABLE, VALUES, ANALYZE

sloop only reads, and CREATE is not a statement that reads — sloop's words this time, and the hint says exactly why sloop had to be the one to say them. Two fences on those two engines, both needed, and the message tells you which one you hit.

What this is not

It is not a permission system. A read-only transaction stops the statement from writing; it does not stop the role from reading anything that role may read. If a database holds something a person should not see, that is a grant to get right, not a flag on this command.

The flags query takes are on the command reference, generated from --help.