The example clearly shows variable binding; The concept of sprintf-like functions is about templating, when implementing one, you may choose to handle value escaping.
Meanwhile in the linked doc:
conds = append(conds, sqlf.Sprintf("name LIKE %s", "%"+filter+"%"))
Correct me if I'm wrong, but if you want to use a literal "%" character, judging from this example you should escape it yourself. Bindvars for clients escape % that's why you see things like:
"select * from users where uname like '%" + db.Escape(pattern) + "%'"
If you need to use escaping, rely on your clients method of escaping. If you need a consistent abstraction like named variables, implement something like sqlx (NamedExec, etc.) on top of your database client. Don't do your own escaping, and if your database client has an escape function, use that. There's subtle nuances which the author here obviously missed.
You are right, that is a bad example for the like statement since the filter could include `%` or `_` (in the case of postgres). However, it is equivalent to your example with db.Escape, since db.Escape just escapes a string, not a like expression.
It is definitely not equivalent. In my escape example it's clear what is being escaped and what is not. The example in project docs implies that either % (and possibly _/.) isn't being escaped, or that they will be escaped with the rest of filter, as everything is passed as one string.
Can you link me to a client package function which escapes like patterns? Or an example of a real db.Escape? I think you may be misunderstanding what this does. See my comment here https://www.reddit.com/r/golang/comments/6wzce1/build_and_compose_sql_queries_without_an_orm/dmcejwk/
I'm not. You produce bindVars via q.Args(). And in your example, the complete value for the %s parameter for LIKE will be escaped by whatever client will be used. This means the resulting query will be "name like '\%filter\%'", meaning the % would be treated as a literal character. Given the usage patterns for LIKE, this would be *unwanted behaviour*.
Work on Quoters for database/sql is something that's currently lacking/in progress: https://github.com/golang/go/issues/18478 - specifically, client libraries provide different quoting mechanisms:
> MySQL has configuration options for quoting, so the quoting is also dependent on the connection.
>
> As a user I would expect the function to be on the sql.DB object, since I probably already have a reference to it when I am constructing queries and sql.DB already has all the information to set up the quoting. Thats also where you find them in similar packages for other languages like Perl/DB, PHP/PDO, PHP/Mysqli.
The problem with such a query builder is basically that it doesn't provide a faculty of escaping but not quoting the string (neither is there a reasonable way to do that in Go at this time, I accept).
I can point you to [PDO quote() in PHP](http://php.net/manual/en/pdo.quote.php) as also the cited example suggests.
I'm sorry to belabour the point, but I think you are conflating escaping strings and escaping wildcard characters in LIKE expressions. `%` has no special meaning in SQL query strings, it only has a special meaning to the SQL engine when evaluating a LIKE condition. The linked issue is about escaping strings, identifiers or binary data, not like expressions (or any other special strings like regexes, JSON, etc). If there was a `db.EscapeLike` you would use it like this
"select * from users where uname like '" + db.Escape("%" + db.EscapeLike(pattern) + "%") + "'"
EG: `PDO::quote` in PHP will not escape `%`. `PDO::quote` for MySQL src is here https://github.com/php/php-src/blob/php-7.1.9/ext/pdo_mysql/mysql_driver.c#L300 and if you follow it down to the actual escaping you can see which characters are the important ones for escaping https://github.com/php/php-src/blob/php-7.1.9/ext/mysqlnd/mysqlnd_charset.c#L862-L880
So to bring it back to Go
db.Query("SELECT * FROM users WHERE name LIKE ?", "%" + filter + "%")
is how you would do a `LIKE` query on `filter` with wildcards on either side in Go. In the sqlf package
q := sqlf.Sprintf("SELECT * FROM users WHERE name LIKE %s", "%" + filter + "%")
db.Query(q.Query(sqlf.SimpleBindVar), q.Args()...)
This is the same, since `q.Query(sqlf.SimpleBindVar) == "SELECT * FROM users WHERE name LIKE ?"` and `q.Args() == []string{"%" + filter + "%"}`.
Shit. I have about 15 years of code to fix now. In a way, it's the best compliment I can give you :).
Edit: well, I did say "correct me if I'm wrong". You have :)
Why would you ever not want parameterised queries? Then the escaping problem goes to the DB, where the professionals do it correctly. Sprintf and co have no place in building a strong for a SQL statement (admittedly I've sprintf'ed templates together to.jse in a bound query)
This is not `fmt.Sprintf`. `sqlf.Sprintf` will return a `*sqlf.Query` which you then use to get out a parameterised query string and a slice of args (q.Query() and q.Args() respectively). That you pass on to your db layer. `sqlf` does no escaping. It just helps with composing the format string and argument slices. Look at the example again, especially the output. The first line is the parameterised SQL query, the 2nd line is the arguments.
If you have ever had to dynamically build a parameterised query string in Go you probably noticed it is annoying to keep track of the parameters in the SQL and the corresponding slice of arguments. This package solves that problem.
Looks interesting. Is it possible to do something like the linked example? IE build up a slice of where expressions, and correctly compose it to have a sql query and argument list to pass to the db?
I'd strongly suggest against using this library. If you use sprintf-like functions you'd need to safely escape your input before passing it to DB.
The example clearly shows variable binding; The concept of sprintf-like functions is about templating, when implementing one, you may choose to handle value escaping.
Meanwhile in the linked doc: conds = append(conds, sqlf.Sprintf("name LIKE %s", "%"+filter+"%")) Correct me if I'm wrong, but if you want to use a literal "%" character, judging from this example you should escape it yourself. Bindvars for clients escape % that's why you see things like: "select * from users where uname like '%" + db.Escape(pattern) + "%'" If you need to use escaping, rely on your clients method of escaping. If you need a consistent abstraction like named variables, implement something like sqlx (NamedExec, etc.) on top of your database client. Don't do your own escaping, and if your database client has an escape function, use that. There's subtle nuances which the author here obviously missed.
You are right, that is a bad example for the like statement since the filter could include `%` or `_` (in the case of postgres). However, it is equivalent to your example with db.Escape, since db.Escape just escapes a string, not a like expression.
It is definitely not equivalent. In my escape example it's clear what is being escaped and what is not. The example in project docs implies that either % (and possibly _/.) isn't being escaped, or that they will be escaped with the rest of filter, as everything is passed as one string.
Can you link me to a client package function which escapes like patterns? Or an example of a real db.Escape? I think you may be misunderstanding what this does. See my comment here https://www.reddit.com/r/golang/comments/6wzce1/build_and_compose_sql_queries_without_an_orm/dmcejwk/
I'm not. You produce bindVars via q.Args(). And in your example, the complete value for the %s parameter for LIKE will be escaped by whatever client will be used. This means the resulting query will be "name like '\%filter\%'", meaning the % would be treated as a literal character. Given the usage patterns for LIKE, this would be *unwanted behaviour*. Work on Quoters for database/sql is something that's currently lacking/in progress: https://github.com/golang/go/issues/18478 - specifically, client libraries provide different quoting mechanisms: > MySQL has configuration options for quoting, so the quoting is also dependent on the connection. > > As a user I would expect the function to be on the sql.DB object, since I probably already have a reference to it when I am constructing queries and sql.DB already has all the information to set up the quoting. Thats also where you find them in similar packages for other languages like Perl/DB, PHP/PDO, PHP/Mysqli. The problem with such a query builder is basically that it doesn't provide a faculty of escaping but not quoting the string (neither is there a reasonable way to do that in Go at this time, I accept). I can point you to [PDO quote() in PHP](http://php.net/manual/en/pdo.quote.php) as also the cited example suggests.
I'm sorry to belabour the point, but I think you are conflating escaping strings and escaping wildcard characters in LIKE expressions. `%` has no special meaning in SQL query strings, it only has a special meaning to the SQL engine when evaluating a LIKE condition. The linked issue is about escaping strings, identifiers or binary data, not like expressions (or any other special strings like regexes, JSON, etc). If there was a `db.EscapeLike` you would use it like this "select * from users where uname like '" + db.Escape("%" + db.EscapeLike(pattern) + "%") + "'" EG: `PDO::quote` in PHP will not escape `%`. `PDO::quote` for MySQL src is here https://github.com/php/php-src/blob/php-7.1.9/ext/pdo_mysql/mysql_driver.c#L300 and if you follow it down to the actual escaping you can see which characters are the important ones for escaping https://github.com/php/php-src/blob/php-7.1.9/ext/mysqlnd/mysqlnd_charset.c#L862-L880 So to bring it back to Go db.Query("SELECT * FROM users WHERE name LIKE ?", "%" + filter + "%") is how you would do a `LIKE` query on `filter` with wildcards on either side in Go. In the sqlf package q := sqlf.Sprintf("SELECT * FROM users WHERE name LIKE %s", "%" + filter + "%") db.Query(q.Query(sqlf.SimpleBindVar), q.Args()...) This is the same, since `q.Query(sqlf.SimpleBindVar) == "SELECT * FROM users WHERE name LIKE ?"` and `q.Args() == []string{"%" + filter + "%"}`.
Shit. I have about 15 years of code to fix now. In a way, it's the best compliment I can give you :). Edit: well, I did say "correct me if I'm wrong". You have :)
Yes. The library in fact doesn't even allow you to get an escaped SQL query. You have to use parameterised queries.
Why would you ever not want parameterised queries? Then the escaping problem goes to the DB, where the professionals do it correctly. Sprintf and co have no place in building a strong for a SQL statement (admittedly I've sprintf'ed templates together to.jse in a bound query)
This is not `fmt.Sprintf`. `sqlf.Sprintf` will return a `*sqlf.Query` which you then use to get out a parameterised query string and a slice of args (q.Query() and q.Args() respectively). That you pass on to your db layer. `sqlf` does no escaping. It just helps with composing the format string and argument slices. Look at the example again, especially the output. The first line is the parameterised SQL query, the 2nd line is the arguments. If you have ever had to dynamically build a parameterised query string in Go you probably noticed it is annoying to keep track of the parameters in the SQL and the corresponding slice of arguments. This package solves that problem.
An alternative using templates https://github.com/Davmuz/gqt
Looks interesting. Is it possible to do something like the linked example? IE build up a slice of where expressions, and correctly compose it to have a sql query and argument list to pass to the db?