Connecting to Oracle Database with password Containing "@"

[chandrartw]

Trying to connect oracle with this URL )

But got this instead

[error] failed to initialize database, got error params=user=Soracle password="SECRET-z4Enewm1A2k=" 
timezone=local poolIncrement=1 poolMaxSessions=1000 poolMinSessions=1 poolSessionMaxLifetime=1h0m0s
poolSessionTimeout=5m0s poolWaitTimeout=30s extAuth=0: ORA-12154: TNS:could not resolve the connect identifier specified
2021/02/11 13:44:06 params=user=Soracle password="SECRET-z4Enewm1A2k=" connectString=123@10.60.185.186:1521/tibret
timezone=local poolIncrement=1 poolMaxSessions=1000 poolMinSessions=1 poolSessionMaxLifetime=1h0m0s
poolSessionTimeout=5m0s poolWaitTimeout=30s extAuth=0: ORA-12154: TNS:could not resolve the connect identifier specified
exit status 1

[dk333]

there are two errors here. first your password format is wrong. it should be below

db, err := gorm.Open(oracle.Open("Soracle/\"oracle@123\"@10.60.185.186:1521/tibret")

second, there is a upstream module error, ie. godror's bug. the driver could not resolve the oracle password with double quote. i have tried my data source name is "U/\"!£@%^&*\"@192.168.146.1:1521/orcl" but it parese it as user: "U/"!£" password, host and instance name : "%^&*"@192.168.146.1:1521/orcl"

github.com/godror/godror@v0.20.0/dsn/dsn.go

at row 678. you can trace the code.

ref: Symbols to avoid in an Oracle password

[dk333]

you can fix this by modified github.com/godror/godror@v0.20.0/dsn/dsn.go

as following.

1. add func

func splitDSN(s string) []string {
    var result []string
    re := regexp.MustCompile("(^\"?.+?\"?)/(\"?.+\"?)@(.*)/(.*)")
    groupNames := re.SubexpNames()
    for matchNum, match := range re.FindAllStringSubmatch(s, -1) {
        idx := 0
        for groupIdx, group := range match {
            if idx == 0 {
                idx++
                continue
            }
            result = append(result, strings.ReplaceAll(group, "\"", ""))
            name := groupNames[groupIdx]
            if name == "" {
                name = "*"
            }
            if 1 == 2 {
                fmt.Printf("#%d text: '%s', group: '%s'\n", matchNum, group, name)
            }
        }
    }
    // fmt.Println(result)
    return result
}

2. modify parseUserPassw to below

// parseUserPassw splits of the username/password@ from the connectString.
func parseUserPassw(dataSourceName string) (user, passw, connectString string) {
    if i := strings.Index(dataSourceName, "://"); i >= 0 &&
        strings.IndexFunc(
            dataSourceName[:i],
            func(r rune) bool { return !('a' <= r && r <= 'z' || 'A' <= r && r <= 'Z' || '0' <= r && r <= '9') },
        ) < 0 {
        return "", "", dataSourceName
    }
    ups := splitQuoted(dataSourceName, '@')
    var extra string
    if len(ups) == 1 { // user/pass, no '@'
        if i := strings.Index(strings.ToUpper(ups[0]), " AS SYS"); i >= 0 {
            ups[0], extra = ups[0][:i], ups[0][i:]
        }
    }
    //fmt.Printf("ups=%v extra=%q\n", ups, extra)
    userpass := splitQuoted(ups[0], '/')
    //fmt.Printf("ups=%q\nuserpass=%q\n", ups, userpass)
    if len(ups) == 1 && len(userpass) == 1 {
        return "", "", dataSourceName + unquote(extra)
    }

    user = unquote(userpass[0])
    if len(userpass) > 1 {
        passw = unquote(userpass[1])
    }
    if len(ups) == 1 {
        return user, passw, unquote(extra)
    }
    // return user, passw, unquote(ups[1] + extra)

    res := splitDSN(dataSourceName)
    user = res[0]
    passw = res[1]
    host := res[2]
    dbname := res[3]

    // fmt.Println(user, passw, host+"/"+dbname)
    return user, passw, host + "/" + dbname
    // return user, passw, unquote(ups[1])
}

[nurfauzanhidayat]

1. You can use connectString

   connectString := fmt.Sprintf("(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=%s)(PORT=%s)))(CONNECT_DATA=(SID=%s)))", os.Getenv("DBHOST"), os.Getenv("DBPORT"), os.Getenv("DBSID"))
   dsn:= fmt.Sprintf(`user="%s" password="%s" TimeZone="Asia/Makassar" connectString="%s"`, os.Getenv("DBUSER"), os.Getenv("DBPASS"), connectString)
   db, err := gorm.Open(oracle.Open(dsn), &gorm.Config{})