Why I think dynamic queries are an extremely bad idea

I've been thinking about this for several days now, and I have finally formulated an opinion about the whole "dynamic query" argument. I'm going to throw my hat into the ring and give you my two cents.

As the title of this post suggests, I think dynamic queries are extremely bad for several reasons. I feel so strongly about this that I'm not exactly sure where to start. First is security. When you use a direct parameterized select statement, you have to open up direct query permissions on your database. This is EXTREMELY UNSAFE. I've written an article on taking care of your data layer, and it's due out in a couple weeks, so I'll give you a quick quote: "If you're going to open up your tables to direct select statements, you might as well not even bother trying to hide the key under the mat, because you've already left the front door wide open" When you use SPROCS, you can explicitly deny access to direct access statements, and only provide access to the SPROCS. This way, even if your database IS hacked, the only way it can be accessed, if you're smart and have a different user for your connection string than you do for your admin, then the only way they can get access to your data is by RUNNING the sprocs from somewhere else. It's a huge security risk that you leave yourself wide open for.

Second is usability. For one thing, as a community, we can't even get everyone writing a proper 5-layer application yet, LET ALONE get them to the next step, which would be dynamic, automated systems. I would be more than happy attempting to overcome the security obstacles of a dynamic system, but I still see a majority of developers bring their poor ASP coding skills into the .NET world. Prime examples are: hard-coding direct selects statements into the assembly, failure to separate business logic from UI, not using code-behinds, putting data access code in code-behinds (not using a DAL layer), and poor OOP design (Hungarian notation for objects, bad object names, bad namespacing, etc). I had to unlearn what I learned about ASP to be able to write efficient ASP.NET code. Until we as a community can overcome those hurdles, I don't believe that we should be introducing such advanced techniques as coding options.

Finally, and I believe most importantly, is maintainability. Your Application should not need to know a darned thing about your database, except the name of the SPROC and the parameters it needs, to operate. PERIOD. The MS Data Access Application Blocks make is so that you don;t even have to hard-code parameters into your DAL, it will automatically discover them for you. Once they are discovered, they are cached for even better performance. If you have to recompile your application because you changed a table name (a topic I wrote about just the other day), then you are not yet ready to be a full-fledged Jedi. To quote Scott Cate, "You might as well write a one-layered app"

In conclusion, I think it's a really bad idea to generate dynamic queries on the fly in your application layer. A better solution would be to have a dynamic SPROC that accepts parameters that dynamically handle the situational requirements. That would better harness the power of the SQL engine, and keep your app disconnected from your data.

4 Comments

  • You still have to open direct query permissions on the database, which is completely unacceptable. I'd rather write more DAL code then leave my system open for attack, and you should too.

  • Opening direct query permissions is by no means leaving the system open for attack. If your web server or network gets compromised in the first place, that is the issue. Put the SQL server behind a firewall and enforce proper security on your web tier and you shouldn't have any issues. Assuming someone compromised a web server in the first place, not having query permissions doesn't mean much. They don't need query permissions to screw you over.

  • Wait until my article comes out, and I will explain my security concepts further. I've spent a long time developing what I believe to be the best security architecture for SQL out there, which is why you may not quite understand what I am talking about yet.

  • Robert:


    Without your article it's perfectly understandable what to do: you don't have that much options when it comes to webapplications and sqlserver spread over more than 1 machine. Even when they're run on 1 machine, you will probably use 1 connection string and use 1 user to connect to the database (because all accesses are anonymous f.e.).

Comments have been disabled for this content.