Software Development

aspnet_regiis “Could not load file or assembly ‘SimpleAuthentication.Core’ or one of its dependencies.”

I was recently following Jouni Heiknieme’s blog post on Encrypting connection strings in Windows Azure web applications when I stumbled across a problem.

The issue was that I wasn’t encrypting the connectionStrings section, I was encrypting a custom section (one provided by SimpleAuthentication). And in order to encrypt that section, aspnet_regiis needs access to the DLL that defines the config section. If it cannot find the DLL it needs it will respond with an error message:

C:\dev\Xander.HorribleCards\src\Xander.HorribleCards.UI.Web>aspnet_regiis -pef "authenticationProviders" . -prov "Pkcs12Provider" 
Microsoft (R) ASP.NET RegIIS version 4.0.30319.18408 
Administration utility to install and uninstall ASP.NET on the local machine. 
Copyright (C) Microsoft Corporation.  All rights reserved. 
Encrypting configuration section... 
An error occurred creating the configuration section handler for authenticationProviders: Could not load file or assembly 'SimpleAuthentication.Core' or one of 
its dependencies. The system cannot find the file specified. (C:\dev\Xander.HorribleCards\src\Xander.HorribleCards.UI.Web\web.config line 7) 
Could not load file or assembly 'SimpleAuthentication.Core' or one of its dependencies. The system cannot find the file specified. 
Failed!

And here is the relevant part of the web.config file

<?xml version="1.0" encoding="utf-8"?> 
<configuration> 
  <configSections> 
    <sectionGroup name="system.web.webPages.razor" type="System.Web.WebPages.Razor.Configuration.RazorWebSectionGroup, System.Web.WebPages.Razor, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"> 
      <section name="pages" type="System.Web.WebPages.Razor.Configuration.RazorPagesSection, System.Web.WebPages.Razor, Version=2.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35" requirePermission="false" /> 
    </sectionGroup> 
    <section name="authenticationProviders" type="SimpleAuthentication.Core.Config.ProviderConfiguration, SimpleAuthentication.Core" /> 
  </configSections>

It took searching through a few forum posts before I eventually found the answer. Most were pointing in the right general direction. You either have to load the assembly that defines the config section into the GAC (not possible for me as it was a third party assembly that was not strong named) or put it where aspnet_regiis was looking for it.

All the non-GAC solutions that I found were hacky horrible things that put the assembly somewhere in the .NET folder.

My problem was that where everyone was saying to put it wasn’t working for me. So I loaded up Process Monitor to look to see where exactly the aspnet_regiis was looking. It turns out that because I was using the 64bit version of the command prompt I should be looking in C:\Windows\Microsoft.NET\Framework64\v4.0.30319

I put the assembly in that directory and the aspnet_regiis worked and the relevant section was encrypted, it was runnable and I could store it to source control without other people knowing what my secret keys are.

Round tripping the encryption/decryption

I also had some issues round tripping the encrypted and decrypted config file while developing. I kept getting the error message:

Decrypting the relevant config settings
Microsoft (R) ASP.NET RegIIS version 4.0.30319.18408
Administration utility to install and uninstall ASP.NET on the local machine.
Copyright (C) Microsoft Corporation.  All rights reserved.
Decrypting configuration section...
Failed to decrypt using provider 'Pkcs12Provider'. Error message from the provider: Keyset does not exist
 (C:\dev\Xander.HorribleCards\src\Xander.HorribleCards.UI.Web\web.config line 65)

Keyset does not exist

Failed!

It turned out to be a permissions issue on the private key. This post “Keyset does not exist” on Stack Overflow helped on how to resolve that.

Tip of the Day

Tip of the Day: Getting TFS to remember you each time you open Visual Studio

Because the TFS Server where I work is not on the domain, it will prompt you for credentials each time you log in (unless you’ve previously used the web access and checked the “Remember Me” option). If you don’t want to use the web access portal, you can still get TFS to remember your credentials and not ask you each time you log in.

Go in to the control panel and select “User Accounts”

In the next screen click “Manage Windows Credentials”

In the next screen click “Add Windows Credential”

Then type your details into the form, and press “OK”

You’ll see your new set of credentials appear in the Credential Manager page:

Now when you open up Visual Studio it won’t prompt you for your credentials all the time.

Software Development, Tip of the Day

Tip of the day: How to tell why your app couldn’t log on to SQL Server

When you get a log in failure on SQL Server the message you get back from SQL Server Management Studio, or in a .NET Exception is vague for security. They don’t want to give away too much information just in case.

For example, the exception message will be something like “Login failed for user ‘someUser’.” which doesn’t give you much of a clue as to what is actually happening. There could be a multitude of reasons that login failed.

If you want more information about why a log-in failed you can open up the event viewer on the machine that SQL Server is installed on and have a look. You’ll find a more detailed message there.

The wider messages may be things like:

  • “Login failed for user ‘someUser’. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]”
  • Login failed for user ‘someUser’. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
  • Login failed for user ‘someUser’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
    Note: This could be because the database doesn’t exist, or because the user doesn’t have permissions to the database.
Software Development

If you really must do dynamic SQL…

I may have mentioned in previous posts and articles about SQL Injection Attacks that dynamic SQL (building SQL commands by concatenating strings together) is a source of failure in the security of a data driven application. It becomes easy to inject malicious text in there to cause the system to return incorrect responses. Generally the solution is to use parameterised queries

However, there are times where you may have no choice. For example, if you want to dynamically reference tables or columns. You can’t do that as the table name or column name cannot be replaced with a parameter. You then have to use dynamic SQL and inject these into a SQL command.

The problem

It is possible for SQL Server to do that concatenation for you. For example:

CREATE PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @sql nvarchar(max) =
		'SELECT ' + @ColumnName +
		' FROM ' + @TableName +
		' WHERE Id = '+cast(@Id as nvarchar(20));
	EXEC(@sql)
END
GO

This is a simple stored procedure that gets some data dynamically. However, even although everything is neatly parameterised it is no protection. All that has happened is that the location for vulnerability (i.e. the location of the construction of the SQL) has moved from the application into the database. The application is now parameterising everything, which is good. But there is more to consider than just that.

Validating the input

The next line of defence should be verifying that the table and column names passed are actually valid. In SQL Server you can query the INFORMATION_SCHEMA views to determine whether the column and tables exist.

If, for example, there is a table called MainTable in the database you can check it with a query like this:

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'MainTable'

And it will return:

INFORMATION_SCHEMA.TABLES

There is a similar view for checking columns. For example:

INFORMATION_SCHEMA.COLUMNS

As you can see, the INFORMATION_SCHEMA.COLUMNS view also contains sufficient detail on the table so that when we implement it we only have to make one check:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT ' + @ColumnName +
            ' FROM ' + @TableName +
            ' WHERE Id = '+cast(@Id as nvarchar(20));
        EXEC(@sql)
    END
END
GO

Formatting the input

The above is only part of the solution, it is perfectly possible for a table name to contain characters that mean it needs to be escaped. (e.g. a space character or the table may share a name with a SQL keyword). To escape a table or column name it is enclosed in square brackets, so a table name of My Table becomes [My Table] or a table called select becomes [select].

You can escape table and column names that wouldn’t ordinarily require escaping also. It makes no difference to them.

The code now becomes:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT [' + @ColumnName + '] ' +
            'FROM [' + @TableName + '] ' +
            'WHERE Id = '+cast(@Id as nvarchar(20));
        EXEC(@sql)
    END
END
GO

But that’s not quite the full story.

Really formatting the input

What if you have a table called Cra]zee Table? Okay – Why on earth would you have a table with such a stupid name? It happens, and it is a perfectly legitimate table name in SQL Server. People do weird stuff and you have to deal with it.

At the moment the current stored procedure will simply fall apart when presented with such input. The call to the stored procedure would look like this:

EXEC GetData 1, 'Cra]zee Table', 'MadStuff'

And it gets past the validation stage because it is a table in the system. The result is a message:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'Table'.

The SQL produced looks like this:

SELECT [MadStuff] FROM [Cra]zee Table] WHERE Id = 1

By this point is should be obvious why it failed. The SQL Parser interpreted the first closing square bracket as the terminator for the escaped section.

There are other special characters in SQL that require special consideration and you could write code to process them before adding it to the SQL string. In fact, I’ve seen many people do that. And more often than not they get it wrong.

The better way to deal with that sort of thing is to use a built in function in SQL Server called QUOTENAME. This will ensure the column or table name is properly escaped. The stored procedure we are now building now looks like this:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT ' + QUOTENAME(@ColumnName) +
            ' FROM ' + QUOTENAME(@TableName) +
            ' WHERE Id = '+cast(@Id as nvarchar(20));
        EXEC(@sql)
    END
END
GO

Things that can be parameterised

There is still something that can be done to this. The Id value is being injected in to the SQL string, yet it is something that can quite easily be parameterised.

The issue at the moment is that the SQL String is being executed by using the EXECUTE command. However, you cannot pass parameters into this sort of executed SQL. You need to use a stored procedure called sp_executesql. This allows parameters to be defined and passed into the dynamically created SQL.

The stored procedure now looks like this:

ALTER PROCEDURE GetData
	@Id INT,
	@TableName sysname,
	@ColumnName sysname
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS
               WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName)
    BEGIN
        DECLARE @sql nvarchar(max) =
            'SELECT ' + QUOTENAME(@ColumnName) +
            ' FROM ' + QUOTENAME(@TableName) +
            ' WHERE Id = @Identifier';
        EXEC sp_executesql @sql, N'@Identifier int',
                           @Identifier = @Id
    END
END
GO

This is not quite the end of the story. There are performance improvements that can be made when using sp_executesql. You can find out about these in the SQL Server books-online.

And finally…

If you must use dynamic SQL in stored procedures do take care to ensure that all the data is validated and cannot harm your database. This is an area in which I tread very carefully if I have no other choice.

Try and consider every conceivable input, especially inputs outside of the bounds of your application. Remember also, that defending your database is a multi-layered strategy. Even if you have the best firewalls and security procedures elsewhere in your system a determined hacker may find a way though your other defences and be communicating with the database in a way in which you didn’t anticipate. Assume that an attacker has got through your other defences, how do you provide the data services to your application(s) yet protect the database?

Software Development

Tip of the Day #14: A Step to PCI Compliance

If you have a public facing website that accepts credit card payments from customers they you?ll be looking to become PCI compliant. This means you need to improve the security of your website to prevent attack and to prevent data being intercepted by third parties.

SSL 2.0 is now seen as weak and insecure, yet IIS will by default accept connections from older browsers that want to use this. It can be turned off, but it isn?t obvious how to do that. Here?s how to turn off SSL 2.0 on IIS or Microsoft Support has a reference on How to disable PCT 1.0, SSL 2.0, SSL 3.0 or TLS 1.0 in IIS (Internet Information Services).

While many PCI auditing companies will tell you if you are using SSL 2.0 or any other weak techniques, the quick test to ensure the server is not serving pages using SSL 2.0 is to change the Advanced Options in Internet Explorer to only support SSL 2.0.

Internet Options 1 (SSL)

After that I went to a secure page in the site and got the following error message:

Internet Explorer cannot display the webpage

Most likely causes:
  • You are not connected to the Internet.
  • The website is encountering problems.
  • There might be a typing error in the address.

What you can try:

Diagnose Connection Problems
More information

This problem can be caused by a variety of issues, including:

  • Internet connectivity has been lost.
  • The website is temporarily unavailable.
  • The Domain Name Server (DNS) is not reachable.
  • The Domain Name Server (DNS) does not have a listing for the website’s domain.
  • If this is an HTTPS (secure) address, click Tools, click Internet Options, click Advanced, and check to be sure the SSL and TLS protocols are enabled under the security section.

For offline users

You can still view subscribed feeds and some recently viewed webpages.
To view subscribed feeds

  1. Click the Favorites Center button , click Feeds, and then click the feed you want to view.

To view recently visited webpages (might not work on all pages)

  1. Click Tools , and then click Work Offline.
  2. Click the Favorites Center button , click History, and then click the page you want to view.

To ensure the site was working normally, I reset the settings to allow only support SSL 3.0 and TLS 1.0 and tried again.

Internet Options 2 (SSL)

This time I got the page I was expecting.

Note: You cannot use FireFox to perform this quick test as it does not support SSL 2.0.

Internet Options 3 (SSL/FF)

Software Development

Follow up on what not to develop

Back in May I wrote about a substandard website I attempted to use in an article entitled “What not to Develop”. I also sent the hotel an email at the same time telling them of the failing of their website, however, I never got a response.

When the post went live initially, I got asked on twitter to name and shame the company in question. I suppose publically decrying a company has the effect that if people start doing that then companies will be pressurised in to providing a better service or product. These days I do not to put in a blog post the name of the company in question until I’ve given them a chance to respond to any email I might have sent. I sent the email on 16 May 2009 at 17:21 (BST), I think that’s quite enough time for a response.

I’ve decided to publish some more details so that people can at least learn from the mistake and not repeat them elsewhere. Essentially, this is an extract of the email (slightly reformatted to fit this blog)

Hello,

I tried to book on your website last night and it didn’t work – it advertised a rate to me then refused to book it. I then tried to use your Contact Us page to send you a message and that also broke and said “The web site you are accessing has experienced an unexpected error. Please contact the website administrator. ”

I don’t know who the web site administrator is, but I can guess it is someone employed by TIG Global given this news story: http://www.hospitalitynet.org/news/4036652.search. Personally, if that is the quality they are delivering I wouldn’t use them again as they are not very good and are at best turning away potential customers and at worst exposing you to needless risk.

In order to [help you to] track down the errors I’ve gone back and replicated the initial problem annotating the pages as I go. You will find a number of graphics files attached.

Southwark Rose Hotel Step 1

In [the above image] I show the initial details of my availability search. Check in Friday 31st July, check out Sunday 2nd Aug. 1 adult, 0 children.

Southwark Rose Hotel Step 2

In [the above image] I show the next page. This was a pop-up, so opened a new window. The details at the top are correct and match what I’d previously entered. The description of the “Weekend Advanced Purchase” sounds perfect “Valid Friday-Sunday throughout 2009″. I see that it is £150 for the “Total price of the stay”. I press the book button.

Southwark Rose Hotel Step 3

In [the above image] I show the next page. This was another pop-up, so opened a second window. I now have 3 windows open just for your hotel. (Is this really necessary?). I spot that the number of nights has increased to 3, so I go to change it back to two. I then get an unhelpfully terse error message that says “Minimum stay: 3″ [See the next image]

Southwark Rose Hotel Step 3 error

At this point I’m some what irritated by the experience so go hunting for your contact us page. I see that it is a form only without an email address. I fill in the form and when I’m ready I press the “Submit” button. At this point I get an error page back that includes the message “The following information is meant for the website developer for debugging purposes.” You might want to tell those developers that this information is also useful for attackers and they shouldn’t be displaying it to the public. If the developers were any good what they would have done is get the website to log the information internally and display a general message to the user. If they wanted to tie up a user’s experiences with what is in the log then they might also include a randomly generated (say a GUID – globally unique identifier) identifier that is put in the log and displayed so a user can refer to when explaining what problems they were having at the time.

The error message that should have never been displayed is [as follows].

Vomiting SQL for no good reason

The details in the error page also contain my original complaint. I think I now understand where the American formatting of culture specific information (e.g. dates) is coming from.The company that produced your website was American and in their arrogance just assumed everyone else was just as comfortable using MONTH/DAY/year. I suspect that same arrogance was also responsible for the other failings I’ve pointed out here.

Regards,

Colin.

So, there you are. The hotel is the Southwark Rose Hotel, and their website was produced by TIG Global. (I’ve recently noticed it actually says that at the bottom of the web pages and I need not have searched for relevant press releases!). Incidentally, you can click on any of the graphics to be taken to my Flickr account to see the full sized version.

Misc

BBC repeating mindless nonsense

I’ve just read a report from the BBC that simply repeats some mindless drivel about SQL Injection Attacks from a spokesman for the US Department of Justice. According to the BBC:

Edward Wilding, a fraud investigator, told the BBC that this method was “a pretty standard way” for fraudsters to try to access personal data.

It “exploits any vulnerability in a firewall and inserts a code to gather information,” he explained.

It, however, does not point out that Mr Wilding is incorrect. It simply regurgitates the standard patter about firewalls without question. SQL Injection attacks have absolutely nothing to do with firewalls. They are all to do with leveraging mistakes in the way the application communicates with the database. An application which already has valid rights to communicate with the database. Firewalls may be in place to stop direct access to the database, but a SQL Injection attack takes a secondary route to get there.

In short, SQL Injection Attacks are the result of poor software development practices. Something, the prevalence of which, I’ve blogged about previously. I’ve also blogged about how to reduce the attack surface of the application with respect to SQL Injection attacks. In fact, it is so mind-numbingly easy to reduce the ability for someone to launch a SQL Injection Attack on an application I’m surprised developers are still allowed to get away with it.

But back to the article:

Mr Wilding said that chip-and-pin did provide some protection against SQL attacks, but there was little consumers could do to protect themselves against this kind of fraud.

You have to be kidding me! Chip-and-pin also has nothing to do with a SQL Injection attack. It cannot protect you from one. A SQL Injection Attack is an attack on a database, not the actual physical card. All chip-and-pin can do is ensure that a person using a credit or debit card knows the pin. Chip and pin is not used in online transactions. It is not used in telephone transactions.

“The real vulnerability, I suspect, is internet and telephone transactions. But this is a failure in the configuration of [corporate] firewalls,” he said.

Back to the firewall nonsense again. I repeat that firewalls cannot protect against SQL Injection Attacks because the route to the database is a valid one via a third process. (My machine runs the first process, the database is the second process the application being attacked is the third process) However, the BBC is still blithely repeating this misattribution of blame.

If blame is to be placed anywhere then it must surely be at the door of the developers who wrote the payment system that could so easily be hacked in order to gain sufficient access to the database to get all this data. This, of course, is if you believe that it was a SQL Injection Attack. The BBC could have got that bit wrong too!

UPDATE (@ 19:30)

I’ve just noticed that the BBC have reworked the article and it was last updated an hour ago. It does now contain a side box that I didn’t see before that at least, in some small way, explains that a SQL Injection Attack are “weaknesses in companies’ programming which allows them to get behind firewalls”. The main article now contains the paragraph:

The method is believed to involve exploiting errors in programming to get behind compnay [sic]  frewall’s [sic] and accessing [sic] data.

As you can see by the spelling and grammatical mistakes it must have been rather hastily put together. One quote from Mr Wilding has also changed from being reported as:

“The real vulnerability, I suspect, is internet and telephone transactions. But this is a failure in the configuration of [corporate] firewalls,” he said.

to:

“The real vulnerability [for cardholders], I suspect, is Internet and telephone transactions using credit cards were most vulnerable, he said, though added it was a failure of corporations, not customers.

Yet again, this looks like it was hastily put together because of the poor punctuation.

Come on, BBC, this is not journalism but reactive rubbish! Do you even understand what you are actually reporting?