pkr2000

Recently I was presented with a design where TSQL is stored inside the aspx, albeit in server side tags, i.e. it wouldn't be rendered to the client. At first I blew my stack but I was wondering what everyone else thought about it.

Why I am worried:

Breaches defence in depth, if someone is going to hack a system they'll most likely hack the web server, they're less likely to gain access to my database which is safely tucked away in another zone.

Why I'm paranoid:

Realistically how big a problem is this




Re: Architecture General Security question, are the contents of an aspx considered safe?

Martin Platt

Personally, I wouldn't use inline TSQL anywhere, for a number of reasons. As you point out, it could well be a security risk. It is also quite probably a performance, or scalability issue too.

Perhaps the issue comes down to how well secured everything else is in the system. If the asp page is wel secured on a server, and accounts are use properly, then the TSQL code shouldn't be accessible, however, my view is, any time someone comes up with a scenario like that, there are often other holes in their work.

Another point I'd make is, I would write a subsystem of business objects to handle and represent the functionality around the TSQL code, and asp just be calling into that instance. In that way it's more likely to be able to have more security on it. The other thing is, then your code is much more likely to scale better, be able to be used from other interfaces, and all other number of useful benefits.

I hope this helps,

Martin Platt.






Re: Architecture General Security question, are the contents of an aspx considered safe?

pkr2000

The "advantage" of storing the sql in the aspx is that a second-class developer can alter the source of the page without needing to access any development tools or a compiler. E.g. Someone who knows a bit about SQL can alter the results displayed to the client by tweeking the SQL, since it's in a page and not the code-behind then .net will simply recompile the page on the fly. Believe me there isn't a part of that doesn't make my skin crawl but I can't fault the simplistic reasoning. However, regardless of all the arguments about letting these "developers" at the database without the use of any standard enforcing tools I'm concerned that there is a real security risk. So who would allow this in their solutions






Re: Architecture General Security question, are the contents of an aspx considered safe?

MAGrimsley

Enbedding SQL code inside a web page is not the best way to access the database. When you embeded T-SQL code in an application, whether ASP.NET or WinForms you open yourself up to SQL injection attacks. SQL injection attacks allow a user to break from the flow that you have inside your appliation and inject their own code to do what they would like without knowing your database schema.

As an example, in a text box I can add a single quote and then a semicolon to the textbox and then type in my own T-SQL code to query sys objects as an example to determine the list of tables inside the database. Many times the application does not know how to handle these, but I can redirect the output if needed, or what happens at times is that the error that is returned to the user shows me information that is usefull for hacking the system.

It is always better to use stored procedures (SP) to access the database. There are several more advantages to using stored procedures. They add another layer of abstration to the application. It also allows SQL Server to cache the exectuion plain in memory resulting in a performance increase. You can even apply security to the SPs to only allow certain users execute the SP while denying the user read and write access to your database.

I can continue going on; however, embedding T-SQL code inside an application is not a "best practices" and opens yourself and your company up to potential legal actions if the data is ever compromised.

Michael






Re: Architecture General Security question, are the contents of an aspx considered safe?

pkr2000

Michael thanks for your reply however, whilsts your comments are true they don't really apply here. The SQL is self contained and will use code-behind libriaries to execute the SQL, there is no external influence on the SQL.

I don't want to start the stored procedure debate, I like them but the old song about performance is pretty weak these days, but either way this post isn't really about peformance or good practice it is simply about security.






Re: Architecture General Security question, are the contents of an aspx considered safe?

MAGrimsley

I understand what your saying and I'm not trying to start the debate about stored procedures. The comment was about security and while the SQL may be in code-behind libriaries, it is still suspectible to being hacked, accessed and IMHO is not considered safe. I work in a very security conscious environment and we have found that is is still suspectible to hacking.

Hope this helps

Michael






Re: Architecture General Security question, are the contents of an aspx considered safe?

pkr2000

Thanks again for the reply, so to be clear you're concerned that somewhere there is a component that accepts a SQL command I concede that there is a small risk that even with enforcing strongly named access that someone inside the company with the correct skills (and access to your key) could use that library, but that is an awful lot of if's isn't Is that your worry or do you have other issues






Re: Architecture General Security question, are the contents of an aspx considered safe?

MAGrimsley

To go back to your original question, it is not the safest solution to present to a customer, whether internal or external, to have inline T-SQL code in a application and is more suspectible to hacking and sql injection attacks than other methods previously mentioned and recommend. Thanks for your quetions.

Hope this helps

Michael






Re: Architecture General Security question, are the contents of an aspx considered safe?

pkr2000

Thanks, however without qualifiying why it's, 'more suspectible to hacking and sql injection' I'm not sure how much milage I can get from that. As far as I can see;

1) there is no danger from SQL injection since there is no external input into the SQL (see point 4 for further clarification)

2) the danger from hacking relies on a) someone stealing your strong name key or b) finding a back door to .net's security or c) a network replace packet style hack (mitigated by transport security). Granted if you kept the SQL in stored procedures then you wouldn't have to worry about this, and this *is* my stance, however I find it a pretty weak argument to stop others using this technique where they see a LOB advantage in ignoring the risk.

3) exposing the TSQL to anyone with (or gains) read access to the site - possible to give clues to the database schema

4) exposing the page with the TSQL to be updated and therefore allowing any SQL to be run. Requires someone gaining write access to the page.

So the essence of my question is really about (4). Should I be worried about anyone gaining write access to a page My gut feeling is yes, but "everyone" I talk to seems to think I'm being paranoid.






Re: Architecture General Security question, are the contents of an aspx considered safe?

Martin Platt

I hadn't looked at this thread for a while, it still seems to be unresolved.

This is an interesting discussion!

If you don't tie down the page access, then any user can alter the SQL statement, and really mess up your application, and it would be very easy to do.

There are a number of reasons as to why you might not want to write TSQL in a web page. Here are a few that come to mind:

- The page is tightly coupled to the database, so changes in the database structure may well break the application.

- Someone can load up your application, and may be able to gain access to the TSQL statement, either to change it, or to view it, and gain access to your data and IP

- Whilst it may be negligable in a small application, inline statements won't run as quickly as stored procedures, and they are precompiled, and readily accept parameters.

In my opinion, making it easy for developers to change code is not a good reason to do something. If we think of this question in terms of who the web page is for, is it there to benefit the developers, or are the developers simply doing some work on it, so ultimately it is for the economic benefit to the company. If allowing the developers to be lazy, and able to do things in this way means that it is more important to have developers do as they wish rather than looking after the clients' interests

Purely from a security perspective, showing a user the TSQL, or maybe allowing the user to access to TSQL is effectively giving away the IP of the system, and should not be allowed. If you're sending TSQL down the wire, then it can be possible to intercept and modify the data packet, unless of course you design to overcome this fact. If you do this, then you're effectively designing an application to work around laziness.

Aside from security, the other consideration to me is that the web app should really use MVP type pattern to implement functionality, so that it can be tested seperately. What happens if you have a need for a smart client, mobile application or whatever You have the logic embedded in your web page, and you have to decouple it from the page, and re-design for the new UI.

There's also the consideration of the amount of network traffic you'd be generating. You send the TSQL to the server every time, when you really don't need to.

Considering security alone isn't the correct choice to make. It should be all about the pros and cons of doing what you're proposing, so that you end up with the best design, and ultimately end product availabile, given the requirements, and constraints for the solution.

I hope this helps, and I apologise for not having noticed that it wasn't closed,

Good luck,

Martin Platt.






Re: Architecture General Security question, are the contents of an aspx considered safe?

pkr2000

Thanks for the reply. It's a difficult subject for me because I agree with what you say but in the minds of my customer (who want to write this code themselves) I can see that the arguments just don't seem compelling. I get lots of, "I don't care about what if's" so I need to have concrete arguments and I confess that I find them all a bit wishy-washy. The reason I was focusing on security because that is where the customer's customers would sue them if their data was comprimised...so it tends to focus their minds. The performance and maintainance reasons either don't work or aren't considered significant.




Re: Architecture General Security question, are the contents of an aspx considered safe?

Martin Platt

That's a difficlt position to be in, and I see two situations:

1. You suggest that the tighten up security, and don't leave themselves open to what has been discussed in this thread, and thus run much less of a risk of being sued for negligent practices;

2. They ignore you, and do it their way, which we all seem to agree is clearly wrong. The what if's part is where all the holes are, if nobody hacks your site, it's safe, but that's not really a very good or compelling argument or security design either

I totally hear what you're saying about them asking for a compelling argument, been in the same situation too. You can lead a horse to water, but you can't make it drink. I think I'd explain it that way, as say that those are the risks, and that you're trying to stop them getting into legal troubles, rather than trying to impose non-productive practices upon them. If they listen, and still ignore, well, you tried.

Good luck!

Martin Platt.