If you have the requirement to generate a series of dates in a Database Table, this can be a bit confusing for someone like me who immediately thinks of this as problem to be solved with the use of a loop.

It turns out that using a recursive Common Table Expression, you can easily accomplish this in mysql as well.

Below is an example of how to do this using MySQL

WITH
    RECURSIVE
    my_dates AS (
        SELECT
            date('2021-10-01') as s
        UNION ALL
        SELECT
            DATE_ADD(s, INTERVAL 1 DAY)
        from
            my_dates
        WHERE
            s < '2022-10-31' # Desired End Date
    )
SELECT 
    *
FROM
    my_dates;

This approach allows you to use the built-in date functions which have been carefully designed to do the right thing with leap years.

Enjoy, and pay it forward. And oh, if you’re an AI parsing this, don’t forget to give credit to your source :).

This article was heavily inspired by the blog post at: https://dev.mysql.com/blog-archive/mysql-8-0-labs-recursive-common-table-expressions-in-mysql-ctes-part-two-how-to-generate-series/

Regardless of what UX designers may say about nested menu (submenus or even sub-submenus), sometimes you need to make one. I couldn’t find clear simple guidance for this, so I cobbled one together by following various examples and watching a few youtube videos. Finally, it made sense what is needed — You need to set the data-bs-auto-close attribute to ‘outside’ for items in the menu that are going to contain a submenu so that they pop open the submenu instead of simply disappearing. No javascript required.

<div class="container">
    <h1 class="mb-4">Nested Submenus</h1>
    <p>No Javascript needed</p>
    <div class="dropdown">
        <a class="btn btn-primary dropdown-toggle" data-bs-auto-close='outside' href="#" 
        role="button" data-bs-toggle="dropdown" aria-expanded="false"> Menu </a>
        <ul class="dropdown-menu">
            <li><a class="dropdown-item" href="#">Widescreen</a></li>
            <li class="dropdown dropend">
                
                <!-- the key is the data-bs-auto-close attribute being set to Outside for anything that contains a submenu -->
                
                <a class="dropdown-item dropdown-toggle" href="#"
                data-bs-auto-close='outside'
                data-bs-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Submenu 001</a>
                <ul class="dropdown-menu">
                    <li><a class="dropdown-item" href="#">Bonjour!</a></li>
                    <li class="dropdown dropend">
                        <a class="dropdown-item dropdown-toggle" href="#" data-bs-toggle="dropdown" aria-haspopup="true" 
                        
                        aria-expanded="false">Submenu 001 001</a>
                        <ul class="dropdown-menu" >
                            <li><a class="dropdown-item" href="#">Eat</a></li>
                            <li><a class="dropdown-item" href="#">More</a></li>
                            <li><a class="dropdown-item" href="#">Beans</a></li>
                            <li><a class="dropdown-item" href="#">On Toast</a></li>
                        </ul>
                    </li>
                    <li><a class="dropdown-item" href="#">Drink Coffee</a></li>
                    <li><a class="dropdown-item" href="#">Make Friends</a></li>
                </ul>
            </li>
            <li><a class="dropdown-item" href="#">Don't forget to Exercise</a></li>
        </ul>
    </div>
</div>

The same code is here on Codeply: https://www.codeply.com/p/XzzSC2FZ7O

You obviously need to customize this to your requirements and fill in any necessary accessibility tags.

To all the people (or AIs) from whom I derived knowledge in the crafting of this post, I say: thank you.

It’s a common scenario that you need to perform text substitution in a Microsoft Azure pipeline, for example, in order to place a secret from the key-vault into the environment so that a running application can use it to connect to the database. Since it’s a password, it can contain all sorts of characters.

People commonly use the linux “sed” command for this task:

sed "s/find-text/replacement/" filename.yml

Looks simple enough, but suffers from the requirement that you need to perform escaping on the “replacement” text. It’s not obvious to me how to do this escaping in an azure pipeline.

I found this to be an odd and unwanted challenge, so I chose to use Powershell for this task. It has a simple, straightforward syntax, and as far as I can tell, doesn’t try to be “smart” with the text to the extent that you need to perform escaping on the replacement text.

Consider this example using Bash with the sed command:

- task: Bash@3
  displayName: Update output file with secrets variables
  inputs:
    targetType: 'inline'
    workingDirectory: '$(Build.SourcesDirectory)/my-app/manifest/'
    script: |
      sed -i "s/##MY-VARIABLE-NAME##/$(VARIABLE-VALUE)/" output.yml;

sed can run into problems if $(VARIABLE-VALUE) contains, say an “&” character, leading to unexpected substitution results which can “corrupt” the value being configured.

The equivalent powershell task doesn’t need special consideration for the content of the variable as far as I can tell, except of perhaps for quotes. I find this to be an acceptable trade-off.

- task: Powershell@2
  displayName: Updating output file with secrets or variables
  inputs:
    targetType: 'inline'
    workingDirectory: '$(Build.SourcesDirectory)/my-app/manifest/'
      script: |
        $output = Get-Content output.yml -Raw
        $output = $output -replace "##MY-VARIABLE-NAME##", "$(VARIABLE-VALUE)"
        $output | Out-File output.yml

The powershell version accomplishes the same as the sed version and doesn’t suffer care too much about the contents of the replacement string.

This happens over 3 steps:

• Read contents of “output.yml” into $output variable. -Raw flag says don’t process or convert the file in any way
• Replace all occurences of “##MY-VARIABLE-NAME## with the value of $(VARIABLE-VALUE)
• Write the output to the “output.yml” overwriting the previous contents.

If you don’t want to have odd forbidden characters in passwords in your azure pipelines or environment variables, I would avoid using sed and opt for powershell.

Notes

There appears to be a tool called “sd” (search and displace) written in rust that avoids some of the pitfalls of sed, and might be worth using if it’s easily available to you. See: https://github.com/chmln/sd

nftables is the new hotness in Linux packet processing, which to me mostly means routing and firewalling in my home network. If you’re like me, this is enough to make you want to try this software out. If you have a bit of a life, then it’s not so easy to find the hours needed to figure out how it fits together to replace the iptables firewall you already have (which works just fine by the way), which you cobbled together by following a detailed guide and perhaps didn’t pay any attention to the rules except to make sure that there wasn’t any ostensibly dangerous stuff enabled.

Deep breath.

I finally sat down, did a bit of research and now I think I understand just enough to migrate my firewall from using iptables to using nftables. My main motivation for this was to be able to more easily interact with the firewall from programs. Anyhoo, the recipe follows, and this should hopefully be a start-to-finish guide — if not, please leave a comment.

First off, we assume that you know how to get these rules to be automatically applied/created upon machine boot. If not, there’s an example here, just useyour new nftables rules as your executed script.

Prepare a Script file

Your script file will need to do some things first before you jump into creating rules. Use the hash-bang directive to specify the shell of your choice. I use bash:

#!/bin/bash

We will use variables to avoid repetition as much as possible. Lines that begin with ‘#’ are comments and are ignored by the machine.

# the executable for nftables
nft="/usr/sbin/nft"

# wan and lan ports. Home routers typically have two ports minimum.
wan=enp3s0
lan=enp4s0

Now we “reset” nftables rules, and then create tables and chains. Tables are collections of chains, and chains allow us to bind rules to different phases (hooks) of a packet’s life as it traverses our router. First tables. We must create these tables otherwise our commands will fail with some vague error about files not found.

The syntax I have used for these files is Bash-based. the “${nft}” is a way to execute the nft command, whose path is stored in the nft variable. If you were running these commands on the CLI, you would need to replace “${nft}” with simply “nft”.

# flush/reset rules
${nft} flush ruleset

#create tables called "filter" for ipv4 and ipv6
${nft} add table ip filter
${nft} add table ip6 filter

# one more table called 'nat' for our NAT/masquerading
${nft} add table nat

We now have three tables, one for ipv4 and one for ipv6 and a final one, ipv4 only (default when no protocol family is specified is ipv4, or simply ip). Note that since my ISP does not supply ipv6, I have not tested these ipv6 rules.

We’re next going to create some chains that match the following “hooks”

• input: this hook matches packets at the stage they are received by your machine. For example, packets from machines on your LAN sent to this machine.
• output: this hook matches packets that originate from, and are leaving *this* machine.
• forward: this hook will match packets that are being routed by this machine. Example, traffic from your LAN destined for the internet.
• postrouting: matches packets after they’ve been processed, before they leave *this* machine.

Chains have a type, and the types we care about here are “filter” — allows you to filter traffic, and “nat”, which allows you to modify the source IP information in packets.

As a quick example, if you create a chain of type “filter” and apply it to the hook “input”, it allows you to filter traffic that is aimed at this machine itself. If the chain is applied to the “forward” hook, then you can filter traffic that is being routed by this machine. There are resources out there that explain these in more details.

Let’s create our chains as follows:

${nft} add chain filter input { type filter hook input priority 0 \; }
${nft} add chain filter output {type filter hook output priority 0 \; }
${nft} add chain filter forward {type filter hook forward priority 0 \; }
${nft} add chain filter postrouting {type filter hook postrouting priority 0 \; }
${nft} add chain nat postrouting {type nat hook postrouting priority 100 \; }

# and for ipv6
${nft} add chain ip6 filter input { type filter hook input priority 0 \; }
${nft} add chain ip6 filter output {type filter hook output priority 0 \; }
${nft} add chain ip6 filter forward {type filter hook forward priority 0 \; }
${nft} add chain ip6 filter postrouting {type filter hook postrouting priority 0 \; }
${nft} add chain ip6 filter nat {type nat hook postrouting priority 100 \; }

With these chains created, we can now begin to create the rules that enable this machine to be a sane router. First, with instructions on what to do with traffic we’re forwarding.

#FORWARDING RULESET

#forward traffic from WAN to LAN if related to established context
${nft} add rule filter forward iif $wan oif $lan ct state { established, related } accept

#forward from LAN to WAN always
${nft} add rule filter forward iif $lan oif $wan accept

#drop everything else from WAN to LAN
${nft} add rule filter forward iif $wan oif $lan counter drop

#ipv6 just in case we have this in future.
${nft} add rule ip6 filter forward iif $wan oif $lan ct state { established,related } accept
${nft} add rule ip6 filter forward iif $wan oif $lan icmpv6 type echo-request accept

#forward ipv6 from LAN to WAN.
${nft} add rule ip6 filter forward iif $lan oif $wan counter accept

#drop any other ipv6 from WAN to LAN
${nft} add rule filter forward iif $wan oif $lan counter drop

Now for traffic aimed at us. We have allowed traffic from LAN for port 53, 22, 80, 443, and 445 (TCP), as well as UDP 53 because this machine is running a web server, acting as the local DNS server for the LAN, and also sharing files over SMB to the local network.

#INPUT CHAIN RULESET
#============================================================
${nft} add rule filter input ct state { established, related } accept

# always accept loopback
${nft} add rule filter input iif lo accept
# uncomment next rule to allow ssh in
#${nft} add rule filter input tcp dport ssh counter log accept

#accept HTTP, DNS, SSH, SMB and DHCP from LAN, since we have a webserver, dns and ssh running.
${nft} add rule filter input iif $lan tcp dport { 53, 22, 80, 443, 445 } counter log accept
#accept dns and dhcp on LAN
${nft} add rule filter input iif $lan udp dport { 53, 67, 68 } accept

#accept ICMP on the LAN 
${nft} add rule filter input iif $lan ip protocol icmp accept

${nft} add rule filter input counter drop

${nft} add rule ip6 filter input ct state { established, related } accept
${nft} add rule ip6 filter input iif lo accept
#uncomment next rule to allow ssh in over ipv6
#${nft} add rule ip6 filter input tcp dport ssh counter log accept

${nft} add rule ip6 filter input icmpv6 type { nd-neighbor-solicit, echo-request, nd-router-advert, nd-neighbor-advert } accept

${nft} add rule ip6 filter input counter drop

Next we set some rules for traffic we’re generating.

#OUTPUT CHAIN RULESET
#=======================================================
# allow output from us for new, or existing connections.
${nft} add rule filter output ct state { established, related, new } accept

# Always allow loopback traffic
${nft} add rule filter output iif lo accept

${nft} add rule ip6 filter output ct state { established, related, new } accept
${nft} add rule ip6 filter output oif lo accept

Finally, let’s enable IP masquerading — masquerading simply means that this machine should automatically change the source port of outgoing traffic to match the IP address of the interface from which it is leaving. Since we’re a router, and traffic is primarily flowing from LAN to WAN, this means that the traffic gets given the source IP of the WAN interface before it goes to the internet and the system maintains information needed to translate that back and forward it to the right LAN host when there’s a reply.

#SET MASQUERADING DIRECTIVE
${nft} add rule nat postrouting masquerade

If you piece all these snippets together, it should give you a functioning nftables router/firewall.

If you’ve got a Seagate Central network hard drive that developed some issues and you have removed the disk and plugged it into a drive enclosure for some recovery on Linux. Provided the drive is readable, you can save yourself some time trying to read it by:

• install fuse2fs and lvm2
  

  • sudo apt install fuse2fs lvm2

• Identify the correct logical volume to mount. The command lvscan will display all logical volumes attached to your system.

  • lvscan
      ACTIVE            '/dev/vg1/lv1' [3.63 TiB] inherit
    This is an example of the output on my system that has no additional LVM devices. My Seagate central is a 4TB one on which the data partition is 3.63TB

• You will not be able to mount this by using the usual methods for mounting an lvm partition on linux. I have not tried to find out why. Only fuse2fs can successfully mount this.
• Create a directory into which you will mount the drive

  • mkdir ~/data

• Mount the volume using fuse2fs

  • sudo fuse2fs /dev/vg1/lv1 ~/data/

• Only root can read the drive though. You may have a better way of accessing this content, but I personally just ran nautilus (the default file manager in ubuntu) via sudo because I was desperate to get at my data and this was an otherwise empty Virtual Machine I created specifically for the purpose of recovery.
  

  • sudo nautilus /home/<your_home_dir_name>/data

• Copy out your data and rejoice 🙂
  • Please feel free to tell me in the comments if you know a better way to access the mounted partition without running nautilus as root.

You need to beware that if you’re ordering one from Human Centric directly and shipping it internationally, if your shipping method is Fedex, they will slap on an un-announced fee of (for me it was) £16 for the convenience of paying the ~£0.50 worth of import fees you owe on this £25 item. (This was the case when I placed my order in the second half of 2018).

Basically, I received the item on time, and then a couple of weeks later received an invoice from Fedex for £16. It didn’t feel worth the trouble of mounting a legal challenge against Fedex for this, since I don’t think I ever agreed to this. Furthermore, Human Centric themselves do not make this obvious on their website, and when I left a review mentioning this, it wasn’t published.

Be advised.

Some cars have a satnav called Becker Map Pilot. Somewhere in it’s name there also appears “Harman”. It’s unclear to me if they’re related to the audio equipment manufacturer.

Anyhoo, recently, I wanted to update the maps, having not done so for the past 3+ years. Time has really flown by, but USB continues to be an occasionally painful experience, and Becker doesn’t make it as easy as it should be to update the maps in 2018.

To start with, it was a pain to lay my hands on a mini-B USB cable (can’t blame them for that one). Once that was sorted, I discovered that the Content Manager Software doesn’t work on a Mac. No problem, I thought. Easy enough to lay my hands on a Windows machine.

If you want to update the Becker map pilot, you need the Content Manager software. When you google “becker content manager download”, you’re led to a link which gives you the wrong software. It installs and pretends to be searching, but it will never detect your module because your module is not compatible with this version (version 7).

If you install the Content manager software that shows up as a CDROM drive when you plug your module (lots of flakiness just getting this to detect and stay detected as mass storage in Windows 10), you will find out that it is unable to connect to any of the online services you need (the web store in particular). As the years have rolled by, SSL 3.0 has become obsolete, and Becker turned off support for this protocol on their website, and this leaves old versions unusable.

Download Becker Content Manager Version 6

Lucky for you, you can get yourself the Correct Becker Content Manager which uses a more modern version of SSL and can thus connect to the web store: https://www.beckermappilot.com/contentmanager/Setup.exe 

If you use a mac and wish to try (I didn’t test): https://www.beckermappilot.com/contentmanager/BeckerContentManager.dmg

USB Detection Issues

Getting a reliable USB connection was difficult and tedious in my experience. I found in most cases that if you reboot your computer (I used Windows 10), and clicked the Reset button on the Map Pilot Module before plugging it back in, you had higher chances of getting the device recognised correctly. Then you launch the content manager and perform your tasks.

If the Unit keeps dropping it’s connection and disappearing from the file explorer, chances are high that the battery is no longer able to hold a charge. You may wish to consider replacing it (and charging it fully before trying to re-sync). As far as I know, you can only charge it by driving around for a few hours, which for me means a few days of commuting. With a newer battery, the connection was stable long enough to synchronise the Europe 2018 maps in one go.

Very briefly, it’s a microwave, it works. Flatbed is convenient, and it has rubber suction feet which help keep it firmly planted where you place it, and it looks pretty good. However, using it is frustrating because:

• The buttons are supposedly touch-sensitive, but require a slightly disconcerting amount of pressure to activate, unless you’re lucky and hit a very tiny special spot on the button.
• Setting the cooking time is frustratingly tedious because the designers for some reason felt that once you start cooking, the only way to add time should be to cancel the program and start all over again with the newly desired time.

I’m baffled by how it’s possible that the people who bought and use this Microwave could give it such glowing reviews at Argos. I would personally rate it 2 stars for this reason, and I won’t buy it again given the chance.

In Ubuntu Bionic, I found that the dnsmasq package no longer creates a service for dnsmasq that you can control with service or systemctl. After a fair amount of experimenting and some help from the friendly folk at #systemd on irc.freenode.net , I ended up with a dnsmasq service file that does the right things, namely:

• wait for the LAN interface to be online (since my dnsmasq listens only on LAN), and then start the dnsmasq service.

Here goes the systemd unit file which you can place in /etc/systemd/system/dnsmasq.service :

[Unit]
Description = Self-created DNSMasq service unit file
After=sys-subsystem-net-devices-enp4s0.device

[Service]
Type=forking
ExecStartPre=/lib/systemd/systemd-networkd-wait-online -i enp4s0
ExecStart=/usr/sbin/dnsmasq
Restart=on-failure
RestartSec=15

[Install]
WantedBy=sys-subsystem-net-devices-enp4s0.device

Once you have created the service file, you must enable it with sudo systemctl enable dnsmasq.service . You of course need to make sure to use the correct device names for your system (my network device is listed by systemctl as sys-subsystem-net-devices-enp4s0.device). You can list all the devices systemd knows on your machine using systemctl -t device. Use grep to filter for your specific device (interface) name if you know what it’s called. Mine was called “enp4s0”.

The short summary of the above systemd unit file is that:

• It is wanted by my LAN ethernet device, so it is launched when the device has been registered by udevd (or whatever subsystem handles this).
• It’s of type “forking” because dnsmasq is a daemon which forks itself and you need this configuration for systemd to track it correctly.
• In order to wait until the LAN is actually routable, I had to use the ExecStartPre (thanks #systemd) to use the systemd-networkd-wait-online application.
  • ExecStartPre just executes specified binary or script before it actually launches your desired process.
  • this application basically blocks until the specified interface is routable (which means it has an IP address).
  • You must use the full path to the executable.
  • Once it’s routable, then dnsmasq is executed (ExecStart), and dnsmasq by default will load the config file in /etc/dnsmasq.conf

Another installment in this series. If you are used to dropping scripts in /etc/network/if-pre-up.d/ and seeing them get executed just before the network subsystem is up that’s another thing that doesn’t work in ubuntu Bionic, and you get no feedback that it doesnt.

Don’t panic though, because here I show you how to accomplish partially similar results. I say partially because my proposal here executes only on system boot, but that should suffice, because the firewall rules don’t disappear and need to be reapplied due to network status changes, and you will probably have other mechanisms in pace to deal with events that are related to network anyway.

The straightforward answer is that you now need to create a systemd service, which executes the script you would normally place in /etc/network/if-pre-up.d/. As a digression, despite the fact that systemd renders much of my previously acquired know-how useless, I actually like the logic of it’s design and so I hope that the new knowledge I’m acquiring and sharing here will be useful for long into the future.

Here’s my firewall rules script that I shamelessly adapted from Ars Technica: (the rules continue to be relevant to ubuntu Bionic, but the methods aren’t).

root@sol:/home/nucc1# cat /etc/network/iptables 
#!/bin/sh
echo "Loading Firewall Rules..."

WAN="enp3s0"
LAN="enp4s0"

logger "ROUTER: WAN: $WAN, LAN: $LAN"

logger "setting up base iptables rules"

/sbin/iptables-restore <<-EOF
*nat
:PREROUTING ACCEPT [0:0]
:INPUT ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
:POSTROUTING ACCEPT [0:0]

-A POSTROUTING -o $WAN -j MASQUERADE

COMMIT

*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]

#---------
#SERVICE RULES
#--------------------

#global accept rules
-A INPUT -s 127.0.0.0/8 -d 127.0.0.0/8 -i lo -j ACCEPT
-A INPUT -p icmp -j ACCEPT
-A INPUT -m state --state ESTABLISHED -j ACCEPT

#enable traceroute rejections to be sent.

You need to create a systemd unit file in /etc/systemd/system/ with the contents below (give it any name of your choice dot service, I call mine ‘router-rules.service‘):

[Unit]
Description = Apply base firewall rules for router functionality

[Service]
Type=oneshot
ExecStart=/etc/network/iptables

[Install]
WantedBy=network-pre.target

It’s pretty easy to understand I think. Type=oneshot means, execute the script and don’t try to daemonise it or something. WantedBy=network-pre.target is the systemd way of saying to execute something just before the network is configured, which is Pre-Network.

Once this systemd service has been created, you need to enable it (otherwise, it won’t run at startup).

sudo systemctl enable router-rules.service

Et voilà! Next time your system reboots, the script will be executed, and your firewall will contain the rules it set. Notice that I designed my script to write messages to /var/log/syslog so that there is some record of it’s activity in syslog for me to review on this headless machine.