Primary reason not to do SQL was SQL Injection and wrong way devs write SQL. That's why we shifted out from SQL to ORM, but for high performance queries, we ended up doing that in SQL. This creates testing nightmare.
Mocking is hard, creating unique instance of DB, seeding it, running 1000s of tests against it in parallel, all of that is hard, I never said it is impossible. It becomes terribly slow. I have created the tool to do it and we are doing it, but it is too slow compared to ORM's with in memory mocking.
"Primary reason not to do SQL was SQL Injection..."
That strikes me as like saying "We do not use the Math library, because devs keep using division instead of multiplication"
All languages are a tool for getting stuff done - use a tool intelligently and you get intelligent results. Use a tool poorly and you get a poor result. I don't see how SQL is any different to any other tool in that regard ?
For one man army with knowledge of SQL Injection, your argument is solid, but if you have 100s of developers, avoiding SQL Injection without ORM is impossible unless you have time to review every query ever written.
Akash I really think that a basic crash course(or even a youtube video link) to 100s of developers on how to avoid SQL injections (eg by using say Prepared Statements ) is better than using a less optimized solution.
Also, it helps the developers become better at their craft and you would be helping their growth in their careers.
Have you ever had a team of 100 backend engineers working on an API without each of them having to be in sub-teams with each team having a team lead before?
My argument is simple. It is the role of the team lead or more senior guys on a team to ensure that SQL injection the most basic error while writing SQL does not happen. If they can't ensure that then the app would be buggy anyways
Serious question: Why are there 100 unique developers writing queries directly against the one same database, whether via SQL plaintext, query builders or even ORMs?
I see only two scenarios:
They are all reimplementing the same tiny API over and over again. This API should be owned by one team and provided as a library, network endpoint, or both.
The database has grown too complex and has long become the bottleneck for development by such a large number of people. It should be split so that teams own their schemas (without necessarily having access to production data in these schemas)
I'm a dev with a strong *NIX sysadmin background. I've been programming for 20+ years, started with IRC scripts, C, Python, PHP, Ruby/Rails, Node/JS, Go and Elxir. Full time on Ruby,Elixir and Rust.
You can use database wrappers rather than full ORMs. They let you to write queries in your programming language and then “translate it”” into the native db syntax. Elixir Ecto is an excellent example of this technique (or at least, the best I know of). I prefer this to, for example, Ruby’s ActiveRecord/ActiveModel.
I'm a dev with a strong *NIX sysadmin background. I've been programming for 20+ years, started with IRC scripts, C, Python, PHP, Ruby/Rails, Node/JS, Go and Elxir. Full time on Ruby,Elixir and Rust.
I'm sorry, but I disagree. An ORM is something different from a database wrapper.
An ORM is mostly peculiar of OOP languages, because it represents your database (tables, columns, etc...) in form of objects and they are very tighted between eah other. If your library also generates queries for you, that is a plus.
A database wrapper is simpler and more generic. I used a bad explanation in my last comment but the gist is that I use it to communicate with a db in a different way than an ORM. For example, I could write my query manually, then the library wraps/sanitizes it before talking with the db. How I map the result to my language data structure is a different problem, just like how it might generate a query for me.
SQL injection is caused by underuse of SQL, not overuse of SQL.
If the logic was parameterized stored procedures in the DB, not queries string-built on the application server, there wouldn't be a vulnerability.
In most industries, it's ridiculous to allow arbitrary query access to production database.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
Primary reason not to do SQL was SQL Injection and wrong way devs write SQL. That's why we shifted out from SQL to ORM, but for high performance queries, we ended up doing that in SQL. This creates testing nightmare.
If you put SQL in the db it might be hard to test but if it's in code it shouldn't be hard to test. Thanks!
Mocking is hard, creating unique instance of DB, seeding it, running 1000s of tests against it in parallel, all of that is hard, I never said it is impossible. It becomes terribly slow. I have created the tool to do it and we are doing it, but it is too slow compared to ORM's with in memory mocking.
Thinking of tesing with databae is hard, mocking it or at max using a in memory db is the better approach IMHO.
"Primary reason not to do SQL was SQL Injection..."
That strikes me as like saying "We do not use the Math library, because devs keep using division instead of multiplication"
All languages are a tool for getting stuff done - use a tool intelligently and you get intelligent results. Use a tool poorly and you get a poor result. I don't see how SQL is any different to any other tool in that regard ?
For one man army with knowledge of SQL Injection, your argument is solid, but if you have 100s of developers, avoiding SQL Injection without ORM is impossible unless you have time to review every query ever written.
I can only speak from my area of expertise (Oracle) but in that instance, a single query will tell me where SQL injection risk points are.
connor-mcdonald.com/2016/05/30/sql...
Akash I really think that a basic crash course(or even a youtube video link) to
100s of developerson how to avoid SQL injections (eg by using say Prepared Statements ) is better than using a less optimized solution.Also, it helps the developers become better at their craft and you would be helping their growth in their careers.
Try it with 100 developers and let me know if all of them follow it correctly !!
Have you ever had a team of 100 backend engineers working on an API without each of them having to be in sub-teams with each team having a team lead before?
My argument is simple. It is the role of the team lead or more senior guys on a team to ensure that
SQL injectionthe most basic error while writing SQL does not happen. If they can't ensure that then the app would be buggy anywaysSerious question: Why are there 100 unique developers writing queries directly against the one same database, whether via SQL plaintext, query builders or even ORMs?
I see only two scenarios:
You can use database wrappers rather than full ORMs. They let you to write queries in your programming language and then “translate it”” into the native db syntax. Elixir Ecto is an excellent example of this technique (or at least, the best I know of). I prefer this to, for example, Ruby’s ActiveRecord/ActiveModel.
My 2 cents
You can call database wrapper or full ORMs, but it is basically ORM of different sort when you don't write sql directly.
I'm sorry, but I disagree. An ORM is something different from a database wrapper.
An ORM is mostly peculiar of OOP languages, because it represents your database (tables, columns, etc...) in form of objects and they are very tighted between eah other. If your library also generates queries for you, that is a plus.
A database wrapper is simpler and more generic. I used a bad explanation in my last comment but the gist is that I use it to communicate with a db in a different way than an ORM. For example, I could write my query manually, then the library wraps/sanitizes it before talking with the db. How I map the result to my language data structure is a different problem, just like how it might generate a query for me.
SQL injection is caused by underuse of SQL, not overuse of SQL.
If the logic was parameterized stored procedures in the DB, not queries string-built on the application server, there wouldn't be a vulnerability.
In most industries, it's ridiculous to allow arbitrary query access to production database.