How can I store binary data in MySQL?

I'm using the MySQL driver from http://github.com/go-sql-driver/mysql

I need to store the binary representation of an IP address in MySQL in a BINARY(4) column.

To do this, I've tried:

    startSlice := net.ParseIP(rangeStart).To4()
    var startBytes [4]byte
    copy(startSlice[:], startBytes[0:4])

    endSlice := net.ParseIP(rangeEnd).To4()
    var endBytes [4]byte
    copy(endSlice[:], endBytes[0:4])

    r, e := db.Exec("UPDATE AIPRangesBlocks SET BinRangeStart = ?, BinRangeEnd = ? WHERE IPGRID = ?", startBytes, endBytes, id)
    fmt.Println("result of update:", r)
    if e != nil {
        fmt.Println(e)
    }

Note that I used the copy command to convert from a []byte slice to simply a [4]byte array, but I get this error:

sql: converting Exec argument #0's type: unsupported type [4]uint8, a array

If I do it directly as net.ParseIP("some_ip").To4(), I get this error:

sql: converting Exec argument #0's type: unsupported type net.IP, a slice

How do I send the binary data?

If I use a hexadecimal string, it will execute the query, but I'm not getting the right values on retrieval.

I tried hex.EncodeToString() and "0x" + hex.EncodeToString() and neither are working properly.

Here's an example:

66.182.64.0 becomes 42b64000

If I store "42b64000" in my MySQL column, I get back:

52 50 98 54

If I store "0x42b64000" in my MySQL column, I get back:

48 120 52 50

How do I fix this?

If you store "42b64000" in my MySQL column, you get back:

52 50 98 54

Decoded into ASCII this is the start of the string which you gave it, eg

"42b6"

Which suggest that passing the IP address slice as a string will work, eg

startSlice := net.ParseIP(rangeStart).To4()
endSlice := net.ParseIP(rangeEnd).To4()
r, e := db.Exec("UPDATE AIPRangesBlocks SET BinRangeStart = ?, BinRangeEnd = ? WHERE IPGRID = ?", string(startSlice), string(endSlice), id)
fmt.Println("result of update:", r)
if e != nil {
    fmt.Println(e)
}

In go strings are essentially a read only version of []byte and you can cast between them with no problems (other than a bit of memory copying). Hopefully the quoting in the MySQL driver can deal with NULs in the string.

You could try to store it as an uint32 with encoding/binary: binary.LittleEndian.Uint32(net.ParseIP(rangeStart).To4())
and binary.LittleEndian.PutUint32(destinationSlice, colValue).
If LittleEndian gives the wrong results for your other applications, use BigEndian instead.

You are right upto the part of converting it to HEX string.

Here is how I solved it (for both IPv4 and IPv6):

1. Assume that your table is:

> CREATE TABLE ip_addr (ip VARBINARY(16));

2. Prepare the INSERT or UPDATE statement as follows:

> stmt := db.Prepare("INSERT INTO ip_addr (ip) VALUES (UNHEX(?))")

3. In the 'Go' code:

> if ip.To4() != nil {
> ip_hex = hex.EncodeToString(ip.To4())
> } else {
> ip_hex = hex.EncodeToString(ip.To16())
> }
> stmt.Exec(ip_hex)