Go postgresql LIKE query

Question

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
}

OneOfOne · Accepted Answer · 2014-08-09 02:17:17Z

54

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;

edited Aug 9, 2014 at 2:17

OneOfOne

100k22 gold badges195 silver badges187 bronze badges

answered Aug 9, 2014 at 1:42

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

sescob27 Over a year ago

now it says: pq: got 1 parameters but the statement requires 0 as if it doesn't recognize the $1

mu is too short Over a year ago

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.

sescob27 Over a year ago

LIKE '%' || $1 || '%' works, '%?%' says pq: got 1 parameters but the statement requires 0

425nesp Over a year ago

Can confirm: WHERE name LIKE %?% does not work. Results in near "%": syntax error. The answer is: WHERE name LIKE '%'||?||'%'.

Andy Song Over a year ago

@Gordon Linoff, I was struggling of how to use LIKE and % in golang with sqlite3 and came across this post. Saved my day.

|

Anton Yurchenko · Accepted Answer · 2022-01-18 16:17:42Z

1

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
}

answered Jan 18, 2022 at 16:17

Anton Yurchenko

6195 silver badges5 bronze badges

Comments

Mohit · Accepted Answer · 2019-08-30 08:16:39Z

-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

answered Aug 30, 2019 at 8:16

Mohit

71 bronze badge

3 Comments

Mohit Over a year ago

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

ranu Over a year ago

This is probably vulnerable to SQL injections.

Yandry Pozo Over a year ago

this is for sure a code vulnerable to SQL injection, please consider editing the answer

user2259500 · Accepted Answer · 2022-02-15 12:51:35Z

-1

This works for me

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

answered Feb 15, 2022 at 12:51

user2259500

192 bronze badges

3 Comments

Yandry Pozo Over a year ago

this code is vulnerable to SQL injections, please consider editing the answer

mr.vea Dec 13, 2024 at 21:31

@YandryPozo can you expand on that? Does adding % breaks Parameterized query?

Yandry Pozo Jul 22 at 20:50

it's not about the %, it's about concating a variable to the query

Julien Ricard · Accepted Answer · 2021-01-11 15:56:27Z

-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+ '%'))

answered Jan 11, 2021 at 15:56

Julien Ricard

3242 silver badges11 bronze badges

Comments

TheROX · Accepted Answer · 2018-11-06 08:18:48Z

-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))

answered Nov 6, 2018 at 8:18

TheROX

1061 silver badge13 bronze badges

1 Comment

Dariush Abbasi Over a year ago

Its unsafe, sqlinjection can happen in it

Collectives™ on Stack Overflow

Go postgresql LIKE query

6 Answers 6

7 Comments

Comments

3 Comments

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

7 Comments

Comments

3 Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related