不能在db.Query的参数中使用缓冲区（类型为bytes.Buffer）作为类型字符串

Question

I have written a SQL query in buffer.WriteString() but not able to use that buffer in db.Query().

buffer.WriteString(fmt.Sprintf(`SELECT c.id, c.company_name, ss.start_date, ss.shift_length, ss.bill_rate, ss.ot_hrs, ss.dt_hrs, ts.pay_rate, ts.wc_rate, ts.paid, td.wc
        FROM company c
        JOIN users u1 ON c.id = u1.company_id
        JOIN schedule s ON u1.id = s.user_id
        JOIN schedule_shifts ss ON s.id = ss.schedule_id
        JOIN technician_shifts ts ON ss.id = ts.shift_id
        JOIN users u ON u.id = ts.technician_id
        JOIN technician_details td ON td.user_id = u.id
        JOIN master_technicians mt ON mt.id = u.master_technician_id
        WHERE mt.id = %d AND ts.confirmed = 'yes' AND ts.paid = 'yes'`, masterID))

    if e.Type == "today" {
        buffer.WriteString(fmt.Sprintf(`AND ts.paid_on = CURDATE()`))
    } else if e.Type == "weekly" {
        buffer.WriteString(fmt.Sprintf(`AND ts.paid_on > DATE_SUB(NOW(), INTERVAL 1 WEEK)`))
    } else if e.Type == "monthly" {
        buffer.WriteString(fmt.Sprintf(`AND ts.paid_on > DATE_SUB(NOW(), INTERVAL 1 MONTH)`))
    } else {
        if e.StartDate != "" && e.EndDate != "" {
            if e.StartDate == e.EndDate {
                buffer.WriteString(fmt.Sprintf(`AND ts.paid_on = %s`, e.StartDate))
            } else {
                buffer.WriteString(fmt.Sprintf(`AND ts.paid_on >= %s AND ts.paid_on <= %s`, e.StartDate, e.EndDate))
            }
        }
    }

rows, err := db.Query(buffer)
    if err != nil {
        log.Panic(err.Error())
    }

It gives me the error that

cannot use buffer (type bytes.Buffer) as type string in argument to db.Query

How can i do that?

Answer 1

The direct answer to your question is provided by the docs, and is simply to use the String() method:

rows, err := db.Query(buffer.String())

Having said that, a buffer probably doesn't make sense here. For building strings, the strings.Builder type is more efficient.

But for your particular use case, building SQL queries this way is dangerous and error-prone. Several suggestions:

1. NEVER, EVER EVER use string concatenation or Sprintf/Fprintf to insert values into SQL queries. You'll open yourself up to SQL injection attacks. Instead, always use parameterized queries.
2. Large if/else blocks are error-prone and hard to read. Use a proper switch statement instead.
3. You can use an SQL query builder, but in your case, you have only one optional portion of the query. Just use standard string concatenation for that.

I've rewritten your code considering the above concerns, and now it looks like this:

var paidCondition string
args := []interface{}{masterID}
switch (
case e.Type == "today":
    paidCondition = `AND ts.paid_on = CURDATE()`
case e.Type == "weekly":
    paidCondition = `AND ts.paid_on > DATE_SUB(NOW(), INTERVAL 1 WEEK)`
case e.Type == "monthly":
    paidCondition = `AND ts.paid_on > DATE_SUB(NOW(), INTERVAL 1 MONTH)`
case e.StartDate != "" && e.EndDate != "" && e.StartDate == e.EndDate:
    paidCondition = `AND ts.paid_on = ?`
    args = append(args, e.StartDate)
case e.StartDate != "" && e.EndDate != "":
    paidCondition = `AND ts.paid_on >= ? AND ts.paid_on <= ?`
    args = append(args, e.StartDate, e.EndDate)
)

query := `SELECT c.id, c.company_name, ss.start_date, ss.shift_length, ss.bill_rate, ss.ot_hrs, ss.dt_hrs, ts.pay_rate, ts.wc_rate, ts.paid, td.wc
    FROM company c
    JOIN users u1 ON c.id = u1.company_id
    JOIN schedule s ON u1.id = s.user_id
    JOIN schedule_shifts ss ON s.id = ss.schedule_id
    JOIN technician_shifts ts ON ss.id = ts.shift_id
    JOIN users u ON u.id = ts.technician_id
    JOIN technician_details td ON td.user_id = u.id
    JOIN master_technicians mt ON mt.id = u.master_technician_id
    WHERE mt.id = ? AND ts.confirmed = 'yes' AND ts.paid = 'yes' ` + paidCondition

rows, err := db.Query(query, args...)
if err != nil {
    log.Panic(err.Error())
}

And finally, a couple other improvements, which are not directly related to your question or my suggested code above:

1. buffer.WriteString(fmt.Sprintf(...)) is overly verbose. The shorter version is: fmt.Fprintf(buffer,...).
2. You're using fmt.Sprintf() in several places where no formatting is taking place. Just remove this entirely. For example, replace fmt.Sprintf("AND ts.paid_on = CURDATE()") with simply "AND ts.paid_on = CURDATE()".