Functional indexes for faster queries (10 examples)

[davidktw]

SQL is a native language for controlling RDBMS, if you don't know enough about it, it's only going to be your lost. ORM is not going to save your day, SQL is.

 

[Trader11]

ORM is for reducing chance of SQL Injection

 

[davidktw]

ORM is for reducing chance of SQL Injection

BS. SQL already solved(not even reduce) this problem with Parameter Bindings.

https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

https://blog.saeloun.com/2019/10/28...a way,Preventing injection attacks by quoting

Parameter Binding is not the same query escaping technique before it.

One thing both techniques cannot solve is incompetence.
Personally I will NEVER consider a software developer knows RDBMS unless he/she knows SQL well. Learning SQL and learning it more than just the simplistic CRUD operations exposes the person to the real power of RDBMS because SQL is just a command language, what a software developer really need to understand is how the data is manipulated and processed within the database so that you know why you want to design it that way.

The video I just shared is just a thin flake of icing on top of the iceberg of understanding RDBMS. But even that thin flake will bring you further than abstracting yourself away from the RDBMS using ORM.

One is in flavour of exposing you to RDBMS technologies, the other is in flavour of abstracting you from RDBMS. It is a fallacy thinking that you will keep changing from one database type to another across projects and really across all the different variants of RDBMS, they are not all so different. ORM simply add on quite a fair bit of complexity and overheads to give you this abstraction which most of the time you don’t even need it. How often your projects jump between databases and how often you uses database specific features for more than 80% of your work accessing the database when using SQL?

If you do have a project that uses multiple variants of RDBMS which in my opinion is rare, a simple DAO design that uses polymorphism and factory design would solve abstract the problem from the rest of the code.

 

[Trader11]

BS. SQL already solved(not even reduce) this problem with Parameter Bindings.

https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html

https://blog.saeloun.com/2019/10/28/bind-parameters-in-activerecord-sql-queries/#:~:text=Bind parameters are a way,Preventing injection attacks by quoting

Parameter Binding is not the same query escaping technique before it.

One thing both techniques cannot solve is incompetence.
Personally I will NEVER consider a software developer knows RDBMS unless he/she knows SQL well. Learning SQL and learning it more than just the simplistic CRUD operations exposes the person to the real power of RDBMS because SQL is just a command language, what a software developer really need to understand is how the data is manipulated and processed within the database so that you know why you want to design it that way.

The video I just shared is just a thin flake of icing on top of the iceberg of understanding RDBMS. But even that thin flake will bring you further than abstracting yourself away from the RDBMS using ORM.

One is in flavour of exposing you to RDBMS technologies, the other is in flavour of abstracting you from RDBMS. It is a fallacy thinking that you will keep changing from one database type to another across projects and really across all the different variants of RDBMS, they are not all so different. ORM simply add on quite a fair bit of complexity and overheads to give you this abstraction which most of the time you don’t even need it. How often your projects jump between databases and how often you uses database specific features for more than 80% of your work accessing the database when using SQL?

If you do have a project that uses multiple variants of RDBMS which in my opinion is rare, a simple DAO design that uses polymorphism and factory design would solve abstract the problem from the rest of the code.

Yes. But there are many careless developers who don't use parameter bindings. They just concatenate the queries with user inputs. So it's not solved yet!

 

[davidktw]

Yes. But there are many careless developers who don't use parameter bindings. They just concatenate the queries with user inputs. So it's not solved yet!

It is solved. What is not solved as I have already mentioned is Incompetence. What you have mentioned is incompetence in the engineers to choose the tool. It is a solved problem to protect your home with locks to most petty burglars, but it is incompetence if one doesn’t lock the door.

 

[Trader11]

It is solved. What is not solved as I have already mentioned is Incompetence. What you have mentioned is incompetence in the engineers to choose the tool. It is a solved problem to protect your home with locks to most petty burglars, but it is incompetence if one doesn’t lock the door.

I would like my developers (even the worst) to be protected by default. Like the new JS framework like REACT have default protection against XSS unless the developers do something special

Fewer XSS bugs appear in applications built with modern web frameworks. These frameworks steer developers towards good security practices and help mitigate XSS by using templating, auto-escaping, and more. That said, developers need to be aware of problems that can occur when using frameworks insecurely such as: [LIST] [*][I]escape hatches[/I] that frameworks use to directly manipulate the DOM [*]React’s dangerouslySetInnerHTML without sanitising the HTML [*]React cannot handle javascript: or data: URLs without specialized validation [*]Angular’s bypassSecurityTrustAs* functions [*]Template injection [*]Out of date framework plugins or components [*]and more [/LIST]

 

[davidktw]

I would like my developers (even the worst) to be protected by default. Like the new JS framework like REACT have default protection against XSS unless the developers do something special

Fewer XSS bugs appear in applications built with modern web frameworks. These frameworks steer developers towards good security practices and help mitigate XSS by using templating, auto-escaping, and more. That said, developers need to be aware of problems that can occur when using frameworks insecurely such as: [LIST] [*][I]escape hatches[/I] that frameworks use to directly manipulate the DOM [*]React’s dangerouslySetInnerHTML without sanitising the HTML [*]React cannot handle javascript: or data: URLs without specialized validation [*]Angular’s bypassSecurityTrustAs* functions [*]Template injection [*]Out of date framework plugins or components [*]and more [/LIST]

Build your framework, build your own components, build your own product. That is the way to show real concrete implementations how things should be done. The less you touch the internals, the less you need to address mistakes in them.

There are low code design patterns you can use to build your own stuffs. With the right design, you can abstract a lot of such things at much higher level than spring boot, react, or what not frameworks. These frameworks are useful, but it doesn’t mean all your developers need to work at this level. You can assign good ones to be working at this level, really good ones to be touching the database, and those less competent ones to be using APIs designed in the system.

Ask yourself a simple question, if one developer did something well, is the same developer going to be writing the same thing again and again for you? If not, why should the next developer do the same thing as good or better? Also ask yourself, how many times do you want to do the same thing again and again, go thru the same of UAT, Pentest, Perftest, just to find that the same mistake occur again? Why not give yourself the assurance by not doing the same thing again? Spend the effort in building new stuffs, enhancing existing stuffs, and not building the same stuff again and again.

The best learning experience is seeing something that works, not reading up tons of articles. It is simple to tell you why a metal plane will fly, but it will be eye opening showing you one that will fly.

Anything else is pointless.
In any case, the choice is yours.