用于Postgres JSON插入的原始参数化字符串

Question

According to this link I should use raw `` strings to execute queries to a SQL database with Golang to avoid SQL injections. For my use case I am trying to use Postgres' json type for one of my data objects.

My structs are as follows ~

type LessonDB struct {  // for DB data retrieval
    ID     int    `db:"id"`
    Lesson string `db:"lesson"`
}

type Lesson struct {  // for general data operations
    ID    int    `json:"id"`
    Name  string `json:"name"`
    Pages []Page `json:"pages,omitempty"`
}

My query is executed as follows ~

func (l *Lesson) Insert() error {
    query := `
        INSERT INTO lessons (lesson)
        VALUES ('{
            "name": "$1"
        }')
        RETURNING id;
    `
    err := db.QueryRow(query, l.Name).Scan(&l.ID)
    return err
}

PostMan returns an error saying ~ " pq: got 1 parameters but the statement requires 0 "

While troubleshooting with fmt.PrintLn(query, l.Name) it appears as though the raw strings parameter isn't working and the "name" field still evaluates to "$1"

Answer 1

The problem is the $1 is inside a quoted string, so it's just treated as part of a literal SQL value. You're inserting literally {"name": "$1"}.

You can't insert part of a value that way. Instead you have to construct the value in Go and insert the whole value.

func (l *Lesson) Insert() error {
    query := `
        INSERT INTO lessons (lesson)
        VALUES ($1)
        RETURNING id;
    `
    // Demonstration only, don't produce JSON like this.
    value := fmt.Sprintf("{\"name\": \"%s\"}", l.Name)
    err := db.QueryRow(query, value).Scan(&l.ID)
    return err
}