diff options
| author | Eugene Triguba <eugenetriguba@gmail.com> | 2024-08-18 14:51:00 -0400 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-08-18 14:51:00 -0400 |
| commit | d2186ed58d907a81648aa62ffa17ec20fb2d213e (patch) | |
| tree | a5b2e26f1f96f333d48a8b20126ecc9ead697029 /internal/repositories | |
| parent | Multiple issue fixes in readme (#3) (diff) | |
| download | cli-d2186ed58d907a81648aa62ffa17ec20fb2d213e.zip | |
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
Diffstat (limited to 'internal/repositories')
| -rw-r--r-- | internal/repositories/migration_db_repo.go | 73 | ||||
| -rw-r--r-- | internal/repositories/migration_db_repo_test.go | 112 |
2 files changed, 145 insertions, 40 deletions
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") + } +} |