Go MySQL queries with SET variables-CodePudding

I'm trying to clean up how Go is calling MySQL queries by setting some variables before running the query with a decent amount of case statements around a single value. The query I'm attempting to run works fine on the console but is failing on me with a syntax issue at the SELECT when running it through Go. Is something like this possible?

func (d *DB) SelectByUserId(uid string, srt string, pg, lim int) ([]Inventory, error) {
    query := `
    SET @user_id := ?,
        @orderBy := ?;
    SELECT
        *
    FROM
        inventory
    WHERE
        user_id = @user_id
    ORDER BY
        (CASE WHEN @orderBy = 'type,asc' THEN type END),
        (CASE WHEN @orderBy = 'type,desc' THEN type END) DESC,
        (CASE WHEN @orderBy = 'visible,asc' THEN visible END),
        (CASE WHEN @orderBy = 'visible,desc' THEN visible END) DESC,
        (CASE WHEN @orderBy = 'create_date,asc' THEN create_date END),
        (CASE WHEN @orderBy = 'create_date,desc' THEN create_date END) DESC,
        (CASE WHEN @orderBy = 'update_date,asc' THEN update_date END),
        (CASE WHEN @orderBy = 'update_date,desc' THEN update_date END) DESC
    LIMIT ?,?;
    `
    
    rows, err := d.Query(
        query,
        uid,
        srt,
        pg*lim,
        lim,
    )

    if err != nil {
        return nil, err
    }
    defer rows.Close()

    result := make([]Inventory, 0)
    for rows.Next() {
        var inv Inventory
        if err := rows.Scan(
            &inv.Id,
            &inv.UserId,
            &inv.Type,
            &inv.Name,
            &inv.Description,
            &inv.Visible,
            &inv.CreateDate,
            &inv.UpdateDate); err != nil {
            return result, err
        }
        result = append(result, inv)
    }
    if err = rows.Err(); err != nil {
        return result, err
    }

    return result, nil
}

Now, if I take out the SET pieces and replace all the @ variables with the ? and then pass the srt variable in multiple times like below this all works. But would really like to not have to have a query call such as:

rows, err := d.Query(
        query,
        uid,
        srt,
        srt,
        srt,
        srt,
        srt,
        srt,
        srt,
        srt,
        pg*lim,
        lim)

The error being reported isn't very helpful:

Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT\n    *\nFROM\n    inventory\nWHERE\n    user_id = @user_id\nORDER BY\n ' at line 3

Thanks for your help.

CodePudding user response：

For those interested I've solved my issue with a few updates.

There is are settings on the DSN when connecting ?...&multiStatements=true&interpolateParams=true
After adding the above I started getting a new error regarding the collation (Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='. I went through and converted the DB and the tables to utf8mb4_general_ci and everything is working as expected.

Thank you to those that provided their solutions but this is the route we wound up taking.

CodePudding user response：

Like most query interfaces, the Query() function can only execute one SQL statement at a time. MySQL's prepared statements don't work with multi-query.

You could solve this by executing the SET statement in one call, then the SELECT in a second call. But you'd have to take care to ensure they are executed on the same database connection, or else the connection pool is likely to run them on different connections. So you'd need to do something like:

conn, err := d.Conn(context.TODO())

conn.QueryContext(context.TODO(), "SET ...")
conn.QueryContext(context.TODO(), "SELECT ...")

Alternatively, change the way you prepare the ORDER BY so you don't need user-defined variables.

The way I'd do it is to build the ORDER BY statement in Go code instead of in SQL, using a string map to ensure a valid column and direction is used. If the input is not in the map, then set a default order to the primary key.

validOrders := map[string]string{
    "type,asc":         "type ASC",
    "type,desc":        "type DESC",
    "visible,asc":      "visible ASC",
    "visible,desc":     "visible DESC",
    "create_date,asc":  "create_date ASC",
    "create_date,desc": "create_date DESC",
    "update_date,asc":  "update_date ASC",
    "update_date,desc": "update_date DESC",
}
orderBy, ok := validOrders[srt]
if !ok {
    orderBy = "id ASC"
}
query := fmt.Sprintf(`
    SELECT ...
    WHERE user_id = ?
    ORDER BY %s
    LIMIT ?, ?
`, orderBy)

This is safe with respect to SQL injection, because the function input is not interpolated into the query. It's the value from my map that is interpolated into the query, and the value is under my control. If someone tries to input some malicious value, it won't match any key in my map, so it'll just use the default sort order.

CodePudding user response：

Unless drivers implement a special interface, the query is prepared on the server first before execution. Bindvars are therefore database specific:

MySQL: uses the ? variant shown above
PostgreSQL: uses an enumerated $1, $2, etc bindvar syntax
SQLite: accepts both ? and $1 syntax
Oracle: uses a :name syntax
MsSQL: @ (as you use)

I guess that's why you can't do what you want with query().