Go Postgresql Like Query

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

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.%%'"))

How to parametrize a dynamic query in Go

Figured it out, rough untested example of how I ended up doing it in case anyone else runs into this.

 var counter int = 1
 var parameters []interface{}

 for _, d:= range data{
    if counter != 1 {
        fullQuery = fullQuery + " UNION "
    }
    fullQuery = fullQuery + fmt.Sprintf(`
SELECT 
    price_`+fmt.Sprint(d.type)+` as price,                
  FROM products
  WHERE products.id = ANY($%v) and products.store= $%d
  
`, counter, counter+1)
   counter+=2
   parameters = append(parameters, pq.Array(d.ids), d.store)

}

err := sqlx.Select(db, &dataStruct, fullQuery, parameters...)

Will still need to validate column names prior to querying to prevent injection.

SQL LIKE condition to check for integer?

That will select (by a regex) every book which has a title starting with a number, is that what you want?

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

Go and IN clause in Postgres

Pre-building the SQL query (preventing SQL injection)

If you're generating an SQL string with a param placeholder for each of the values, it's easier to just generate the final SQL right away.

Note that since values are strings, there's place for SQL injection attack, so we first test if all the string values are indeed numbers, and we only proceed if so:

tags := []string{"1", "2", "3"}
buf := bytes.NewBufferString("SELECT COUNT(id) FROM tags WHERE id IN(")
for i, v := range tags {
    if i > 0 {
        buf.WriteString(",")
    }
    if _, err := strconv.Atoi(v); err != nil {
        panic("Not number!")
    }
    buf.WriteString(v)
}
buf.WriteString(")")

Executing it:

num := 0
if err := Db.QueryRow(buf.String()).Scan(&num); err != nil {
    log.Println(err)
}

Using ANY

You can also use Postgresql's ANY, whose syntax is as follows:

expression operator ANY (array expression)

Using that, our query may look like this:

SELECT COUNT(id) FROM tags WHERE id = ANY('{1,2,3}'::int[])

In this case you can declare the text form of the array as a parameter:

SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])

Which can simply be built like this:

tags := []string{"1", "2", "3"}
param := "{" + strings.Join(tags, ",") + "}"

Note that no check is required in this case as the array expression will not allow SQL injection (but rather will result in a query execution error).

So the full code:

tags := []string{"1", "2", "3"}

q := "SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])"
param := "{" + strings.Join(tags, ",") + "}"

num := 0
if err := Db.QueryRow(q, param).Scan(&num); err != nil {
    log.Println(err)
}

Parameter with pattern matching syntax in a PostgreSQL query

Your syntax is wrong, try

user := "%"+user+"%"

rows, err := Db.Query("SELECT * FROM table where name like $1", user)

if err!=nil{
    fmt.Println(err)
}

What am I not getting about Go sql query with variables?

It looks like you are using https://github.com/lib/pq based on the error message and it's docs say that

pq uses the Postgres-native ordinal markers, as shown above

I've never known a database engine that allows the parameterized values in anything other than values. I think you are going to have to resort to string concatenation. I don't have a Go compiler available to me right now, but try something like this. Because you are inserting the table name by concatination, you need it sanitized. pq.QuoteIdentifier should be able to help with that.

func SelectAll (table string) {
    db, err := sql.Open("postgres","user=postgres dbname=mydb sslmode=disable")

        if err != nil {
                 fmt.Println(err)
        }

        defer db.Close()

        table = pq.QuoteIdentifier(table)
        rows, err := db.Query(fmt.Sprintf("SELECT * FROM %v", table))

        if err != nil {
                fmt.Println(err)
        } else {

                PrintRows(rows)
        }

}

EDIT: Thanks to hobbs to pointing out pq.QuoteIdentifier

---

Related Topics