A postrophe a day keeps the doctor busy

A little while back one of our customers called us to say the FileManager was broken (this was on an EPiServer 4.6 site). They got an exception when trying to open it.

The exception was an SQL exception.

It turned out that one of the editors had created a folder called: "DVD'ere". Ah...

EPiServer was in a manner of speaking performing an SQL injection attack on itself by not escaping the apostrophe in the folder name.

So we figured it would be enough to find the entry in the datatable, rename the folder name manually and everything would be fine. But it wasn't. The name of the folder was somehow still present in the database.

Luckily there was EPiServer support - even though they couldn't give us a hotfix, they guided us through which procedures had to be executed in which order. And all was well again. The customer was happy.

Then another editor created a folder called "CD'ere"...

So we took all the statements and wrapped them in a nice tight bundle and now when an editor decides it's time use an apostrophe, we can run this script exchanging the foldername. To be perfectly on the safe side, you could argue that it should be wrapped in a transaction, but we never got around to that...

Use at own risk and remember to backup your database first. It has been used for folders that are created with an apostrophe. We haven't used it for the day when somebody decides to rename an existing folder containing folders and files. But then you have EPiServer support :-)

NB: Later again we edited the EPiServer files that are used for creating and renaming files and folders, so it warns and halts when an apopstrophe is in the name - if you do that you won't need this script at all :-7

DECLARE @folderID uniqueidentifier;
DECLARE @parentID uniqueidentifier;
DECLARE @folderName varchar(200);

SET @folderName = 'insert_your_foldername_here' -- 'DVD''ere'
SELECT     @folderID = pkID
FROM         tblItem
WHERE     (Name LIKE @folderName);

IF (@folderID IS NULL)
BEGIN
    SELECT 'Could not find the folder: "' + @folderName + '"';
END
ELSE
BEGIN
    SELECT @parentID = FromId FROM tblRelation WHERE toID=@folderID
    CREATE TABLE #relationTable (toID uniqueidentifier);
    INSERT INTO #relationTable EXEC RelationListFrom @FromId=@folderID,@SchemaId=0;
    DECLARE @numberOfRelations int;
    SELECT @numberOfRelations = COUNT(*) FROM #relationTable;
    DROP TABLE #relationTable;
    IF (@numberOfRelations = 0)
    BEGIN
        EXEC RelationRemove @FromId=@parentID,@ToId=@folderID      
        EXEC ItemDelete @Id=@folderID
        SELECT 'Item deleted';
    END
    ELSE
        SELECT 'Did not delete';
END