Try our conversational search powered by Generative AI!

theodor.klostergaard@creuna.dk
Jan 30, 2009
  3496
(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)
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

Jan 30, 2009

Comments

Please login to comment.
Latest blogs
Build a headless blog with Astro and Optimizely SaaS CMS

I’m a big fan of using the right tool for the right job. I’m also a big fan of Astro , for the right use case. Let's explore Astro to see what it's...

Jacob Pretorius | May 28, 2024

Microsoft announces Natural language to SQL

Finally, Microsoft launches "Natural language to SQL," after it has been available for several months in Optimizely CMS!

Tomas Hensrud Gulla | May 23, 2024 | Syndicated blog

Five easy ways to start personalizing your content right now

If you clicked on this article, you already know that getting the right message to the right person at the right time helps drive conversions and...

Kara Andersen | May 23, 2024

ExtendedCms.TinyMceEnhancements – serwer side webp support

Today I will introduce another small feature of TinyMceEnhancements plugin. The functionality is used to automatically detect whether a browser...

Grzegorz Wiecheć | May 22, 2024 | Syndicated blog

Azure AI Language– Detect Healthcare Content in Optimizely CMS

In this blog post, I showcase how the Azure AI Language service's Text Analytics for health feature can be used to detect healthcare content within...

Anil Patel | May 22, 2024 | Syndicated blog

Stott Security Version 2 So Far

In December 2023, I unveiled the initial version of Stott Security version 2. Although I typically announce each version I release on LinkedIn and...

Mark Stott | May 22, 2024