25

I'm working with Go and PostgreSQL (pq driver), I have the following query

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC

If I exec this query directly in PostgreSQL it work but in Golang says:

pq: syntax error at or near "%"

How can I fix it? I tried with "\%" but didn't works. thanks.

here is the complete source code

func FindByName(name *string) ([]*Product, error) {
    db, err := db.StablishConnection()
    if err != nil {
            log.Fatal(err)
            panic(err)
    }
    defer db.Close()

    query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
        FROM products AS p
        WHERE LOWER(p.name) LIKE %$1% ORDER BY p.rate DESC`

    product_rows, err := db.Query(query, name)

    if err != nil {
            return nil, err
    }

    if product_rows == nil {
            return nil, errors.New("No Products Named " + *name)
    }

    products := []*Product{}
    for product_rows.Next() {
            product := new(Product)
            err = product_rows.Scan(&product.Id,
                    &product.Name,
                    &product.Description,
                    &product.Price,
                    &product.Image,
                    &product.Rate)
            if err != nil {
                    panic(err)
            }
            products = append(products, product)
    }
    return products, nil
}
sescob27
  • 647
  • 1
  • 8
  • 17

6 Answers6

47

You need to put the like pattern in single quotes:

SELECT p.id, p.name, p.description, p.price, p.image, p.rate
FROM products AS p
WHERE LOWER(p.name) LIKE '%' || $1 || '%'
ORDER BY p.rate DESC;
OneOfOne
  • 95,033
  • 20
  • 184
  • 185
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    now it says: pq: got 1 parameters but the statement requires 0 as if it doesn't recognize the $1 – sescob27 Aug 09 '14 at 01:48
  • 3
    Wouldn't you need `LIKE '%' || $1 || '%'` in order for the placeholder to be recognized as a placeholder? Or do the string concatenation in Go and use `LIKE $1`. – mu is too short Aug 09 '14 at 01:52
  • 4
    `LIKE '%' || $1 || '%'` works, `'%?%'` says pq: got 1 parameters but the statement requires 0 – sescob27 Aug 09 '14 at 02:13
  • Can confirm: `WHERE name LIKE %?%` does not work. Results in `near "%": syntax error`. The answer is: `WHERE name LIKE '%'||?||'%'`. – 425nesp Mar 03 '15 at 04:50
  • Confirm WHERE name LIKE '%'||?||'%' works with sqlite3 – Andy Song Aug 15 '17 at 15:51
  • @AndySong . . . If you have a question about SQLite, then don't use an answer to a Postgres question. But, I do believe that it should work in SQLite. – Gordon Linoff Aug 16 '17 at 01:28
  • 2
    @Gordon Linoff, I was struggling of how to use LIKE and % in golang with sqlite3 and came across this post. Saved my day. – Andy Song Aug 16 '17 at 16:46
0

I guess this is the most correct way of doing this:

query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE CONCAT('%%',$1::text,'%%') ORDER BY p.rate DESC`

product_rows, err := db.Query(query, name)

if err != nil {
        return nil, err
}
Anton Yurchenko
  • 540
  • 4
  • 5
-1

Dont put qoutes when you are preparing your query. Just provide the value with qoutes and % sign. This will solve the problem. tried and tested.

Solution: query := SELECT p.id, p.name, p.description, p.price, p.image, p.rate FROM products AS p WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC

product_rows, err := db.Query(query, "'%" + name + "%'")

I got my soltion from this thread

Mohit
  • 7
  • 1
  • Just found out that the solution depends on the version of Golang because after upgrading golang to 1.10 my solution stopped working and I had to go for '%' || $1 || '%' solution. So do try different solutions to make things work – Mohit Sep 04 '19 at 03:49
  • 5
    This is probably vulnerable to [SQL injections](https://stackoverflow.com/questions/26345318/how-can-i-prevent-sql-injection-attacks-in-go-while-using-database-sql). – ranu Mar 05 '20 at 17:33
  • this is for sure a code vulnerable to SQL injection, please consider editing the answer – Yandry Pozo Aug 02 '22 at 16:08
-2
query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`

product_rows, err := db.Query(query, '%' + name+ '%'))
Julien Ricard
  • 316
  • 2
  • 11
-2

This works for me

query := "SELECT ProductId,Name,Description,Price,SKU FROM Products WHERE Name LIKE ?"
rows, err := r.db.QueryContext(ctx, query, "%"+name+"%")
user2259500
  • 9
  • 2
-3

According to this issue your query must not contain '%' sign, but "name" parameter must be quoted by '%'

query := `SELECT p.id, p.name, p.description, p.price, p.image, p.rate
    FROM products AS p
    WHERE LOWER(p.name) LIKE $1 ORDER BY p.rate DESC`

product_rows, err := db.Query(query, fmt.Sprintf("%%%s%%", name))
TheROX
  • 96
  • 13