Wildcard character - * or %

[Hazark]

I have a 5.3.4 installation that will moved and updated the latest soon, so I'm testing on my local machine.

One thing I noticed that on Upgrade5320to5400MySQL.sql, all asterisk characters replaced by % and all ?'s replaced by _. Is that new behaviour (single character wildcard is _ and multichar is % - just like SQL) or I'm missing something. Documentation still shows * is the wildcard character but I'm learned to not trust the documentation blindly

Also given that HMS >= 5.6 && MySQL >= 5.6 is that changes enough for avoiding indexing errors (the multibyte issue when emojis used in subject)?

Code: Select all

ALTER TABLE `hm_messages`
	COLLATE='utf8mb4_general_ci',
	CONVERT TO CHARSET utf8mb4;
	
ALTER TABLE `hm_message_metadata`
	COLLATE='utf8mb4_general_ci',
	CONVERT TO CHARSET utf8mb4;

Thanks

[mattg]

I have a 5.3.4 installation that will moved and updated the latest soon, so I'm testing on my local machine.

One thing I noticed that on Upgrade5320to5400MySQL.sql, all asterisk characters replaced by % and all ?'s replaced by _. Is that new behaviour (single character wildcard is _ and multichar is % - just like SQL) or I'm missing something. Documentation still shows * is the wildcard character but I'm learned to not trust the documentation blindly

Depends what the wildcard is used for.
Still need * in whitelisting

Also given that HMS >= 5.6 && MySQL >= 5.6 is that changes enough for avoiding indexing errors (the multibyte issue when emojis used in subject)?

Code: Select all

ALTER TABLE `hm_messages`
	COLLATE='utf8mb4_general_ci',
	CONVERT TO CHARSET utf8mb4;
	
ALTER TABLE `hm_message_metadata`
	COLLATE='utf8mb4_general_ci',
	CONVERT TO CHARSET utf8mb4;

Does that changes the tables, and the existing values??
If so, then yes that should be all that is needed

[SorenR]

I have a 5.3.4 installation that will moved and updated the latest soon, so I'm testing on my local machine.

One thing I noticed that on Upgrade5320to5400MySQL.sql, all asterisk characters replaced by % and all ?'s replaced by _. Is that new behaviour (single character wildcard is _ and multichar is % - just like SQL) or I'm missing something. Documentation still shows * is the wildcard character but I'm learned to not trust the documentation blindly

Depends what the wildcard is used for.
Still need * in whitelisting

The GUI will translate between the two...

It's been mentioned before. viewtopic.php?f=7&t=25020

[Hazark]

Still need * in whitelisting

Actually the Upgrade5320to5400MySQL makes the change on the hm_whitelist and hm_greylist_whitelist tables. I think wildcard usage in rules is unaffected.

Code: Select all

update hm_whitelist set whiteemailaddress = replace(whiteemailaddress, '/', '//');

update hm_whitelist set whiteemailaddress = replace(whiteemailaddress, '%', '/%');

update hm_whitelist set whiteemailaddress = replace(whiteemailaddress, '_', '/_');

update hm_whitelist set whiteemailaddress = replace(whiteemailaddress, '?', '_');

update hm_whitelist set whiteemailaddress = replace(whiteemailaddress, '*', '%');

update hm_greylisting_whiteaddresses set whiteipaddress = replace(whiteipaddress, '/', '//');

update hm_greylisting_whiteaddresses set whiteipaddress = replace(whiteipaddress, '%', '/%');

update hm_greylisting_whiteaddresses set whiteipaddress = replace(whiteipaddress, '_', '/_');

update hm_greylisting_whiteaddresses set whiteipaddress = replace(whiteipaddress, '?', '_');

update hm_greylisting_whiteaddresses set whiteipaddress = replace(whiteipaddress, '*', '%');

Does that changes the tables, and the existing values??
If so, then yes that should be all that is needed

Yes, thanks for the confirmation.

[Hazark]

I have a 5.3.4 installation that will moved and updated the latest soon, so I'm testing on my local machine.

One thing I noticed that on Upgrade5320to5400MySQL.sql, all asterisk characters replaced by % and all ?'s replaced by _. Is that new behaviour (single character wildcard is _ and multichar is % - just like SQL) or I'm missing something. Documentation still shows * is the wildcard character but I'm learned to not trust the documentation blindly

Depends what the wildcard is used for.
Still need * in whitelisting

The GUI will translate between the two...

It's been mentioned before. viewtopic.php?f=7&t=25020

Thanks for the link, somehow I can't find it before opening this topic.

But I'm doubting about that on the fly *-to-% thing, because it is not happening on my test machine. What I type on gui (%, _, ? or *) is exactly reflected to database.

I'll look at source code to figure out what's going on...

[Hazark]

Well, the code that does the parsing is located at https://github.com/hmailserver/hmailser ... r.cpp#L302
which calling it from here https://github.com/hmailserver/hmailser ... he.cpp#L66

It tries to write a regexp from user input and it clearly uses * and ? as wildcard. I am not sure why Upgrade5320to5400MySQL.sql does this conversion.

If I'm not missing a big point, this conversion should break the existing whitelist entries.

[mattg]

yes, I believe that it did break whitelist entries

There was a significant number of changes from 5.3.X to 5.4
#1 being that 5.4 is open source, 5.X.Y (where X <4) was closed source, but still free as in cost $0

The other main point of difference was that the full file paths are no longer stored in the database, and existing entries will need to be updated. (DDS should do that in most cases, but occasionally a few entries need to be changed manually.) I think that Autoban was added about then too...

A lot has changed since 5.3.4 and you should upgrade to latest for a number of reasons.

How many whitelist entries do you have?

edit:-- I see that the upgrading recommendations ended at 5.3
I'll work on fixing that

[Hazark]

I have ~50 whitelist entries, I can fix my existing records or simply don't run that rename SQL's. (I'm not using DBUpdater.exe so it is not a problem for me)

Probably Upgrade5320to5400MySQL file needs to be changed, it will break whitelist entries when updating from version =< 5.320 and will cause confusion for who can't examine source code or manually edit their db.
Is this topic applicable as a bug report or it should be a github issue?

Thanks.

[mattg]

GitHub issue please

And link to this thread