From d2186ed58d907a81648aa62ffa17ec20fb2d213e Mon Sep 17 00:00:00 2001 From: Eugene Triguba Date: Sun, 18 Aug 2024 14:51:00 -0400 Subject: GH-4: Add configurable migrations table name (#6) * Add configurable migrations table name * Update codecov to be informational * Add mockdb and couple of extra tests * remove extra files * Add list query error test * Add readme update for custom migration table --- internal/bolttest/db.go | 51 +++++++++-- internal/commands/down.go | 2 +- internal/commands/status.go | 2 +- internal/commands/up.go | 2 +- internal/configloader/configloader.go | 16 ++-- internal/configloader/configloader_test.go | 46 ++++++---- internal/repositories/migration_db_repo.go | 73 +++++++++------ internal/repositories/migration_db_repo_test.go | 112 ++++++++++++++++++++---- internal/storage/db.go | 43 +++++---- 9 files changed, 255 insertions(+), 92 deletions(-) (limited to 'internal') diff --git a/internal/bolttest/db.go b/internal/bolttest/db.go index d03012f..2d21762 100644 --- a/internal/bolttest/db.go +++ b/internal/bolttest/db.go @@ -1,6 +1,7 @@ package bolttest import ( + "database/sql" "fmt" "os" "testing" @@ -15,10 +16,10 @@ func NewTestDB(t *testing.T) storage.DB { db, err := storage.NewDB(connectionConfig) assert.Nil(t, err) - DropTable(t, db, "bolt_migrations") + DropTable(t, db, connectionConfig.MigrationsTable) DropTable(t, db, "tmp") t.Cleanup(func() { - DropTable(t, db, "bolt_migrations") + DropTable(t, db, connectionConfig.MigrationsTable) DropTable(t, db, "tmp") assert.Nil(t, db.Close()) }) @@ -28,12 +29,13 @@ func NewTestDB(t *testing.T) storage.DB { func NewTestConnectionConfig() configloader.ConnectionConfig { return configloader.ConnectionConfig{ - Driver: os.Getenv("BOLT_DB_DRIVER"), - DBName: os.Getenv("BOLT_DB_NAME"), - Host: os.Getenv("BOLT_DB_HOST"), - Port: os.Getenv("BOLT_DB_PORT"), - User: os.Getenv("BOLT_DB_USER"), - Password: os.Getenv("BOLT_DB_PASSWORD"), + Driver: os.Getenv("BOLT_DB_DRIVER"), + DBName: os.Getenv("BOLT_DB_NAME"), + Host: os.Getenv("BOLT_DB_HOST"), + Port: os.Getenv("BOLT_DB_PORT"), + User: os.Getenv("BOLT_DB_USER"), + Password: os.Getenv("BOLT_DB_PASSWORD"), + MigrationsTable: os.Getenv("BOLT_DB_MIGRATIONS_TABLE"), } } @@ -41,3 +43,36 @@ func DropTable(t *testing.T, db storage.DB, tableName string) { _, err := db.Exec(fmt.Sprintf("DROP TABLE IF EXISTS %s;", tableName)) assert.Nil(t, err) } + +type MockDB struct { + ExecFunc func(query string, args ...interface{}) (sql.Result, error) + QueryFunc func(query string, args ...interface{}) (*sql.Rows, error) + QueryRowFunc func(query string, args ...interface{}) *sql.Row + TxFunc func(fn storage.TxFunc) error + CloseFunc func() error + TableExistsFunc func(tableName string) (bool, error) +} + +func (m *MockDB) Exec(query string, args ...interface{}) (sql.Result, error) { + return m.ExecFunc(query, args...) +} + +func (m *MockDB) Query(query string, args ...interface{}) (*sql.Rows, error) { + return m.QueryFunc(query, args...) +} + +func (m *MockDB) QueryRow(query string, args ...interface{}) *sql.Row { + return m.QueryRowFunc(query, args...) +} + +func (m *MockDB) Tx(fn storage.TxFunc) error { + return m.TxFunc(fn) +} + +func (m *MockDB) Close() error { + return m.CloseFunc() +} + +func (m *MockDB) TableExists(tableName string) (bool, error) { + return m.TableExistsFunc(tableName) +} diff --git a/internal/commands/down.go b/internal/commands/down.go index f991c69..b537b17 100644 --- a/internal/commands/down.go +++ b/internal/commands/down.go @@ -61,7 +61,7 @@ func (cmd *DownCmd) Execute( } defer db.Close() - migrationDBRepo, err := repositories.NewMigrationDBRepo(db) + migrationDBRepo, err := repositories.NewMigrationDBRepo(cfg.Connection.MigrationsTable, db) if err != nil { consoleOutputter.Error(err) return subcommands.ExitFailure diff --git a/internal/commands/status.go b/internal/commands/status.go index 1e176b9..6c305fe 100644 --- a/internal/commands/status.go +++ b/internal/commands/status.go @@ -51,7 +51,7 @@ func (m *StatusCmd) Execute( } defer db.Close() - migrationDBRepo, err := repositories.NewMigrationDBRepo(db) + migrationDBRepo, err := repositories.NewMigrationDBRepo(cfg.Connection.MigrationsTable, db) if err != nil { consoleOutputter.Error(err) return subcommands.ExitFailure diff --git a/internal/commands/up.go b/internal/commands/up.go index 77e0b8d..c2cd3ef 100644 --- a/internal/commands/up.go +++ b/internal/commands/up.go @@ -61,7 +61,7 @@ func (cmd *UpCmd) Execute( } defer db.Close() - migrationDBRepo, err := repositories.NewMigrationDBRepo(db) + migrationDBRepo, err := repositories.NewMigrationDBRepo(cfg.Connection.MigrationsTable, db) if err != nil { consoleOutputter.Error(err) return subcommands.ExitFailure diff --git a/internal/configloader/configloader.go b/internal/configloader/configloader.go index c13977a..0f724a6 100644 --- a/internal/configloader/configloader.go +++ b/internal/configloader/configloader.go @@ -54,12 +54,13 @@ type MigrationsConfig struct { } type ConnectionConfig struct { - Host string `toml:"host" envconfig:"BOLT_DB_HOST"` - Port string `toml:"port" envconfig:"BOLT_DB_PORT"` - User string `toml:"user" envconfig:"BOLT_DB_USER"` - Password string `toml:"password" envconfig:"BOLT_DB_PASSWORD"` - DBName string `toml:"dbname" envconfig:"BOLT_DB_NAME"` - Driver string `toml:"driver" envconfig:"BOLT_DB_DRIVER"` + Host string `toml:"host" envconfig:"BOLT_DB_HOST"` + Port string `toml:"port" envconfig:"BOLT_DB_PORT"` + User string `toml:"user" envconfig:"BOLT_DB_USER"` + Password string `toml:"password" envconfig:"BOLT_DB_PASSWORD"` + DBName string `toml:"dbname" envconfig:"BOLT_DB_NAME"` + Driver string `toml:"driver" envconfig:"BOLT_DB_DRIVER"` + MigrationsTable string `toml:"migrations_table" envconfig:"BOLT_DB_MIGRATIONS_TABLE"` } func NewConfig() (*Config, error) { @@ -73,6 +74,9 @@ func NewConfig() (*Config, error) { DirectoryPath: "migrations", VersionStyle: VersionStyleTimestamp, }, + Connection: ConnectionConfig{ + MigrationsTable: "bolt_migrations", + }, } if !errors.Is(err, ErrConfigFileNotFound) { _, err = toml.DecodeFile(filePath, &cfg) diff --git a/internal/configloader/configloader_test.go b/internal/configloader/configloader_test.go index 89c4368..352f243 100644 --- a/internal/configloader/configloader_test.go +++ b/internal/configloader/configloader_test.go @@ -19,6 +19,7 @@ func TestNewConfigDefaults(t *testing.T) { check.Equal(t, cfg.Migrations.DirectoryPath, "migrations") check.Equal(t, cfg.Migrations.VersionStyle, configloader.VersionStyleTimestamp) + check.Equal(t, cfg.Connection.MigrationsTable, "bolt_migrations") } func TestNewConfigWithInvalidVersionStyle(t *testing.T) { @@ -41,6 +42,7 @@ func TestNewConfigFindsFileAndPopulatesConfigStruct(t *testing.T) { bolttest.UnsetEnv(t, "BOLT_DB_PASSWORD") bolttest.UnsetEnv(t, "BOLT_DB_NAME") bolttest.UnsetEnv(t, "BOLT_DB_DRIVER") + bolttest.UnsetEnv(t, "BOLT_DB_MIGRATIONS_TABLE") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_DIR_PATH") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_VERSION_STYLE") expectedCfg := configloader.Config{ @@ -49,12 +51,13 @@ func TestNewConfigFindsFileAndPopulatesConfigStruct(t *testing.T) { VersionStyle: configloader.VersionStyleSequential, }, Connection: configloader.ConnectionConfig{ - Host: "testhost", - Port: "1234", - User: "testuser", - Password: "testpassword", - DBName: "testdb", - Driver: "postgresql", + Host: "testhost", + Port: "1234", + User: "testuser", + Password: "testpassword", + DBName: "testdb", + Driver: "postgresql", + MigrationsTable: "test_table", }, } tmpdir := t.TempDir() @@ -74,12 +77,13 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { VersionStyle: configloader.VersionStyleSequential, }, Connection: configloader.ConnectionConfig{ - Host: "testhost", - Port: "1234", - User: "testuser", - Password: "testpassword", - DBName: "testdb", - Driver: "mysql", + Host: "testhost", + Port: "1234", + User: "testuser", + Password: "testpassword", + DBName: "testdb", + Driver: "mysql", + MigrationsTable: "test_table", }, } bolttest.CreateConfigFile(t, &fileCfg, "bolt.toml") @@ -90,12 +94,13 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { VersionStyle: configloader.VersionStyleTimestamp, }, Connection: configloader.ConnectionConfig{ - Host: "envtesthost", - Port: "4321", - User: "envtestuser", - Password: "envtestpassword", - DBName: "envtestdb", - Driver: "postgresql", + Host: "envtesthost", + Port: "4321", + User: "envtestuser", + Password: "envtestpassword", + DBName: "envtestdb", + Driver: "postgresql", + MigrationsTable: "different_table", }, } t.Setenv("BOLT_MIGRATIONS_VERSION_STYLE", string(envCfg.Migrations.VersionStyle)) @@ -106,6 +111,7 @@ func TestNewConfigCanBeOverridenByEnvVars(t *testing.T) { t.Setenv("BOLT_DB_PASSWORD", envCfg.Connection.Password) t.Setenv("BOLT_DB_NAME", envCfg.Connection.DBName) t.Setenv("BOLT_DB_DRIVER", envCfg.Connection.Driver) + t.Setenv("BOLT_DB_MIGRATIONS_TABLE", envCfg.Connection.MigrationsTable) cfg, err := configloader.NewConfig() assert.Nil(t, err) @@ -119,6 +125,7 @@ func TestNewConfigSearchesParentDirectories(t *testing.T) { bolttest.UnsetEnv(t, "BOLT_DB_PASSWORD") bolttest.UnsetEnv(t, "BOLT_DB_NAME") bolttest.UnsetEnv(t, "BOLT_DB_DRIVER") + bolttest.UnsetEnv(t, "BOLT_DB_MIGRATIONS_TABLE") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_DIR_PATH") bolttest.UnsetEnv(t, "BOLT_MIGRATIONS_VERSION_STYLE") expectedCfg := configloader.Config{ @@ -126,6 +133,9 @@ func TestNewConfigSearchesParentDirectories(t *testing.T) { DirectoryPath: "differentmigrationsdir", VersionStyle: configloader.VersionStyleSequential, }, + Connection: configloader.ConnectionConfig{ + MigrationsTable: "migration_table", + }, } tmpdir := t.TempDir() bolttest.CreateConfigFile(t, &expectedCfg, filepath.Join(tmpdir, "bolt.toml")) diff --git a/internal/repositories/migration_db_repo.go b/internal/repositories/migration_db_repo.go index 26cc2b3..9e069f4 100644 --- a/internal/repositories/migration_db_repo.go +++ b/internal/repositories/migration_db_repo.go @@ -4,6 +4,7 @@ import ( "database/sql" "errors" "fmt" + "regexp" "strings" "github.com/eugenetriguba/bolt/internal/models" @@ -20,37 +21,59 @@ type MigrationDBRepo interface { } type migrationDBRepo struct { - db storage.DB + migrationTableName string + db storage.DB } // NewMigrationDBRepo initializes the MigrationDBRepo with a // database. Furthermore, it ensures the migration table it // operates on exists. If it is unable to create or confirm // the table exists, an error is returned. -func NewMigrationDBRepo(db storage.DB) (MigrationDBRepo, error) { - migrationTableExists, err := db.TableExists("bolt_migrations") +func NewMigrationDBRepo(migrationTableName string, db storage.DB) (MigrationDBRepo, error) { + err := sanitizeTableName(migrationTableName) if err != nil { return nil, fmt.Errorf( - "unable to confirm bolt_migrations database table exists: %w", + "invalid migration table name: %w", + err, + ) + } + + migrationTableExists, err := db.TableExists(migrationTableName) + if err != nil { + return nil, fmt.Errorf( + "unable to confirm '%s' database table exists: %w", + migrationTableName, err, ) } if !migrationTableExists { - _, err := db.Exec(` - CREATE TABLE bolt_migrations( + _, err := db.Exec(fmt.Sprintf(` + CREATE TABLE %s ( version VARCHAR(255) PRIMARY KEY NOT NULL ); - `) + `, migrationTableName)) if err != nil { return nil, fmt.Errorf( - "unable to create bolt_migrations database table: %w", + "unable to create '%s' database table: %w", + migrationTableName, err, ) } } - return &migrationDBRepo{db: db}, nil + return &migrationDBRepo{migrationTableName: migrationTableName, db: db}, nil +} + +func sanitizeTableName(tableName string) error { + // Allow alphanumeric characters, underscores, and a single dot for schema.table + validTableName := regexp.MustCompile(`^[a-zA-Z0-9_]+(\.[a-zA-Z0-9_]+)?$`) + if !validTableName.MatchString(tableName) { + return errors.New( + "a migration table name must only contain alphanumeric or underscore characters " + + "and optionally a single dot for schema-qualified names") + } + return nil } // List retrieves a map of migration models that can be @@ -58,11 +81,12 @@ func NewMigrationDBRepo(db storage.DB) (MigrationDBRepo, error) { // will be ones that have been applied, and their message // will always be an empty string. func (mr migrationDBRepo) List() (map[string]*models.Migration, error) { - rows, err := mr.db.Query("SELECT version FROM bolt_migrations;") + rows, err := mr.db.Query(fmt.Sprintf("SELECT version FROM %s;", mr.migrationTableName)) if err != nil { return nil, fmt.Errorf( "unable to execute query to select versions from "+ - "bolt_migrations database table: %w", + "'%s' database table: %w", + mr.migrationTableName, err, ) } @@ -99,7 +123,7 @@ func (mr migrationDBRepo) List() (map[string]*models.Migration, error) { // Check err first before looking at whether the version is applied. func (mr migrationDBRepo) IsApplied(version string) (bool, error) { var scanResult int - err := mr.db.QueryRow("SELECT 1 FROM bolt_migrations WHERE version = ?", version). + err := mr.db.QueryRow(fmt.Sprintf("SELECT 1 FROM %s WHERE version = ?", mr.migrationTableName), version). Scan(&scanResult) if errors.Is(err, sql.ErrNoRows) { return false, nil @@ -122,7 +146,7 @@ func (mr migrationDBRepo) Apply( upgradeScript string, migration *models.Migration, ) error { - err := applyMigration(mr.db, upgradeScript, *migration) + err := mr.applyMigration(upgradeScript, *migration) if err != nil { return err } @@ -138,7 +162,7 @@ func (mr migrationDBRepo) ApplyWithTx( migration *models.Migration, ) error { err := mr.db.Tx(func(db storage.DB) error { - err := applyMigration(db, upgradeScript, *migration) + err := mr.applyMigration(upgradeScript, *migration) if err != nil { return err } @@ -152,17 +176,16 @@ func (mr migrationDBRepo) ApplyWithTx( return nil } -func applyMigration( - db storage.DB, +func (mr migrationDBRepo) applyMigration( upgradeScript string, migration models.Migration, ) error { - _, err := db.Exec(upgradeScript) + _, err := mr.db.Exec(upgradeScript) if err != nil { return fmt.Errorf("unable to execute upgrade script: %w", err) } - _, err = db.Exec("INSERT INTO bolt_migrations(version) VALUES(?)", migration.Version) + _, err = mr.db.Exec(fmt.Sprintf("INSERT INTO %s(version) VALUES(?)", mr.migrationTableName), migration.Version) if err != nil { return fmt.Errorf( "unable to insert migration: %w", @@ -180,7 +203,7 @@ func (mr migrationDBRepo) Revert( downgradeScript string, migration *models.Migration, ) error { - err := revertMigration(mr.db, downgradeScript, *migration) + err := mr.revertMigration(downgradeScript, *migration) if err != nil { return err } @@ -196,7 +219,7 @@ func (mr migrationDBRepo) RevertWithTx( migration *models.Migration, ) error { err := mr.db.Tx(func(db storage.DB) error { - err := revertMigration(db, downgradeScript, *migration) + err := mr.revertMigration(downgradeScript, *migration) if err != nil { return err } @@ -210,20 +233,20 @@ func (mr migrationDBRepo) RevertWithTx( return nil } -func revertMigration( - db storage.DB, +func (mr migrationDBRepo) revertMigration( downgradeScript string, migration models.Migration, ) error { - _, err := db.Exec(downgradeScript) + _, err := mr.db.Exec(downgradeScript) if err != nil { return fmt.Errorf("unable to execute downgrade script: %w", err) } - _, err = db.Exec("DELETE FROM bolt_migrations WHERE version = ?", migration.Version) + _, err = mr.db.Exec(fmt.Sprintf("DELETE FROM %s WHERE version = ?", mr.migrationTableName), migration.Version) if err != nil { return fmt.Errorf( - "unable to remove reverted migration from bolt_migrations table: %w", + "unable to remove reverted migration from %s table: %w", + mr.migrationTableName, err, ) } diff --git a/internal/repositories/migration_db_repo_test.go b/internal/repositories/migration_db_repo_test.go index 37efe37..1163b9c 100644 --- a/internal/repositories/migration_db_repo_test.go +++ b/internal/repositories/migration_db_repo_test.go @@ -1,6 +1,8 @@ package repositories_test import ( + "database/sql" + "errors" "testing" "time" @@ -16,7 +18,7 @@ func TestNewMigrationDBRepo_CreatesTable(t *testing.T) { assert.Nil(t, err) assert.False(t, exists) - _, err = repositories.NewMigrationDBRepo(testdb) + _, err = repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) exists, err = testdb.TableExists("bolt_migrations") @@ -24,6 +26,31 @@ func TestNewMigrationDBRepo_CreatesTable(t *testing.T) { assert.True(t, exists) } +func TestNewMigrationDBRepo_CreateTableExecError(t *testing.T) { + mockDB := &bolttest.MockDB{ + TableExistsFunc: func(tableName string) (bool, error) { + return false, nil + }, + ExecFunc: func(query string, args ...interface{}) (sql.Result, error) { + return nil, errors.New("exec error") + }, + } + _, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) + assert.NotNil(t, err) + assert.ErrorContains(t, err, "unable to create 'bolt_migrations' database table: exec error") +} + +func TestNewMigrationDBRepo_TableExistsError(t *testing.T) { + mockDB := &bolttest.MockDB{ + TableExistsFunc: func(tableName string) (bool, error) { + return false, errors.New("table exists failed") + }, + } + _, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) + assert.NotNil(t, err) + assert.ErrorContains(t, err, "unable to confirm 'bolt_migrations' database table exists: table exists failed") +} + func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { testdb := bolttest.NewTestDB(t) _, err := testdb.Exec(`CREATE TABLE bolt_migrations(id INT NOT NULL PRIMARY KEY)`) @@ -31,7 +58,7 @@ func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { _, err = testdb.Exec(`INSERT INTO bolt_migrations(id) VALUES (1);`) assert.Nil(t, err) - _, err = repositories.NewMigrationDBRepo(testdb) + _, err = repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) var count int @@ -46,7 +73,7 @@ func TestNewMigrationDBRepo_TableAlreadyExists(t *testing.T) { func TestList_EmptyTable(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migrations, err := repo.List() @@ -54,9 +81,26 @@ func TestList_EmptyTable(t *testing.T) { assert.Equal(t, len(migrations), 0) } +func TestList_QueryError(t *testing.T) { + mockDB := &bolttest.MockDB{ + TableExistsFunc: func(tableName string) (bool, error) { + return true, nil + }, + QueryFunc: func(query string, args ...interface{}) (*sql.Rows, error) { + return nil, errors.New("query error") + }, + } + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", mockDB) + assert.Nil(t, err) + + _, err = repo.List() + assert.NotNil(t, err) + assert.ErrorContains(t, err, "unable to execute query to select versions from 'bolt_migrations' database table: query error") +} + func TestList_SingleResult(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101000000" @@ -75,7 +119,7 @@ func TestList_SingleResult(t *testing.T) { func TestList_ShortVersion(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101" @@ -94,7 +138,7 @@ func TestList_ShortVersion(t *testing.T) { func TestIsApplied_WithNotApplied(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101010101" @@ -105,7 +149,7 @@ func TestIsApplied_WithNotApplied(t *testing.T) { func TestIsApplied_WithApplied(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) version := "20230101010101" @@ -117,9 +161,24 @@ func TestIsApplied_WithApplied(t *testing.T) { assert.Equal(t, applied, true) } +func TestIsApplied_SQLInjectionAttempt(t *testing.T) { + db := bolttest.NewTestDB(t) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) + assert.Nil(t, err) + + version := "20230101010101'; DROP TABLE bolt_migrations; --" + applied, err := repo.IsApplied(version) + assert.Nil(t, err) + assert.Equal(t, applied, false) + + exists, err := db.TableExists("bolt_migrations") + assert.Nil(t, err) + assert.True(t, exists) +} + func TestApply(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -137,7 +196,7 @@ func TestApply(t *testing.T) { func TestApply_MalformedSql(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -149,7 +208,7 @@ func TestApply_MalformedSql(t *testing.T) { func TestApplyWithTx_ExecErr(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -160,7 +219,7 @@ func TestApplyWithTx_ExecErr(t *testing.T) { func TestApplyWithTx_SuccessfullyApplied(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -178,7 +237,7 @@ func TestApplyWithTx_SuccessfullyApplied(t *testing.T) { func TestRevert(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) @@ -207,7 +266,7 @@ func TestRevert(t *testing.T) { func TestRevert_MalformedSql(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") migration.Applied = true @@ -219,7 +278,7 @@ func TestRevert_MalformedSql(t *testing.T) { func TestRevertWithTx_ExecErr(t *testing.T) { db := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(db) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", db) assert.Nil(t, err) migration := models.NewTimestampMigration(time.Now(), "test") @@ -230,7 +289,7 @@ func TestRevertWithTx_ExecErr(t *testing.T) { func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { testdb := bolttest.NewTestDB(t) - repo, err := repositories.NewMigrationDBRepo(testdb) + repo, err := repositories.NewMigrationDBRepo("bolt_migrations", testdb) assert.Nil(t, err) _, err = testdb.Exec(`CREATE TABLE tmp(id INT NOT NULL PRIMARY KEY)`) @@ -256,3 +315,26 @@ func TestRevertWithTx_SuccessfullyReverted(t *testing.T) { assert.Nil(t, err) assert.Equal(t, count, 0) } + +func TestNewMigrationDBRepo_InvalidTableName(t *testing.T) { + db := bolttest.NewTestDB(t) + invalidTableNames := []string{ + "bolt_migrations; DROP TABLE users; --", // SQL injection attempt + "bolt_migrations`", // Backtick is not allowed + "bolt_migrations'", // Single quote is not allowed + "bolt_migrations\"", // Double quote is not allowed + "bolt_migrations;", // Semicolon could terminate the SQL statement + "bolt_migrations\\", // Backslash is not allowed + "myschema..migrations_table", // Double dot, which is invalid for schema.table format + "myschema.migrations_table.", // Trailing dot is not allowed + "myschema.migrations_table;", // Semicolon in schema-qualified name is not allowed + ".migrations_table", // Leading dot is not allowed + "myschema.migrations_table$", // Dollar sign is not allowed + } + + for _, tableName := range invalidTableNames { + _, err := repositories.NewMigrationDBRepo(tableName, db) + assert.NotNil(t, err) + assert.ErrorContains(t, err, "invalid migration table name") + } +} diff --git a/internal/storage/db.go b/internal/storage/db.go index 1d4cada..b73d250 100644 --- a/internal/storage/db.go +++ b/internal/storage/db.go @@ -51,9 +51,20 @@ type sqlExecutor interface { QueryRow(query string, args ...interface{}) *sql.Row } -type DB struct { +type TxFunc func(db DB) error + +type DB interface { + Exec(query string, args ...interface{}) (sql.Result, error) + Query(query string, args ...interface{}) (*sql.Rows, error) + QueryRow(query string, args ...interface{}) *sql.Row + Tx(fn TxFunc) error + Close() error + TableExists(tableName string) (bool, error) +} + +type SqlDB struct { executor sqlExecutor - sqlDB *sql.DB + conn *sql.DB adapter DBAdapter } @@ -69,12 +80,12 @@ type DB struct { func NewDB(cfg configloader.ConnectionConfig) (DB, error) { driver, exists := supportedDrivers[cfg.Driver] if !exists { - return DB{}, ErrUnsupportedDriver + return SqlDB{}, ErrUnsupportedDriver } db, err := sql.Open(driver.name, driver.adapter.CreateDSN(cfg)) if err != nil { - return DB{}, fmt.Errorf("%w: %v", ErrMalformedConnectionString, err) + return SqlDB{}, fmt.Errorf("%w: %v", ErrMalformedConnectionString, err) } // Note: `sql.Open` only validates the connection string we provided is sane. @@ -82,50 +93,48 @@ func NewDB(cfg configloader.ConnectionConfig) (DB, error) { // the database to ensure the connection string is fully valid. err = db.Ping() if err != nil { - return DB{}, fmt.Errorf("%w: %v", ErrUnableToConnect, err) + return SqlDB{}, fmt.Errorf("%w: %v", ErrUnableToConnect, err) } - return DB{executor: db, sqlDB: db, adapter: driver.adapter}, nil + return SqlDB{executor: db, conn: db, adapter: driver.adapter}, nil } // Close closes the database connection. Any further // queries will result in errors, and you should call // NewDB again after if you'd like to run more. -func (db DB) Close() error { - return db.sqlDB.Close() +func (db SqlDB) Close() error { + return db.conn.Close() } // Exec is a wrapper around the sql.DB Exec. -func (db DB) Exec(query string, args ...any) (sql.Result, error) { +func (db SqlDB) Exec(query string, args ...any) (sql.Result, error) { newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) return db.executor.Exec(newQuery, args...) } // Query is a wrapper around the sql.DB Query. -func (db DB) Query(query string, args ...any) (*sql.Rows, error) { +func (db SqlDB) Query(query string, args ...any) (*sql.Rows, error) { newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) return db.executor.Query(newQuery, args...) } // QueryRow is a wrapper around the sql.DB QueryRow. -func (db DB) QueryRow(query string, args ...any) *sql.Row { +func (db SqlDB) QueryRow(query string, args ...any) *sql.Row { newQuery := db.adapter.ConvertGenericPlaceholders(query, len(args)) return db.executor.QueryRow(newQuery, args...) } // TableExists checks if the tableName exists within the // database currently connected to. -func (db DB) TableExists(tableName string) (bool, error) { +func (db SqlDB) TableExists(tableName string) (bool, error) { return db.adapter.TableExists(db.executor, tableName) } -type txFunc func(db DB) error - // Tx executes fn within a transaction block. If // fn returns an error, the transaction will be rolled // back. Otherwise, it will be committed. -func (db *DB) Tx(fn txFunc) error { - tx, err := db.sqlDB.Begin() +func (db SqlDB) Tx(fn TxFunc) error { + tx, err := db.conn.Begin() if err != nil { return fmt.Errorf( "unable to start transaction: %w", @@ -136,7 +145,7 @@ func (db *DB) Tx(fn txFunc) error { // Create a shallow clone of the DB instance for // the transaction scope with a tx executor. - txDB := *db + txDB := db txDB.executor = tx err = fn(txDB) -- cgit v1.2.3