Redirection Attack in ScrewTurnWiki Administration

I installed the latest verion of ScrewTurnWiki 5, first to obtain a valid database schema, and second to click through the Administration menu to figure out what can be configured in what I considered the last “official” edition of STW.

Unfortunately, I can only find the binary release, as opposed to all other versions of ScrewTurnWiki that come also with source code (or only with source code).

So I entered the Administration menu and clicked through the menu items, when suddenly a click on Global Admin Home redirected me to an ad site, and away from STW.

After a bit of research the code, it turned out that the version update check tries to retrieve information on whether a newer software version exists, but handles the result of the HTTP request incorrectly: The resulting HTML is simply copied into the admin back-end. Technically speaking, the HTML is passed through in an <asp:Literal>.

For version 5, the update check calls the URL http://www.sunhorizon.info/Version1/Wiki/1.htm, which was once a domain hosting STW, but now abandoned.

The request returns

<html><head><title>Loading…</title></head><body><script type=’text/javascript’>window.location.replace(‘http://www.sunhorizon.info/Version1/Wiki/1.htm?js=…&sid=…guid…&#8217;);</script></body></html>

and it is obvious that embedding this content as literal HTML immediately performs a Javascript redirect with tracking parameters js= and sid=, and off it goes to whatever ad netware is being lucky.

The easiest workaround to solve this redirection attack is to locate the AdminGlobalHome.aspx file, and modify the <asp:Literal ID=”lblSystemStatusContent”> element to include a property Mode=”Encode” to disable harmful HTML:

<asp:Literal ID="lblSystemStatusContent" runat="server" 
  meta:resourcekey="lblSystemStatusContentResource1" 
  Mode="Encode" />

or to set Visible=”false”.

Versions 3 and 4 seem to query http://www.screwturn.eu/Version/Wiki/3.htm and http://www.screwturn.eu/Version4.0/Wiki/4.htm, also an abandoned STW domain, but without Javascript redirection.

 

Viewing ELMAH Logs of multiple applications

I recently added ELMAH and ELMAH.Mvc to a couple of web applications, and configured them to log into the same MSSQL database.

The table ELMAH_Error distinguishes the source of error in the columns Host (storing the host name of the web application) and Application (storing the IIS Configuration Path of the web application, for explanations see e.g. here).

By default, the /elmah handler only displays the error messages of the current application.

However, I wanted to see the errors of all ELMAH-enabled applications.

After some research into the C# code and the MSSQL database, the solution is surprisingly simply:

  • Create a new database, e.g. “elmah-read”
  • Create synonyms to the original table and the logging SP (we won’t really need this SP)
CREATE SYNONYM ELMAH_Error FOR Elmah.dbo.ELMAH_Error 
GO
CREATE SYNONYM ELMAH_LogError FOR Elmah.dbo.ELMAH_LogError 
GO
  • Copy the stored procedure [dbo].[ELMAH_GetErrorXml] and remove the @Application from the WHERE clause
WHERE [ErrorId] = @ErrorId
 --AND [Application] = @Application
  • Copy the stored procedure [dbo].[ELMAH_GetErrorsXml], removing the @Application from the WHERE clauses, and extending the selected [host] column
 SELECT 
  errorId = [ErrorId], 
  application = [Application],
  host = [Host] + ' ' + 
    RIGHT( [Application], CHARINDEX( '/', REVERSE( [Application] ) + '/' ) - 1 ),
  • Create an empty ASP.Net Web Application in VS, add Elmah Core Library and Elmah.Mvc from NuGet, and configure the connection string as in the original web applications
  • To allow remote access to the log viewer, add in web.config:
<elmah>
      <security allowRemoteAccess="true"/>
</elmah>
  • Don’t forget to properly configure the Authentication feature of the log viewer application in IIS
  • Run

Feature Request

You know that your product is missing a critical feature, if a quick search (case in point: “Firefox search bookmark folder name”) brings up forum entries dating back at least 5 years:

"firefox search bookmark folder name"

“firefox search bookmark folder name”

Surprises Changing the Length of a Character Field in SSMS

I needed to convert a couple of CHAR and NCHAR columns to their VARCHAR/NVARCHAR equivalent, and noticed a peculiar behavior of the SSMS (2008 R2) table designer:

For example, when the original column was defined as CHAR(10), and I added the characters VAR at the beginning of the Data Type field and tabbed to the next column, the editor would not keep the resulting VARCHAR(10), but change the data type to VARCHAR(50), as is the default length for VARCHAR and NVARCHAR columns in the designer.

I found that this behavior was discussed in this SO question as occurring in SSMS 2005, confirmed in 2008, and, by personal experience, still exists in 2008R2 and 2012.

It seems that MS either does not care, or that it does not want to fix weird bugs due to “compatibility issues”.

VS – Side-Effects of Upgrading a Project’s .Net Framework Version

The other day I was playing around with two of my applications, checktsql and SMOscript, as I was considering to include the functionality of checktsql into SMOscript.

Now, up to the current versions, both applications have been developed using Visual Studio 2008 targeting .Net 2.0. (I prefer to keep requirements to a minimum). But now I thought it was time to migrated them to VS2010/3.5 (client framework), and came across a couple of unresolved mysteries.

Missing Exceptions in SqlCommand.Execute*()

The first mystery is that the behavior of the Execute* methods changed such that ExecuteNonQuery() and ExecuteReader() do not reliably raise exceptions any more if an error occurs in T-SQL.

Take the example

CREATE PROCEDURE TestTemp AS
    CREATE TABLE #T (id int)    
    SELECT * FROM #T    
GO

which checktsql tries to verify (with the SET FMTONLY ON option) like this

BEGIN TRAN
SET FMTONLY ON; EXECUTE [dbo].TestTemp
ROLLBACK

(Note that the transaction is actually created by SqlConnection.BeginTransaction(), but essentially it’s the same mechanism)

SSMS returns an error:

Msg 208, Level 16, State 0, Procedure TestTemp, Line 6
Invalid object name '#T'.

What I noticed after migrating the code to VS2010/.Net3.5 is that some (most?) stored procedures cause the error to be raised as an exception in .Net, and some do not. However, the behavior is consistent for each stored procedure.

Fortunately, I noticed the different behavior only when using the SET FMTONLY ON option, but still, there is a difference depending on which VS or framework version is used.

Teh internets did not really help me – I found a couple of discussions, but no documentation of the change, or how to get the original semantics back:

SMO dependency on System.Core

When I tried to debug (and understand) above issue, and reverted the application back to .Net 2.0, but still build with VS2010, I suddenly got the error message during build:

The primary reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” could not be resolved because it has an indirect dependency on the framework assembly “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089” which could not be resolved in the currently targeted framework. “.NETFramework,Version=v2.0”. To resolve this problem, either remove the reference “Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL” or retarget your application to a framework version which contains “System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

There was nothing I could do to get VS2010 to the original behavior (i.e.: compile successfully) targeting .Net 2.0, even though the same assemblies were referenced both in the VS2008 and the VS2010 project.

This thread on MS connect shows the “amusing” dependency zigzag that SMO assemblies implement, switch dependencies even between minor releases:

Posted by Chris Dennis on 4/20/2011 at 10:19 AM
Installing SQL Server 2008R2 Cumulative Update 7 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1777.0 which again depends on System.Core v3.5.
Posted by Chris Dennis on 3/25/2011 at 12:50 PM
Installing SQL Server 2008R2 Cumulative Update 6 upgrades Microsoft.SqlServer.Management.SqlParser to 10.50.1765.0 which does not depend on System.Core v3.5. Only version 10.50.1750.9 has a dependency on System.Core v3.5.
Posted by Microsoft on 3/25/2011 at 11:08 AM
Your assertion is correct the Microsoft.SqlServer.Management.SqlParser took a dependency on System.Core v3.5. Any projects which reference this assembly directly or indirectly need to be targeted to .NET Framework V3.5.
This is a nightmare!

Dealing with NHibernate System.IndexOutOfRangeException

Whenever I call SQL Server stored procedures from an NHibernate (3.2) application, I generate proxy methods that look like this

IList<T> MyProcedure<T>(ISession session
  , [some parameters]
)
{
  IQuery sp = session.CreateSQLQuery(@"BEGIN
    EXECUTE MyProcedure
            :SomeParameter
END")
    .AddEntity(typeof(T));
  sp.SetParameter<[datatype]>("SomeParameter", SomeParameter);
  ...;
  sp.SetResultTransformer(Transformers.RootEntity);
  //sp.SetTimeout(timeout);
  return sp.List<T>();
}

and manually create the data class MyProcedureResult and the corresponding mapping class MyProcedure_Result.

The SP is invoked like this:

var result = Procedures.MyProcedure<MyProcedureResult>(session, [parameters]);

This usually works, but recently I experienced an exception when calling a specific SP:

[Data]

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.IndexOutOfRangeException: [Data]

Source Error: 

Stack Trace: 

[IndexOutOfRangeException: [Data]]
   System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) +5043194
   System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) +67
   NHibernate.Driver.NHybridDataReader.GetOrdinal(String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Driver\NHybridDataReader.cs:363
   NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String name) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:236
   NHibernate.Type.NullableType.NullSafeGet(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\NullableType.cs:195
   NHibernate.Type.AbstractType.Hydrate(IDataReader rs, String[] names, ISessionImplementor session, Object owner) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Type\AbstractType.cs:131
   NHibernate.Persister.Entity.AbstractEntityPersister.Hydrate(IDataReader rs, Object id, Object obj, ILoadable rootLoadable, String[][] suffixedPropertyColumns, Boolean allProperties, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Persister\Entity\AbstractEntityPersister.cs:2511
   NHibernate.Loader.Loader.LoadFromResultSet(IDataReader rs, Int32 i, Object obj, String instanceClass, EntityKey key, String rowIdAlias, LockMode lockMode, ILoadable rootPersister, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:988
   NHibernate.Loader.Loader.InstanceNotYetLoaded(IDataReader dr, Int32 i, ILoadable persister, EntityKey key, LockMode lockMode, String rowIdAlias, EntityKey optionalObjectKey, Object optionalObject, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:943
   NHibernate.Loader.Loader.GetRow(IDataReader rs, ILoadable[] persisters, EntityKey[] keys, Object optionalObject, EntityKey optionalObjectKey, LockMode[] lockModes, IList hydratedObjects, ISessionImplementor session) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:875
   NHibernate.Loader.Loader.GetRowFromResultSet(IDataReader resultSet, ISessionImplementor session, QueryParameters queryParameters, LockMode[] lockModeArray, EntityKey optionalObjectKey, IList hydratedObjects, EntityKey[] keys, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:334
   NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:468
   NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:246
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1590

[GenericADOException: could not execute query
[ BEGIN
    EXECUTE MyProcedure
            @p0
           ,@p1
END ]
[SQL: BEGIN
    EXECUTE MyProcedure
            @p0
           ,@p1
END]]
   NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1599
   NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1497
   NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces, IType[] resultTypes) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Loader\Loader.cs:1487
   NHibernate.Impl.StatelessSessionImpl.ListCustomQuery(ICustomQuery customQuery, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:376
   NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters, IList results) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:353
   NHibernate.Impl.StatelessSessionImpl.List(NativeSQLQuerySpecification spec, QueryParameters queryParameters) in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\StatelessSessionImpl.cs:363
   NHibernate.Impl.SqlQueryImpl.List() in d:\CSharp\NH\NH\nhibernate\src\NHibernate\Impl\SqlQueryImpl.cs:160

Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.547

As you can see, NH expects the column name [Data], which does not exist in the result set, causing GetOrdinal() to throw an exception.

What’s going on here?

Well, the stored procedure returns a result set containing the column name Data, but NH tries to find the column [Data].

After stepping through the source code of NH 3.2, I claim to partially understand how this happens.

When the entities and mappings are initialized, NH calls connection.GetSchema(DbMetaDataCollectionNames.ReservedWords) to check whether column names are reserved words. If a column name is found to be a reserved word, the name is flagged to be quoted and/or aliased when used in SQL.

If, however, the column name occurs in the result set of an SP, aliasing cannot be applied, and quoting is not required.

Quoting using backticks (`name`) as proposed on SO did not help either.

After many experiments, my solution was to simply rename the columns in the SP (DataX), and add a column name mapping

map.Column("DataX");

to the class map.