Run SQL Queries Safely with Mono's sqlsharp Client

You will finish with a small, repeatable workflow for using the installed sqlsharp command: check its version, set a Mono data provider, open a connection, run a read-only query, and capture results when needed. The examples use SQLite so they can stay local and avoid changing a server database.

Allow about fifteen minutes. You need a shell and the mono-devel package. This guide targets the copy installed here as mono-devel 6.8.0.105+dfsg-3.6ubuntu2, with the command at /usr/bin/sqlsharp. Provider assemblies and connection-string details vary between Mono installations, so treat the local help and provider list as the authority for your host.

1. Confirm the installed client

Check which executable will run and record the package version:

$ command -v sqlsharp
/usr/bin/sqlsharp
$ dpkg-query -W -f='${Package} ${Version}\n' mono-devel
mono-devel 6.8.0.105+dfsg-3.6ubuntu2

The manpage documents three command-line switches: -f filename reads SQL# commands from a file, -o filename sends command results to a file, and -s selects silent mode. These switches are not SQL statements. Once the client is running, commands begin with a backslash, such as \h for help and \q to quit.

Checkpoint: command -v sqlsharp returns the binary you intend to use, and the package query returns a version rather than an empty result.

2. Start a session and inspect the available providers

Start the client without sudo:

$ sqlsharp
Welcome to SQL#. The interactive SQL command-line client
SQL#

The banner and prompt may wrap differently on your terminal. At the prompt, ask for the providers registered with this installation:

SQL# \ListProviders

Short commands are available too: \listp lists providers, \p NAME sets one, and \h displays command help. The manpage lists names such as sqlite, sqlclient, odbc, mysql, postgresql, oracle, sybase and firebird. The installed client may report a different usable set, so do not assume that an assembly is present merely because a provider appears in old documentation.

Checkpoint: identify the exact provider name printed by \ListProviders. If SQLite is absent, stop here or choose a provider you have deliberately installed and understand.

3. Set a local SQLite connection

At the SQL# prompt, select the SQLite provider and set a file connection string:

SQL# \provider sqlite
SQL# \connectionstring URI=file:/tmp/sqlsharp-example.db
SQL# \defaults

The SQLite example follows the manpage's documented URI=file:SqliteTest.db form, with an explicit temporary path. If the database file does not exist, the provider may create it when the connection opens. That is a state change, although it is confined to /tmp/sqlsharp-example.db. Remove that file only after checking that it is the test database and that no other process needs it:

$ test ! -e /tmp/sqlsharp-example.db || ls -l /tmp/sqlsharp-example.db

The connection string is not a password vault. Do not put a real password into a shell history, a shared batch file or an output log. For a server provider, prefer \bcs when supported: it prompts for connection parameters and does not echo the password. The resulting string is still sensitive and should not be saved casually.

4. Open the connection and check the prompt

Open the configured connection:

SQL# \open
SQL#

A successful open is not the same as a successful query. If the provider is missing, the connection string is malformed, or the file cannot be opened, read the error before changing anything. Check the current settings with \defaults, correct the provider or connection string, then retry \open. Do not add sudo as a first response: it can hide an ownership mistake without fixing the provider configuration.

To close the connection without leaving the client, use:

SQL# \close

Checkpoint: \defaults shows the provider and connection string you intended, and \open returns to the SQL# prompt without a provider or connection error.

5. Run a read-only query

SQL# collects a query in its buffer. A semicolon at the end executes it immediately; otherwise enter \e on the next line:

SQL# SELECT name FROM sqlite_master WHERE type = 'table';

On a new SQLite database, an empty result is expected because no tables have been created. That is a useful safe test: it proves the query reached the database without inventing application data. If you need to inspect the buffer before executing, leave off the semicolon and use \print:

SQL# SELECT name FROM sqlite_master WHERE type = 'table'
SQL# \print
SQL# \e

Use \r to clear an accidental or stale buffer before entering a new statement. This matters in an interactive session because a query left in the buffer can be combined with the next input in a way that is easy to miss.

Do not use \exenonquery for a first test. It is intended for non-SELECT SQL, such as an insert, update or delete. Those statements can change data and need a deliberate backup, transaction or rollback plan appropriate to the provider. \exescalar is for a single value, while \exexml filename writes query output to an XML file and depends on the provider's data adapter support.

6. Use files without losing track of execution

For repeatable work, \f filename reads a batch of SQL# commands and interprets them as it reads them. Any SQL statements in that file are executed. That is a serviceable batch mechanism, but it is not a dry run. Review the file before invoking it:

$ sed -n '1,120p' ./read-only.sql
\provider sqlite
\connectionstring URI=file:/tmp/sqlsharp-example.db
\open
SELECT name FROM sqlite_master WHERE type = 'table';
\q
$ sqlsharp -f ./read-only.sql

Keep credentials out of batch files. Use a file mode that limits access if a provider requires a secret, and remove the file through your normal secure handling process after checking its contents. The batch above only opens a temporary SQLite database and performs a catalogue query, but a copied file can become dangerous when its SQL is edited later.

To save SQL currently in the buffer, use \save filename. To load text into the buffer without immediately executing it, use \load filename. These are different from \f, which interprets the file as commands. Confusing them is a common way to execute more than you intended.

7. Capture output and leave cleanly

Use \o filename inside SQL# to write results from commands to a file:

SQL# \o /tmp/sqlsharp-results.txt
SQL# SELECT name FROM sqlite_master WHERE type = 'table';
SQL# \q
$ sed -n '1,80p' /tmp/sqlsharp-results.txt

The command-line -o filename switch is another way to direct output. Treat captured output as potentially sensitive: result files can contain customer data, query text or connection diagnostics. Check the destination before overwriting it, and remove the temporary file after verifying that it is no longer needed.

Quit with \q or \Q. In scripted or non-interactive use, provide an explicit quit command in the input. This old client can behave poorly when input reaches end of file during interactive help or shutdown, so a pipe that merely closes stdin is not a reliable substitute for \q.