Run SQL Safely with unixODBC isql and a DSN

Someone hands you a database name and a driver and asks you to "just test it," and isql does that without writing a line of application code. This guide walks through configuring one ODBC data source, testing it with isql, and running a small SQL batch without changing the database by accident. The examples target unixODBC 2.3.12, installed here as packages unixodbc and unixodbc-common. Allow about fifteen minutes, plus the time needed to obtain the correct driver name and database details from your database administrator.

This guide assumes that the ODBC driver itself is already installed. It does not install a driver, create a database, or grant permissions. Those details are driver- and database-specific.

1. Check the installed tools

Start with read-only checks. They need no elevated privileges:

$ command -v isql
/usr/bin/isql
$ isql --version
unixODBC 2.3.12
$ command -v iusql
/usr/bin/iusql
$ iusql --version
unixODBC 2.3.12

isql and iusql are aliases in the unixODBC toolset, but they are not identical clients. iusql includes built-in Unicode support and connects through SQLDriverConnect; isql normally uses SQLConnect. A Unicode-only data source can therefore work with iusql when isql does not, which is a useful fact to keep in your back pocket the day a connection mysteriously fails on one command but not the other.

Checkpoint: If neither command exists, stop here and install or repair the unixODBC package through your normal system process. Do not copy a binary from an unrelated host.

2. Choose the DSN configuration file

Use $HOME/.odbc.ini for a DSN used by one account. Use /etc/odbc.ini for a system-wide definition. The client searches both locations, with the user file taking precedence. Reading or writing the user file is an ordinary user action. Editing the system file normally needs elevated privileges.

Check the paths compiled into your installation before assuming the defaults:

$ odbcinst -j

If odbcinst is not installed, consult your distribution's unixODBC package. The odbc.ini manual says build options can change these paths, so a file in the expected location is not proof the client is using it.

Before editing a system file, warn other users and services that you are changing shared connection configuration. Make a backup first:

$ sudo cp --preserve=mode,ownership,timestamps /etc/odbc.ini /etc/odbc.ini.before-my-dsn

That is the first command in this guide requiring elevation. To undo the change later, replace the file only after checking the backup path and contents:

$ sudo cp --preserve=mode,ownership,timestamps /etc/odbc.ini.before-my-dsn /etc/odbc.ini

For a first test, prefer the user file so a syntax mistake cannot disrupt other accounts.

3. Add a minimal DSN

Open $HOME/.odbc.ini in your editor and add a section like this. Replace every value marked with a placeholder. Do not guess the driver name: it must exactly match a driver section in odbcinst.ini.

[ODBC Data Sources]
ExampleDB = Example database

[ExampleDB]
Driver = Example ODBC Driver
Description = Test connection to the example database
Database = example_database
Servername = db.example.invalid

The [ODBC Data Sources] section is mandatory. Its ExampleDB entry is a label and must have a matching [ExampleDB] section. The Driver key is required, and its value must match the driver definition section exactly. Database and Servername are common keys, but their meanings and any additional keys come from the installed driver.

Keep credentials out of this first example. If a driver requires credentials in the DSN, protect the file with permissions appropriate to that account and remember that a password in a command line can be exposed through shell history or process inspection. Do not paste a real password into a shared transcript.

Checkpoint: Inspect the file for spelling and section-name mismatches before connecting.

$ sed -n '1,120p' "$HOME/.odbc.ini"
$ ls -l "$HOME/.odbc.ini"

4. Test the DSN interactively

Use the DSN name as the first argument. Supply a database user and password only when your driver requires them and you have a safe way to enter them:

$ isql ExampleDB DB_USER 'REPLACE_WITH_PASSWORD'

A successful connection enters an interactive prompt. The exact prompt and result formatting depend on the driver. Run a harmless metadata command first:

SQL> help
SQL> help help

The built-in help command lists tables, while help table lists columns for a named table. Exit with the client command recognised by your driver or with the terminal interrupt if necessary.

Warning: do not begin with DROP, DELETE or an unbounded UPDATE. A connection test should not alter data.

If the DSN contains UID or PASSWORD, positional USER and PASSWORD arguments override those values. Treat that precedence as a common source of confusion when a test appears to use the wrong account.

5. Run a controlled batch

Batch mode reads SQL from standard input. In the ordinary -b mode, put one SQL command on each line and finish the file with a blank line. The query below is read-only, but replace the table name with one that exists in your database:

SELECT * FROM example_table;
$ printf '%s\n\n' 'SELECT * FROM example_table;' > /tmp/isql-check.sql
$ isql ExampleDB DB_USER 'REPLACE_WITH_PASSWORD' -b < /tmp/isql-check.sql

The output is driver-dependent. A successful run prints column headings and rows, followed by the client's normal completion status. If your SQL spans multiple lines, use -n and terminate the input with the GO command instead. Do not assume a semicolon alone changes the client's line handling.

For machine-readable text, choose a delimiter explicitly. -d takes the delimiter character; -x 0x09 uses a tab. Add -c to print column names as the first row. These options belong together: -c is documented for use with -d or -x.

$ isql ExampleDB DB_USER 'REPLACE_WITH_PASSWORD' -b -x 0x09 -c < /tmp/isql-check.sql

Do not use -w for a data interchange format without checking the consumer. It wraps results in an HTML table, which is useful for a quick report but is not CSV or tab-separated output.

6. Use a connection string when the DSN is not enough

A leading semicolon tells iusql to treat the argument as a connection string rather than a bare DSN lookup. This lets you override DSN settings or provide a driver directly:

$ iusql ";DSN=ExampleDB;UID=DB_USER;PWD=REPLACE_WITH_PASSWORD" -v

A complete string can omit the DSN and name the driver itself:

$ iusql ";Driver=Example ODBC Driver;UID=DB_USER;PWD=REPLACE_WITH_PASSWORD" -v

Security boundary: connection-string syntax is security-sensitive. Keep the command private, quote it correctly, and do not place a real secret in a script that others can read. For a password containing a semicolon, the iusql syntax uses braces and a terminating semicolon, for example PWD={value;with;semicolons};.

7. Diagnose a failed connection

Re-run the failing command with -v for fuller ODBC diagnostics. This is read-only, although the driver may log details elsewhere:

$ isql ExampleDB DB_USER 'REPLACE_WITH_PASSWORD' -v

Trace mode in odbcinst.ini can provide more detail, but enable it only for a short diagnostic window. Traces may contain connection details. Disable the setting and remove sensitive trace files after the test according to your local policy.

Done means