Hmm. I'm going to have to disagree about Stored Procedures providing security. You can do all sorts of bad things using stored procedures that may result in unintended code execution!

Perhaps the most naive example: https://pastebin.com/acQqhDvy

I think they're more useful for organization and abstraction than security. Then again, a well organized and smartly abstracted system can lead to better security!

But I think bind parameters are probably a better example of security.

Binding effectively separates the data from the logic. So you define two separate types of things, and then safely join those things together by binding them. It doesn't matter too much whether that happens in the application making a call to the database or in the database in a stored procedure. Obviously this same concept can be applied at many different points along the application stack. The analogous concept in the UI is templating. You define a template and then safely inject data into that template.

> I finally figured out it was because a stored procedure does exactly what the grandparent post says: It treats all inputs as data with no possibility to run as code.

This isn't well defined. Take this pseudocode stored procedure (OK, it's a python function):

    def retrieve_relevant_data(user_input):
        if user_input == 1:
            return BACKING_STORE[5]
        elif user_input == 2:
            perform_side_effects()
            return BACKING_STORE[1]
        else:
            return "Go away."

You can provide any input to that. You could think of this as a function which "treats all input as data with no possibility to run as code" (it never calls eval!). But you could also usefully think of this as defining a tiny virtual machine with opcodes 1 and 2. If you think of it that way, you'll be forced to conclude that it does run user input as code, but the difference is in how you're labeling the function, not in what the function does.

The security gain from a stored procedure, on this analysis, is not that it won't run user input as code. It will! The security gain comes from replacing the full capability of the database ("run code on your local machine") with the smaller, whitelisted set of capabilities defined in the stored procedure.

Parameter-ized query builders are possible in every SQL library.

String escaping SQL? How is anyone thinking that is still a thing in 2017? The problem has been solved for two decades

Stored procedures are bad in so many ways - they harder to deploy and revert than code, harder to unit test* , harder to refactor and every implementation that I have ever seen that has business logic in stored procedures instead of microservices/packages/modules have been a nightmare to maintain.

* At least with .Net/Entity Framework/Linq you mock out your dbcontext and test your queries with an in memory List<>

https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113)....

Yeah, I thought the same thing until I found a colleague who was very fond of calling exec_sql in stored procedures, with the argument being a concatenation of the sp arguments.

I think you mean parameterised queries. Stored procedures are a slightly different thing.