Raw parameterized strings for postgres json insert

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"

@beiping96 I've already tried that. When I do, I get an error "pq: invalid input syntax for type json". — Joseph Vargas
– Joseph Vargas, Commented Mar 27, 2019 at 7:47
@JosephVargas It doesn't effect the answer, but be sure you're using jsonb for the Postgres column. The performance is much better. — Schwern
– Schwern, Commented Mar 27, 2019 at 8:07

Schwern · Accepted Answer · 2019-03-27 08:02:01Z

3

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
}

answered Mar 27, 2019 at 8:02

Schwern

167k28 gold badges225 silver badges370 bronze badges

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

Collectives™ on Stack Overflow

Raw parameterized strings for postgres json insert

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related