How to use wildcard in sql query in golang

Question

query := "select p.project_id,p.name,p.org_id,p.user_id,p.email,p.status_ind,p.approver,p.favorite,p.create_timestamp,t.name as tagname,count(b.name) as total,t.colorCode from project p inner join tag t on p.project_id = t.project_id  ";
    q2 := " left join build b on p.project_id = b.project_id AND b.status_ind = \"created\" AND p.status_ind = :status group by p.project_id, t.colorCode "
    //query := "select project.project_id,project.name,project.org_id,project.user_id,project.email,project.status_ind,project.approver,project.favorite,project.create_timestamp,tag.name AS tagname, tag.colorCode from project INNER JOIN tag on project.project_id = tag.project_id where status_ind=:status "
    if q["name"] != "" {
        query = query  + " AND p.name LIKE %:name " 
    }
    query+=q2
    query += " ORDER BY create_timestamp DESC "
    query += " LIMIT :limit;"

Here name is parameter which is a golang variable and comes from json request. It is throwing me an error of invalid sql whenever I hit the API.

Answer 1

Two things to keep in mind about SQL syntax:

• The argument to LIKE must be a string.

• Parameter placeholders must not be inside SQL string delimiters.

So you need to make the argument to LIKE be a concatenated string of '%' and the value of your parameter.

There are two ways to do this.

The first way is to use CONCAT() to concatenate the literal string '%' and your parameter:

query := `
  SELECT ...
  FROM project p 
  INNER JOIN tag t ON p.project_id = t.project_id  
  LEFT JOIN build b ON p.project_id = b.project_id 
    AND b.status_ind = 'created' 
    AND p.status_ind = ? 
    AND p.name LIKE CONCAT('%', ?)
  GROUP BY p.project_id, t.colorCode
  ORDER BY create_timestamp DESC
  LIMIT ?`

rows, err := db.Query(query, status, name, limit)

The second way is to just use a parameter placeholder, and concatenate the string in Go code before passing it to the query.

query := `
  SELECT ...
  FROM project p 
  INNER JOIN tag t ON p.project_id = t.project_id  
  LEFT JOIN build b ON p.project_id = b.project_id 
    AND b.status_ind = 'created' 
    AND p.status_ind = ? 
    AND p.name LIKE ?
  GROUP BY p.project_id, t.colorCode
  ORDER BY create_timestamp DESC
  LIMIT ?`

namePattern := "%" + name

rows, err := db.Query(query, status, namePattern, limit)

I show in the example above using Go back-tick strings to make it easier to make multi-line strings that may contain literal quote characters. This is the way I write SQL queries in Go.

As far as I know, MySQL only supports positional query parameter placeholders, not named query parameter placeholders.