4 ms·
Stored procedures are really not used enough. They’re incredibly useful, and not just for security. People can make out-of-band updates to the query, like maki
by ed25519FUUU 6y ago
Stored procedures are really not used enough. They’re incredibly useful, and not just for security.
People can make out-of-band updates to the query, like making it more efficient or migrating it, without requiring any changes to the all.
- doctor_eval 6y agoWhen used for business logic they also execute about 20x faster than the same logic encoded in a client, and in far fewer LOC. Getting rid of all those round-trips has a huge effect!
- koolba 6y agoRunning a slow hash operation that should take a non-negligible amount of CPU on your single core per process database server isn’t a great idea. That’s not a rag on stored procedures more generally. Just that specific use case scales poorly to a large number of operations as its inherently cpu bound.
- tpetry 6y agoYou can do the slow pbkdf2 hash on your webserver and use the result for your stored procedure to check the password. If the password column is only visible to the stored procedure you have used the best features of both systems.
- koolba 6y agoNo you can’t. To do the hash you need the per user salt before you start hashing. Which would require reading from the DB.
- mvolfik 6y agoThen you can retrieve salt, do hash on server, and use the stored procedure to compare. More requests, but a tradeoff for security \o/
- tpetry 6y agoThere is no reason you can‘t have a deterministic function which generates a hash for the user. A simple sha256(lower(email)) is equally secure as a complete random salt, the only requirement on a salt is to be unique.
- cooperaustinj 6y agoI agree with the password example. I don't agree with people updating queries willy-nilly. Updated queries should go through the same life-cycle of testing (etc) as code, meaning they can simply go out with continuous application deployments anyways.
- citrin_ru 6y agoI have an impression that many developers actively avoid stored procedures, trigger or any 'complex' database features because they don't fit well into a typical CI/CD pipeline which deals only with application code. It is quite possible to test stored procedures, it's just requires some additional work/infrastructure.
- fmajid 6y agoIt's mostly a familiarity and education problem, as well as the fact MySQL didn't have good support for this. Developers are exposed to procedural programming languages at university, but seldom to databases, let alone stored procedures. Unit tests can be written in SQL or PL/PGSQL and will typically run much faster than a CI pipeline.