aboutsummaryrefslogtreecommitdiffstats
path: root/internal/repositories
diff options
context:
space:
mode:
authorEugene Triguba <eugenetriguba@gmail.com>2024-08-18 14:51:00 -0400
committerGitHub <noreply@github.com>2024-08-18 14:51:00 -0400
commitd2186ed58d907a81648aa62ffa17ec20fb2d213e (patch)
treea5b2e26f1f96f333d48a8b20126ecc9ead697029 /internal/repositories
parentMultiple issue fixes in readme (#3) (diff)
downloadcli-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.go73
-rw-r--r--internal/repositories/migration_db_repo_test.go112
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")
+ }
+}