Can you explain exactly where these savings will come from? And how this leads to better quality than reviewing SQL in your code?
In some specific projects where I worked, there were a generic "Framework" written this way. This framework provided "unload data from table" functionality to downstream consumer applications, warehouse, data marts etc. and "execute procedure" functionality to load data from external table.
The framework provided
1. error handling
2. Header and footer verification
3. copy files from source server and send to target server functionality
4. email on error
6. file writing etc
and may more useful functionalities. So,as a dev, I only needed to create views and add this view name and other configs to table, like target location on destination server. Then, the common scheduler will kick off and run all the views in config table, write to file and send it to destination handling any errors.
Similarly, for loading a file into table, i just had to create a procedure which repoints an external table to file and loads it.
As a dev, i didnt have to write or reuse the code so my workload was less. For testers, they didnt have to test all those functionalities which was provided by framework(which would otherwise had to be tested even if i reuse the code). For the organization, it meant less work and more saving. And for architect, every batch job followed same pattern and so no technical debt and improved quality of delivery.
This was win-win for everyone with very little risk of security.
January 16, 2020 - 2:19 pm UTC
I see what you're getting at. I still think you're underestimating the risks.
This site lists known SQL injection breaches: https://codecurmudgeon.com/wp/sql-injection-hall-of-shame/
I count 37 for last year and 3 for 2020 already! There's almost certainly many more that haven't been publicly disclosed (yet).which would otherwise had to be tested even if i reuse the code
All your logging, error handling, etc. should be standard (whether from a 3rd party framework or homegrown). Testers only need to explicitly check this stuff if you've changed it.