mysql_real_escape_string equivalent for Golang

Question

I would like to escape a value that makes up part of a database query, but I can't use parameterized queries.

Does Go have an equivalent of PHP's mysql_real_escape_string I can use to escape the query value?

Answer 1

I came up with my own solution to create the function myself.
Hope it would be useful to someone.

func MysqlRealEscapeString(value string) string {
    replace := map[string]string{"\\":"\\\\", "'":`\'`, "\\0":"\\\\0", "\n":"\\n", "\r":"\\r", `"`:`\"`, "\x1a":"\\Z"}

    for b, a := range replace {
        value = strings.Replace(value, b, a, -1)
    }
    
    return value;
}

1.MysqlRealEscapeString is not right, as below test case will fail

func TestEscape(t *testing.T) {
    mysqlEscapeList := map[string]string{
        "\\": "\\\\", "'": `\'`, "\\0": "\\\\0", "\n": "\\n", "\r": "\\r", `"`: `\"`, "\x1a": "\\Z"}

    for old, want := range mysqlEscapeList {
        testEscape(t, old, want)
    }
    testEscape(t, `<p>123</p><div><img width="1080" />`, `<p>123</p><div><img width=\"1080\" />`)
}
func testEscape(t *testing.T, origin, want string) {
    escaped := MysqlRealEscapeString(origin)
    assert.Equal(t, want, escaped)
}

2. use this one instead

func Escape(sql string) string {
    dest := make([]byte, 0, 2*len(sql))
    var escape byte
    for i := 0; i < len(sql); i++ {
        c := sql[i]

        escape = 0

        switch c {
        case 0: /* Must be escaped for 'mysql' */
            escape = '0'
            break
        case '\n': /* Must be escaped for logs */
            escape = 'n'
            break
        case '\r':
            escape = 'r'
            break
        case '\\':
            escape = '\\'
            break
        case '\'':
            escape = '\''
            break
        case '"': /* Better safe than sorry */
            escape = '"'
            break
        case '\032': //十进制26,八进制32,十六进制1a, /* This gives problems on Win32 */
            escape = 'Z'
        }

        if escape != 0 {
            dest = append(dest, '\\', escape)
        } else {
            dest = append(dest, c)
        }
    }

    return string(dest)
}

Answer 2

If the entire query - or any part of the query that goes beyond a single value - is passed from the command line, there is nothing for you to escape.

mysql_real_escape_string and its cousins are for sanitizing single values, to prevent anyone with access to the value before it is inserted into the query from "breaking out" and fiddling with the query itself.

Given that you are giving access to the entire query to an outside, there is nothing an escape function could do to improve safety.

Your only shot at security here is

• executing the query in a user context that can't do any damage (e.g. you can restrict commands on a per-user basis in mySQL)
• making sure that query errors are properly caught and dealt with
• as Not_a_Golfer suggests in the comments above, parsing the query for anything malicious

Answer 3

Improved answer:

func MysqlRealEscapeString(value string) string {
    var sb strings.Builder
    for i := 0; i < len(value); i++ {
        c := value[i]
        switch c {
        case '\\', 0, '\n', '\r', '\'', '"':
            sb.WriteByte('\\')
            sb.WriteByte(c)
        case '\032':
            sb.WriteByte('\\')
            sb.WriteByte('Z')
        default:
            sb.WriteByte(c)
        }
    }
    return sb.String()
}

Answer 4

For this you can use Prepared Queries.

To Retrieve Multiple Rows

stmt, err := db.Prepare("select id, name from users where id = ?")
if err != nil {
    log.Fatal(err)
}
defer stmt.Close()
rows, err := stmt.Query(1)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
    // ...
}
if err = rows.Err(); err != nil {
    log.Fatal(err)
}

To Retrieve Single Row

var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
    log.Fatal(err)
}
fmt.Println(name)