Just curious about this because at the when I started this app, I spent a lot of time in SQL Profiler trying to figure out why things were not working as I expected them to That was when I noticed that Strataframe was sending a lot of exec SP_EXECUTESQL commands to the server. Now, with my current client and since this is a winforms app, allowing Strataframe to do this does not concern me. However, my husband performed as DBA for Charles Schwab for 18 months and there is absolutely no way that they would allow a user access to system stored procesures (especially with the threat of SQL injection) - the database was totally locked down.

So how does Strataframe get around this situation?

I don't have enough experience with SQL server yet, and have some concerns to help me understand the situation:

1. Why using exec SP_EXECUTESQL commands would be a problem?
2. If a database is locked down so no SP could be used, then why a DBA will have any SP in the database?
• I believe in this case, only a limited internal users would get access to those SP for internal use.

Since I have not have the chance to use SP yet, in the above situation, I would guess your application will have to re-create the SP equivalent using Fill methods in the BOs.

SQL Injection. using SP_EXECUTESQL a user can execture anything including DROP TABLE - see the problem? It is a major security hole - not such a bug deal in a winforms app, but certainly cause for condern in a webforms app.

By "locked down" I mean no access to System stored procedures. The users would still have access to the user stored procedures.

I was thinking about this while I was on the treadmill this morning. I think that perhaps a good solution here would be for the framework to allow the developer to specify a stored procedure to execute when the search button in the browse window is clicked. This stored procedure would accept as parameters all of the values entered in the search screen, the assumption would be that the parameters are named the same as the field names in the underlying data. Then that user accessible stored procedure would build the SQL string and call SP_EXECUTESQL. Doing it this way is not a security risk. The same thing applies to getting back the PKs for newly inserted records. The defined interface would be that the SP always returns the PK as an output parameter.

The only reason that I bring this up is because I am really starting to get very productive with this framework in a very short time. I love the BrowseDialog and want to be able to continue to use it even at companies that have paranoid DBSs liek my husband

Marcia G Akins (09/08/2008)

---

The only reason that I bring this up is because I am really starting to get very productive with this framework in a very short time. I love the BrowseDialog and want to be able to continue to use it even at companies that have paranoid DBSs liek my husband

This is a very interesting topic on security and honestly, I really don't know how the Browser Dialog works internally, so I'll wait for the SF team to comment on this one. 

I think that there is a disconnect here somewhere as StrataFrame does not call sp_executesql anywhere...if in fact we did then I would have to agree with your husband that this would be bad practice.  But I can assure you that we do not call this method anywhere within the framework.  Now if you saw this popup within a profiler then it could be a downstream SQL call coming from someplace outside of the framework...I have actually seen SQL Server execute this sproc within a profiler before...but just to put your concern to rest, we do not explicitily call sp_executesql anywhere within the framework nor will we ever.

Thanks for putting my mind at ease . I do not know why we were seing the calls to SP_EXECUTESQL when the browse dialog was doing its searching. My husband just assumed that it was the framework.

You guys seem pretty bright, so I could not imagine you putting in something that is such a big security hole.

BTW, now that I am getting comfortable with the basics of how the framework works, I am getting really productive. My client will be suitably impressed with how far I have gotten with this project in just over a week. Keep up the good work!

Glad my response helped clear things up.  Thanks for your kind words....it is always nice to hear!