sqlsmith

package module
v0.0.0-...-c8be989 Latest Latest
Warning

This package is not in the latest version of its module.

Go to latest
Published: Nov 4, 2026 License: Apache-2.0 Imports: 14 Imported by: 1

README

go-sqlsmith

Go version of SQLsmith.

Usage

import (
	sqlsmith_go "github.com/chaos-mesh/go-sqlsmith"
)

func gosmith() {
	ss := sqlsmith_go.New()

	// load schema
	ss.LoadSchema([][5]string{
		// members table
		[5]string{"games", "members", "BASE TABLE", "id", "int(11)"},
		[5]string{"games", "members", "BASE TABLE", "name", "varchar(255)"},
		[5]string{"games", "members", "BASE TABLE", "age", "int(11)"},
		[5]string{"games", "members", "BASE TABLE", "team_id", "int(11)"},
		// teams table
		[5]string{"games", "teams", "BASE TABLE", "id", "int(11)"},
		[5]string{"games", "teams", "BASE TABLE", "team_name", "varchar(255)"},
		[5]string{"games", "teams", "BASE TABLE", "created_at", "timestamp"},
	})

	// use games database
	ss.SetDB("games")

	// generate select statement AST without scema information
	node := ss.SelectStmt(5)

	// fill the tree with selected schema and get SQL string
	sql, err := ss.Walk(node)
}

CLI connection and test safety

Use a database account scoped to a disposable test schema. The binaries no longer need SUPER or change global sql_mode; the driver sets the existing generator mode (NO_ENGINE_SUBSTITUTION) on each physical session, including worker and reconnected sessions. Generated DDL and DML still run with the chosen account's privileges.

Both binaries accept the existing -host, -port, -user, -passwd, -socket and -db options. Prefer SQLSMITH_PASSWORD or -password-file to putting a password in process arguments. A password file may end with LF or CRLF; other characters are preserved. A nonempty -passwd takes precedence over the environment; -password-file takes precedence over the environment and cannot be combined with a nonempty -passwd. Connection logs omit the password.

Remote TCP connections require -tls, which verifies the server certificate and hostname. Use -tls-ca /path/to/ca.pem for a private CA, and optionally -tls-server-name db.example when the connection address differs from the certificate name. There is no certificate-verification bypass. Local loopback and Unix-socket connections continue to support plaintext. An isolated remote test network without TLS requires explicit -allow-insecure-transport. Passwordless disposable instances require explicit -allow-empty-password. The existing root user default remains for local test compatibility; use -user sqlsmith with a schema-scoped account for normal runs.

For example, an administrator can provision a dedicated schema and grant only its privileges to an existing test user:

CREATE DATABASE sqlsmith_test;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX
  ON sqlsmith_test.* TO 'sqlsmith'@'<runner-host>';

Run the fuzzer using a mounted password file:

make
./bin/sqlsmith -host db.example -user sqlsmith -db sqlsmith_test \
  -password-file /run/secrets/mysql-password -tls -tls-ca /path/to/ca.pem

The integration binary replaces its selected schema and refuses to connect unless -clear is supplied. It uses -db consistently instead of always dropping the database named test. make integration-test no longer selects the host MySQL socket implicitly; pass the intended disposable target:

make integration-test TEST_FLAGS='-host 127.0.0.1 -port 3306 -user sqlsmith -db sqlsmith_test -password-file /run/secrets/mysql-password -clear'

The same -clear opt-in remains available on bin/sqlsmith. Bootstrap statements and successful worker DDL have timestamped audit context without connection credentials.

Security regression tests

go test ./... covers SQL identifier and literal round trips, comment-safe optimizer hints, reservation boundaries, stable function generation and connection options. To include real-database tests (also under -race), supply a disposable schema already granted to the sqlsmith account:

SQLSMITH_TEST_DB=sqlsmith_test SQLSMITH_TEST_PORT=3306 \
SQLSMITH_TEST_PASSWORD_FILE=/run/secrets/mysql-password \
  go test -race ./...

SQLSMITH_PASSWORD can be used instead of SQLSMITH_TEST_PASSWORD_FILE. SQLSMITH_TEST_SOCKET selects a local socket. SQLSMITH_TEST_TLS_CA additionally enables the live TLS handshake tests over TCP: a trusted certificate succeeds, while an untrusted CA or wrong hostname fails. CI exercises MySQL 5.7 and 8.4 with a generated password and a schema-scoped user.

The library's public SQL generation signatures remain unchanged. Identifiers are quoted rather than restricted to ASCII: reserved words, spaces, Unicode and embedded backticks remain usable. Optional optimizer hints are omitted when a referenced identifier contains a comment terminator. Use one mutable SQLSmith instance per goroutine, or synchronize external access, including access to its exported schema maps. The CLI synchronizes its shared generator while allowing database executions to proceed concurrently.

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

This section is empty.

Types

type DDLOptions

type DDLOptions struct {
	// if OnlineDDL is set to false
	// DDL generation will look up tables other generators which are doing DMLs with the tables
	// the DDL generated will avoid modifing these tables
	// if OnlineDDL set to true
	// DDL generation will not avoid online tables
	OnlineDDL bool
	// if OnlineDDL is set to false
	// Tables contains all online tables which should not be modified with DDL
	// pocket will collect them from other generator instances
	Tables []string
}

DDLOptions for DDL generation

type DMLOptions

type DMLOptions struct {
	// if OnlineTable is set to true
	// generator will rand some tables from the target database
	// and the following DML statements before transaction closed
	// should only affects these tables
	// else there will be no limit for affected tables
	// if you OnlineDDL field in DDLOptions is true, you may got the following error from TiDB
	// "ERROR 1105 (HY000): Information schema is changed. [try again later]"
	OnlineTable bool
}

DMLOptions for DML generation

type DataGenerator

type DataGenerator struct {
	// contains filtered or unexported fields
}

DataGenerator defines data generator

func (*DataGenerator) Next

func (d *DataGenerator) Next() []string

Next returns data batch

type SQLSmith

type SQLSmith struct {
	Rand      *rand.Rand
	Databases map[string]*types.Database

	Node ast.Node
	// contains filtered or unexported fields
}

SQLSmith is a mutable SQL generator. Use one instance per goroutine or synchronize access, including access to its exported schema maps.

func New

func New() *SQLSmith

New create SQLSmith instance

func (*SQLSmith) AlterTableStmt

func (s *SQLSmith) AlterTableStmt(opt *DDLOptions) (string, error)

AlterTableStmt alter table

func (*SQLSmith) BatchData

func (s *SQLSmith) BatchData(total, batchSize int) ([]string, error)

BatchData generate testing data by schema in given batch return SQLs with insert statement

func (*SQLSmith) BeginWithOnlineTables

func (s *SQLSmith) BeginWithOnlineTables(opt *DMLOptions) []string

BeginWithOnlineTables begins a transaction with some online tables

func (*SQLSmith) CreateIndexStmt

func (s *SQLSmith) CreateIndexStmt(opt *DDLOptions) (string, error)

CreateIndexStmt create index

func (*SQLSmith) CreateTableStmt

func (s *SQLSmith) CreateTableStmt() (string, string, error)

CreateTableStmt create table

func (*SQLSmith) Debug

func (s *SQLSmith) Debug()

Debug turn on debug mode

func (*SQLSmith) DeleteStmt

func (s *SQLSmith) DeleteStmt() (string, string, error)

DeleteStmt implement delete statement from AST

func (*SQLSmith) EndTransaction

func (s *SQLSmith) EndTransaction() []string

EndTransaction ends transaction and set every table offline

func (*SQLSmith) GenData

func (s *SQLSmith) GenData(total, batch int) (*DataGenerator, error)

GenData returns data generator

func (*SQLSmith) GetCurrDBName

func (s *SQLSmith) GetCurrDBName() string

GetCurrDBName returns current selected dbname

func (*SQLSmith) GetDB

func (s *SQLSmith) GetDB(db string) *types.Database

GetDB get current database without nil

func (*SQLSmith) Hint

func (s *SQLSmith) Hint() bool

Hint ...

func (*SQLSmith) InsertStmt

func (s *SQLSmith) InsertStmt(fn bool) (string, string, error)

InsertStmt implement insert statement from AST

func (*SQLSmith) InsertStmtStr

func (s *SQLSmith) InsertStmtStr(fn bool) (string, string, error)

InsertStmtStr make random insert statement SQL

func (*SQLSmith) LoadSchema

func (s *SQLSmith) LoadSchema(records [][5]string, indexes map[string][]string)

LoadSchema init schemas, tables and columns record[0] dbname record[1] table name record[2] table type record[3] column name record[4] column type

func (*SQLSmith) SelectForUpdateStmt

func (s *SQLSmith) SelectForUpdateStmt(depth int) (string, string, error)

SelectForUpdateStmt make random select statement SQL with for update lock

func (*SQLSmith) SelectStmt

func (s *SQLSmith) SelectStmt(depth int) (string, string, error)

SelectStmt make random select statement SQL

func (*SQLSmith) SetDB

func (s *SQLSmith) SetDB(db string)

SetDB set current database

func (*SQLSmith) SetHint

func (s *SQLSmith) SetHint(hint bool)

SetHint ...

func (*SQLSmith) SetStable

func (s *SQLSmith) SetStable(stable bool)

SetStable set stable to given value

func (*SQLSmith) Stable

func (s *SQLSmith) Stable()

Stable set generated SQLs no rand

func (*SQLSmith) UpdateStmt

func (s *SQLSmith) UpdateStmt() (string, string, error)

UpdateStmt make random update statement SQL

func (*SQLSmith) Walk

func (s *SQLSmith) Walk(tree ast.Node) (string, string, error)

Walk will walk the tree and fillin tables and columns data

Directories

Path Synopsis
internal
dbutil
Package dbutil shares connection safety between the CLI tools.
Package dbutil shares connection safety between the CLI tools.

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL