ASP.NET / SQL Server: Storing complex queries within the SQL Server database

Question

I am working on an application where certain SQL Server queries will be limited to specific users based on a group identity.

It seems to make sense to store these within the application database where I can associate them with group names and it is easy to add/remove in future.

The queries are quite complex though and take a number of parameters such as JQuery to and from date fields, current username etc. So the query is constructed like:

MyQuery = "SELECT * FROM Table1 WHERE Username = '" + System...CurrentUser +"' AND Somedate > '" + from.text +'";

Now I'm unsure how to take this code and create an equivalent representation in the database. I thought about using specific identifiers such as %USERNAME%, %FROMDATE% etc then use a String Replace function but I'm not sure that would carry over the single quotes.

Would something like Replace("%USERNAME%", "'" + CurrentUser + "'") work?

Any better ideas!

Two things are:

Restrict some queries based on user groups.
Reasonably easy to add/remove/edit queries in the future (not hardcoded into the C#?)

Stupid question: couldn't you just create those as stored procedures (with parameters), and handle permissions using standard SQL Server features?? — marc_s
– marc_s, Commented May 10, 2011 at 16:34
marc_s - that should be an answer. I wanted to post that as an answer myself, but you beat me to it. I'd feel like a heel if I posted it as an answer when you've already put it as a comment. Also I would have included a link to the OWASP article on SQL Injection. Post that as an answer and I'll vote it up. — David
– David, Commented May 10, 2011 at 16:41
databases.about.com/od/sqlserver/a/storedprocedure.htm and owasp.org/index.php/SQL_injection — David
– David, Commented May 10, 2011 at 16:44
I'll look at the stored procedures option. I am aware of SQL injection, this is an internal business system and the users cannot access the SQL statements only the admin — MattP
– MattP, Commented May 10, 2011 at 16:46
I know this is going to sound argumentative and I don't mean to, but that is a horrible assumption to make. Once you create the stored procedure, you have no control over whether or not a future developer will make use of the stored procedure. Even if you DO know for sure, it's still best to be in the habit of always coding securely no matter what. Secure coding is as much a habit as a skill, and the mindset NEEDS to be paranoid. Assume the worst will happen. — David
– David, Commented May 10, 2011 at 16:56

marc_s · Accepted Answer · 2011-05-11 09:32:33Z

4

First off: BIG WARNING: concatenating together your SQL statements bears a great danger of SQL injection attacks. You should avoid this technique at all costs and use parametrized queries instead.

Second of all: couldn't you just create those "complex queries" as stored procedures (with parameters) - that's what stored procedures are really good at - encapsulating complex queries into a simple call from the outside.

You could then handle permissions using standard SQL Server features - give those users (and/or groups) EXECUTE permission on that stored procedure who are allowed to call it.

edited May 11, 2011 at 9:32

answered May 10, 2011 at 20:32

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

ASP.NET / SQL Server: Storing complex queries within the SQL Server database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related