Skip to main content

mysql case when then end

Use case

I use a Dovecot IMAP & POP server, against a MySQL user database. In database, the users are stored this way:

id pwd
mihamina@gulfsat.mg $1$HSBxVt/clgdj0L5Fgzm61
mihamina@freedsl.mg $1$7TCTJJMG9cUvAlWuAbvE.
mihamina@rktmb.org $1$H23xVt.bbgdj0L5Fgzm61

On my server I have mutiple IP adresses:

  • A legacy IP (1.2.3.4) where "@rktmb.org" users sometimes log in only with the localpart, sometimes with the full email address
  • A legacy IP (5.6.7.8) where "@freedsl.mg" users sometimes log in only with the localpart, sometimes with the full email address
  • A last IP (7.8.9.0) where users log in with the full email address

In usual condition, on a mail system, all users either all login with the full email address or all with the localpart. But on my alien system, they mix their habits, for legacy reasons.

Therefore:

  • The "mihamina@rktmb.org" user always logs in on the 1.2.3.4 IP but sometimes with "mihamina", sometimes with "mihamina@rktmb.org"
  • The "mihamina@freedsl.mg" user always logs in on the 5.6.7.8 IP but sometimes with "mihamina", sometimes with "mihamina@freedsl.mg"
  • The other users always log in on 7.8.9.0 with their full email address

The SQL query using CASE WHEN THEN END

Dovecot allows to use "%u" as username and "%l" as local IP in SQL queries. This is how I did it:

 SELECT id AS user, pwd AS password  FROM passwdc  WHERE (((id = '%u') OR (id = (                               CASE                                 WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org')                                 ELSE ('%u@freedsl.mg')                               END                              ))) AND en = 1)
or 
 SELECT id AS user, pwd AS password  FROM passwdc  WHERE (((id = '%u') OR (id = (                               CASE                                 WHEN ("%l" = "1.2.3.4") THEN ('%u@rktmb.org')                                 WHEN ("%l" = "5.6.7.8") THEN ('%u@freedsl.mg')                               END                              ))) AND en = 1)

Popular posts from this blog

npm run build base-href

Using NPM to specify base-href When building an Angular application, people usually use "ng" and pass arguments to that invocation. Typically, when wanting to hard code "base-href" in "index.html", one will issue: ng build --base-href='https://ngx.rktmb.org/foo' I used to build my angular apps through Bamboo or Jenkins and they have a "npm" plugin. I got the habit to build the application with "npm run build" before deploying it. But the development team once asked me to set the "--base-href='https://ngx.rktmb.org/foo'" parameter. npm run build --base-href='https://ngx.rktmb.org/foo did not set the base href in indext.html After looking for a while, I found https://github.com/angular/angular-cli/issues/13560 where it says: You need to use −− to pass arguments to npm scripts. This did the job! The command to issue is then: npm run build -- --base-href='https://ngx.rktmb.org/foo...
Read more

VMWare Keyboard Latency

Workstation VM UI lag when typing When using a VMWare Workstation VM, I noticed there is a latency when typing in the keyboard and the real appearance of the typed character. I searched and found: Noticeable typing lag in Linux VM terminals since v16.2 upgrade on Linux host To make it short, what solved it for me: Disable 3D acceleration in the VM setting .
Read more

wget maven ntlm proxy

How to make wget, curl and Maven download behind an NTLM Proxy Working on CentOS, behind an NTLM proxy: yum can deal without problem with a NTLM Proxy wget, curl and Maven cannot The solution is to use " cntlm ". " cntlm " is a NTLM client for proxies requiring NTLM authentication. How it works Install "cntlm" Configure "cntlm"  by giving it your credentials by giving it the NTLM Proxy Start "cntlm" deamon (it listens to "127.0.0.1:3128") Configure wget, curl and Maven to use "cntlm" instead of using directly the NTLM Proxy Note: You will have then a kind of 2 stages Proxy : cntlm + the NTLM proxy Configure CNTLM After installing cntlm, the configuration file is in "cntlm.conf". You must have your domain (in the Windows meaning), proxy login and  proxy password. Mine are respectively: rktmb.org, mihamina, 1234abcd (yes, just for the example) You must have you NTLM Proxy Hostnama or IP ...
Read more