Go MySQL queries with SET variables

Question

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.

djneely · Accepted Answer · 2022-09-04 12:29:44Z

4

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

There 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.

edited Sep 4, 2022 at 12:29

answered Sep 2, 2022 at 16:14

djneely

1,16915 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Karwin · Accepted Answer · 2022-09-02 15:26:54Z

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.

Murat Kara · Accepted Answer · 2022-09-02 15:31:42Z

0

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().

answered Sep 2, 2022 at 15:31

Murat Kara

8315 silver badges17 bronze badges

Collectives™ on Stack Overflow

Go MySQL queries with SET variables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related