Jan 30, 2009
(0 votes)

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)
    SELECT 'Could not find the folder: "' + @folderName + '"';
    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)
        EXEC RelationRemove @FromId=@parentID,@ToId=@folderID      
        EXEC ItemDelete @Id=@folderID
        SELECT 'Item deleted';
        SELECT 'Did not delete';

Jan 30, 2009


Please login to comment.
Latest blogs
Content Delivery API – The Case of the Duplicate API Refresh Token

Creating a custom refresh provider to resolve the issues with duplicate tokens in the DXC The post Content Delivery API – The Case of the Duplicate...

David Lewis | Sep 29, 2022 | Syndicated blog

New Optimizely certifications - register for beta testing before November 1st

In January 2023, Optimizely is making updates to the current versions of our certification exams to make sure that each exam covers the necessary...

Jamilia Buzurukova | Sep 28, 2022

Optimizely community meetup - Sept 29 (virtual + Melbourne)

Super excited to be presenting this Thursday the 29th of September at the Optimizely community meetup. For the full details and RSVP's see the...

Ynze | Sep 27, 2022 | Syndicated blog

Preview multiple Visitor Groups directly while browsing your Optimizely site

Visitor groups are great - it's an easy way to add personalization towards market segments to your site. But it does come with it's own set of...

Allan Thraen | Sep 26, 2022 | Syndicated blog

The Report Center is finally back in Optimizely CMS 12

With Episerver.CMS.UI 12.12.0 the Report Center is finally re-introduced in the core product.

Tomas Hensrud Gulla | Sep 26, 2022 | Syndicated blog

Dynamic Route in ASP.NET Core When MapDynamicControllerRoute Does Not Work

Background Creating one of the add-on for Optimizely I had to deal with challenge to register dynamically route for the API controller. Dynamic rou...

valdis | Sep 25, 2022 | Syndicated blog