Repository: amacneil/dbmate (go)

Issue:
DB connection strings do not support exclamation points in password
I was getting an "Error 1045: Access denied for user...", given a valid connection string. Checked privileges and made sure everything was right on database side. It seems that mysql is fine with connection strings with exclamation points in them, while net/url mangles them and passes them to mysql as %21, which mysql interprets literally.

Pull request:
Fix special chars passwords in MySQL Driver
This PR aims to fix #57 
When MySQL password included special chars such as exclamation point or @ (at) MySQL backend errored out (invalid password).

https://github.com/amacneil/dbmate/blob/87c22515a9511c680e0391199cc3f53e0fc57eaf/pkg/dbmate/mysql.go#L21-L39

url.userinfo.String() (which gets called inside url.String()) returns %-encoded strings and MySQL interprets it as an actual password.
Now the function percent-decodes it first before returning.

This PR adds a test to cover special chars in MySQL.

Patch:
diff --git a/pkg/dbmate/migrations.go b/pkg/dbmate/migrations.go
index fb96d64..2c0f7b6 100644
--- a/pkg/dbmate/migrations.go
+++ b/pkg/dbmate/migrations.go
@@ -41,8 +41,13 @@ func parseMigration(path string) (Migration, Migration, error) {
 	return up, down, err
 }
 
-var upRegExp = regexp.MustCompile(`(?m)^--\s*migrate:up\s+(.+)*$`)
-var downRegExp = regexp.MustCompile(`(?m)^--\s*migrate:down\s+(.+)*$`)
+var upRegExp = regexp.MustCompile(`(?m)^--\s*migrate:up(\s*$|\s+\S+)`)
+var downRegExp = regexp.MustCompile(`(?m)^--\s*migrate:down(\s*$|\s+\S+)$`)
+var emptyLineRegExp = regexp.MustCompile(`^\s*$`)
+var commentLineRegExp = regexp.MustCompile(`^\s*--`)
+var whitespaceRegExp = regexp.MustCompile(`\s+`)
+var optionSeparatorRegExp = regexp.MustCompile(`:`)
+var blockDirectiveRegExp = regexp.MustCompile(`^--\s*migrate:[up|down]]`)
 
 // parseMigrationContents parses the string contents of a migration.
 // It will return two Migration objects, the first representing the "up"
@@ -53,68 +58,143 @@ func parseMigrationContents(contents string) (Migration, Migration, error) {
 	up := NewMigration()
 	down := NewMigration()
 
-	upMatch := upRegExp.FindStringSubmatchIndex(contents)
-	downMatch := downRegExp.FindStringSubmatchIndex(contents)
-
-	didNotDefineUpBlock := len(upMatch) == 0
-	onlyDefinedUpBlock := len(upMatch) != 0 && len(downMatch) == 0
+	upDirectiveStart, upDirectiveEnd, hasDefinedUpBlock := getMatchPositions(contents, upRegExp)
+	downDirectiveStart, downDirectiveEnd, hasDefinedDownBlock := getMatchPositions(contents, downRegExp)
 
-	if didNotDefineUpBlock {
+	if !hasDefinedUpBlock {
 		return up, down, fmt.Errorf("dbmate requires each migration to define an up bock with '-- migrate:up'")
-	} else if onlyDefinedUpBlock {
-		up.Contents = strings.TrimSpace(contents)
-		up.Options = parseMigrationOptions(contents, upMatch[2], upMatch[3])
-	} else {
-		upStart := upMatch[0]
-		downStart := downMatch[0]
+	} else if statementsPrecedeMigrateBlocks(contents, upDirectiveStart, downDirectiveStart) {
+		return up, down, fmt.Errorf("dbmate does not support statements defined outside of the '-- migrate:up' or '-- migrate:down' blocks")
+	}
 
-		upEnd := downMatch[0]
-		downEnd := len(contents)
+	upEnd := len(contents)
+	downEnd := len(contents)
 
-		// If migrate:down was defined above migrate:up, correct the end indices
-		if upMatch[0] > downMatch[0] {
-			upEnd = downEnd
-			downEnd = upMatch[0]
-		}
+	if hasDefinedDownBlock && upDirectiveStart < downDirectiveStart {
+		upEnd = downDirectiveStart
+	} else if hasDefinedDownBlock && upDirectiveStart > downDirectiveStart {
+		downEnd = upDirectiveStart
+	} else {
+		downEnd = -1
+	}
 
-		up.Contents = strings.TrimSpace(contents[upStart:upEnd])
-		up.Options = parseMigrationOptions(contents, upMatch[2], upMatch[3])
+	upDirective := substring(contents, upDirectiveStart, upDirectiveEnd)
+	downDirective := substring(contents, downDirectiveStart, downDirectiveEnd)
 
-		down.Contents = strings.TrimSpace(contents[downStart:downEnd])
-		down.Options = parseMigrationOptions(contents, downMatch[2], downMatch[3])
-	}
+	up.Options = parseMigrationOptions(upDirective)
+	up.Contents = substring(contents, upDirectiveStart, upEnd)
+
+	down.Options = parseMigrationOptions(downDirective)
+	down.Contents = substring(contents, downDirectiveStart, downEnd)
 
 	return up, down, nil
 }
 
-var whitespaceRegExp = regexp.MustCompile(`\s+`)
-var optionSeparatorRegExp = regexp.MustCompile(`:`)
-
-// parseMigrationOptions parses the options portion of a migration
-// block into an object that satisfies the MigrationOptions interface,
-// i.e., the 'transaction:false' piece of the following:
+// parseMigrationOptions parses the migration options out of a block
+// directive into an object that implements the MigrationOptions interface.
+//
+// For example:
 //
-//     -- migrate:up transaction:false
-//     create table users (id serial, name string);
-//     -- migrate:down
-//     drop table users;
+//     fmt.Printf("%#v", parseMigrationOptions("-- migrate:up transaction:false"))
+//     // migrationOptions{"transaction": "false"}
 //
-func parseMigrationOptions(contents string, begin, end int) MigrationOptions {
-	mOpts := make(migrationOptions)
+func parseMigrationOptions(contents string) MigrationOptions {
+	options := make(migrationOptions)
 
-	if begin == -1 || end == -1 {
-		return mOpts
+	// strip away the -- migrate:[up|down] part
+	contents = blockDirectiveRegExp.ReplaceAllString(contents, "")
+
+	// remove leading and trailing whitespace
+	contents = strings.TrimSpace(contents)
+
+	// return empty options if nothing is left to parse
+	if contents == "" {
+		return options
 	}
 
-	optionsString := strings.TrimSpace(contents[begin:end])
+	// split the options string into pairs, e.g. "transaction:false foo:bar" -> []string{"transaction:false", "foo:bar"}
+	stringPairs := whitespaceRegExp.Split(contents, -1)
+
+	for _, stringPair := range stringPairs {
+		// split stringified pair into key and value pairs, e.g. "transaction:false" -> []string{"transaction", "false"}
+		pair := optionSeparatorRegExp.Split(stringPair, -1)
 
-	optionGroups := whitespaceRegExp.Split(optionsString, -1)
-	for _, group := range optionGroups {
-		pair := optionSeparatorRegExp.Split(group, -1)
+		// if the syntax is well-formed, then store the key and value pair in options
 		if len(pair) == 2 {
-			mOpts[pair[0]] = pair[1]
+			options[pair[0]] = pair[1]
 		}
 	}
 
-	return mOpts
+	return options
+}
+
+// statementsPrecedeMigrateBlocks inspects the contents between the first character
+// of a string and the index of the first block directive to see if there are any statements
+// defined outside of the block directive. It'll return true if it finds any such statements.
+//
+// For example:
+//
+// This will return false:
+//
+// statementsPrecedeMigrateBlocks(`-- migrate:up
+// create table users (id serial);
+// `, 0, -1)
+//
+// This will return true:
+//
+// statementsPrecedeMigrateBlocks(`create type status_type as enum('active', 'inactive');
+// -- migrate:up
+// create table users (id serial, status status_type);
+// `, 54, -1)
+//
+func statementsPrecedeMigrateBlocks(contents string, upDirectiveStart, downDirectiveStart int) bool {
+	until := upDirectiveStart
+
+	if downDirectiveStart > -1 {
+		until = min(upDirectiveStart, downDirectiveStart)
+	}
+
+	lines := strings.Split(contents[0:until], "\n")
+
+	for _, line := range lines {
+		if isEmptyLine(line) || isCommentLine(line) {
+			continue
+		}
+		return true
+	}
+
+	return false
+}
+
+// isEmptyLine will return true if the line has no
+// characters or if all the characters are whitespace characters
+func isEmptyLine(s string) bool {
+	return emptyLineRegExp.MatchString(s)
+}
+
+// isCommentLine will return true if the line is a SQL comment
+func isCommentLine(s string) bool {
+	return commentLineRegExp.MatchString(s)
+}
+
+func getMatchPositions(s string, re *regexp.Regexp) (int, int, bool) {
+	match := re.FindStringIndex(s)
+	if match == nil {
+		return -1, -1, false
+	}
+	return match[0], match[1], true
+}
+
+func substring(s string, begin, end int) string {
+	if begin == -1 || end == -1 {
+		return ""
+	}
+	return s[begin:end]
+}
+
+func min(a, b int) int {
+	if a < b {
+		return a
+	}
+	return b
 }
diff --git a/pkg/dbmate/mysql.go b/pkg/dbmate/mysql.go
index 0edaad9..55c405a 100644
--- a/pkg/dbmate/mysql.go
+++ b/pkg/dbmate/mysql.go
@@ -19,23 +19,36 @@ type MySQLDriver struct {
 }
 
 func normalizeMySQLURL(u *url.URL) string {
-	normalizedURL := *u
-	normalizedURL.Scheme = ""
-
 	// set default port
-	if normalizedURL.Port() == "" {
-		normalizedURL.Host = fmt.Sprintf("%s:3306", normalizedURL.Host)
+	host := u.Host
+
+	if u.Port() == "" {
+		host = fmt.Sprintf("%s:3306", host)
 	}
 
 	// host format required by go-sql-driver/mysql
-	normalizedURL.Host = fmt.Sprintf("tcp(%s)", normalizedURL.Host)
+	host = fmt.Sprintf("tcp(%s)", host)
 
-	query := normalizedURL.Query()
+	query := u.Query()
 	query.Set("multiStatements", "true")
-	normalizedURL.RawQuery = query.Encode()
 
-	str := normalizedURL.String()
-	return strings.TrimLeft(str, "/")
+	queryString := query.Encode()
+
+	// Get decoded user:pass
+	userPassEncoded := u.User.String()
+	userPass, _ := url.QueryUnescape(userPassEncoded)
+
+	// Build DSN w/ user:pass percent-decoded
+	normalizedString := ""
+
+	if userPass != "" { // user:pass can be empty
+		normalizedString = userPass + "@"
+	}
+
+	normalizedString = fmt.Sprintf("%s%s%s?%s", normalizedString,
+		host, u.Path, queryString)
+
+	return normalizedString
 }
 
 // Open creates a new database connection


Test patch:
diff --git a/pkg/dbmate/migrations_test.go b/pkg/dbmate/migrations_test.go
index 967846f..e01d390 100644
--- a/pkg/dbmate/migrations_test.go
+++ b/pkg/dbmate/migrations_test.go
@@ -16,7 +16,7 @@ drop table users;`
 	up, down, err := parseMigrationContents(migration)
 	require.Nil(t, err)
 
-	require.Equal(t, "-- migrate:up\ncreate table users (id serial, name text);", up.Contents)
+	require.Equal(t, "-- migrate:up\ncreate table users (id serial, name text);\n", up.Contents)
 	require.Equal(t, true, up.Options.Transaction())
 
 	require.Equal(t, "-- migrate:down\ndrop table users;", down.Contents)
@@ -34,10 +34,10 @@ drop table users;
 	up, down, err = parseMigrationContents(migration)
 	require.Nil(t, err)
 
-	require.Equal(t, "--migrate:up\ncreate table users (id serial, name text);", up.Contents)
+	require.Equal(t, "--migrate:up\ncreate table users (id serial, name text);\n\n", up.Contents)
 	require.Equal(t, true, up.Options.Transaction())
 
-	require.Equal(t, "--migrate:down\ndrop table users;", down.Contents)
+	require.Equal(t, "--migrate:down\ndrop table users;\n", down.Contents)
 	require.Equal(t, true, down.Options.Transaction())
 
 	// It is acceptable for down to be defined before up
@@ -50,10 +50,10 @@ create table users (id serial, name text);
 	up, down, err = parseMigrationContents(migration)
 	require.Nil(t, err)
 
-	require.Equal(t, "-- migrate:up\ncreate table users (id serial, name text);", up.Contents)
+	require.Equal(t, "-- migrate:up\ncreate table users (id serial, name text);\n", up.Contents)
 	require.Equal(t, true, up.Options.Transaction())
 
-	require.Equal(t, "-- migrate:down\ndrop table users;", down.Contents)
+	require.Equal(t, "-- migrate:down\ndrop table users;\n", down.Contents)
 	require.Equal(t, true, down.Options.Transaction())
 
 	// It supports turning transactions off for a given migration block,
@@ -66,17 +66,64 @@ ALTER TYPE colors ADD VALUE 'orange' AFTER 'red';
 	up, down, err = parseMigrationContents(migration)
 	require.Nil(t, err)
 
-	require.Equal(t, "-- migrate:up transaction:false\nALTER TYPE colors ADD VALUE 'orange' AFTER 'red';", up.Contents)
+	require.Equal(t, "-- migrate:up transaction:false\nALTER TYPE colors ADD VALUE 'orange' AFTER 'red';\n", up.Contents)
 	require.Equal(t, false, up.Options.Transaction())
 
 	require.Equal(t, "", down.Contents)
 	require.Equal(t, true, down.Options.Transaction())
 
 	// It does *not* support omitting the up block.
-	migration = `-- drop users table
-begin;
+	migration = `-- migrate:down
+drop table users;
+`
+
+	_, _, err = parseMigrationContents(migration)
+	require.NotNil(t, err)
+	require.Equal(t, "dbmate requires each migration to define an up bock with '-- migrate:up'", err.Error())
+
+	// It allows leading comments and whitespace preceding the migrate blocks
+	migration = `
+-- This migration creates the users table.
+-- It'll drop it in the event of a rollback.
+
+-- migrate:up
+create table users (id serial, name text);
+
+-- migrate:down
 drop table users;
-commit;
+`
+
+	up, down, err = parseMigrationContents(migration)
+	require.Nil(t, err)
+
+	require.Equal(t, "-- migrate:up\ncreate table users (id serial, name text);\n\n", up.Contents)
+	require.Equal(t, true, up.Options.Transaction())
+
+	require.Equal(t, "-- migrate:down\ndrop table users;\n", down.Contents)
+	require.Equal(t, true, down.Options.Transaction())
+
+	// It does *not* allow arbitrary statements preceding the migrate blocks
+	migration = `
+-- create status_type
+CREATE TYPE status_type AS ENUM ('active', 'inactive');
+
+-- migrate:up
+ALTER TABLE users
+ADD COLUMN status status_type DEFAULT 'active';
+
+-- migrate:down
+ALTER TABLE users
+DROP COLUMN status;
+`
+
+	_, _, err = parseMigrationContents(migration)
+	require.NotNil(t, err)
+	require.Equal(t, "dbmate does not support statements defined outside of the '-- migrate:up' or '-- migrate:down' blocks", err.Error())
+
+	// It requires an at least an up block
+	migration = `
+ALTER TABLE users
+ADD COLUMN status status_type DEFAULT 'active';
 `
 
 	_, _, err = parseMigrationContents(migration)
diff --git a/pkg/dbmate/mysql_test.go b/pkg/dbmate/mysql_test.go
index 6912b45..681bc75 100644
--- a/pkg/dbmate/mysql_test.go
+++ b/pkg/dbmate/mysql_test.go
@@ -52,6 +52,15 @@ func TestNormalizeMySQLURLCustom(t *testing.T) {
 	require.Equal(t, "bob:secret@tcp(host:123)/foo?flag=on&multiStatements=true", s)
 }
 
+func TestNormalizeMySQLURLCustomSpecialChars(t *testing.T) {
+	u, err := url.Parse("mysql://duhfsd7s:123!@123!@@host:123/foo?flag=on")
+	require.NoError(t, err)
+	require.Equal(t, "123", u.Port())
+
+	s := normalizeMySQLURL(u)
+	require.Equal(t, "duhfsd7s:123!@123!@@tcp(host:123)/foo?flag=on&multiStatements=true", s)
+}
+
 func TestMySQLCreateDropDatabase(t *testing.T) {
 	drv := MySQLDriver{}
 	u := mySQLTestURL(t)