The wonders of multicast DNS

“Every release of Ubuntu is more like the Mac.” That’s what one of my coworkers said when I pointed out that you no longer need to pass around IP addresses for copying files from one workstation to another on the LAN. The machines announce themselves on the LAN by multicast DNS. This means that you can acccess other machines as hostname.local and It Just Works™.

And it’s not just Ubuntu, the service is provided by avahi-daemon and the name resolution works thanks to nss-mdns. Fedora and SuSE also have these pieces of software in the default configuration.

It’s strange how few people have noticed.

Tutorial: Role-Based Access Control with CakePHP

In an earlier post I talked about access control with CakePHP‘s AclComponent and how it might be used for the simple-yet-powerful roled-based access control, where users can have multiple roles and permission checks are reduced to verifying if the user has a certain role. In this tutorial we’ll add role based access to the CakeBB sample app. It is a basic forum system where you have several forums, each forum has several topics, and topics have at least one post. Users can create forums, start new topics, post replies to topics and so on.

Currently CakeBB has no concept of users, which means that anyone can do anything and everyone’s posts show up as by the “Guest” user. To fix that we’ll add a user model, require that users log in for any action that’s not just reading, and enforce roles.

About AuthComponent

What is “auth,” anyway? Well it turns out that there are two kinds: authentication and authorization, and it’s important to make a clear distinction. Both are needed to build a secure system.

Authentication is making sure that you are who you say you are. Like when they ask to see your passport when boarding a flight on an airport. They want to be sure it’s really you and not some random guy that stole your boarding pass and says he’s you.

Authorization is making sure that you are allowed to do what you are about to do. Like when they ask to see you boarding pass when boarding a flight. They want to be sure you’re actually on that flight.

CakePHP’s AuthComponent does both. It checks authentication against a database table, username-and-password-style. It can check authorization in five different ways. It’s actually really easy to use, but the API may seem confusing because there is no high level distinction between authentication and authorization.

Adding authentication to CakeBB

Download CakeBB here or get the latest version from github. I assume you already have done some stuff with CakePHP so I won’t walk through setting up mod_rewrite or configuring a database. The CakePHP book already does a good job with it.

Create the database tables for CakeBB with app/config/schema/cakebb.sql.

Adding a user model

In a real forum you’d probably store all kinds of information about the users: email address, time of last activity, IP address and so on. Here we’ll get by with just the user name and password. Let’s go and create the the Cake model class and the database table:

<?php
// app/models/user.php
class Users extends AppModel {
    var $name = 'User';
    var $hasMany = array (
        'Topics' => array ('className'=>'Topic'),
        'Posts' => array ('className'=>'Post'),
    );
}
?>
CREATE TABLE users (
  id INTEGER NOT NULL AUTO_INCREMENT,
  username VARCHAR(60),
  password CHAR(40),
  PRIMARY KEY(id),
  UNIQUE INDEX(username)
)

Why CHAR(40) for password? Because AuthComponent generates a 40-character hash code and uses it instead of the plain password.

While we’re in the database, let’s add the user_id column to the topics and posts, so we have some way of knowing who wrote them.

ALTER TABLE topics ADD COLUMN user_id INTEGER;
ALTER TABLE posts ADD COLUMN user_id INTEGER;

Also edit the Post and Topic models to add the “belongs to User” relationship. Below an example for the Post model.

// app/models/post.php
    var $belongsTo = array (
	'Topic' => array (
	    'className' => 'Topic',
	),
	'User' => array (
	    'className' => 'User',
	),
    );

Requiring users to log in

Let’s first secure the application. Cake’s AppController class is perfect for that: what ever you put there is inherited by all the other controllers since it’s the super class. Create app/app_controller.php, it should look like this:

<!-- app/app_controller.php -->
<?php
class AppController extends Controller {
    var $components = array ('Auth', 'Acl', 'Session');

    function beforeFilter() {
        // This callback is executed before the action
        // don't require logging in for index and view actions
        $this->Auth->allow('index', 'view');
    }
}
?>

You can now browse the existing forums and read posts. For anything else you are redirected to /users/login. There you are greeted with a big red error message telling you that the users controller doesn’t exist.

So let’s create users controller in app/controllers/users_controller.php. The users controller must have methods for logging in and out:

<?php
class UsersController extends AppController {
    var $name = 'Users';
    var $scaffold;

    function login() {
	// implemented by AuthComponent
    }
    function logout() {
	$this->redirect($this->Auth->logout());
    }
}
?>

The “login” method can be left empty because AuthComponent verifies the password. The “logout” method logs the user out by calling Auth->logout and redirects the user to another page. Thanks to cakephp’s scaffolding we get a basic admin interface for free.

Storing the user id in posts and topics

If you now try to post a topic or a reply it still shows up by Guest. This is because the user id is not being stored. To fix it we have to edit the add action in TopicsController, and the reply action in PostsController. We modify the posted form data to include the missing user_id field.

// app/controllers/topics_controller.php
    function add($forumId) {
	if (!empty($this->data)) {
	    $this->data['Topic']['user_id'] = $this->Auth->user('id');
	    $this->data['Posts'][0]['user_id'] = $this->Auth->user('id');
	    if ($this->Topic->saveAll($this->data)) {
	        ...
// app/controllers/posts_controller.php
    function reply($id = null) {
	$this->autoRender = false;
	if ($this->data) {
	    $this->data['Post']['user_id'] = $this->Auth->user('id');
	    if ($this->Post->save($this->data)) {
	        ...

As you can see, the id of the current user can be accessed through AuthComponent.

View and layouts

We’re still missing a basic login page where you could actually type in their username and password. Place this into app/views/users/login.ctp:

<h2>Log in</h2>

<?php
echo $form->create('User');
echo $form->input('username');
echo $form->input('password');
echo $form->end();
?>

If you now try logging in you are sent back to the log in page with now error message. It’s not surprising that it doesn’t work, we have yet to create a user account. But we really should show error messages. So open app/views/layouts/default.ctp, find the div with id=”content” and add and echo for the “auth” flash message:

<div id="content">
    <?php echo $this->Session->flash('auth'); ?>
    <?php echo $this->Session->flash(); ?>
    <?php echo $content_for_layout; ?>
</div>

Creating the first user account

To create the first user account

  1. pick a username and password and
  2. try to log in.

Ignore the error message, go to the SQL debug output in the bottom of the page. Copy the hashed password from the query that failed to return rows, and add the user to the users table manually. For example if you picked user “foo” and password “bar”:

INSERT INTO users (username, password) VALUES ('foo',
    'c1f5bd1d0e4cd2321e5cc6752a87676513fccff6');

Now you should be able to log in to the application and use everything as before.

Creating roles

We’ll establish three kinds of user roles: admins, mods, and regular users. Like in the earlier post, let’s assume the admin role doesn’t automatically grant the mod role’s permissions just to make things more interesting. The easiest way to create the role structure is with Cake’s AclShell. Open a terminal, go to the application directory, and give the following commands:

cake/console/cake acl initdb
cake/console/cake acl create aco / forum
cake/console/cake acl create aco forum admin
cake/console/cake acl create aco forum mod
cake/console/cake acl create aco mod user

Separating the roles from the root–like here with the “forum” node–may be a good idea. Consider the possibility that in the future you may need to add a new major feature to your application with access controlled through a new role hierarchy. The ACO/role tree now looks like this:

cake/console/cake acl view aco
Aco tree:
---------------------------------------------------------------
  [1] forum
    [2] admin
    [3] mod
      [4] user
---------------------------------------------------------------

Granting access to roles in AclShell

To bootstrap working with ACL you’ll have to create the ARO for a user and assign permissions manually. To make things not fail in the beginning we’ll give ourselves all the forum permissions. Assuming you’ve already created a user for yourself and it has id 1, give the following commands:

cake/console/cake acl create aro / forum-users
cake/console/cake acl grant forum-users user all
cake/console/cake acl create aro forum-users User.1
cake/console/cake acl grant User.1 forum all

Like with the roles, we are separating the users from the root in case later you need to do things differently.

What about the users that we create through the application, don’t they need AROs as well? They do, and it can be automated with Cake’s ACL behaviour. Just add the following snippet to the User model class.

// app/models/user.php
    var $actsAs = array('Acl' => array ('type'=>'requester'));
    function parentNode() {
        return 'forum-users';
    }

I find the ARO tree structure less useful than the ACO tree but I guess it makes sense if you have a lot of users and want to assign default roles based on the users’ department or group. Now that we have the roles in place and one role assigned to a user, let’s see how we would program this beast.

Checking the user is authorized

Like I said above, AuthComponent has five ways to check authorization. Which method is used is controlled by the $authorize member variable. Here we set $authorize to ‘controller’ in AppController’s beforeFilter, which means that AuthComponent will call the controller’s isAuthorized

// app/app_controller.php
    function beforeFilter() {
        // don't require logging in for index and view actions
        $this->Auth->allow('index', 'view');
        // check authorization by calling isAuthorized
        $this->Auth->authorize = 'controller';
    }

(If you are wondering about the four other values you can use: Long story short, if you set it to the string controller, model, or object, AuthComponent delegates the check to a method called “isAuthorized” of another object, which can be a controller, a model, or an arbitrary PHP object. The other two options are “crud” and “actions.” If you use them AuthComponent will assume the actions or crud are defined as ACOs and check access with AclComponent.)

Implementing isAuthorized

OK, now we want to make sure that the forum users don’t do anything their roles don’t permit. For example, we require the user has the admin role to add, edit, or delete forums and users. This is done by adding the following snippet.

// app/controllers/forums_controller.php, users_controller.php
    function isAuthorized() {
	$userId = $this->Auth->user('id');
	$aro = array('model' => 'User', 'foreign_key' => $userId);
	return $this->Acl->check($aro, 'forum/admin', '*');
    }

The index and view actions will still be accessible because we allow them in the AppController. As you can see the access check is handled with the Acl component.

The case for topics and posts is a little different because we want normal users to be able to modify or delete only their own posts, while users with the mod role can edit and delete any posts. This means that for the edit and delete actions we need to check whose post it is, like below.

// app/controllers/topics_controller.php
    function isAuthorized() {
	$userId = $this->Auth->user('id');
	$aro = array('model' => 'User', 'foreign_key' => $userId);
	// Require "mod" role for editing other's posts
	$role = 'forum/mod/user';
	switch ($this->action) {
	case 'delete':
	case 'edit':
	    $this->Topic->id = $this->params['pass'][0];
	    $ownerId = $this->Topic->field('user_id');
	    if ($ownerId != $userId) $role = 'forum/mod';
	}
	return $this->Acl->check($aro, $role, '*');
    }

The code to add to PostsController is the same, replacing Topic with Post.

We’re pretty much done by now, only one thing is missing.

Assigning roles through the application

Now you can add users, but how do give the moderator or admin role to someone? You could use AclShell like we did before, but it would be nicer to be able to do it via web.

Granting access to roles with AclComponent

When a user shows to be trustworthy you can promote her to moderator:

// app/controllers/users_controller.php
    function promoteToModerator($userId) {
	$aro = array('model'=>'User', 'foreign_key'=>$userId);
	$this->Acl->allow($aro, 'forum/mod', '*');
	$this->Session->setFlash('User promoted to moderator');
	$this->redirect(array('action'=>'index'));
    }

If the user abuses her power and you have to demote her, call inherit instead of allow:

$this->Acl->inherit($aro, 'forum/mod', '*');

You could use deny instead of inherit but be aware of the difference. If at a later point you decide to grant the role to the user’s group/department (i.e. ARO parent node). With deny you actively block the user from the role, no matter what permissions her group has. With inherit you merely revert the access to the default values.

Now you can promote and demote users by accessing URLs directly. Building a complete control panel with role assignment is an exercise left to the reader :)

Well, that’s it for today. We have gone from not having any user information to having a sophisticated ACL controlling what users can do. As always, if you find any errors or have comments or criticism drop me a line.

Simpler role-based access control for CakePHP

It’s been a rough ride but I think I’m finally getting the hang of CakePHP‘s AclComponent. The official documentation kind of sucks, unless you’re a Lord of the Rings fan. Great, but how do I apply this in a real world application.

I needed full role-based access control in a -application I’m working on. In the end I did find an elegant way to do it with Cake’s ACL implementation.

It doesn’t surprise me that many Cake users have built solutions of their own (like Studio Canaria) or have decided to copy the solution from the ACL blog tutorial where users can have only one role (like Gilang, lemoncake, or realm3). That’s fine if in your application you never need more than one role per user, but this was not my case.

Why role-based access control?

In role-based access control roles are sets of permissions to perform operations. Application users are given access to roles instead of individual permissions. Since the user-to-permission assignment is now decoupled through the role, doing things like promoting  a user from to an admin becomes easy.

Note that the definition of which permissions go with which role doesn’t have to be configurable. Things like record-level protection, where a user to edit a post only if she is the post’s owner, might even be impossible to do through ACL only. (Related video: Zed Shaw – The ACL is Dead) The permission-to-role assignment may have to be hard-coded in the application logic. Guess what: it often is.

Roles in CakePHP’s ACL

Since you are reading this you probably already know that CakePHP’s ACL consists of trees of AROs and ACOs, and of relationships between them. You’ll recall that AROs are “access request objects,” i.e. who we want to protect the ACOs from. ACOs are “access control objects,” i.e. what we want to protect.

You might think that roles should be AROs because “they access stuff.” In RBAC roles are sets of permissions, and users want to access the roles. Now users are AROs, and roles are ACOs. (Edward A. Webb has a similar system but doesn’t call it RBAC.)

For example, suppose we are constructing a forum system. Immediately we detect sets of permissions to use in access control: creating forums and changing system settings (admin role), dealing with spam and misbehaving users (mod role), and creating new topics and posts (user role).

Forum role permission nesting
Nesting of roles. Mod role's permissions include the user role's.

ACOs make a tree, they can be nested. Nesting means that we can make subroles that give only a part of the permissions. For example if we define the user role within the mod role, users with mod role automatically get access to the normal user stuff. For the sake of the example let’s make the admin role unrelated. Now having the admin role doesn’t automatically give you the right to ban users or post new topics. It might not make sense in a realworld forum but let’s do it anyway just to make things more interesting.

This role structure is easily created with the AclShell:

cake acl create aco / Forum
cake acl create aco Forum Mod
cake acl create aco Mod User
cake acl create aco Forum Admin

Now the user’s access to a particular role can be checked with something like

$this->Acl->check($aro, 'Forum/Mod', '*')

That’s really all there is to it. Coming up next: Full CakePHP Auth + ACL tutorial.

Not so poor man’s cron jobs for CakePHP

A.k.a. “How to make AuthComponent think the user is someone else”

The other day I needed to add subscribable email reports to a CakePHP web application. The system should generate the reports automatically every few days and send them automatically to their owners. In a Unix setting that would be job for cron.

Unfortunately my hosting, while otherwise awesome, doesn’t offer cron. The usual solution in these cases is “poor man’s cron:” on every page load, check and execute any pending scheduled jobs. The tricky bit is this: How do you convince AuthComponent (and the rest of the application) that the job is executed by its owner, not by the visitor?

Background: Cron is a Unix daemon for scheduled job execution. Cron is often used for system maintenance, but normal users can use it too. The output of a job is emailed to its owner.

The model

Since users need to be able to define their own jobs I decided it makes sense to store the jobs in the database. The data we need are foreign key to the User model “owner_id,” a schedule, a subject for the emails that are sent, and the URL of the job for execution. The schedule can be defined as a “date of next execution” and the execution period in days since all I need is execute the action every N days.

CREATE TABLE periodic_jobs (
  id        int NOT NULL AUTO_INCREMENT,
  owner_id  int DEFAULT NULL,
  next_exec date DEFAULT NULL,
  period    int DEFAULT NULL,
  subject   tinytext DEFAULT NULL,
  url       tinytext DEFAULT NULL,
  PRIMARY KEY (id), INDEX (next_exec)
);

Then we’ll need a Cake model class to bind to the user model. Mine is called User, you might have to change this:

<?php
// app/models/periodic_job.php
class PeriodicJob extends AppModel {
    var $name = 'PeriodicJob';
    var $belongsTo = array (
        'Owner' => array (
            'className' => 'User', // change this if needed
            'foreignKey' => 'owner_id'
    ));
}
?>

The controller

Cake makes it very easy to execute arbitrary actions. The requestAction function generates a fully rendered view, ready to be sent to the users with EmailComponent. Great, so we can stick a bunch of requestAction calls in some AppController callback, and be done with it!

But remember that requestAction function will create a new instance of AuthComponent to use with the new request. How do we execute the job as its owner, considering the application uses AuthComponent?

Switching users turns out to be pretty easy. AuthComponent stores the information of the authenticated user in the session, with sessionKey as the key. All you need is one call to Session->write, and all instances of AuthComponent in Cake will assume the other user’s identity. But there’s still one small complication.

If the user hasn’t logged in yet AuthComponent::sessionKey will not be set. In this case we just have to assume the default value for sessionKey. Setting it won’t do any good because requestAction will create a new instance of AuthComponent.

The code for checking for and executing pending jobs will look like below. I’ve created a new controller to keep AppController as light as possible.

<?php
// app/controllers/periodic_jobs.php

define('EMAIL_FROM', 'periodicjobs@example.com');

class PeriodicJobsController extends AppController {

    var $scaffold; // quick and dirty admin interface
    var $components = array ('Email');

    function checkAndExecutePending() {
	$this->autoRender = false;
	$jobs = $this->PeriodicJob->find('all', array (
	    'conditions' => array (
		'next_exec < NOW()'
 	    )
 	));
        // Save user from original request
        $origSession = false;
        if ($this->Auth->sessionKey) {
            $sessionKey = $this->Auth->sessionKey;
            $origSession = $this->Session->read($sessionKey);
        } else {
            // Assume AuthComponent will use the default sessionKey
            $sessionKey = 'Auth.'.$this->Auth->userModel;
        }
	foreach ($jobs as $job) {
	    $email = $job['Owner']['email'];
	    $url = $job['PeriodicJob']['url'];
            $period = $job['PeriodicJob']['period'];
            // Assume owner's identity
	    $this->Session->write($sessionKey, $job['Owner']);
            // Run the job
	    $result =& $this->requestAction($url, array('return'));
            // Email the results, update date if successful
	    $this->Email->reset();
	    $this->Email->from = EMAIL_FROM;
	    $this->Email->to = $email;
	    $this->Email->subject = $job['PeriodicJob']['subject'];
	    $this->Email->sendAs = 'html';
	    if ($this->Email->send($result)) {
		$this->log("Emailed $url to $email", 'periodic-jobs');
		$this->PeriodicJob->id = $job['PeriodicJob']['id'];
		$this->PeriodicJob->saveField('next_exec',
                        strftime('%F', strtotime("+$period days")));
	    } else {
		$this->log("Can't email $url to $email", 'periodic-jobs');
	    }
	}
        // Restore user from original request, or delete if not logged in
        if ($origSession) {
	    $this->Session->write($sessionKey, $origSession);
        } else {
            $this->Session->delete($sessionKey);
        }
    }
}
?>

Integrating with AppController

We’ll use the AppController::afterFilter-hook to check and execute pending jobs on every page load. To save resources we can limit the checks to once per session. This also prevents an infinite recursive loop, where the afterFilter executed for a PeriodicJob would call checkAndExecutePending again.

<?php
// app/app_controller.php

class AppController extends Controller {

	var $components = array ('Auth', 'Session');

	function beforeFilter() {
		$this->Auth->loginAction = array(
                        'controller' => 'users', 'action' => 'login');
	}

	function afterFilter() {
		$key = 'PeriodicJob.checked';
		if (!$this->Session->check($key)) {
		    $this->Session->write($key, true);
		    if (!class_exists('PeriodicJobsController')) {
		        App::import('Controller', 'PeriodicJobs');
		    }
		    $crtl = new PeriodicJobsController();
		    $crtl->constructClasses();
		    $crtl->checkAndExecutePending();
		}
	}
}
?>

Update: Want to see it run? Here’s a minimal sample Cake app without the “cake” directory, with a database schema and some test data. I’ve tested it with Cake 1.2.7 and 1.3.3, but it should work with other versions too. Download periodic_jobs-20100822.zip.

I haven’t checked if this method works also with AuthComponent’s authorization mechanisms. Here I use Auth for user authentication only.

Fixing mangled characters in MySQL

Some 30000 UTF-8 encoded posts in a popular Spanish forum had been stored in a column defined as latin1 with the usual consequences: accented characters like á é ó ñ got mangled into á é ó ñ, also known as mojibake. To complicate matters more, the more recent content was properly encoded in latin1, so a bulk conversion wouldn’t work. I thought we might as well get this issue fixed since we’re planning to upgrade from phpBB2 to phpBB3 shortly anyway.

It turns out that the least painful solution has to do with the convert(… using …)-function, available since MySQL 4.1. You just have to know what you’re doing.

The MySQL documentation has a page on column character set conversions but it’s missing practical examples, in particular on what to do when you have a mix of encodings in one column. People suggest different techniques to solve this problem, but sadly I find the writing lacking in clarity. The latter one actually suggests that the storage encoding of a text column is determined by the collation! (Collations actually only define how strings are ordered, which depends on the language you speak. For example in Finnish and Swedish Ä is a letter on its own right and is ordered after Z, while in English it is equivalent to A.)

My guess is that what people find most confusing are the implicit conversions. For instance when you insert data into a text column it is silently converted into the column encoding. So far the clearest guide to MySQL charsets and collations I’ve seen is by Shlomi Noach.

Many people recommend migrating everything to UTF-8 but I decided keep latin1. The site is Spanish-language only, so latin1 is enough. Remember that what MySQL calls “latin1” is not ISO-8859-1 but actually windows-1252, so we’re not losing the euro-character, curly quotes etc.

How Charsets Work in MySQL

Non-binary MySQL strings are basically octet strings with an encoding marker. The encoding specifies how bytes are decoded into characters. Each string can have an encoding of its own; there is no single “working encoding” like in Java for instance. You can check the encoding used for a string with the CHARSET-function, and see the byte values with the HEX-function:

mysql> select "ñ", charset("ñ") as cs, hex("ñ") as hex;
+----+------+------+
| ñ  | cs   | hex  |
+----+------+------+
| ñ  | utf8 | C3B1 |
+----+------+------+

The convert(.. using ..)-function changes the encoding marker and the underlaying bytes so that the new string encodes the same string as the old one:

mysql> set @c = convert("ñ" using latin1);
mysql> select @c, charset(@c) as cs, hex(@c) as hex;
+------+--------+------+
| @c   | cs     | hex  |
+------+--------+------+
| ñ    | latin1 | F1   |
+------+--------+------+

(Here you see one of those implicit conversions: the MySQL client converts the latin1 string into UTF-8 so that my UTF-8 terminal can show it correctly.)

What about binary strings? They are effectively octet sequences without a charset marker, so the convert(… using …) function simply slaps a charset marker on the byte string. The same thing happens when you store a binary string in a non-binary column. Therefore, if you start with a mangled latin1 string like “niño,” you can easily correct it with convert(binary … using utf8):

mysql> set @c = convert("niño" using latin1);
mysql> select convert(binary @c using utf8);
+-------------------------------+
| convert(binary @c using utf8) |
+-------------------------------+
| niño                          |
+-------------------------------+

(Here I first convert it to latin1 because my client encoding is UTF-8. With a latin1 text column I wouldn’t have to do this extra step.)

You could omit the explicit call to convert if you were updating or inserting into a UTF-8 column, but this is not the case here.

The Solution

If all posts suffered from this problem, it would be easy to do a bulk update to recode all the text with SQL like this:

-- convert ALL posts from utf8 back to latin1
update posts set post_text = convert(binary post_text using utf8);

Remember that only some posts are encoded in UTF-8 while others are in latin1, so this doesn’t work: the latin1 posts contain byte sequences that are not valid in utf8. For these posts MySQL gives you a piece of the converted data and the warning “1300 Invalid utf8 character string.” I could try detecting the posts that have mangled text by comparing the original and converted lengths:

-- convert ONLY utf8 posts back to latin1
update posts set post_text = @txt where length(post_text) =
    length(@txt := convert(binary post_text using utf8));

But no. In fact, there were forum posts that have mangled UTF-8 characters mixed with regular latin1. If you try to run “convert” on that you get a piece of converted data and the warning “1300 Invalid utf8 character string.” So in the end I was reduced to running the following replace function, for the 128 different bytes from 128 to 255:

update posts set post_text = replace(binary post_text,
    convert(char(0xF1 using latin1) using utf8), char(0xF1));

Wait, what? Why so complicated? Well, first you have strip the post_text of its encoding marker so that replace works on bytes, not on characters. Second, char produces binary strings, which can be converted to UTF-8 by going through latin1:

mysql> select hex(convert(char(0xF1 using latin1) using utf8));
+--------------------------------------------------+
| hex(convert(char(0xF1 using latin1) using utf8)) |
+--------------------------------------------------+
| C3B1                                             |
+--------------------------------------------------+

Unfortunately you can’t use char(0xF1 using utf8) because char interprets its argument as a list of bytes to be encoded as a string, and F1 is not valid UTF-8.

So there. I hope this helps somebody.