dongyaofu0599 2019-03-27 07:30
浏览 98
已采纳

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

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"

  • 写回答

1条回答 默认 最新

  • dsimib1625 2019-03-27 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
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 kali终端update时出现这样的情况
  • ¥15 matlab调用stl文件时报错说内存不足
  • ¥20 医学图像格式、医学图像后处理
  • ¥15 MATLAB中的矩阵分离问题
  • ¥15 服务端可运行语言,解析unity文件
  • ¥15 英飞凌TC387使用MCAL唤醒TJA1145问题
  • ¥15 android tv图标显示异常
  • ¥20 (标签-AR|关键词-预测分析)
  • ¥15 QT IFW 自定义界面添加lineedit小键盘输入数字无效果
  • ¥15 python thinter动态建立Entry并读取数据