time_zone DSN parameter value requires quoting

[dolmen]

To set the time_zone from the DSN, the value must be quoted. But this is not correctly documented.

Examples (named time zone really work only if the time_zone tables exists on the server):

Wanted value	Fails	Works
+00:00	&time_zone=%2B00%3A00	&time_zone=%27%2B00%3A00%27
UTC	&time_zone=UTC	&time_zone=%27UTC%27
Europe/Paris	&time_zone=Europe%2FParis	&time_zone=%27Europe%2FParis%27

I suppose the same issue will happen for any session parameter that is of type string.

I find that using quoting in counter-intuitive, so I'm not sure if this is that the documentation that should be fixed.

[julienschmidt]

Right below: "The values must be url.QueryEscape'ed!"

[julienschmidt]

Sorry, quoted not escaped. That was a little too fast..

[julienschmidt]

Even though it feels unnatural, I don't think there is much we can do about it.
Keeping a list of all variables that need to be quoted is certainly not an option.

So we have to fix the documentation. Any suggestions how to phrase it?

[arnehormann]

How about https://github.com/go-sql-driver/mysql/blob/master/connection.go#L55 -> err = mc.exec("SET " + param + "=\"" + strconv.Quote(val) + "\"") (just with an alternative to Quote hitting all must-be-escaped MySQL chars).
MySQL extracts the relevant type from a string anyway...

[dolmen]

@arnehormann Too late. Code in the wild is already relying on the existing behaviour. That's what happens when issues are reviewed 10 months after the report.

I'm writing a documentation patch.

[arnehormann]

@dolmen we can also detect if it starts quoted and only apply this otherwise...

[dolmen]

@arnehormann a full fix would also have to take care of also handling quotes quoted inside the value.
proxy_user=%27abc%27%27def%27 for SET proxy_user='abc''def' to set value abc'def
(I don't have access to a MySQL db now to check this is valid syntax)

[arnehormann]

as I said: just with an alternative to Quote hitting all must-be-escaped MySQL chars
See http://dev.mysql.com/doc/refman/5.7/en/string-literals.html#character-escape-sequences

[arnehormann]

By me, licensed CC0 - public domain:

var hex10 = "0000000000000000111111111111111122222222222222223333333333333333444444444444444455555555555555556666666666666666777777777777777788888888888888889999999999999999aaaaaaaaaaaaaaaabbbbbbbbbbbbbbbbccccccccccccccccddddddddddddddddeeeeeeeeeeeeeeeeffffffffffffffff"
var hex01 = "0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef0123456789abcdef"

func appendQuoted(dst, data []byte) []byte {
    offset := 0
    dst = append(dst, '"')
    for j, d := range data {
        var esc byte
        switch d {
        default:
            if d < '\x20' || d >= '\x7f' {
                dst = append(dst, data[offset:j]...)
                dst = append(dst, '\\', 'x', hex10[d], hex01[d])
                offset = j + 1
            }
            continue
        case '\\':
            esc = '\\'
        case '\n':
            esc = 'n'
        case '\t':
            esc = 't'
        case '\r':
            esc = 'r'
        case '\b':
            esc = 'b'
        case '\a':
            esc = 'a'
        case '\f':
            esc = 'f'
        case '\v':
            esc = 'v'
        case '"':
            esc = '"'
        }
        dst = append(dst, data[offset:j]...)
        dst = append(dst, '\\', esc)
        offset = j + 1
    }
    if offset < len(data) {
        dst = append(dst, data[offset:]...)
    }
    return append(dst, '"')
}

[dolmen]

@arnehormann I already have production code that has &time_zone=%27%2B00%3A00%27 in the DSN. You still haven't acknowledged that your proposed fix would accept this and produce the same result.

[arnehormann]

I did acknowledge it. I just didn't write the fix yet.

[arnehormann]

@dolmen I can't fully dig into it right now, but something along these lines should fix it: arnehormann@aed6b36

I'd appreciate if you could test it and get back to us.