diff options
| author | Simon Rettberg | 2023-10-30 10:04:51 +0100 |
|---|---|---|
| committer | Simon Rettberg | 2023-10-30 10:04:51 +0100 |
| commit | cf4d3241e2a987baf14d7f4a298ac99dc7ffdb75 (patch) | |
| tree | e9d7f11a8dff4fa26c4fc7bebd32cb5189263087 | |
| parent | [systemstatus] Add warning sign to tab on outdated DB (diff) | |
| download | slx-admin-cf4d3241e2a987baf14d7f4a298ac99dc7ffdb75.tar.gz slx-admin-cf4d3241e2a987baf14d7f4a298ac99dc7ffdb75.tar.xz slx-admin-cf4d3241e2a987baf14d7f4a298ac99dc7ffdb75.zip | |
[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.
| -rw-r--r-- | modules-available/statistics/install.inc.php | 5 |
1 files changed, 4 insertions, 1 deletions
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()); |