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, Mar 27 '19 at 07: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, Mar 27 '19 at 08:07
@Shcwern Thanks! Already using jsonb but appreciate the tip! — Joseph Vargas, Mar 27 '19 at 08:13

score 2 · Accepted Answer · answered Mar 27 '19 at 08:02

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
}

Raw parameterized strings for postgres json insert

1 Answers1