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
Optimizely SendGrid SMTP host is deprecated

SendGrid is a services for sending email that is included in Optimizely DXP. Previously was the recommended SMTP server to use,...

Tomas Hensrud Gulla | Dec 4, 2022 | Syndicated blog

Hosting Optimizely CMS 12 on Docker Engine

Since Optimizely CMS can now be deployed as a Docker container, here is a demonstration of building, running and scaling an Optimizely CMS 12 site ...

Stefan Holm Olsen | Dec 4, 2022 | Syndicated blog

How to use CacheTagHelper with content areas in Optimizely CMS 12

I might be going out on a limb here - if you have a better solution, feel very free to share it!  Upgrading your Optimizely web application from .N...

Andreas J | Dec 2, 2022

The 1001st Piece in your 1000 Piece Puzzle: .NET Default Interface Functions

I was recently working with a client who wanted a reasonably large subsystem added to Optimizely that would add automated management to their...

Greg J | Nov 28, 2022 | Syndicated blog