mysql split string by comma and count
SELECT (LENGTH(`table`.`fieldname`) - LENGTH( REPLACE( `table`.`fieldname` , ',', '' ) ) + 1) AS `number` FROM `table` WHERE TRIM( IFNULL( `table`.`fieldname` , '' ) ) > ''
git cheat sheet
Delete all local branches but master and the current one
git branch | grep -v "master" | grep -v ^* | xargs git branch -D;
what did I do? aka git last commit
git diff @~..@
What did I do the last week?
git log --stat --since='1 Week Ago' --graph --pretty=oneline --abbrev-commit --date=relative
git log only the relevant merges
git log --all --graph --decorate --oneline --simplify-by-decoration
show all untracked files
git ls-files --others --exclude-standard
last tag
describe --tags --abbrev=0
htaccess if the host matches
It is a hassle to have different configurations in development and production. Often it is required to protect the new webpage with user and password. Publishing that config on production on the other hand would be fatal.
With Apache 2.4 that is pretty easy done
<If "%{HTTP_HOST} == 'stage.example.com'">
AuthType basic
AuthName "private"
AuthUserFile /home/example/.htpasswd
Require valid-user
</If>
Fun with windows subsystem for linux Part 3
Now there Debian available over the Microsoft Store on Windows 10. Very small RAM usage, much better than the ubuntu version.
Open PowerShell as Administrator and run:
Enable-WindowsOptionalFeature -Online -FeatureName Microsoft-Windows-Subsystem-Linux
Then Open the Microsoft Store and search for debian.
The File are now in C:\Users\%USERNAME%\AppData\Local\Packages\TheDebianProject.DebianGNULinux_*
I wonder why it is no longer in C:\Users\%USERNAME%\AppData\Local\lxss\
For having correct displayed german characters I had to change the console
sudo dpkg-reconfigure locales sudo apt install console-setup sudo dpkg-reconfigure console-setup
MariaDB Case Sensitive Table names on Windows
I had an issue while migrating (My)SQL data back and forth from Linux and Windows. When ever I made come changes on my local windows dev system and tried to apply the changes to the production system the SQL statement failed. Later I noticed that the table names on Windows were no matter what I tried in lower case. The MariaDB docs told me that the table names on Windows where always lower case and case insensitive. What a bummer.
Searching I foundlower_case_table_names in the docs. With the values 0,1 and 2.
0 (Unix) = table names and aliases and database names are compared in a case-sensitive manner.
1 (Windows) = names are stored in lowercase and not compared in a case-sensitive manner.
2 (Mac OS X) = names are stored as declared, but compared in lowercase.
I tried 0 and got
[ERROR] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory resides on a case-insensitive file system. Please use a case sensitive file system for your data directory or switch to a case-insensitive table name mode. [ERROR] Aborting
But setting it to 2 works. No longer problems
[mysqld] datadir=C:/Program Files/MariaDB 10.2/data port=3306 innodb_buffer_pool_size=1003M character-set-server=utf8 performance_schema = ON lower_case_table_names = 2 [client] port=3306 plugin-dir=C:/Program Files/MariaDB 10.2/lib/plugin
I wonder cause in the old days of using MySQL it never was a problem.
http/2.0 sslciphersuites with 256 bit alias crypto wars part six meeting HIPPA
The chosen SSL Config was very good! But for I client I had to meet the specs from PCI DSS[1], HIPAA[2] and NIST[3].
The server already was PCI DSS ready. However since there are medical data it had to meet HIPAA too.
It turned out that HIPAA does not allow the nice CHACHA20-POLY1305 ciphers and I had to enable SSLStaplingCache that I turned of when I used StartSSL Certs cause of the timeout / outage from the response server from start ssl.
<If "%{SERVER_PORT} == '443'">
<IfModule mod_headers.c>
Header always set Strict-Transport-Security "max-age=15553000; preload"
</IfModule>
</If>
SSLUseStapling On
SSLSessionCache shmcb:/opt/apache2/logs/ssl_gcache_data(512000)
SSLStaplingCache shmcb:/opt/apache2/logs/ssl_stapling_data(512000)
SSLOptions +StrictRequire +StdEnvVars -ExportCertData
SSLProtocol -all +TLSv1.1 +TLSv1.2
SSLCompression Off
SSLHonorCipherOrder On
SSLCipherSuite ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-SHA384:ECDHE-RSA-AES256-SHA:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-SHA256:DHE-RSA-AES256-SHA
SSLOpenSSLConfCmd ECDHParameters secp384r1
SSLOpenSSLConfCmd Curves sect571r1:sect571k1:secp521r1:sect409k1:sect409r1:secp384r1:sect283k1:sect283r1:secp256k1:prime256v1
H2Direct OnI still get an A+ on ssllabs plus all green lights on htbridge ssl test.
[1] Payment Card Industry Data Security Standard
[2] Health Insurance Portability and Accountability Act
[3] National Institute of Standards and Technology
Access Controller for Apache 2.2 and 2.4 in one. Migrate httpd Apache config
Even though Apache 2.4 is available for a long time. Switching config might be easy on the config files itself. But inside code it can be harder, since that code might has to work on both versions. Developers often use .htaccess files. That is not recommended for performance, but a quick easy way for testing and development.
One solution is to use a .htaccess file that supports both versions.
<IfVersion < 2.4>
order allow,deny
deny from all
</IfVersion>
<IfVersion >= 2.4>
Require all denied
</IfVersion>http/2.0 sslciphersuites with 256 bit alias crypto wars part five A+ at SSL Test
At Qualys SLL Test labs tests I never had 100% for Key Exchange. Even adding a 4096 Diffie Hellman key did not do the trick.
Now I found adding
SSLOpenSSLConfCmd ECDHParameters secp384r1
to the config from Part 4 does the trick!
Now I can have all your bars on Qualys SSL Test at 100% without having an insane config no client can connect to.
rm -rf argument list too long
Well to often here is this the error message rm -rf argument list too long cause there are too many file in the directory. With the help of a little script those files can be deleted.
for FILE in `ls` do rm $FILE done
Office 365 get users last password change
This requires admin access to Azure / Office 365
Import-Module MSOnline $login = Get-Credential Connect-MsolService -Credential $login Get-MSOLUser -All | Select DisplayName, UserPrincipalName, LastPasswordChangeTimestamp, PasswordNeverExpires | Sort-Object LastPasswordChangeTimestamp
Note You must have the PowerShell Azure Active Directory (MSOL) Cmdlets installed for this script to work. You can download them here:
https://msdn.microsoft.com/en-us/library/azure/jj151815.aspx?f=255&MSPPError=-2147217396