Comments (8)
+1, I'm currently using weird work-arounds for IN support, also. Curious -- why expand to (?, ?, ?)
and renumber all parameters, instead of keeping the parameter number the same, and escaping each slice value in-place? With Postgres:
ids := []int{4, 17, 3}
name := "Rob"
executor.Select("SELECT * FROM Users WHERE Id IN $1 AND Name = $2", ids, name)
// SELECT * FROM TABLE WHERE Id IN (4, 17, 3) AND Name = 'Rob';
from gorp.
At least in MySQL, parameters are sent separately from the query. The client library does not do the escaping; the server does.
from gorp.
That does bring up the possibility of either:
- Implementing sql escaping for strings
- Limiting the IN (?) syntax to ids ints, which do not require escaping.
from gorp.
-1. I'm new to gorp but I've written numerous database abstraction layers in the past. Rewriting the user-provided query string would violate expectations of any users expecting Gorp to behave like other ORMs or database libraries, and it could reduce efficiency due to the inability to reuse pre-existing prepared statements. Implementing escaping in Go could also be less efficient and will result in challenges relating to character encodings. Using placeholders as they are designed to be used, and letting the underlying database libraries handle the details, will result in a cleaner and less surprising Gorp.
from gorp.
Your objections seem to be:
- The behavior would be surprising.
- Removes ability to use prepared statements
- Escaping in Go would be brittle and less efficient.
I'm not sure if you object to the overall idea or only the implementation suggested by @purohit -- in any case, I think none of those objections apply to the suggestion as originally posed (which does not involve SQL escaping in Go). I see no reason why the current behavior is less surprising than having IN (?) expand a slice parameter, there is no ability currently to use prepared statements (and Gorp could transparently add a prepared statement cache anyway), and the initial suggestion does not involve escaping in Go.
from gorp.
Objection #1 still applies to @coopernurse's idea as well as your original suggestion, but as I've thought about this more I'm thinking the productivity wins, sometimes-confusing error messages, and other minor issues would perhaps be worth it if the manipulation is implemented in database-specific Dialects rather than on all operations.
The origin of my concern is that it requires Gorp to become confident in manipulating the query string provided by the user for the interpretation of the database. Not all query strings look like "SQL" -- for example, ODBC drivers support bizarre operations and syntaxes, complex statements like stored procedure definitions can contain ?s unsuitable for replacement, numbered placeholders, named placeholders, Postgres array notation, embedded scripting languages could use ? as an operator, etc -- but the majority of these could be addressed if Gorp only attempts to perform these operations when they are not likely to cause issues. Implementing them on Dialect might be the right place. Another benefit of adding it to Dialect is that it becomes user-configurable.
Also, the database/sql driver Valuer interface allows slices of bytes. It is thus reasonable to allow passing a slice of bytes as a query parameter when writing queries that touch blobs. There may be other cases like this that would require special treatment, so marking the slices to expand with a wrapper might be the safest thing to do.
I agree that the prepared statement cache could be added safely later.
from gorp.
Sure, part of the Dialect makes sense. I agree that expecting to manipulate the query in all cases is too challenging. For example, in SQLite alone there appear to be 5 different types of placeholders:
http://www.sqlite.org/c3ref/bind_blob.html
On the other side of the spectrum, MySQL just requires a sequence of '?' to be generated, which is absolutely trivial and which I could be pretty confident would not go wrong.
With respect to "How do we know if it's meant to be a slice or a single valued []byte", we could say that a list-valued parameter has to have a type marker wrapping, e.g.
type List interface{}
dbm.Select(&objs, "select id, name from foo where id in (?)", gorp.List(ids))
Shrug. It may be too brittle to implement for more complicated placeholders. On the other side, making everyone implement their own seems like a shame.
from gorp.
+1 to the List wrapper.
Another option would be to support Gorp-specific markup that isn't specific to any dialect but would let the developer register handlers for well-defined tags. Example:
SELECT id, name FROM foo WHERE id IN ({{list "ids"}})
or even:
dbm.Exec("INSERT INTO t1 (c1, c2, c3) VALUES
{{values t}}}", [][]interface{}{{"a","b","c"},{"d","e","f"}})
... which would prepare the following placeholder-applicable statement:
INSERT INTO t1 (c1, c2, c3) VALUES
(?, ?, ?),
(?, ?, ?);
The template-based approach shifts the burden of syntactical correctness onto the developer, which might be less surprising and require less magic overall.
from gorp.
Related Issues (20)
- SqlServerDialect invalid create schema statement
- If there is a default value for the tag, it will always be inserted.
- v3 module naming causes error when requested HOT 3
- Is there a postgres read replicas support?
- Get without assertion HOT 2
- update fails to find table
- select on conflict HOT 2
- Object Mapper design/functionality?
- How about making a v3 branch the default branch? (suggestions for future mainenance) HOT 2
- Some naming violates "Go Code Review Comments (Initialisms)" HOT 2
- maybeExpandNamedQuery & driver.Valuer conflict HOT 1
- Support sql.IsolationLevel for transactions
- security: Add support for another column along with autoincrment column in where clause in update statement
- DbMap.Select fails when passed only one parameter *time.Time.
- Oracle: Create the table is not working
- context cannot be passed to DbMap.Begin
- per-transaction timeout interval setting in DbMap
- Snowflake support
- Issue with Parameter Info when execute QueryContext with parameter encode.
- CreateTablesIfNotExists() generates an error when using sql server driver
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from gorp.