From cf4d3241e2a987baf14d7f4a298ac99dc7ffdb75 Mon Sep 17 00:00:00 2001
From: Simon Rettberg
Date: Mon, 30 Oct 2023 10:04:51 +0100
Subject: [statistics] Fix mysql regex to delete non-ASCII values

Before updating columns to ASCII only, make sure there are no
entries that contain invalid characters. Unfortunately mysql
took the stupid approach of treating a-z as "any alphabetic
character" by default, so we'd retain Ä and ſ too.
Also, stupid me for not verifying this every time I use a
regex engine anywhere.
---
 modules-available/statistics/install.inc.php | 5 ++++-
 1 file changed, 4 insertions(+), 1 deletion(-)

diff --git a/modules-available/statistics/install.inc.php b/modules-available/statistics/install.inc.php
index 0afaa3c7..bc8a5c91 100644
--- a/modules-available/statistics/install.inc.php
+++ b/modules-available/statistics/install.inc.php
@@ -305,6 +305,7 @@ if (!tableHasColumn('machine', 'live_id45size')) {
 
 // 2021-08-19 Enhanced machine property indexing
 if (stripos(tableColumnType('statistic_hw_prop', 'prop'), 'varchar(64)') === false) {
+	Database::exec("DELETE FROM statistic_hw_prop WHERE prop NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
 	$ret = Database::exec("ALTER TABLE statistic_hw_prop
     	MODIFY `prop` varchar(64) CHARACTER SET ascii NOT NULL,
     	ADD `numeric` bigint(20) DEFAULT NULL");
@@ -314,6 +315,7 @@ if (stripos(tableColumnType('statistic_hw_prop', 'prop'), 'varchar(64)') === fal
 	$res[] = UPDATE_DONE;
 }
 if (stripos(tableColumnType('machine_x_hw_prop', 'prop'), 'varchar(64)') === false) {
+	Database::exec("DELETE FROM machine_x_hw_prop WHERE prop NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
 	$ret = Database::exec("ALTER TABLE machine_x_hw_prop
     	MODIFY `prop` varchar(64) CHARACTER SET ascii NOT NULL,
     	ADD `numeric` bigint(20) DEFAULT NULL");
@@ -323,7 +325,7 @@ if (stripos(tableColumnType('machine_x_hw_prop', 'prop'), 'varchar(64)') === fal
 	$res[] = UPDATE_DONE;
 }
 if (stripos(tableColumnType('statistic_hw', 'hwname'), 'char(32)') === false) {
-	Database::exec("DELETE FROM statistic_hw WHERE hwname NOT REGEXP '^[a-zA-Z0-9_ \\=@\\*\\-]+$'");
+	Database::exec("DELETE FROM statistic_hw WHERE hwname NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
 	$ret = Database::exec("ALTER TABLE statistic_hw MODIFY `hwname` char(32) CHARACTER SET ascii NOT NULL,
 		MODIFY `hwtype` char(16) CHARACTER SET ascii NOT NULL");
 	if ($ret === false) {
@@ -332,6 +334,7 @@ if (stripos(tableColumnType('statistic_hw', 'hwname'), 'char(32)') === false) {
 	$res[] = UPDATE_DONE;
 }
 if (stripos(tableColumnType('machine_x_hw', 'devpath'), 'char(32)') === false) {
+	Database::exec("DELETE FROM machine_x_hw WHERE devpath NOT REGEXP BINARY '^[a-zA-Z0-9_ =@*!.:/\\\\-]+$'");
 	$ret = Database::exec("ALTER TABLE machine_x_hw MODIFY `devpath` char(32) CHARACTER SET ascii NOT NULL");
 	if ($ret === false) {
 		finalResponse(UPDATE_FAILED, 'Changing devpath of machine_x_hw failed: ' . Database::lastError());
-- 
cgit v1.2.3-55-g7522