Ask HN: Do you use stored procedures for CRUD operations in your database? • Hacker News Board

Re: Ask HN: Do you use stored procedures for CRUD operations in your database?

#11

Post by foobarbaz33 » Sun, Dec 17, 2023, 3:51 AM UTC

> doesn't allow SQL injection

Here's an injection vulnerable stored proc call.

    string badSql = "exec fooProc " + arg;

User input need to be parameterized whether you are calling a stored proc or not.

Re: Ask HN: Do you use stored procedures for CRUD operations in your database?

#12

Post by max_hammer » Sun, Dec 17, 2023, 11:25 AM UTC

I work as data engineer and won't advise using stored procedures.

It's pain to debug and document.

Re: Ask HN: Do you use stored procedures for CRUD operations in your database?

#13

Post by kasey_junk » Mon, Dec 18, 2023, 8:38 PM UTC

This was a much more common practice when the architecture pattern was to have many applications accessing (and writing) to the same db. These days that’s pretty uncommon so the development overhead isn’t usually worth it.

That said, I have had use recently to split out which tables can be written by which applications and then abstracting the selects into views.

Re: Ask HN: Do you use stored procedures for CRUD operations in your database?

#14

Post by ensocode » Tue, Dec 19, 2023, 8:53 AM UTC

It probably depends on the use case. We usually try to avoid stored procedures as it is in-transparent, not really easy to debug and finally it is business logic in the data store which isn't a good approach in my perspective.