SQLsmith: a random SQL generator that has found 118 database bugs

Csmith applied to SQL

SQLsmith is a random SQL query generator. Its paragon is Csmith, the random C program generator that proved valuable for quality assurance in C compilers, and the analogy is precise. Csmith produces syntactically valid C that a compiler has never seen, and the compiler bugs it found were found because the generator was able to produce shapes a human tester would not write. SQLsmith does the same for a SQL server: it reads the schema of the database under test, generates queries against it, and sends them. The generator is grammar-driven rather than template-driven, and the README notes that when you test against an RDBMS that does not support some of the grammar there will be a burst of syntax errors on startup, which disappear once the tool blacklists the productions that consistently lead to errors. That blacklist is a feedback loop: the tool learns what the target cannot parse.

What it has found

The track record is stated plainly: since 2015 it found 118 bugs in alphas, betas and releases in the supported products, including security vulnerabilities in released versions. Additional bugs were squashed in extensions and libraries such as orafce and glibc. The score list is kept on the project wiki. The claim that matters for a reader is not the count but the shape of the findings, and the README gives an example of what a finding looks like when the verbose output flags a cache lookup failure while querying a catalog view. The investigation shows a hash join where the planner puts a function call in a context where it sees non-index identifiers, and the README is careful to say this one is more of a curiosity than a bug. Even so, it illustrates the method: the generator found a combination of query shape and catalogue view that the developers had not tried.

Two classes to add a new database

It currently supports generating queries for PostgreSQL, SQLite 3 and MonetDB, and the extension point is unusually small. To add support for another RDBMS you need to implement two classes providing schema information about, and connectivity to, the device under test. Two classes, one for the schema and one for the connection, and everything else in the tool is database agnostic. The repository layout shows the same split: postgres.cc and postgres.hh, sqlite.cc and sqlite.hh, and monetdb.cc and monetdb.hh sit alongside the generator proper in expr.cc, grammar.cc, random.cc, prod.cc and schema.cc, with a dut.hh that stands for device under test. There is also an impedance.cc, which is where the mismatch between what a database calls a type and what the generator can build is handled.

The safety warning is not a formality

The usage section is where the tool warns about itself. SQLsmith connects to the target database to retrieve the schema for query generation and to send the generated queries to. Currently all generated statements are rolled back. Then comes the warning: beware that SQLsmith does call functions that could possibly have side effects, such as pg_terminate_backend, and you should use a suitably underprivileged user for its connection to avoid this. So the rollback protects your data, but not your server's state, and the distinction is the one to hold on to. A rolled-back statement can still have terminated a backend, written to a log, or consumed a sequence. This is also why the tool mentions that users developing extensions might be interested in exposing their code to SQLsmith's random workload, since the extension is usually the thing you want to hammer.

The command line and the three targets

The invocation has a target option per database and a set of common flags.

sqlsmith --verbose --target="host=/tmp port=65432 dbname=regression"
sqlsmith --verbose --sqlite="file:$HOME/.mozilla/firefox/places.sqlite?mode=ro"
sqlsmith --verbose --monetdb="mapi:monetdb://localhost:50000/smith"

The SQLite example is worth reading twice, because it points the tool at a real file in read-only mode, the places database of a Firefox profile, which is a good illustration of the use case: point the generator at an arbitrary schema and let it find the edges. The flags are mostly about controlling the run. The seed option sets the random number generator with a specified integer instead of the process ID, which is what makes a failing run reproducible. The dry-run option prints the queries instead of executing them. The max-queries option terminates after a number of queries. The exclude-catalog option stops the generator using catalogue relations, and the dump-all-queries and dump-all-graphs options dump the queries and the generated abstract syntax trees for debugging.

Reading the verbose symbols

The verbose flag makes the tool emit progress to stderr, one symbol per query sent to the server, and there are five. A dot means the query was generated and executed with an ok sqlstate. The letter S is a syntax error, and the README says plainly that these are bugs in SQLsmith and should be reported. A lowercase t is a timeout, and SQLsmith sets a statement timeout of one second. A capital C is a broken connection, which happens when a query crashes the server. A lowercase e is any other error. That last set of symbols is the operating contract of a fuzzing run: a syntax error is a generator defect, a crash is a server defect, and the timeout exists so that one pathological query does not stall the whole run.

Large-scale testing and the logging schema

The log-to option allows logging of hundreds of SQLsmith instances into a central PostgreSQL database, and the repository contains log.sql with the schema SQLsmith expects along with additional views to generate reports on the logged content. There is also a log-v1.0-to-v1.2.sql file, which is the migration for that schema between versions, a sign of a tool that has been used at scale long enough to have schema churn. The repository also holds known.txt and known_re.txt, which are presumably the blacklists of productions and regular expressions that the generator has learned to avoid, along with boring_sqlstates.txt for the states it does not care about. A Doxyfile.in sits at the root for documentation generation, and the Makefile.am and configure.ac files show it still uses the autotools build alongside a CMakeLists.txt.

Where SQLsmith is the wrong choice

Four limits apply. It is a fuzzing tool, so it finds crashes and syntax handling problems rather than semantic ones, and a database that parses everything correctly but computes the wrong answer will pass every run. It needs a live database with a real schema, which means it cannot be pointed at a design document, and the schema it reads is the schema as it happens to be at that moment, so a reproducible run needs a fixed schema as well as a fixed seed. The generator is grammar-driven and the grammar is SQL-shaped, so a database with unusual extensions will produce the startup burst of syntax errors the README warns about, and you will spend time on the impedance rather than on the findings. And the side effects are real, so the tool cannot safely run against production or against anything with data you care about, no matter how well you scope the account.

Editorial conclusion

Use SQLsmith when you are testing a database server or an extension against queries nobody thought to write, since it reads the live schema and generates grammar-valid SQL against it, which is a different thing from a fixed corpus of test cases. Do not point it at a database with real data or real privileges. Verify first that the account you connect with is underprivileged, because the tool calls functions with side effects, that you know which grammar features the target supports, since a burst of syntax errors on startup is expected while the tool blacklists the productions that consistently fail, and that you read the symbol legend before treating an error as a server bug, because a syntax error symbol is a bug in the generator rather than in the database.

Frequently asked questions

Official sources

1. anse1/sqlsmith on GitHub
2. Issues
3. License: GPL-3.0
4. README
5. Releases