The sqlx library gives weird base64 encoded looking results

Aiden

I am using this library from http://jmoiron.github.io/sqlx/ to do a query. Following the documentation led me to the code below.

func cities(w http.ResponseWriter, r *http.Request, _ httprouter.Params) error {
    var main string
    var secondary string
    var limit string
    queryParams := make(map[string]interface{})

    if k := r.PostFormValue("keyword"); k != "" {
        main = "city.name LIKE :keyword"
        queryParams["keyword"] = k + "%"
    }

    if sk := r.PostFormValue("secondaryKeyword"); sk != "" && sk != "null" {
        secondary = "OR city.name = :secondaryKeyword"
        queryParams["secondaryKeyword"] = sk
    }

    if mr := r.PostFormValue("maxResults"); mr != "" {
        limit = "LIMIT :maxResults"
        queryParams["maxResults"] = mr
    }

    if lr := r.PostFormValue("lastRequest"); lr != "" && lr == "1" {
        limit = ""
    }

    query := fmt.Sprintf(`
        SELECT
            city.geonameid AS cityId,
            city.name AS cityName,
            COALESCE(admin1.name_local, '') AS admin1Name,
            country.name AS countryName,
            CONCAT_WS(' ', city.name, city.asciiname, country.name) AS searchString
        FROM geonames_cities1000 AS city
            INNER JOIN geonames_countryinfo AS country
                ON city.iso_alpha2 = country.iso_alpha2
            LEFT OUTER JOIN geonames_admin1_codes_ascii as admin1
                ON admin1.code = CONCAT(city.iso_alpha2, '.', city.admin1_code)
        WHERE %s %s
        ORDER BY city.name ASC %s;
    `, main, secondary, limit)

    nstmt, err := sql.DB.PrepareNamed(query)
    if err != nil {
        return err
    }

    rows, err := nstmt.Queryx(queryParams)
    if err != nil {
        return err
    }

    results := []interface{}{}

    for rows.Next() {
        row := make(map[string]interface{})
        err := rows.MapScan(row)
        if err != nil {
            return err
        }
        results = append(results, row)
    }

    b, err := json.Marshal(results)
    if err != nil {
        return err
    }
    w.Write(b)
    return nil
}

Sending a POST request postman chrome plugin with these values:

keyword: "tron"
maxResults: 7
lastRequest: 0

gave this JSON output:

[
    {
        "admin1Name": "VXR0YXJhZGl0",
        "cityId": 1605268,
        "cityName": "VHJvbg==",
        "countryName": "VGhhaWxhbmQ=",
        "searchString": "VHJvbiBUcm9uIFRoYWlsYW5k"
    },
    {
        "admin1Name": "Q2FsYWJyaWE=",
        "cityId": 8949073,
        "cityName": "VHJvbmNh",
        "countryName": "SXRhbHk=",
        "searchString": "VHJvbmNhIFRyb25jYSBJdGFseQ=="
    },
    {
        "admin1Name": "QXJhZ29u",
        "cityId": 3107444,
        "cityName": "VHJvbmNow7Nu",
        "countryName": "U3BhaW4=",
        "searchString": "VHJvbmNow7NuIFRyb25jaG9uIFNwYWlu"
    },
    {
        "admin1Name": "UHVlYmxh",
        "cityId": 8859151,
        "cityName": "VHJvbmNvbmFs",
        "countryName": "TWV4aWNv",
        "searchString": "VHJvbmNvbmFsIFRyb25jb25hbCBNZXhpY28="
    },
    {
        "admin1Name": "U2NobGVzd2lnLUhvbHN0ZWlu",
        "cityId": 2821000,
        "cityName": "VHLDtm5kZWw=",
        "countryName": "R2VybWFueQ==",
        "searchString": "VHLDtm5kZWwgVHJvbmRlbCBHZXJtYW55"
    },
    {
        "admin1Name": "U8O4ci1UcsO4bmRlbGFn",
        "cityId": 3133880,
        "cityName": "VHJvbmRoZWlt",
        "countryName": "Tm9yd2F5",
        "searchString": "VHJvbmRoZWltIFRyb25kaGVpbSBOb3J3YXk="
    },
    {
        "admin1Name": "VG9uZ3Nh",
        "cityId": 1252408,
        "cityName": "VHJvbmdzYQ==",
        "countryName": "Qmh1dGFu",
        "searchString": "VHJvbmdzYSBUcm9uZ3NhIEJodXRhbg=="
    }
]

Why are the results like this? Putting the results in spew.Dump() will give something like this (I'm just outputting one of the rows):

(map[string]interface {}) (len=5) {
  (string) (len=11) "countryName": ([]uint8) (len=6 cap=6) {
   00000000  4e 6f 72 77 61 79                                 |Norway|
  },
  (string) (len=12) "searchString": ([]uint8) (len=26 cap=26) {
   00000000  54 72 6f 6e 64 68 65 69  6d 20 54 72 6f 6e 64 68  |Trondheim Trondh|
   00000010  65 69 6d 20 4e 6f 72 77  61 79                    |eim Norway|
  },
  (string) (len=6) "cityId": (int64) 3133880,
  (string) (len=8) "cityName": ([]uint8) (len=9 cap=9) {
   00000000  54 72 6f 6e 64 68 65 69  6d                       |Trondheim|
  },
  (string) (len=10) "admin1Name": ([]uint8) (len=15 cap=15) {
   00000000  53 c3 b8 72 2d 54 72 c3  b8 6e 64 65 6c 61 67     |S..r-Tr..ndelag|
  }

What do I do wrong here?'

EDIT:

I tried doing as Elwinar suggested with structs instead. But something really weird is going on.

This code works:

type City struct {
    AdminName    string `json:"admin1Name"   db:"admin1Name"`
    CityID       int64  `json:"cityId"       db:"cityId"`
    CityName     string `json:"cityName"     db:"cityName"`
    CountryName  string `json:"countryName"  db:"countryName"`
    SearchString string `json:"searchString" db:"searchString"`
}

But this code does not work and outputs error "missing destination name cityId":

type City struct {
    CityId       int64  `json:"cityId"       db:"cityId"`
    CityName     string `json:"cityName"     db:"cityName"`
    Admin1Name   string `json:"admin1Name"   db:"admin1Name"`
    CountryName  string `json:"countryName"  db:"countryName"`
    SearchString string `json:"searchString" db:"searchString"`
}

What is the difference?

Solution: Must separate the tags with space key. Can not do spacing with tab key, and can not use commas to separate tags.

Elwinar

As spew.Dump tells you, the SQL driver returns []uint8 for textual column. In fact, it is equivalent to []byte, which json.Marshal encode as base64 strings.

The simplest way to work around that is to scan your rows into a real struct (which sqlx does quite well with StructScan), which will have string fields so that json.Marshal will display them as you expect. You can control the name of both the SQL and JSON names of the field using tags, so your entity can have conventionnal names in every language…

Example:

type City struct {
    AdminName    string `json:"admin1Name"   sql:"admin1Name"`
    CityID       int64  `json:"cityId"       sql:"cityId"`
    CityName     string `json:"cityName"     sql:"cityName"`
    CountryName  string `json:"countryName"  sql:"countryName"`
    SearchString string `json:"searchString" sql:"searchString"`
}

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Decoded value of base64 string when re-encoded(to base64) gives different output

From Dev

Decode base64 encoded file and print results to the console

From Dev

Some utf-8 strings base64 encoded by php can not be decoded using iOS base64 library?

From Dev

Some utf-8 strings base64 encoded by php can not be decoded using iOS base64 library?

From Dev

Transperent PNG above DIV gives weird results

From Dev

UNION mysql gives weird numbered results

From Dev

memcpy gives me weird results with struct

From Dev

Transperent PNG above DIV gives weird results

From Dev

Android base64 encoded string invalid?

From Dev

Create base64 encoded images with JavaScript

From Dev

Base64 encoded image in email

From Dev

base64 JSON encoded strings in nodejs

From Dev

Displaying base64 encoded image in rails

From Dev

It is possible to know if a string is encoded in base64?

From Dev

Is a base64 encoded string unique?

From Dev

Serialize in JSON a base64 encoded data

From Dev

PDF encoded in base64, how to decode it?

From Dev

Decode base64 data encoded in bash

From Dev

Security concerns with base64 encoded images

From Dev

Are Base64 encoded UUIDs unique?

From Dev

Download base64 encoded file

From Dev

encoded image in base64 back to image

From Dev

decode base64 encoded value

From Dev

It is possible to know if a string is encoded in base64?

From Dev

Invalid encoded Base64 image data

From Dev

Decoding big base64 encoded blobs

From Java

pandas dataframe: Integrate over time gives weird results

From Dev

OpenCV's Java bindings Mat.get() gives weird results

From Dev

date_default_timezone_set is producing weird results, what gives?

Related Related

HotTag

Archive