Joseph Edmonds

Joseph's Blog

MySQL String Cleanup Function

It’s fairly common to have scenarios where you are trying to push text data into a brittle system that can not handle text data beyond very simple ASCII.

You can try all kinds of approaches, but one fairly simple one is to create a function such as the one below and then you can easily reuse that function across various queries and columns. This is a nice application of DRY to MySQL.

CREATE DEFINER=`YOUR_DB_USER`@`%` FUNCTION `YOUR_DB_NAME`.`cleanString`( str longtext ) RETURNS longtext CHARSET latin1
    DETERMINISTIC
BEGIN 
DECLARE startTag, endTag INT DEFAULT 1;
    DECLARE i, len SMALLINT DEFAULT 1;
    DECLARE ret longtext DEFAULT '';
    DECLARE c text;
    IF str IS NULL THEN
        RETURN ret;
    END IF;
    IF str = '' THEN
        RETURN ret;
    END IF;
    -- strip HTML tags
    stripTags:
    LOOP
        SET startTag = LOCATE("<", str, startTag);
        IF (!startTag) THEN LEAVE stripTags; END IF;
        SET endTag = LOCATE(">", str, startTag);
        IF (!endTag) THEN SET endTag = startTag; END IF;
        SET str = INSERT(str, startTag, endTag - startTag + 1, "");
    END LOOP;
    SET len = CHAR_LENGTH(str);
    -- now remove any non ascii characaters
    REPEAT
        BEGIN
            SET c = MID(str, i, 1);
            IF c REGEXP '[ -~]' THEN
                SET ret = CONCAT(ret, c);
            END IF;
            SET i = i + 1;
        END;
    UNTIL i > len END REPEAT;
    RETURN ret;
END

The function will take basically any text you can throw at it thanks to using the longtext type. It will use a little bit of XML magic to remove any HTML tags and then it will break the string into characters and loop over them, skipping out any that don’t match characters between space and tilde. This removes line breaks and all kinds of special characters.

Note this is a hugely destructive approach and not something you should do if you are pushing the data to somewhere valuable and important where the data must be very accurate. It is a good quick fix though where you just need to push some text in and can accept a little bit of mangling as long as it just goes in there without any more fuss.

MicroDeps – A New PHP Concept

The PHP ecosystem benefits from a wide range of really great libraries and frameworks. I strongly encourage people to use these. However, some times you just want to keep it lean and mean. For these kinds of times, you probably just think to roll your own solution from scratch, however that does see you writing a lot of boilerplate code.

MicroDeps

So I am proposing a solution, a middle ground. MicroDeps will be very small libraries that aim not to be abstraction layers or wrappers, but instead they will be small utilites that make working with raw PHP a little bit easier and more productive.

I’ve kicked this off with the first MicroDep library – MicroDeps/PDO

This is not a PDO wrapper! It is simply a set of factories and configuration management to assist with getting a working PDO instance with some sane configuration. The PDO you will get by using it is plain old PDO.

I’ve written it with all the clean code paradigms I can muster, used the best static analysis tools at their most brutally strict settings and ensured that test coverage is 100% and that it even withstands mutation testing.

This is an alternative to something like Doctrine/DBAL which aims to be a much more robust and extensive wrapper with a lot of functionality.

So why would you use a MicroDep?

Well firstly, I think you might want to look at them so that you can learn a bit more about PHP internals, rather than having everything abstracted away and learning userland library and framework code.

Secondly, you might just want to keep things really simple – perhaps you are just spinning up a simple script.

Thirdly, you might be wanting to keep things as lean as possible for performance reasons

Other Ideas

One of my ideas with this would be to actually encourage you to copy/paste the micro dep straight into your normal codebase rather than installing it as a composer dependency. I know, it’s crazy right?

Composer and dependencies are great, but some times they come with extra pain that can be hard to manage over time. By reducing the number of dependencies you have in play, you may have an easier time in the long run as all your code is first party and tracked in your main repo.

I am considering writing some tooling to assist with bringing micro deps into your main codebase, though I’m not sure it’s even a good idea or just one of my left field “out of the box” thought tangents.

PHP QA CI

To assist with writing the this MicroDep, I have used another library called PHP QA CI. This is actually a tool that was originally developed for Edmonds Commerce, though it doesn’t really work well with Magento 2 which is the main focus there and so has languished a bit. I wanted to get it bang up to date with the latest versions of the various tools that are bundled and also really test it on a PHP 8 project.

I did use the Edmonds Commerce version to help writing The Art of Modern PHP 8 however the PHP 8 support was a little lacking. I’ve now created a fork of the original and will be working on it and making it work really nicely with PHP 8 and 8.1 when that comes out. As this might be a long running fork, I have registered it on Packagist in it’s own right.

Hope its Useful

I like to share stuff online and I really hope it’s useful to someone else. Feel free to drop me a note, connect on linkedin, message me on Reddit with any questions or comments 🙂

Client Certificate mTLS with Cloudflare

An easy, robust and convenient way to add a strong layer of extra protection to your online systems – client SSL certificates are something I’ve been a big fan of for a number of years now.

Traditionally, getting this set up required a bit of command line wizardy and it was not something that was particularly well documented online.

Now for sites protected by Cloudflare, setting this up is really quite easy.

Cloudflare push this as part of their API Shield, though in fact you can use it to protect any web site.

You can read the docs here which explain the process quite clearly.

  • Create the client certificate within the Cloudflare dashboard
  • Enable mTLS for the host(s) you want to protect with this certificate
  • Create a firewall rule that enforces a valid certificate for the host(s)

Tips

Use a Password Protected P12 Certificate

One thing I suggest you do though to increase security is to password protect the generated key by bundling it into a P12 file. This is a single file that can be easily shared and is useless without a corresponding password.

The plain cert and key files that cloudflare provides are not very secure – anyone who gets them can use them freely. By wrapping these in a P12 file you add an extra layer of security, so even if an email/message is intercepted that contains the file, it is not any use unless the attacker can also get the password (note – you should really use a proper password manager for secure sharing of passwords)

You can copy and paste teh PEM format files into 2 local files, cert.pem and cert.key

Once you have these files, to create a password protected P12 that contains them, you would run a command like this:

openssl pkcs12 -export -in cert.pem -inkey cert.key -out cert.p12

You will be prompted for a password, I suggest a nice long secure password – it is only generally needed to be typed in once, when installing to a browser or device.

When using client SSL, it is very similar to normal server side SSL in that it protects the entire domain. So for example, you might want to create a new subdomain protected.mydomain.com

Within Cloudflare, you will want to configure this new subdomain and ensure that it is proxied through Cloudflare. You might decide to use a Cloudflare Tunnel (formerly Argo Tunnel) to provide a highly secure link between your server and Cloudflare so that no one can find a way to bypass Cloudflare when accessing the protected subdomain.

Combining with Teams/Access

You can (and should) combine this protection with Cloudflare Teams to provide yet another layer of protection. One thing I do suggest though is that hosts/domains that you intend to protect with p12s, you do this first in isolation, and only once you have the p12 protection working should you then configure it in Teams.

This just makes your life a lot easier as you can then easily test the p12 proection using curl

Testing Using Curl

You can test your P12 protection using Curl. Browsers can be a bit funny with P12 certificates and sometimes require a full machine restart to work properly. By using Curl, you remove any caching etc that might be happening in a browser and can get verbose debug information

To access a site with a P12, you can use the following command

curl --cert-type P12 --cert ./cert.p12:MySuperLongSecurePass123 https://protected.domain.co.uk -vvv

Note how the password for the P12 is tacked onto the path to the cert, with a : character separating the path and the password

Get in touch today

Send me a message concerning your idea, problem, or question – and I’ll personally respond as soon as possible.

Connect with me on LinkedIn.