Saturday, 12-Mar-16 20:21:00 CET

Porting my blog for the second time, editing part 2

This is post #47 of my series about how I port this blog from Blogengine.NET 2.5 ASPX on a Windows Server 2003 to a Linux Ubuntu server, Apache2, MySQL and PHP. A so called LAMP. The introduction to this project can be found in this blog post /post/Porting-my-blog-for-the-second-time-Project-can-start.

I started using CkEditor and forgot that I had to launch the editor from a page with proper UTF-8 header so that all accented characters broke. That way I lost two hours. My fault.

The next error I made was missing an end bracket of body so that the scripts etc would not be found. Lost hours there as well. My fault.

I must say the documentation of CKEditor is rather good but it must be frustrating answering questions from people stuck in simple mistakes like the ones I made until now.

Do you remember the injection protection I had been working on in post #36 (https://www.malmgren.nl/post/Porting-my-blog-for-the-second-time-right-sidebar-part-1)? Then I found that text with a question mark could break the injection protection. Any question mark already inserted earlier was 'hijacked' and the question mark I had intended to use was not resolved. So I had to repair the injection protection to turn question marks in arguments into literals.

Another problem with my injection protection was that I converted newlines to html br tags. Well that way I had loads of extra br tags when saving the text. My fault.

After I recovered from all these mistakes I could actually start using CKEditor! But how about the images? Well they come from the database wrapped in place holders so they would show up in the editor as curly bracket URL things so what should I do about them? I had to resolve them of course. Since I already made a routine for this I could just use that to convert the images to real links to images. So this works really well when loading the editor from data that was entered through my import routines but what happens then with the images when I save the text back to the database? If I do nothing they will be saved like resolved. That would be fine but I would prefer if they were converted back to place holders for the URLs.

For this I created a parser that runs over the text and finds the URL records available in the database and resolve these with place holders. What I still need to do it to find out how to handle new images.

To make it possible to change the categories, to check and uncheck a category, I need to render a list of check box items for all the categories. Here below you see how I do this.

$strCatList = "≺br≻";

$strCatQuery = "SELECT c.ID, c.Title, c.Description, c.ID IN (SELECT pc.CategoryID FROM PostCategory pc, Post p WHERE p.ID = pc.PostID AND p.Slug = ?) AS Checked FROM Category c";
$query = GetQueryWithData(1, $strCatQuery, $_SESSION['post']);
$result = $mysqli-≻query($query) or die("Error query.." . mysqli_error($mysqli));
while ($row = mysqli_fetch_array($result))
{
	$strCatID = $row['ID'];
	$strCatTitle = $row['Title'];
	$strCatDescription = $row['Description'];
	if ($row['Checked'] == "1")
	{
		$strCatChecked = "checked";
	}
	else
	{
		$strCatChecked = "";
	}
	
	$strCatList .= "≺input type='checkbox' name='Cat:$strCatID' value='1' $strCatChecked≻$strCatDescription≺br≻";
}

echo $strCatList;

As you might recall the form has as action to call the same php program. So when the page is reloading as a result of the submit we can walk over all the checkboxes and collect a list of IDs of the checked categories. Like here below:

$_strSetCatIDs = "";
foreach($_POST as $key =≻ $value)
{
	if ($_strSetCatIDs != "")
	{
		$_strSetCatIDs .= ", ";
	}
	if (strstr($key, 'Cat:'))
	{
		$x = str_replace('Cat:','',$key);
		$_strSetCatIDs .= $x;
	}
}

The result is a commaseparated list of IDs. There is also a little space after the comma on line 6. Then it starts to become really complex really. The idea here is to create one insert query and one delete query. To do this I need to compare the old situation before edit with the situation as result of the edit by the user. The new situation is given by the previous snippet.

The query creates a combination table of the previous situation and the new situation. Then it is a question of walking the table and create the delete and the insert query clauses.

$strQueryCheckedChanged = "SELECT (SELECT ID FROM Post WHERE Slug = ?) AS PostID, c.ID AS CategoryID, c.ID IN (SELECT pc.CategoryID FROM PostCategory pc, Post p WHERE p.ID = pc.PostID AND p.Slug = ?) AS CheckedBefore, c.ID IN (?) AS CheckedNow FROM Category c";
$query = GetQueryWithData(0, $strQueryCheckedChanged, $_SESSION['post'], $_SESSION['post'], $_strSetCatIDs);
$result = $mysqli-≻query($query) or die("error query.." . mysqli_error($mysqli));
$strInsertPostCategoryClause = "";
$strDeletePostCategoryClause = "";
while ($row = mysqli_fetch_array($result))
{
	$strCheckedBefore = $row['CheckedBefore'];
	$strCheckedNow = $row['CheckedNow'];
	$strCategoryID = $row['CategoryID'];
	$strPostID = $row['PostID'];
	if ($strCheckedBefore != $strCheckedNow)
	{
		# Dectected difference in checkbox setting.
		if ($strCheckedBefore == 1)
		{
			# If checkedbefore then a checkbox has been removed.
			if ($strDeletePostCategoryClause == "")
			{
				$strDeletePostCategoryClause .= "(";
			}
			else
			{
				$strDeletePostCategoryClause .= ", ";
			}
			$strDeletePostCategoryClause .= "(" . $strPostID . "," . $strCategoryID . ")";
		}
		else
		{
			# We are here becuase of a difference and it is not that checkedbefore was checked so it is checked now
			if ($strInsertPostCategoryClause != "")
			{
				$strInsertPostCategoryClause .= ",";
			}
			$strInsertPostCategoryClause .= "(" . $strPostID . "," . $strCategoryID . ")";
		}
	}
}
if ($strDeletePostCategoryClause != "")
{
	$strDeletePostCategoryClause .= ")";
	$strDeletePostCategoryClause = "DELETE FROM PostCategory WHERE (PostID, CategoryID) IN " . $strDeletePostCategoryClause;
	$result = $mysqli-≻query($strDeletePostCategoryClause) or die("error query.." . mysqli_error($mysqli));
}

if ($strInsertPostCategoryClause != "")
{
	$strInsertPostCategoryClause = "INSERT INTO PostCategory (PostID, CategoryID) VALUES " . $strInsertPostCategoryClause;
	$result = $mysqli-≻query($strInsertPostCategoryClause) or die("error query.." . mysqli_error($mysqli));
}

One of the challenges here was that my injection protection did what I wanted, protect! So I had to teach it to handle this case properly.