SQL queries not in one place but assembled dynamically while passing through several system layers. Is this a good practice?

Question

I personally find that makes the life of a developer who has recently joined the project a very sad one. Without almost perfect understanding of the framework mechanisms it makes developing and debugging like a torture, since whenever I get an error or an unexpected behavior I have not a slightest idea where to look. In some rare cases Ctrl+F through the solution helps, but in most cases I either have to ask the senior guy or use try & error approach. In many cases there is no way to test whether it's working save and nice, testers and sadly customers have to check it.

I think that putting queries in stored procedures or at least in one place in code could help.

Is this dynamic approach to queries a standard practice in business applications? I'm personally quite discomfortable with it.

Answer 1

Dynamic queries can also open you up to SQL injection. Having them in stored procedures with paramters helps reduce that risk.

If you do need/want to do queries dynamically in code, I'd suggest having them all within a single data class or similar. This way they are all in one place if any changes are needed, plus then you can see if an existing query already exists before making a new one. This also helsp if you ever need to use a new database or want to switch to store procedures, sicne everything is wrapped within calls in a single location.

Answer 2

You're not wrong, I personally think you should either use stored procedures or have a class specifically for talking to the database. Just assembling queries "wherever" in the code is asking for trouble.

Answer 3

One of the big problems I see with this approach is that there is no clear separation of responsibility. With multiple layers owning different parts of the logic that creates a SQL query you have an application that will not scale well and will be difficult to modify.

Answer 4

Stored procedures are a must. Two principles at play here: good engineering and security

Databases are not OOP nor functional programming languages either (though they pretend to be). A database's strength is in it's ACID nature. To utilize this power, DBs really need their own layer to adhere to those principles.

Secondly, as others have mention, data injection attacks are a HUGE threat. When you have millions of dollars on the line, it is criminally negligent to your share holders or clients that such data isn't protected through use of stored procedures or some type of safe databinding.

If you HAVE to dynamically build a query, the best way to make it secure is to only allow input to be translated to a known integer/enum value. No free text input.

It is easy to normalize a bit of user input and check against known values in an array. I usually do this across multiple layers so that the input is checked multiple times and normalized multiple times.

But again. Stored procedures are a must.

Answer 5

i would like to add that now we have hibernate and linq stored procedures are overkill in most projects. i understand in some situations stored procedures are unavoidable because of performance constraints since shipping masses of data back and forward between the application server and the database is expensive.

my current project uses SPs and my previous job used hibernate. on the current job there are FAR more bugs a lot of them that would never have occured if we were using an ORM. also, on the SP project we had a SQL injection bug which was in a stored procedure. i've never seen an SQL injection problem on a codebase i have worked on before now :) it seems that people's brains just turn off when they start writing stored procedures.

why would you want to have more bugs and do more work just to please some screwed up religious principle: 'we must use stored procedures'.

Answer 6

Although everyone seems to be pointing out SQL injection attacks as a big security vulnerability, that's not the biggest security hole caused by dynamic SQL in my opinion. You can prevent SQL injection attacks if you code the front end properly and without coding your stored procedures properly you can still leave yourself vulnerable.

I think that the biggest security hole in using dynamic SQL is that now the front end user needs to have access to the underlying tables. That means that there is a user out there, whether it's Windows Authentication or a SQL Login, that can muck with the database with almost no rules to stop them. I can't even count the number of times I've gone into a new shop and found that the login information was kept in a config file somewhere in plain text. Even when it wasn't, the developers almost always knew the username/password for the account.

Most security breaches (by a LONG shot) in corporations are inside jobs - disgruntled employees, people looking to make a quick buck by selling SSNs, or people who just don't think there's anything wrong with looking up medical information for their ex-girlfriend from high school. Add to that the developer who issued a "TRUNCATE TABLE" when he was sure that he was on the development server. Then you've also got the people who login and add a row to a table somewhere to "fix" a problem, but they don't understand that whenever you add a row to table X you need to write a row to table Y. Had all access been enforced through stored procedures they wouldn't have been able to do that.