SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


ADO.NET - A Data Access Layer


ADO.NET - A Data Access Layer

Author
Message
Patryk Nowakowski
Patryk Nowakowski
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/PNowa
Razvan Socol
Razvan Socol
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1385 Visits: 129

Patrick,

You wrote "we have to ensure that the SQL command passed to the procedure is actually a SELECT statement". I hope that you realize that the statement could be something like this:

SELECT 1 DELETE _t_spsys_select_log WHERE log_id=(SELECT MAX(log_id) FROM _t_spsys_select_log)

This means that we must trust the developer of the client application to give us a correct SELECT statement. This means that we trust that he only uses hard-coded strings or if he uses anything entered by the user, he validates them very well. If anyone wants to read further on this topic, see:

http://www.nextgenss.com/papers/advanced_sql_injection.pdf

Razvan





Tony Hinkley
Tony Hinkley
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 Visits: 19

Razvan raises a good point, and one that reinforces the suggestion that a data layer is a good idea. It's much easier to prevent SQL Injection and the like in a dedicated data layer, than on every individual SQL call.

Tony


Greg Walker
Greg Walker
SSC Journeyman
SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)SSC Journeyman (83 reputation)

Group: General Forum Members
Points: 83 Visits: 20

A few issues I see with this approach. I'm curious as to your opinion on them:

1) It does nothing to prevent SQL injection attacks. I can pass in something like "select * from table ; delete * from table", which would qualify as a select statement, but would execute both the select and the delete statement. Very dangerous and hard to catch.

2) Aren't you losing all the benefits of running stored procs by using sp_executesql? None of the queries are running against pre-compiled sprocs that way, and some queries can be very long and complex (much more than 1000 characters).

Again, I really like the idea of a data access layer (it's been on our development list for a while now). I'm just not sure what the best approach is for it. I prefer to require the client to access all data through stored procedures (we develop a web based app), which makes it hard to develop a generic interface.

Just my $0.02. I'm curious to hear what other people think.

Greg Walker
DBA
ExpenseWatch.com




Greg Walker
DBA, ExpenseWatch.com
rugha
rugha
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 1

havent read the whole article, just wanted to let you know about the "Data Access Application Block" published by microsoft for that exact purpose, it is quite easy to use, details and downloads can be found here :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp

cheers

rugha




-------------------------
Oh no, I've done it again...
-------------------------
Tatsu
Tatsu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 307

The Data Access Application Block is really quite excellent. This is also another opportunity to plug Code Generation. I know, I know, I promised to write an article on the topic and I started one but it sucked I'll get back to work on it soon.

My point is, you should not have to write 80% of the stored procedures or any of the data access layer. Let a code generator do the generic stuff for you. Check out CodeSmith at http://www.ericjsmith.net/codesmith/ or the Code Generation Network at http://www.codegeneration.net/.



Bryant E. Byrd, BSSE MCDBA MCAD
Business Intelligence Administrator
MSBI Administration Blog
Zvonko M.
Zvonko M.
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1

Hi all,

My DAL starts with Stored Procedures and prepared commands and we use .NET framework components with corresponding typed datasets. Then you can drop your DAL Component and needed datasets to Win/WebForm and call its methods. It is good idea to create typed dataset properties in your component so it will allow you to select dataset instance using IDE property editor - cheers!

Common DAL "framework" should be comprised of component base classes. That's how I see this problem. Btw, when you use SqlCommand object's parameters collection, "sql injection" is not a problem any more.

Have a nice day





R M Buda
R M Buda
Mr or Mrs. 500
Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)Mr or Mrs. 500 (565 reputation)

Group: General Forum Members
Points: 565 Visits: 303
I agree with all Patryks reasons for using a data access layer.

I also re-iterate rugha's comment - see the microsoft SqlHelper class which they publish separately as a freely downloadable Data Access Application Block.

This allows the coder to use static methods to perform most data access tasks which makes things much easier. You can of course build on this to do logging etc...

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp
Igor Makedon
Igor Makedon
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 79

Does DAL refuse Data-Binding approach? In other words can it be used when one wants a GUI control (as opposed to developer's SQL code) to take care about SELECT / UPDATE / INSERT / DELETE?

I am having problems with an old application relying heavily on data binding. It needs to migrate to .NET. The database gradually became pretty ugly during its many-years lifetime.

I'd love to create / use a DAL approach which seems to be the only way to maintain the application's being up and running while "remodeling" is being in progress. But...

What about the first question in this post please?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search