Wildcard character - * or %

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
Hazark
New user
New user
Posts: 26
Joined: 2014-05-08 23:17

Wildcard character - * or %

Post by Hazark » 2015-09-22 15:20

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

User avatar
mattg
Moderator
Moderator
Posts: 20120
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Wildcard character - * or %

Post by mattg » 2015-09-22 23:54

Hazark wrote: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
Hazark wrote: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
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
SorenR
Senior user
Senior user
Posts: 3183
Joined: 2006-08-21 15:38
Location: Denmark

Re: Wildcard character - * or %

Post by SorenR » 2015-09-23 00:20

mattg wrote:
Hazark wrote: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
SørenR.

“With age comes wisdom, but sometimes age comes alone.”
- Oscar Wilde

Hazark
New user
New user
Posts: 26
Joined: 2014-05-08 23:17

Re: Wildcard character - * or %

Post by Hazark » 2015-09-23 00:21

mattg wrote: 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, '*', '%');
mattg wrote: 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
New user
New user
Posts: 26
Joined: 2014-05-08 23:17

Re: Wildcard character - * or %

Post by Hazark » 2015-09-23 00:27

SorenR wrote:
mattg wrote:
Hazark wrote: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
New user
New user
Posts: 26
Joined: 2014-05-08 23:17

Re: Wildcard character - * or %

Post by Hazark » 2015-09-23 00:44

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.

User avatar
mattg
Moderator
Moderator
Posts: 20120
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Wildcard character - * or %

Post by mattg » 2015-09-23 01:01

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
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Hazark
New user
New user
Posts: 26
Joined: 2014-05-08 23:17

Re: Wildcard character - * or %

Post by Hazark » 2015-09-23 10:36

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.

User avatar
mattg
Moderator
Moderator
Posts: 20120
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: Wildcard character - * or %

Post by mattg » 2015-09-24 01:32

GitHub issue please

And link to this thread
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

Post Reply