5 ms·
> Querying SQL data involves constructing strings, and then – if you want to avoid SQL injection – successfully lining up placeholders between your pile of stri
by GenericsMotors 9y ago
> Querying SQL data involves constructing strings, and then – if you want to avoid SQL injection – successfully lining up placeholders between your pile of strings and your programming language. If you want to insert data, you’ll probably end up constructing the string (?,?,?,?,?,?,?,?) and counting arguments very carefully.
Uhm what?
At least in .NET+SQL Server land you can give your SQL queries named parameters. If there's a mismatch you'll get an exception saying so...
Perhaps this is a problem in some other stack (PHP?)
- lkuty 9y agoBTW we can use stored procedures in SQL which provides many benefits. It is often forgotten.
- knocte 9y agoHaha, no please, no business logic in the DB in this decade.
- Xylakant 9y agoInstead write a micro service on top of it that holds all the domain knowledge about how the data is structured in the storage layer? Stored procedures are not necessarily business logic, they can be an abstraction layer over the underlying structure, so yes, that can quite well make sense to place it in the DB.
- knocte 9y agolol, go back to your cave
- dotancohen 9y agoHow do you version control that? EDIT: To those mentioning migrations, note that reviewing migrations are like reviewing patches. Sometimes you want to not review a specific patch but rather look at the whole method, and `blame` a specific line or three to get an idea of what the previous developer's intention was. You can't get that type of overview from reviewing each patch or migration.
- matthewmacleod 9y agoI have a system with a lot of logic in the database / the schema is maintained with one of our Rails apps, using the traditional migration tools. It’s pretty awesome.
- mike-cardwell 9y agoNot the OP, but db migrations in git would be one obvious and simple method.
- camus2 9y agoDB migrations.
- GenericsMotors 9y agoIndeed! They are usually my preferred why of interacting with the DB when the queries are complex or hold a good amount of logic. Add user-defined table types used as parameters and you've got a good way to pass batches of data too.
- dotancohen 9y agoWhat about `WHERE foo IN (?,?,?)`. I usually let the language (PHP, Python) handle counting the arguments, but there is no (clean) way to do that with named parameters. And there is no option of using some parameters named and others anonymous, so if there is an `IN` anywhere in the query, then the entire query - and any variation of the query - must use anonymous parameters.
- GenericsMotors 9y ago> What about `WHERE foo IN (?,?,?)`. This is can be automatically handled if your ORM supports it; even a micro-ORM like Dapper does. Otherwise my go-to solution for this if it isn't supported is to pass the collection as a user-defined table type, filled with the values. you can either use WHERE IN or join on this table variable. EDIT: my perspective on this if from working with SQL Server and .NET. I don't know enough about Python or PHP development to comment on those.
- dotancohen 9y agoIf you're using an ORM then you don't worry about creating the prepared statement or SQL string yourself, so the entire issue is moot (See GP post).
- GenericsMotors 9y agoIt's not moot: Dapper doesn't auto-generate the query for you, it's just a thin layer over .NET's SqlClient to reduce boilerplate of converting to and from C# objects. You still have to write your SQL statements, and in your example you'd still be able to refer to your array by name: select SomethingID where AnotherThing in @yourNamedArrayParameter More here: https://github.com/StackExchange/Dapper#list-support https://github.com/StackExchange/Dapper#list-support If you're not using Dapper just use a user-defined table type to hold your data and join on it instead of using WHERE IN.
- dotancohen 9y ago
- Can_Not 9y agoPrepared statements in SQL libraries are standard almost everywhere and have been for a long time.