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 — Snowman, Jun 02 '16 at 21:56

score 5 · Accepted Answer · edited Jun 20 '20 at 09:12

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 '20 at 09:12

Community

1
1

answered Jun 02 '16 at 22:01

Darigaaz

1,414
10
11

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. – trueCamelType Jun 02 '16 at 22:10
ok, I needed to type cast ```mycolumn```. So changing the query to ```SELECT * FROM mytable WHERE mycolumn::text LIKE 'camel.%'``` – trueCamelType Jun 02 '16 at 22:13
1

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.%")` – eduncan911 Jun 03 '16 at 14:00
You are right, but question was about something else) – Darigaaz Jun 03 '16 at 16:38

score 0 · Answer 2 · answered Apr 02 '18 at 13:56

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 02 '18 at 13:56

QuarK

2,306
1
20
24

score 0 · Answer 3 · answered Apr 21 '18 at 22:17

0

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

answered Apr 21 '18 at 22:17

Medone

127
1
3
11

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

3 Answers3