What's wrong with my Postgres query inside of golang (LIKE matching)

Question

I've seen a very similar problem here, but I'm not certain what the pipes do in the command, and it didn't work for me anyway.

So, here's the code I've tried.

rows, err := db.Query(fmt.Sprintf("SELECT * FROM mytable WHERE mycolumn LIKE 'camel.%'"))

And even though, I have no idea what it's for, I also tried with the pipes.

rows, err := db.Query(fmt.Sprintf("SELECT * FROM mytable WHERE mycolumn LIKE 'camel. || %'"))

So, what it should be doing, is matching anything in that column that starts with camel., so camel.*

The error I'm getting for both examples is

pq: syntax error at or near "("

So i'm guessing for some reason it's passing in more of that line as the command than I would like....maybe a quote problem? I've tried a few other things, but nothing has worked. Any help is appreciated.

Can we test a hunch? Try: rows, err := db.Query(`SELECT * FROM mytable WHERE mycolumn LIKE 'camel.%'`) and see if that works better — InkyDigits
– InkyDigits, Commented Jun 2, 2016 at 21:56

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

func Sprintf(format string, a ...interface{}) string

Sprintf formats according to a format specifier and returns the resulting string.

mt.Println(fmt.Sprintf("SELECT * FROM mytable WHERE mycolumn LIKE 'camel%'"))

//Output:
//SELECT * FROM mytable WHERE mycolumn LIKE 'camel%!'(MISSING)
//Ofc postgres will complain

You do not need fmt.Sprintf in this case.

rows, err := db.Query("SELECT * FROM mytable WHERE mycolumn LIKE 'camel.%'")

works fine.

But if you really need to use fmt.Sprintf you must escape '%' with '%%'

rows, err := db.Query(fmt.Sprintf("SELECT * FROM mytable WHERE mycolumn LIKE 'camel.%%'"))

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jun 2, 2016 at 22:01

Darigaaz

1,50010 silver badges12 bronze badges

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

4 Comments

praiseHellRaiseDale Over a year ago

When I use either of those examples, I get pq: operator does not exist: inet ~~ unknown. Looking into that error now, just figured I'd respond first.

praiseHellRaiseDale Over a year ago

ok, I needed to type cast mycolumn. So changing the query to SELECT * FROM mytable WHERE mycolumn::text LIKE 'camel.%'

eduncan911 Over a year ago

Come on... Do NOT use any formatting to build a query string! Flashbacks of the old days of SQL Injection come back to haunt us. Use your SQL package's parameter assignments when running the query, so they are properly escaped. rows, err := db.Query("SELECT * FROM mytable WHERE mycolumn LIKE (?)", "camel.%")

Darigaaz Over a year ago

You are right, but question was about something else)

QuarK · Accepted Answer · 2018-04-02 13:56:32Z

0

You should use Query using prepared statements for security, you can concat using CONCAT :

rows, err := db.Query("SELECT * FROM mytable WHERE mycolumn LIKE CONCAT(?, '%')", camel)

Hope it helps!

answered Apr 2, 2018 at 13:56

QuarK

2,5561 gold badge22 silver badges25 bronze badges

Comments

Medone · Accepted Answer · 2018-04-21 22:17:53Z

0

you can use the LIKE '%' || camel. || '%'

answered Apr 21, 2018 at 22:17

Medone

1271 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

What's wrong with my Postgres query inside of golang (LIKE matching)

3 Answers 3

4 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Linked

Related