DEV Community: hexfloor

Cybersecurity 101 : https certificate chain

hexfloor — Sat, 18 Oct 2025 19:57:27 +0000

Introduction

When I get to this subject once again I often see dolorem ipsum in the eyes of interlocutor. I pay my personal tribute to Tim Berners-Lee who modestly name himself as web developer. Who am I then? Probably web amateur.
HTTP is brilliant. This simple and ingenious protocol brought light to the world.
The question is : what is the link in between the https certificate chain and the icon in the browser near the address bar?

Setup

Let's get the certificate for the dev.to
What we see here is the chain :

Leaf certificate (CN = dev.to)
Intermediate certificate (CN = GlobalSign Atlas R3 DV TLS CA 2024 Q4)
Root certificate (CN = GlobalSign Root CA - R3) - implicitly trusted by the browser

:~$ openssl s_client -connect dev.to:443 -showcerts
Connecting to 151.101.66.217
CONNECTED(00000003)
depth=2 OU=GlobalSign Root CA - R3, O=GlobalSign, CN=GlobalSign
verify return:1
depth=1 C=BE, O=GlobalSign nv-sa, CN=GlobalSign Atlas R3 DV TLS CA 2024 Q4
verify return:1
depth=0 CN=dev.to
verify return:1
---
Certificate chain
 0 s:CN=dev.to
   i:C=BE, O=GlobalSign nv-sa, CN=GlobalSign Atlas R3 DV TLS CA 2024 Q4
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
   v:NotBefore: Jan  7 22:00:10 2025 GMT; NotAfter: Feb  8 22:00:09 2026 GMT
-----BEGIN CERTIFICATE-----
MIIGUDCCBTigAwIBAgIQAWQ1KPLfkC70pN8Jc+wF7zANBgkqhkiG9w0BAQsFADBY
MQswCQYDVQQGEwJCRTEZMBcGA1UEChMQR2xvYmFsU2lnbiBudi1zYTEuMCwGA1UE
AxMlR2xvYmFsU2lnbiBBdGxhcyBSMyBEViBUTFMgQ0EgMjAyNCBRNDAeFw0yNTAx
MDcyMjAwMTBaFw0yNjAyMDgyMjAwMDlaMBExDzANBgNVBAMMBmRldi50bzCCASIw
DQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAOQ2ORjnuRDuJIkWHkjiwUNuu106
2K31kpQ4biv/MyU2aiBFXyfwZA3J3nAVwvWQgfcKGKv4cxc5AMpIn9k6H92+LwyZ
DLmvjTUY0iXgeT06zww7WAkCaVWm4tMrJIIay0FmEefajVgEXiO/eomDPbRP9Wh5
pMYXRhlQ1Xxbi5lV+jZ7YRZ8Qjx0a0dpKW6LTeMx+Ykk41+uZDun0J5S/DdCVdHs
qGQ/i+rGyKaKExROBiveolPbV3nvKmTqHUoNTE9xdx5lmFiX/2ewUNq01dHQSXiK
bunyVILjK2x5mhNSyGUwSUp+rmmSSyulNAN6nVa8OAT63Spzce6qluHAedECAwEA
AaOCA1swggNXMBEGA1UdEQQKMAiCBmRldi50bzAOBgNVHQ8BAf8EBAMCBaAwHQYD
VR0lBBYwFAYIKwYBBQUHAwEGCCsGAQUFBwMCMB0GA1UdDgQWBBR6wUmxn+wr4zFL
M9Aruej3afnhJjBXBgNVHSAEUDBOMAgGBmeBDAECATBCBgorBgEEAaAyCgEDMDQw
MgYIKwYBBQUHAgEWJmh0dHBzOi8vd3d3Lmdsb2JhbHNpZ24uY29tL3JlcG9zaXRv
cnkvMAwGA1UdEwEB/wQCMAAwgZ4GCCsGAQUFBwEBBIGRMIGOMEAGCCsGAQUFBzAB
hjRodHRwOi8vb2NzcC5nbG9iYWxzaWduLmNvbS9jYS9nc2F0bGFzcjNkdnRsc2Nh
MjAyNHE0MEoGCCsGAQUFBzAChj5odHRwOi8vc2VjdXJlLmdsb2JhbHNpZ24uY29t
L2NhY2VydC9nc2F0bGFzcjNkdnRsc2NhMjAyNHE0LmNydDAfBgNVHSMEGDAWgBRg
kewcAvIO/mNPZctisAIqA1jpszBIBgNVHR8EQTA/MD2gO6A5hjdodHRwOi8vY3Js
Lmdsb2JhbHNpZ24uY29tL2NhL2dzYXRsYXNyM2R2dGxzY2EyMDI0cTQuY3JsMIIB
fwYKKwYBBAHWeQIEAgSCAW8EggFrAWkAdgDLOPcViXyEoURfW8Hd+8lu8ppZzUcK
aQWFsMsUwxRY5wAAAZRCyE72AAAEAwBHMEUCIQDc1mIvrvKoBsv7ydejDlWnQ/0v
+71hHEtT8Re/YDG/vAIgam/Q4rLTXWSdR2nSo6s/D+f9mHViZYcwv8BchIGKbvwA
dgAOV5S8866pPjMbLJkHs/eQ35vCPXEyJd0hqSWsYcVOIQAAAZRCyE9RAAAEAwBH
MEUCIEPOQIDuHMa1CskoxOesIu+0gcknyRqpyUnaFnRLFvMgAiEAsABLJF3sSpMf
ydhKYejLUSpyuoM7VSVNGLckc2eb0/MAdwAlL5TCKynpbp9BGnIHK2lcW1L/l6kN
JUC7/NxR7E3uCwAAAZRCyE/2AAAEAwBIMEYCIQDUVJGQvAx/6q85yuMdGh6DVfwE
xCkmEsyE3XG3zaIn1AIhALeFtcR/RJch/+eFo8mcEtQF301IQBoyDxUYEYWhLRS/
MA0GCSqGSIb3DQEBCwUAA4IBAQBWNAPW1x4TKJPDj6NIlzVhyiodHIWBZMRaPVKc
WWo2UPqqToWup3cxArTzVn6Wq90CHvnXT6bGONM9AE9yaG3ulYzcxd4iUl4p8Kka
vWuZxxxN19aH5cw/L+k4VS+M6egPROBcedMEoO0xvfSllTxMc6UQKMkqlzfxu+Xx
mYSph2SFjTTLMj6Dh/jgW6vz0g/rrtxhctBPYKrDgNlLXwDsRZbXfPlmf4+D0v3k
CbbBKOSwO5TjpkIdMp+fZl6JWzGxBoCVjR8HDcIlCv/qfhiEsJkIV+q2H2a/JDzN
fX/26ksGeatVHK4VfwThfrpBf0tTMlJcoWWYgWNiOJEs7/Gq
-----END CERTIFICATE-----
 1 s:C=BE, O=GlobalSign nv-sa, CN=GlobalSign Atlas R3 DV TLS CA 2024 Q4
   i:OU=GlobalSign Root CA - R3, O=GlobalSign, CN=GlobalSign
   a:PKEY: rsaEncryption, 2048 (bit); sigalg: RSA-SHA256
   v:NotBefore: Jul 17 03:09:40 2024 GMT; NotAfter: Jul 17 00:00:00 2026 GMT
-----BEGIN CERTIFICATE-----
MIIEkDCCA3igAwIBAgIRAIF81RQm8t32/1iAIsiFVPYwDQYJKoZIhvcNAQELBQAw
TDEgMB4GA1UECxMXR2xvYmFsU2lnbiBSb290IENBIC0gUjMxEzARBgNVBAoTCkds
b2JhbFNpZ24xEzARBgNVBAMTCkdsb2JhbFNpZ24wHhcNMjQwNzE3MDMwOTQwWhcN
MjYwNzE3MDAwMDAwWjBYMQswCQYDVQQGEwJCRTEZMBcGA1UEChMQR2xvYmFsU2ln
biBudi1zYTEuMCwGA1UEAxMlR2xvYmFsU2lnbiBBdGxhcyBSMyBEViBUTFMgQ0Eg
MjAyNCBRNDCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAOCRJsRiWdvk
//JkfwxqxdyBjpBRyf9hnvqpK0sGXYq/1lELxkHYFc3sI84sotxVuYPTZef+KILX
cTmvs2SNOvpofDDgWG2HmJcTUgf1F7ZEcsAOzSVwPNZUpqsNL4EbxbbH6Bns2RlV
nA5CygsfjDbCRAl3HRgHcPR+oJ2JbgqY6STT+7Kgbn8ZsQ0nG3iWxLKwd/CbAE/v
Ew9ziXHC7PkTLM1AxIXRRySGoUB+EQ4Vf1s42zrc6nTtyNncIFFDmLgMLBOdIXQQ
H1i17r3hmeUrVbNqPzROFson+Uar41e/dWxJHGl//LKOmuG7H8bO0sj6dX3nD5eM
ITtc11WkukMCAwEAAaOCAV8wggFbMA4GA1UdDwEB/wQEAwIBhjAdBgNVHSUEFjAU
BggrBgEFBQcDAQYIKwYBBQUHAwIwEgYDVR0TAQH/BAgwBgEB/wIBADAdBgNVHQ4E
FgQUYJHsHALyDv5jT2XLYrACKgNY6bMwHwYDVR0jBBgwFoAUj/BLf6guRSSuTVD6
Y5qL3uLdG7wwewYIKwYBBQUHAQEEbzBtMC4GCCsGAQUFBzABhiJodHRwOi8vb2Nz
cDIuZ2xvYmFsc2lnbi5jb20vcm9vdHIzMDsGCCsGAQUFBzAChi9odHRwOi8vc2Vj
dXJlLmdsb2JhbHNpZ24uY29tL2NhY2VydC9yb290LXIzLmNydDA2BgNVHR8ELzAt
MCugKaAnhiVodHRwOi8vY3JsLmdsb2JhbHNpZ24uY29tL3Jvb3QtcjMuY3JsMCEG
A1UdIAQaMBgwCAYGZ4EMAQIBMAwGCisGAQQBoDIKAQMwDQYJKoZIhvcNAQELBQAD
ggEBAD3KE6Hs12ahVEfIugBDwbQA1O9Lf/ZOYuhmKI/o5wGEE4RLSe4mEFIRpF+b
mhCipBau7lDPgMLMJb2dwJEUHVi+nUk36y7B9LcnM4CZr0cYaa4PsTkZtpANfkLa
/35Uitk9p+nmcr6KP0v7CU4yJu2zGawK1TMfeEkmyxxPV9dECIM7hiu5vrIVJo1H
F5TNwODKpVnPIUAVMGkdK8B4Mdbd1FVjkhQlMwEUScl2aAsv6ji1K+j5MwPvTU/a
I5JOoIHyMXnq5TwAClAmZxzZ2YHY2fR/oL/59Qy8U+RaBqgK2/Vf93IwFMyPP836
v2EqJED5yaIwUb45KjP7es1C808=
-----END CERTIFICATE-----
---
Server certificate
subject=CN=dev.to
issuer=C=BE, O=GlobalSign nv-sa, CN=GlobalSign Atlas R3 DV TLS CA 2024 Q4
---
No client certificate CA names sent
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: X25519, 253 bits
---
SSL handshake has read 3446 bytes and written 538 bytes
Verification: OK
---
New, TLSv1.2, Cipher is ECDHE-RSA-CHACHA20-POLY1305
Protocol: TLSv1.2
Server public key is 2048 bit
Secure Renegotiation IS supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
    Protocol  : TLSv1.2
    Cipher    : ECDHE-RSA-CHACHA20-POLY1305
    Session-ID: E52D02DFE2963AC716D7FC6CF0DE0B19CD26ED7DBD8702B730B2A764D61FFB93
    Session-ID-ctx:
    Master-Key: 3997597DF103A8BBA763F6DA1146AF234F5110AD20A9AC9E067B45596CCAE908D1F932D7C2B785790985D287DCF55255
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 86400 (seconds)
    TLS session ticket:
    0000 - 12 b1 47 62 83 d2 07 b1-a9 20 09 da 99 9d 01 79   ..Gb..... .....y
    0010 - 67 99 5f 67 29 d7 ec 01-13 b4 21 8d 19 ca da 13   g._g).....!.....
    0020 - dd 97 b2 69 b9 8c ac 3a-17 af fc 93 05 f6 7a c1   ...i...:......z.
    0030 - f3 67 7b 03 3b 40 39 7e-d3 44 b7 88 2e 17 2d f3   .g{.;@9~.D....-.
    0040 - a1 40 e3 27 15 0a 60 fd-01 18 1b 16 07 9c 1a 9f   .@.'..`.........
    0050 - e5 f2 3e ef 22 00 eb 5d-01 e1 ef a7 13 39 55 b1   ..>."..].....9U.
    0060 - 00 ef 3f b0 97 67 f7 e7-c2 b7 32 1d fe c4 eb d3   ..?..g....2.....
    0070 - e6 43 bc 50 33 ac 0e 98-65 09 4c 82 ae a9 84 e2   .C.P3...e.L.....
    0080 - 81 52 f4 04 ba bd 3d 75-51 4f 0a 36 15 5d b4 38   .R....=uQO.6.].8
    0090 - b3 ee e3 54 81 15 41 55-22 b8 93 d7 30 e0 6c d7   ...T..AU"...0.l.
    00a0 - 4c d2 25 ce a4 90 16 1d-e0 b9 09 c3 7b e6 9b 24   L.%.........{..$
    00b0 - 5a d9 0d 4b 58 ba 6f 07-48 a0 30 7a 0a 72 e8 a4   Z..KX.o.H.0z.r..

    Start Time: 1760808666
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: yes
---


closed

Now let's extract the first certificate to dev.to.pem and the second to intermediate.pem.
And then verify both :

:~$ openssl verify -CAfile intermediate.pem dev.to.pem
dev.to.pem: OK
:~$ openssl verify intermediate.pem
intermediate.pem: OK

“We need to go deeper” (from Inception, 2010).

:~$ openssl x509 -in dev.to.pem -noout -text | grep "Signature Algorithm" | head -n 1
        Signature Algorithm: sha256WithRSAEncryption

The following gives that :

hash algorithm is SHA-256
the public key algorithm is RSA
hence we need to verify RSA signature of SHA256(tbsCertificate)

Let's extract the intermediate certificate public key :

:~$ openssl x509 -in intermediate.pem -pubkey -noout > intermediate_pubkey.pem
:~$ cat intermediate_pubkey.pem
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA4JEmxGJZ2+T/8mR/DGrF
3IGOkFHJ/2Ge+qkrSwZdir/WUQvGQdgVzewjziyi3FW5g9Nl5/4ogtdxOa+zZI06
+mh8MOBYbYeYlxNSB/UXtkRywA7NJXA81lSmqw0vgRvFtsfoGezZGVWcDkLKCx+M
NsJECXcdGAdw9H6gnYluCpjpJNP7sqBufxmxDScbeJbEsrB38JsAT+8TD3OJccLs
+RMszUDEhdFHJIahQH4RDhV/WzjbOtzqdO3I2dwgUUOYuAwsE50hdBAfWLXuveGZ
5StVs2o/NE4Wyif5RqvjV791bEkcaX/8so6a4bsfxs7SyPp1fecPl4whO1zXVaS6
QwIDAQAB
-----END PUBLIC KEY-----

Extract the tbsCertificate (to be signed), flash-forward, we need these lines : 4:d=1 hl=4 l=1336 cons: SEQUENCE and 1359:d=1 hl=4 l= 257 prim: BIT STRING

:~$ openssl asn1parse -in dev.to.pem
    0:d=0  hl=4 l=1616 cons: SEQUENCE
    4:d=1  hl=4 l=1336 cons: SEQUENCE
    8:d=2  hl=2 l=   3 cons: cont [ 0 ]
   10:d=3  hl=2 l=   1 prim: INTEGER           :02
   13:d=2  hl=2 l=  16 prim: INTEGER           :01643528F2DF902EF4A4DF0973EC05EF
   31:d=2  hl=2 l=  13 cons: SEQUENCE
   33:d=3  hl=2 l=   9 prim: OBJECT            :sha256WithRSAEncryption
   44:d=3  hl=2 l=   0 prim: NULL
   46:d=2  hl=2 l=  88 cons: SEQUENCE
   48:d=3  hl=2 l=  11 cons: SET
   50:d=4  hl=2 l=   9 cons: SEQUENCE
   52:d=5  hl=2 l=   3 prim: OBJECT            :countryName
   57:d=5  hl=2 l=   2 prim: PRINTABLESTRING   :BE
   61:d=3  hl=2 l=  25 cons: SET
   63:d=4  hl=2 l=  23 cons: SEQUENCE
   65:d=5  hl=2 l=   3 prim: OBJECT            :organizationName
   70:d=5  hl=2 l=  16 prim: PRINTABLESTRING   :GlobalSign nv-sa
   88:d=3  hl=2 l=  46 cons: SET
   90:d=4  hl=2 l=  44 cons: SEQUENCE
   92:d=5  hl=2 l=   3 prim: OBJECT            :commonName
   97:d=5  hl=2 l=  37 prim: PRINTABLESTRING   :GlobalSign Atlas R3 DV TLS CA 2024 Q4
  136:d=2  hl=2 l=  30 cons: SEQUENCE
  138:d=3  hl=2 l=  13 prim: UTCTIME           :250107220010Z
  153:d=3  hl=2 l=  13 prim: UTCTIME           :260208220009Z
  168:d=2  hl=2 l=  17 cons: SEQUENCE
  170:d=3  hl=2 l=  15 cons: SET
  172:d=4  hl=2 l=  13 cons: SEQUENCE
  174:d=5  hl=2 l=   3 prim: OBJECT            :commonName
  179:d=5  hl=2 l=   6 prim: UTF8STRING        :dev.to
  187:d=2  hl=4 l= 290 cons: SEQUENCE
  191:d=3  hl=2 l=  13 cons: SEQUENCE
  193:d=4  hl=2 l=   9 prim: OBJECT            :rsaEncryption
  204:d=4  hl=2 l=   0 prim: NULL
  206:d=3  hl=4 l= 271 prim: BIT STRING
  481:d=2  hl=4 l= 859 cons: cont [ 3 ]
  485:d=3  hl=4 l= 855 cons: SEQUENCE
  489:d=4  hl=2 l=  17 cons: SEQUENCE
  491:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 Subject Alternative Name
  496:d=5  hl=2 l=  10 prim: OCTET STRING      [HEX DUMP]:300882066465762E746F
  508:d=4  hl=2 l=  14 cons: SEQUENCE
  510:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 Key Usage
  515:d=5  hl=2 l=   1 prim: BOOLEAN           :255
  518:d=5  hl=2 l=   4 prim: OCTET STRING      [HEX DUMP]:030205A0
  524:d=4  hl=2 l=  29 cons: SEQUENCE
  526:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 Extended Key Usage
  531:d=5  hl=2 l=  22 prim: OCTET STRING      [HEX DUMP]:301406082B0601050507030106082B06010505070302
  555:d=4  hl=2 l=  29 cons: SEQUENCE
  557:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 Subject Key Identifier
  562:d=5  hl=2 l=  22 prim: OCTET STRING      [HEX DUMP]:04147AC149B19FEC2BE3314B33D02BB9E8F769F9E126
  586:d=4  hl=2 l=  87 cons: SEQUENCE
  588:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 Certificate Policies
  593:d=5  hl=2 l=  80 prim: OCTET STRING      [HEX DUMP]:304E3008060667810C0102013042060A2B06010401A0320A01033034303206082B06010505070201162668747470733A2F2F7777772E676C6F62616C7369676E2E636F6D2F7265706F7369746F72792F
  675:d=4  hl=2 l=  12 cons: SEQUENCE
  677:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 Basic Constraints
  682:d=5  hl=2 l=   1 prim: BOOLEAN           :255
  685:d=5  hl=2 l=   2 prim: OCTET STRING      [HEX DUMP]:3000
  689:d=4  hl=3 l= 158 cons: SEQUENCE
  692:d=5  hl=2 l=   8 prim: OBJECT            :Authority Information Access
  702:d=5  hl=3 l= 145 prim: OCTET STRING      [HEX DUMP]:30818E304006082B060105050730018634687474703A2F2F6F6373702E676C6F62616C7369676E2E636F6D2F63612F677361746C617372336476746C736361323032347134304A06082B06010505073002863E687474703A2F2F7365637572652E676C6F62616C7369676E2E636F6D2F6361636572742F677361746C617372336476746C7363613230323471342E637274
  850:d=4  hl=2 l=  31 cons: SEQUENCE
  852:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 Authority Key Identifier
  857:d=5  hl=2 l=  24 prim: OCTET STRING      [HEX DUMP]:301680146091EC1C02F20EFE634F65CB62B0022A0358E9B3
  883:d=4  hl=2 l=  72 cons: SEQUENCE
  885:d=5  hl=2 l=   3 prim: OBJECT            :X509v3 CRL Distribution Points
  890:d=5  hl=2 l=  65 prim: OCTET STRING      [HEX DUMP]:303F303DA03BA0398637687474703A2F2F63726C2E676C6F62616C7369676E2E636F6D2F63612F677361746C617372336476746C7363613230323471342E63726C
  957:d=4  hl=4 l= 383 cons: SEQUENCE
  961:d=5  hl=2 l=  10 prim: OBJECT            :CT Precertificate SCTs
  973:d=5  hl=4 l= 367 prim: OCTET STRING      [HEX DUMP]:0482016B0169007600CB38F715897C84A1445F5BC1DDFBC96EF29A59CD470A690585B0CB14C31458E70000019442C84EF60000040300473045022100DCD6622FAEF2A806CBFBC9D7A30E55A743FD2FFBBD611C4B53F117BF6031BFBC02206A6FD0E2B2D35D649D4769D2A3AB3F0FE7FD987562658730BFC05C84818A6EFC0076000E5794BCF3AEA93E331B2C9907B3F790DF9BC23D713225DD21A925AC61C54E210000019442C84F510000040300473045022043CE4080EE1CC6B50AC928C4E7AC22EFB481C927C91AA9C949DA16744B16F320022100B0004B245DEC4A931FC9D84A61E8CB512A72BA833B55254D18B72473679BD3F3007700252F94C22B29E96E9F411A72072B695C5B52FF97A90D2540BBFCDC51EC4DEE0B0000019442C84FF60000040300483046022100D4549190BC0C7FEAAF39CAE31D1A1E8355FC04C4292612CC84DD71B7CDA227D4022100B785B5C47F449721FFE785A3C99C12D405DF4D48401A320F15181185A12D14BF
 1344:d=1  hl=2 l=  13 cons: SEQUENCE
 1346:d=2  hl=2 l=   9 prim: OBJECT            :sha256WithRSAEncryption
 1357:d=2  hl=2 l=   0 prim: NULL
 1359:d=1  hl=4 l= 257 prim: BIT STRING

Now :

:~$ openssl asn1parse -in dev.to.pem -out dev.to.der -noout -strparse 4

:~$ cat dev.to.der
0�8�d5(�*�H��   s��0
0X1
   0    UBE10U
U60208220009Z0101.0,U%GlobalSign Atlas R3 DV TLS CA 2024 Q40
�0�v.to0*�H��
��p�����H��Cn�]:ح���8n+�3%6j E_'�d
��s9�H��:ݾ/
           �
            ���5�%�y=:�
LOqwe�X��g�Pڴ���Ix�n��T��+ly�R�e0IJ~�i�K+�4z�V�8��*sq��y���[0�W0U��U�6{a|B<tkGi)n�M�1��$�_�d;�ОR�7BU��d?���Ȧ�N+ޢS�Wy�*d�J
�dev.to0U��0U%+0Uz�I���+�1K3�+���i��&0WU P0Ng�
                                              0B
+�2
040+&https://www.globalsign.com/repository/0
                                            U�00�+��0��0+0�4http://ocsp.globalsign.com/ca/gsatlasr3dvtlsca2024q40+0�>http://secure.globalsign.com/cacert/gsatlasr3dvtlsca2024q4.crt0U#0�`����cOe�b�*X�0HUA0?0=�;�9�7http://crl.globalsign.com/ca/gsatlasr3dvtlsca2024q4.crl0�
+�y�o�kiv�8��|��D_[����n�Y�G
i����X��B�N�G0E!��b/�����ףU�C�/��aKS��`1�� jo���]d�Giң�?���ube�0��\���n�vW���>3���ߛ�=q2%�!�%�a�N!�B�OQG0E C�@��Ƶ
%@���Q�M�␦��I�tK� !�K$]�J���Ja��Q*r��;U%M�$sg���w%/��+)�n�A␦r+i\[R���
         �B�O�H0F!�T���
                       �9��␦�U��)&̄�q�͢'�!����D�!�煣ɜ��MH@␦2��-�

Extract the bit string given 1359:d=1 hl=4 l= 257 prim: BIT STRING

:~$ openssl asn1parse -in dev.to.pem -out signature.der -strparse 1359 -noout

:~$ wc -c signature.der
256 signature.der

:~$ cat signature.der
V4��(�Ï�H�5a�*��d�Z=R�Yj6P��N���w1��V~�����O��8�=Orhm���"R^)�␦�k��M�և��?/�8U/���D�\y���1���<Ls�(�*�7��񙄩�d��4�2>����[�����ar�O`�À�K_��%�|�f�����     ��(�;��B2��f^�[1����
��~��W�f�$<�}��Ky�U��~�AKS2R\�e��cb8�,��

Verification:

:~$ openssl dgst -sha256 -verify intermediate_pubkey.pem -signature signature.der dev.to.der
Verified OK

“We need to go deeper”

Now know that the critical step here is to understand that verification is the following

extract the certificate information besides signature dev.to.der
hash the certificate information
apply public key from the intermediate certificate to the signature
compare both Let's look again at our certificate :

:~$ openssl x509 -in dev.to.pem -noout -text
Certificate:
    Data:
        Version: 3 (0x2)
        Serial Number:
            01:64:35:28:f2:df:90:2e:f4:a4:df:09:73:ec:05:ef
        Signature Algorithm: sha256WithRSAEncryption
        Issuer: C=BE, O=GlobalSign nv-sa, CN=GlobalSign Atlas R3 DV TLS CA 2024 Q4
        Validity
            Not Before: Jan  7 22:00:10 2025 GMT
            Not After : Feb  8 22:00:09 2026 GMT
        Subject: CN=dev.to
        Subject Public Key Info:
            Public Key Algorithm: rsaEncryption
                Public-Key: (2048 bit)
                Modulus:
                    00:e4:36:39:18:e7:b9:10:ee:24:89:16:1e:48:e2:
                    c1:43:6e:bb:5d:3a:d8:ad:f5:92:94:38:6e:2b:ff:
                    33:25:36:6a:20:45:5f:27:f0:64:0d:c9:de:70:15:
                    c2:f5:90:81:f7:0a:18:ab:f8:73:17:39:00:ca:48:
                    9f:d9:3a:1f:dd:be:2f:0c:99:0c:b9:af:8d:35:18:
                    d2:25:e0:79:3d:3a:cf:0c:3b:58:09:02:69:55:a6:
                    e2:d3:2b:24:82:1a:cb:41:66:11:e7:da:8d:58:04:
                    5e:23:bf:7a:89:83:3d:b4:4f:f5:68:79:a4:c6:17:
                    46:19:50:d5:7c:5b:8b:99:55:fa:36:7b:61:16:7c:
                    42:3c:74:6b:47:69:29:6e:8b:4d:e3:31:f9:89:24:
                    e3:5f:ae:64:3b:a7:d0:9e:52:fc:37:42:55:d1:ec:
                    a8:64:3f:8b:ea:c6:c8:a6:8a:13:14:4e:06:2b:de:
                    a2:53:db:57:79:ef:2a:64:ea:1d:4a:0d:4c:4f:71:
                    77:1e:65:98:58:97:ff:67:b0:50:da:b4:d5:d1:d0:
                    49:78:8a:6e:e9:f2:54:82:e3:2b:6c:79:9a:13:52:
                    c8:65:30:49:4a:7e:ae:69:92:4b:2b:a5:34:03:7a:
                    9d:56:bc:38:04:fa:dd:2a:73:71:ee:aa:96:e1:c0:
                    79:d1
                Exponent: 65537 (0x10001)
        X509v3 extensions:
            X509v3 Subject Alternative Name:
                DNS:dev.to
            X509v3 Key Usage: critical
                Digital Signature, Key Encipherment
            X509v3 Extended Key Usage:
                TLS Web Server Authentication, TLS Web Client Authentication
            X509v3 Subject Key Identifier:
                7A:C1:49:B1:9F:EC:2B:E3:31:4B:33:D0:2B:B9:E8:F7:69:F9:E1:26
            X509v3 Certificate Policies:
                Policy: 2.23.140.1.2.1
                Policy: 1.3.6.1.4.1.4146.10.1.3
                  CPS: https://www.globalsign.com/repository/
            X509v3 Basic Constraints: critical
                CA:FALSE
            Authority Information Access:
                OCSP - URI:http://ocsp.globalsign.com/ca/gsatlasr3dvtlsca2024q4
                CA Issuers - URI:http://secure.globalsign.com/cacert/gsatlasr3dvtlsca2024q4.crt
            X509v3 Authority Key Identifier:
                60:91:EC:1C:02:F2:0E:FE:63:4F:65:CB:62:B0:02:2A:03:58:E9:B3
            X509v3 CRL Distribution Points:
                Full Name:
                  URI:http://crl.globalsign.com/ca/gsatlasr3dvtlsca2024q4.crl
            CT Precertificate SCTs:
                Signed Certificate Timestamp:
                    Version   : v1 (0x0)
                    Log ID    : CB:38:F7:15:89:7C:84:A1:44:5F:5B:C1:DD:FB:C9:6E:
                                F2:9A:59:CD:47:0A:69:05:85:B0:CB:14:C3:14:58:E7
                    Timestamp : Jan  7 22:00:11.254 2025 GMT
                    Extensions: none
                    Signature : ecdsa-with-SHA256
                                30:45:02:21:00:DC:D6:62:2F:AE:F2:A8:06:CB:FB:C9:
                                D7:A3:0E:55:A7:43:FD:2F:FB:BD:61:1C:4B:53:F1:17:
                                BF:60:31:BF:BC:02:20:6A:6F:D0:E2:B2:D3:5D:64:9D:
                                47:69:D2:A3:AB:3F:0F:E7:FD:98:75:62:65:87:30:BF:
                                C0:5C:84:81:8A:6E:FC
                Signed Certificate Timestamp:
                    Version   : v1 (0x0)
                    Log ID    : 0E:57:94:BC:F3:AE:A9:3E:33:1B:2C:99:07:B3:F7:90:
                                DF:9B:C2:3D:71:32:25:DD:21:A9:25:AC:61:C5:4E:21
                    Timestamp : Jan  7 22:00:11.345 2025 GMT
                    Extensions: none
                    Signature : ecdsa-with-SHA256
                                30:45:02:20:43:CE:40:80:EE:1C:C6:B5:0A:C9:28:C4:
                                E7:AC:22:EF:B4:81:C9:27:C9:1A:A9:C9:49:DA:16:74:
                                4B:16:F3:20:02:21:00:B0:00:4B:24:5D:EC:4A:93:1F:
                                C9:D8:4A:61:E8:CB:51:2A:72:BA:83:3B:55:25:4D:18:
                                B7:24:73:67:9B:D3:F3
                Signed Certificate Timestamp:
                    Version   : v1 (0x0)
                    Log ID    : 25:2F:94:C2:2B:29:E9:6E:9F:41:1A:72:07:2B:69:5C:
                                5B:52:FF:97:A9:0D:25:40:BB:FC:DC:51:EC:4D:EE:0B
                    Timestamp : Jan  7 22:00:11.510 2025 GMT
                    Extensions: none
                    Signature : ecdsa-with-SHA256
                                30:46:02:21:00:D4:54:91:90:BC:0C:7F:EA:AF:39:CA:
                                E3:1D:1A:1E:83:55:FC:04:C4:29:26:12:CC:84:DD:71:
                                B7:CD:A2:27:D4:02:21:00:B7:85:B5:C4:7F:44:97:21:
                                FF:E7:85:A3:C9:9C:12:D4:05:DF:4D:48:40:1A:32:0F:
                                15:18:11:85:A1:2D:14:BF
    Signature Algorithm: sha256WithRSAEncryption
    Signature Value:
        56:34:03:d6:d7:1e:13:28:93:c3:8f:a3:48:97:35:61:ca:2a:
        1d:1c:85:81:64:c4:5a:3d:52:9c:59:6a:36:50:fa:aa:4e:85:
        ae:a7:77:31:02:b4:f3:56:7e:96:ab:dd:02:1e:f9:d7:4f:a6:
        c6:38:d3:3d:00:4f:72:68:6d:ee:95:8c:dc:c5:de:22:52:5e:
        29:f0:a9:1a:bd:6b:99:c7:1c:4d:d7:d6:87:e5:cc:3f:2f:e9:
        38:55:2f:8c:e9:e8:0f:44:e0:5c:79:d3:04:a0:ed:31:bd:f4:
        a5:95:3c:4c:73:a5:10:28:c9:2a:97:37:f1:bb:e5:f1:99:84:
        a9:87:64:85:8d:34:cb:32:3e:83:87:f8:e0:5b:ab:f3:d2:0f:
        eb:ae:dc:61:72:d0:4f:60:aa:c3:80:d9:4b:5f:00:ec:45:96:
        d7:7c:f9:66:7f:8f:83:d2:fd:e4:09:b6:c1:28:e4:b0:3b:94:
        e3:a6:42:1d:32:9f:9f:66:5e:89:5b:31:b1:06:80:95:8d:1f:
        07:0d:c2:25:0a:ff:ea:7e:18:84:b0:99:08:57:ea:b6:1f:66:
        bf:24:3c:cd:7d:7f:f6:ea:4b:06:79:ab:55:1c:ae:15:7f:04:
        e1:7e:ba:41:7f:4b:53:32:52:5c:a1:65:98:81:63:62:38:91:
        2c:ef:f1:aa

And tbsCertificate :

:~$ openssl asn1parse -in dev.to.der -inform DER
    0:d=0  hl=4 l=1336 cons: SEQUENCE
    4:d=1  hl=2 l=   3 cons: cont [ 0 ]
    6:d=2  hl=2 l=   1 prim: INTEGER           :02
    9:d=1  hl=2 l=  16 prim: INTEGER           :01643528F2DF902EF4A4DF0973EC05EF
   27:d=1  hl=2 l=  13 cons: SEQUENCE
   29:d=2  hl=2 l=   9 prim: OBJECT            :sha256WithRSAEncryption
   40:d=2  hl=2 l=   0 prim: NULL
   42:d=1  hl=2 l=  88 cons: SEQUENCE
   44:d=2  hl=2 l=  11 cons: SET
   46:d=3  hl=2 l=   9 cons: SEQUENCE
   48:d=4  hl=2 l=   3 prim: OBJECT            :countryName
   53:d=4  hl=2 l=   2 prim: PRINTABLESTRING   :BE
   57:d=2  hl=2 l=  25 cons: SET
   59:d=3  hl=2 l=  23 cons: SEQUENCE
   61:d=4  hl=2 l=   3 prim: OBJECT            :organizationName
   66:d=4  hl=2 l=  16 prim: PRINTABLESTRING   :GlobalSign nv-sa
   84:d=2  hl=2 l=  46 cons: SET
   86:d=3  hl=2 l=  44 cons: SEQUENCE
   88:d=4  hl=2 l=   3 prim: OBJECT            :commonName
   93:d=4  hl=2 l=  37 prim: PRINTABLESTRING   :GlobalSign Atlas R3 DV TLS CA 2024 Q4
  132:d=1  hl=2 l=  30 cons: SEQUENCE
  134:d=2  hl=2 l=  13 prim: UTCTIME           :250107220010Z
  149:d=2  hl=2 l=  13 prim: UTCTIME           :260208220009Z
  164:d=1  hl=2 l=  17 cons: SEQUENCE
  166:d=2  hl=2 l=  15 cons: SET
  168:d=3  hl=2 l=  13 cons: SEQUENCE
  170:d=4  hl=2 l=   3 prim: OBJECT            :commonName
  175:d=4  hl=2 l=   6 prim: UTF8STRING        :dev.to
  183:d=1  hl=4 l= 290 cons: SEQUENCE
  187:d=2  hl=2 l=  13 cons: SEQUENCE
  189:d=3  hl=2 l=   9 prim: OBJECT            :rsaEncryption
  200:d=3  hl=2 l=   0 prim: NULL
  202:d=2  hl=4 l= 271 prim: BIT STRING
  477:d=1  hl=4 l= 859 cons: cont [ 3 ]
  481:d=2  hl=4 l= 855 cons: SEQUENCE
  485:d=3  hl=2 l=  17 cons: SEQUENCE
  487:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 Subject Alternative Name
  492:d=4  hl=2 l=  10 prim: OCTET STRING      [HEX DUMP]:300882066465762E746F
  504:d=3  hl=2 l=  14 cons: SEQUENCE
  506:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 Key Usage
  511:d=4  hl=2 l=   1 prim: BOOLEAN           :255
  514:d=4  hl=2 l=   4 prim: OCTET STRING      [HEX DUMP]:030205A0
  520:d=3  hl=2 l=  29 cons: SEQUENCE
  522:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 Extended Key Usage
  527:d=4  hl=2 l=  22 prim: OCTET STRING      [HEX DUMP]:301406082B0601050507030106082B06010505070302
  551:d=3  hl=2 l=  29 cons: SEQUENCE
  553:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 Subject Key Identifier
  558:d=4  hl=2 l=  22 prim: OCTET STRING      [HEX DUMP]:04147AC149B19FEC2BE3314B33D02BB9E8F769F9E126
  582:d=3  hl=2 l=  87 cons: SEQUENCE
  584:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 Certificate Policies
  589:d=4  hl=2 l=  80 prim: OCTET STRING      [HEX DUMP]:304E3008060667810C0102013042060A2B06010401A0320A01033034303206082B06010505070201162668747470733A2F2F7777772E676C6F62616C7369676E2E636F6D2F7265706F7369746F72792F
  671:d=3  hl=2 l=  12 cons: SEQUENCE
  673:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 Basic Constraints
  678:d=4  hl=2 l=   1 prim: BOOLEAN           :255
  681:d=4  hl=2 l=   2 prim: OCTET STRING      [HEX DUMP]:3000
  685:d=3  hl=3 l= 158 cons: SEQUENCE
  688:d=4  hl=2 l=   8 prim: OBJECT            :Authority Information Access
  698:d=4  hl=3 l= 145 prim: OCTET STRING      [HEX DUMP]:30818E304006082B060105050730018634687474703A2F2F6F6373702E676C6F62616C7369676E2E636F6D2F63612F677361746C617372336476746C736361323032347134304A06082B06010505073002863E687474703A2F2F7365637572652E676C6F62616C7369676E2E636F6D2F6361636572742F677361746C617372336476746C7363613230323471342E637274
  846:d=3  hl=2 l=  31 cons: SEQUENCE
  848:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 Authority Key Identifier
  853:d=4  hl=2 l=  24 prim: OCTET STRING      [HEX DUMP]:301680146091EC1C02F20EFE634F65CB62B0022A0358E9B3
  879:d=3  hl=2 l=  72 cons: SEQUENCE
  881:d=4  hl=2 l=   3 prim: OBJECT            :X509v3 CRL Distribution Points
  886:d=4  hl=2 l=  65 prim: OCTET STRING      [HEX DUMP]:303F303DA03BA0398637687474703A2F2F63726C2E676C6F62616C7369676E2E636F6D2F63612F677361746C617372336476746C7363613230323471342E63726C
  953:d=3  hl=4 l= 383 cons: SEQUENCE
  957:d=4  hl=2 l=  10 prim: OBJECT            :CT Precertificate SCTs
  969:d=4  hl=4 l= 367 prim: OCTET STRING      [HEX DUMP]:0482016B0169007600CB38F715897C84A1445F5BC1DDFBC96EF29A59CD470A690585B0CB14C31458E70000019442C84EF60000040300473045022100DCD6622FAEF2A806CBFBC9D7A30E55A743FD2FFBBD611C4B53F117BF6031BFBC02206A6FD0E2B2D35D649D4769D2A3AB3F0FE7FD987562658730BFC05C84818A6EFC0076000E5794BCF3AEA93E331B2C9907B3F790DF9BC23D713225DD21A925AC61C54E210000019442C84F510000040300473045022043CE4080EE1CC6B50AC928C4E7AC22EFB481C927C91AA9C949DA16744B16F320022100B0004B245DEC4A931FC9D84A61E8CB512A72BA833B55254D18B72473679BD3F3007700252F94C22B29E96E9F411A72072B695C5B52FF97A90D2540BBFCDC51EC4DEE0B0000019442C84FF60000040300483046022100D4549190BC0C7FEAAF39CAE31D1A1E8355FC04C4292612CC84DD71B7CDA227D4022100B785B5C47F449721FFE785A3C99C12D405DF4D48401A320F15181185A12D14BF

Comput SHA-256 of the tbsCertificate :

:~$ openssl dgst -sha256 dev.to.der
SHA2-256(dev.to.der)= 37058d7b5a35ebe02ea6baf4b267fadf4474c0f87116e16ef870d57b47ef9d09

Now let's decrypt the signature with the public key of the intermediate certificate :

:~$ openssl pkeyutl -verifyrecover -pubin -inkey intermediate_pubkey.pem -in signature.der -pkeyopt rsa_padding_mode:none -hexdump
0000 - 00 01 ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0010 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0020 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0030 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0040 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0050 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0060 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0070 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0080 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
0090 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
00a0 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
00b0 - ff ff ff ff ff ff ff ff-ff ff ff ff ff ff ff ff   ................
00c0 - ff ff ff ff ff ff ff ff-ff ff ff ff 00 30 31 30   .............010
00d0 - 0d 06 09 60 86 48 01 65-03 04 02 01 05 00 04 20   ...`.H.e.......
00e0 - 37 05 8d 7b 5a 35 eb e0-2e a6 ba f4 b2 67 fa df   7..{Z5.......g..
00f0 - 44 74 c0 f8 71 16 e1 6e-f8 70 d5 7b 47 ef 9d 09   Dt..q..n.p.{G...

As you may see in the lines 00e0 and 00f0 are matching with the signature of the tbsCertificate.
Bingo!

Summary

Here above you may see a simple example how to manually verify the certificate validity :

extract the tbsCertificate chunk from the leaf certificate
compute hash
extract public key from the intermediate certificate
decrypt signature of the leaf certificate
compare both - if they are matching then we are good Then there is a question where is the certificate store with the root CA located ? Well, this is product dependent and system dependent. Technology at the present level looks like magic, still it's essential to understand how the internet works. Cheers!

Cybersecurity 101 : data sanitization

hexfloor — Sat, 18 Oct 2025 14:56:37 +0000

Problem

TL;DR : use HMAC-SHA256

Every once in a while there is a need to perform data sanitization on personal data to be in compliance with local regulations and with the common sense.
Unless you are working for Kongo Gumi and even though if you have doubts on whether to keep the data - consider that following the information theory all data is asymptotically public or deleted.
Hence the idea listed below is in no sense a guidance to the internal policies, just a practical method to achieve one goal : replace id that is qualified as a personal data by the id that is a technical construct, which is handy for the machine learning, statistics, business intelligence.

Q-Day

It's no more a question if, and just a question when. Designing a system right now requires consideration of the recent achievements in the domain of post-quantum cryptography

Hashing

Let's consider a simple use case with vehicle registration plates : we get the incoming flux of data from the speed cameras and we wish to understand the patterns leading to the excessive speeding, in this context it's a good idea to replace the license plate number by a surrogate.
The first idea that comes in mind is to apply hashing with SHA-256.
Let's see what could be the caveats.
Input : AA01AA0001
SHA-256 :

:~$ echo -n "AA01AA0001" | sha256sum
557db30f40ad709d5e075eab6b52913faadb40c36f1e29c60cfd40756e9374c7  -

Setup

DISCLAIMER : Only test hashes you are authorized to test. Unauthorized cracking is illegal and unethical.
Let's get kali linux with hashcat

sudo apt update
sudo apt install hashcat
hashcat --version

Testing with 4 digits mask :

:~$ echo -n "AA01AA0001" | sha256sum | cut -d ' ' -f1 > hash.txt
:~$ hashcat -m 1400 -a 3 hash.txt AA01AA?d?d?d?d --status

output :

557db30f40ad709d5e075eab6b52913faadb40c36f1e29c60cfd40756e9374c7:AA01AA0001
Session..........: hashcat
Status...........: Cracked
Hash.Mode........: 1400 (SHA2-256)
Hash.Target......: 557db30f40ad709d5e075eab6b52913faadb40c36f1e29c60cf...9374c7

Testing with full mask will take more time, you may test it yourself to see the estimate on your system.

:~$ hashcat -m 1400 -a 3 hash.txt ?u?u?d?d?u?u?d?d?d?d --status

At this point you can speedup the work by using wordlist or rainbow table, this is out of scope for this guide, the main outcome is that knowing the pattern you may find the original id that has undergone hashing, hence hashing itself is not suitable for data sanitization.

HMAC

The much better alternative which is immune to the procedure listed above and is inherently quantum resistant is HMAC with sufficiently long key, I will be using the standard variation HMAC-SHA256 :

:~$ echo -n 'AA01AA0001' | openssl dgst -sha256 -mac HMAC -macopt hexkey:0000111122223333444455556666777788889999aaaabbbbccccddddeeeeffff
SHA2-256(stdin)= 459d458f308f01b165169bf6ec32d0c8cb2af38be91b26bde73c8121a11451e1

The key above is predictable and hence for demo purposes only, use high-entropy random key for the real case.

Summary

Use of HMAC-SHA256 for data sanitization purposes is a more secure alternative to a simple hashing with SHA256 as the data pattern is usually publicly known.

Bonus

Hashing with SHA256 is not suitable for password imprints either, internet does remember the cases when industry leaders had undergone severe damage due to the storing of simple hashes in the database.
Yes, you may find a good key derivation function, still it's much better solution to use OAuth standard with an authorization server.
Happy Diwali!

Snyk and Sonar : committed credentials security test

hexfloor — Sun, 20 Apr 2025 11:43:45 +0000

Disclaimer : this guide deliberately contains an Easter egg - easy to spot security issue.

Introduction

I've been looking closely to understand the market of the code quality and security providers and got to know the leaders :

Snyk
Sonar

They are both great and are different to certain extent even in terms of pricing model, it's up to you which security provider you prefer for your needs, it's quite certain you need an Enterprise version to protect the business and you definitely need more overall security oriented providers as well, see the Swiss cheese model.
When having been watching some sort of performance around the security subject I have realized that one trivial use-case is quite new for me : committed credentials to the repository, I've witnessed this happening and every time it has been either a design imperfection or a necessity, at the same time the recovery from such an issue is not trivial, that is what I will cover in this guide.

Setup

I will get help from two AI assistants today - let me introduce ChatGPT and Mistral, I hope they will harmonically enhance my both cerebral hemispheres and we get fast to the point and we will have some fun. They have suggested me to use WSL with Kali Linux for this purpose.

Create the demo project:

mkdir demo-project
cd demo-project
git config --global init.defaultBranch main
git config --global user.email "you@example.com"
git config --global user.name "Your Name"
git init
touch index.js
git add -A
git commit -m 'init'

and now I will add a sample of credentials for AWS, Azure and GCP to index.js

cat <<EOF > index.js
// index.js

// Basic Password
const password = 'SecurePass123!';

// AWS Credentials
const awsCredentials = {
  accessKeyId: 'AKIAIOSFODNN7EXAMPLE',
  secretAccessKey: 'wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY'
};

// Azure Credentials
const azureCredentials = {
  subscriptionId: 'dummy_subscription_id',
  clientId: 'dummy_client_id',
  secret: 'dummy_secret',
  tenant: 'dummy_tenant_id'
};

// GCP Credentials
const gcpCredentials = {
  type: 'service_account',
  project_id: 'dummy-project-id',
  private_key_id: 'dummy_private_key_id',
  private_key: '-----BEGIN PRIVATE KEY-----\nDUMMY_PRIVATE_KEY\n-----END PRIVATE KEY-----\n',
  client_email: 'dummy-service-account@dummy-project-id.iam.gserviceaccount.com',
  client_id: 'dummy_client_id',
  auth_uri: 'https://accounts.google.com/o/oauth2/auth',
  token_uri: 'https://oauth2.googleapis.com/token',
  auth_provider_x509_cert_url: 'https://www.googleapis.com/oauth2/v1/certs',
  client_x509_cert_url: 'https://www.googleapis.com/robot/v1/metadata/x509/dummy-service-account%40dummy-project-id.iam.gserviceaccount.com'
};

// Export the credentials and password
module.exports = {
  password,
  awsCredentials,
  azureCredentials,
  gcpCredentials
};
EOF

git add -A
git commit -m 'security-issue'

Now we are set.

Snyk

I need node to be installed, hence I will just follow the instructions and get nvm first.
Then I've got some help from AI :

npm install -g snyk
snyk --version
snyk code test

at this point I need to create a test account :

snyk auth
snyk code test

Let's enable snyk code :

And see what we have :

OK, good start.

Sonar

Let's install docker
and the follow the documentation

$ docker run -d --name sonarqube -e SONAR_ES_BOOTSTRAP_CHECKS_DISABLE=true -p 9000:9000 sonarqube:latest

and install Sonar CLI (ad-hoc solution)

sudo apt install unzip wget -y
wget https://binaries.sonarsource.com/Distribution/sonar-scanner-cli/sonar-scanner-cli-5.0.1.3006-linux.zip
unzip sonar-scanner-cli-*.zip
sudo mkdir /opt/sonar-scanner
sudo mv sonar-scanner-* /opt/sonar-scanner
echo 'export PATH=$PATH:/opt/sonar-scanner/sonar-scanner-5.0.1.3006-linux/bin' >> ~/.bashrc
source ~/.bashrc

Now open sonar :

http://localhost:9000
login + change password
My Account -> Security -> Generate Tokens
generate sonar-scanner-token

create Sonar configuration file with the generated token :

cat <<EOF > sonar-project.properties
sonar.projectKey=demo-project
sonar.projectName=Demo Project
sonar.projectVersion=1.0
sonar.sources=.
sonar.host.url=http://localhost:9000
sonar.login=<token>
EOF

now run the scanner :

sonar-scanner

I will commit the setup:

git add -A
git commit -m 'sonar-setup'

Issue

Alright we have done a mistake, let's try to perform a recovery, I will just revert the commit leading to the security issue, add another line to the index.js

echo "console.log('demo');" >> index.js
git commit -m 'console-log'

Snyk

Sonar
OK we have another issue, I didn't expect that, this was my Easter egg, funny. However we have no initial credentials related issue.

Is the problem solved ?
NO
You can get to the previous state in git and retrieve the credentials :

git checkout HEAD~3 && cat index.js

Flash forward : I will be using git reflog
Let's do the interactive rebase and remove the commit from history :

git reflog
git checkout HEAD@{1}

git rebase -i HEAD~4

Still I can get the credentials :

git reflog
git checkout HEAD@{7} && cat index.js

Recovery

How to really fix the issue ?

Disclaimer : the technical solution provided below has limited applicability and rather brutal
Let's just proceed with a clean minimalistic setup :

mkdir demo-project
cd demo-project
git init
touch index.js
git add -A
git commit -m 'init'

cat <<EOF > index.js
// index.js
const password = 'SecurePass123!';
EOF

git add -A
git commit -m 'security-issue'

And let's fix :

remove index.js from all git history
expire git reflog
prune dangling blobs
force push (!)
verify

sudo apt install git-filter-repo
which git-filter-repo
git filter-repo --path index.js --invert-paths --force
git reflog expire --expire=now --all
git gc --prune=now --aggressive

Destructive

git push --force --all
git push --force --tags

Verify :

git log --all -- index.js
git grep 'SecurePass123!' $(git rev-list --all)
rm -rf .git/filter-repo
rm -rf .git/lost-found

Post-mortem :

reclone the repository
rotate keys
purge system cache (a topic in itself)

Summary

Long story short : once you have leaked the keys it's a bit of a challenge to recover as yes, you will rotate the keys and the immediate issue will be fixed, whereas another issue is to prevent it from happening again.

There have been real cases of leaking the keys through the reflog by the leaders :

GitHub OAuth Token Leak by Slack – 2015
Uber AWS Key Leak – 2016
Facebook Access Token Leak – 2017

Hence yes, you need some sort of code analysis software in Enterprise edition for your business, as it seems that for Sonar the password leaks are part of the Community edition and leaked api keys detection is a part of the Enterprise edition only, for Snyk the Enterprise edition is also more powerful and contains essential features.
And even then you will have to stay vigilant and to go deeper.
I would suggest to switch to cloud native for the dev purposes, as the whole recovery is pretty complicated due to the caching on different levels.

Happy Easter !

More storage for media : organize files using ChatGPT

hexfloor — Thu, 31 Oct 2024 23:11:22 +0000

Summary

Open Source and Big Tech are equally important, and of course if you can afford to have the latest Mac - just do it, if you can afford to subscribe for 1TB plan from your cloud provider - just do it.
Prior to writing this blog I thought that by now there should exist simple GUI programs to convert all the media formats, and the reality is different - you still need linux OR you still need to pay.
And it's okay to have a choice, it's okay to pay as it saves your time, and it's okay to use linux as it saves your money.
Hence I can freely say here, that you start with the recommendations from ChatGPT, however remember at every point that it's better to fix the metadata and the filename before any compression, and to maintain both during the conversion, hence the commands below should be tailored to your needs:

As not everyone is thrilled with technology, not everyone will bother to install any linux distribution, not everyone will choose Gentoo - hence I can freely post my recommendations and just say that you can save 10 times more storage by just compressing the files from the high quality to the normal.
Not every video deserves to get the Oscar and it's okay.

Why Gentoo then? Building Gentoo is like to produce a single-use sterile dental scalpel, you should know what is the purpose and then this distribution will be your best friend. It's too sharp to be used at an average workplace, it's not for your general use system, it's for the specific purpose of getting the specific job done at maximum performance - this is what Gentoo is about.
Maybe the time has come for the wider adoption of Gentoo, as in the world where lots of people have a skill to build their system we are approaching to the wiser use of energy. Gentoo is eco-friendly.

I'm also grateful to the maintainers of ffmpeg and ImageMagick, thanks to these enthusiasts you may be at the edge of the progress with limited resources.

So yes, give it a try, you may impress others by compressing the size of the media library, and you will impress yourself after having built your first Gentoo server - it will take time, at least 1 hour, and at most - who knows, let it take the time it needs, or try again later. Do it yourself.
How faster is Gentoo than Ubuntu on WSL?
Exactly the same video processing task took 30.97s on Gentoo and 37.32s on Ubuntu, hence you have around of 20% gain. Is it worth it?
The answer is: it depends on your particular case.

Bonus

It may happen that image compression from heic to heic with ImageMagick doesn't work for iPhone 15 pro iOS 18, in this case you may install IrfanView or any other tool, convert heic to jpeg without loss of quality and then use ImageMagick to convert jpeg to heic with compression.
As well you should have some good understanding of what is fps and what is bitrate before compressing video.

This should be a good start for ffmpeg to be used for vertical videos:

for file in ./input/*.mov; do ffmpeg -i "$file" -vf "scale=-2:1280" -r 30 -c:v libx265 -crf 28 -preset medium -c:a copy "./output/$(basename "$file" .mov).mp4"; done

Credits

Happy Diwali, may Lakshmi bring you peace and prosperity, or if she is gone elsewhere - do it yourself.

Happy Halloween!🎃

More storage for media : organize files using ChatGPT : part4, processing on Gentoo

hexfloor — Thu, 31 Oct 2024 22:57:56 +0000

Main article

Introduction

Let's get the maximum performance and install ImageMagick and ffmpeg on Gentoo.
I will use WSL for demo, hence some functionalities will be limited.

Setup Gentoo

Let's get the latest stage3 tarball from the Gentoo Downloads
powershell:

Invoke-WebRequest -Uri "https://distfiles.gentoo.org/releases/amd64/autobuilds/20241027T164832Z/stage3-amd64-openrc-20241027T164832Z.tar.xz" -OutFile "$env:USERPROFILE\Downloads\stage3-amd64-openrc-20241027T164832Z.tar.xz"

mkdir "$env:LOCALAPPDATA\WSL\Gentoo" -Force

wsl --import Gentoo "$env:LOCALAPPDATA\WSL\Gentoo" "$env:USERPROFILE\Downloads\stage3-amd64-openrc-20241027T164832Z.tar.xz"

Let's enter Gentoo:

wsl -d Gentoo

Follow the Gentoo in WSL to setup a user! I will skip this part and will continue with root (not secure).

Hi ChatGPT, could you please suggest the compiler flags and USE flags for Gentoo to have the maximum performance, my use case is ffmpeg with HEVC and ImageMagick with HEIC. Short answer, thank you !

Now let's set compiler and USE flags, I have entered as root:

cat /etc/portage/make.conf

A bit of magic:

echo -e "COMMON_FLAGS=\"-O2 -pipe\"\nCFLAGS=\"-march=native \${COMMON_FLAGS}\"\nCXXFLAGS=\"\${CFLAGS}\"\nFCFLAGS=\"\${COMMON_FLAGS}\"\nFFLAGS=\"\${COMMON_FLAGS}\"\nLC_MESSAGES=C.utf8" | tee /etc/portage/make.conf > /dev/null

Setting USE flags:

echo 'USE="ffmpeg hevc imagemagick heic x264 vaapi vdpau opencl threads jpeg png tiff gpl libx264 libx265 libfdk-aac libmp3lame libopus libvpx nonfree"' | tee -a /etc/portage/make.conf > /dev/null

echo "media-gfx/imagemagick heic libheif" | tee /etc/portage/package.use/imagemagick > /dev/null

echo "media-video/ffmpeg gpl libx264 libx265 libfdk-aac libmp3lame libopus libvpx nonfree" | tee /etc/portage/package.use/ffmpeg > /dev/null

Let's sync and update, at this point you will likely embrace the philosophy of Gentoo:

emerge --sync
emerge --update --deep --newuse @world
emerge --depclean
emerge dev-vcs/git

At this point we have a media server, let's pack it.

ImageMagick on Gentoo

With the USE flags set from the setup:

emerge media-libs/libheif media-libs/libpng gnome-base/librsvg
git clone https://github.com/ImageMagick/ImageMagick.git
cd ImageMagick
./configure --with-heic=yes
make
make install
ldconfig

magick -list format | grep HEIC

ffmpeg on Gentoo

With the USE flags set from the setup:

emerge media-libs/x264 media-libs/x265 media-libs/fdk-aac media-sound/lame media-libs/opus media-libs/libvpx

git clone https://git.ffmpeg.org/ffmpeg.git ffmpeg
cd ffmpeg
./configure --enable-gpl --enable-libx264 --enable-libx265 --enable-libfdk-aac --enable-libmp3lame --enable-libopus --enable-libvpx --enable-nonfree
make
make install

ffmpeg -codecs | grep hevc

Final step

It is the time you have wasted for your Gentoo that makes your Gentoo so important.
Let's add new user:

useradd -m -G users -s /bin/bash <username>
passwd <username>

And reboot Gentoo, now you can enter as <username>:

wsl -u <username> -d Gentoo

Summary

If you are afraid to use Gentoo, come back when you are not afraid or use it while being afraid.

More storage for media : organize files using ChatGPT : part 3, converting MOV to HEVC MPEG-4

hexfloor — Thu, 31 Oct 2024 21:41:08 +0000

Main article
Think to do backups and to fix metadata before processing, see in part 1

Introduction

You might wish to know more about MOV, MPEG-4 and HEVC. You may prompt ChatGPT on my choice of the end format, there are pros and cons, get to know about VVC.

Setup

Given my past experience I will build ffmpeg from sources with HEVC support.

sudo apt update
sudo apt install -y build-essential pkg-config git yasm libx264-dev libx265-dev libnuma-dev libfdk-aac-dev libmp3lame-dev libopus-dev libvpx-dev
sudo apt install nasm
git clone https://git.ffmpeg.org/ffmpeg.git ffmpeg
cd ffmpeg

My favorite part:

./configure --enable-gpl --enable-libx264 --enable-libx265 --enable-libfdk-aac --enable-libmp3lame --enable-libopus --enable-libvpx --enable-nonfree
make
sudo make install

Checking:

ffmpeg -codecs | grep hevc

Great!
Clean up:

cd ..
rm -rf ffmpeg

convert MOV to MPEG-4 HEVC using ffmpeg

The setup is a complicated part, all the rest is trivial.

ffmpeg -i input.mov -c:v libx265 -crf 23 -preset medium -c:a aac -b:a 192k output.mp4

Feel free to use the settings that reflect your need.

Bonus

This should be a good start for you:

Hi ChatGPT, I wish to compress some old videos of mine into the mp4 hevc with normal quality, possibly with some loss, and my best wish would be to reduce the file size as much as we can, however preserving the quality, could you please suggest me the resolution and the bitrate in one line, and the ffmpeg command in the second line ? Short answer, thank you !

ffmpeg -i input.mp4 -c:v libx265 -preset medium -crf 28 -vf "scale=1280:720" -b:v 1500k output.mp4

Well, crf should be enough and it's better to encode audio as well, at any point you should remember about the metadata.
Industrial version:

#!/bin/bash

# Hardcoded input directory and output directory
INPUT_DIR="./input"               # Input directory set to ./input
OUTPUT_DIR="./output"             # Output directory for converted MP4s

# Create the output directory if it doesn't exist
mkdir -p "$OUTPUT_DIR"

# Iterate through all MP4 files in the input directory and subdirectories
find "$INPUT_DIR" -type f -iname "*.mp4" | while read -r mp4_file; do
    # Get the base name of the MP4 file without the extension
    base_name=$(basename "$mp4_file" .mp4)

    # Get video dimensions (width and height) using ffprobe
    dimensions=$(ffprobe -v error -select_streams v:0 -show_entries stream=width,height -of csv=s=x:p=0 "$mp4_file")
    width=$(echo "$dimensions" | cut -d 'x' -f 1)
    height=$(echo "$dimensions" | cut -d 'x' -f 2)

    # Debugging: Log the dimensions
    echo "Dimensions of $mp4_file: Width=$width, Height=$height"

    # Initialize the new filename variable
    new_filename="${OUTPUT_DIR}/${base_name}_converted.mp4"

    # Check if rescaling is needed and apply the appropriate scale
    if [[ "$height" -ge "$width" && "$height" -gt 1280 ]]; then
        # If height >= width and height > 1280, rescale to -2:1280
        scale="-2:1280"
        echo "Rescaling $mp4_file to $scale"
    elif [[ "$width" -gt "$height" && "$width" -gt 1280 ]]; then
        # If width > height and width > 1280, rescale to 1280:-2
        scale="1280:-2"
        echo "Rescaling $mp4_file to $scale"
    else
        # No scaling needed
        scale=""
        echo "No rescaling needed for $mp4_file"
    fi

    # Run ffmpeg with or without scaling, based on the conditions
    ffmpeg -i "$mp4_file" \
           -r 30 -c:v libx265 -crf 28 -preset medium \
           -c:a aac -b:a 192k \
           -metadata creation_time="$formatted_date" \
           ${scale:+-vf "scale=$scale"} \
           "$new_filename"

done

echo "Processing complete."

More storage for media : organize files using ChatGPT : part 2, converting JPEG to HEIC

hexfloor — Tue, 29 Oct 2024 20:44:40 +0000

Main article
Think to do backups and to fix metadata before processing, see in part 1

Introduction

The setup is the outcome of part1, I will focus on image files only: JPEG and HEIC and I will convert everything to HEIC. Why? Because I can, if you can't - use JPEG.
TL;DR : use ImageMagick built from sources --with-heic=yes on linux to make it happen after having installed libheif

Failed attempt to use jrottenberg/ffmpeg:4.1-alpine image to convert JPEG to HEIC

I will use docker image of ffpeg on alpine and I'm skipping steps as you can get this information from the internet search or generative AI, I'm using the ChatGPT as no login is required and most of readers will be able to reproduce my steps.

Thank you! I have jrottenberg/ffmpeg:4.1-alpine image, I wish to convert all the files in the jpg directory to the heic format with the resolution not more than 960x1280 and the size of approximately 300Kb and to have them in the directory jpg_heic, I don't wish to create my dockerfile just to run a command from git bash or cmd on windows

docker run --rm -v <path_to_jpg_directory>:/input -v <path_to_output_directory>:/output jrottenberg/ffmpeg:4.1-alpine \
    -i /input/%01d.jpg -vf "scale=w=960:h=1280:force_original_aspect_ratio=decrease" -b:v 300k /output/%01d.heic

Somehow it didn't work out of the box, let me try another approach:

can we start docker once and mount the volumes once, then execute the loop with the same container ? I think it will be more optimal

docker run -it --rm -v /path/to/jpg:/input -v /path/to/jpg_heic:/output --name ffmpeg-container jrottenberg/ffmpeg:4.1-alpine /bin/sh

for img in /input/*.jpg; do
  ffmpeg -i "$img" -vf "scale=iw*min(960/iw\,1280/ih):ih*min(960/iw\,1280/ih),format=heic" -b:v 300k "/output/$(basename "${img%.jpg}.heic")"
done

This didn't work either:

Let's start over:

Unable to find a suitable output format for '/bin/sh'
/bin/sh: Invalid argument

docker run --rm -v /path/to/jpg:/input -v /path/to/jpg_heic:/output jrottenberg/ffmpeg:4.1-alpine /bin/sh -c "for img in /input/*.jpg; do ffmpeg -i \"\$img\" -vf 'scale=iw*min(960/iw\,1280/ih):ih*min(960/iw\,1280/ih),format=heic' -b:v 300k \"/output/\$(basename \"\${img%.jpg}.heic\")\"; done"

Well...still doesn't work, okay let's use the creator's guide and do the work in the interactive way :

docker run -it --entrypoint=/bin/sh -v /path/to/jpg:/input -v /path/to/jpg_heic:/output jrottenberg/ffmpeg:4.1-alpine

and then run the command inside the container :

for img in /input/*.jpg; do ffmpeg -i "$img" -vf "scale=iw*min(960/iw\,1280/ih):ih*min(960/iw\,1280/ih),format=heic" -b:v 300k "/output/$(basename "$img" .jpg).heic"; done

Still failure, let's make it more simple :

for img in /input/*.jpg; do ffmpeg -i "$img" -vf "scale=iw*min(960/iw\,1280/ih):ih*min(960/iw\,1280/ih)" -c:v hevc -crf 28 "/output/$(basename "$img" .jpg).heic"; done

Another failure, let's check if the format is supported :

ffmpeg -formats | grep heic

No it's not. Okay let's move on and use the latest image.

Failed attempt to use jrottenberg/ffmpeg:latest image to convert JPEG to HEIC

Let's go :

docker pull jrottenberg/ffmpeg

docker run -it --entrypoint=/bin/sh -v /path/to/jpg:/input -v /path/to/jpg_heic:/output jrottenberg/ffmpeg:latest

ffmpeg -formats | grep heic

Still no support for HEIC.
Okay whatever, I will go to the official page now, something I should have considered at the beginning.

Failed attempt to use ffmpeg builds from Gyan Dev to convert JPEG to HEIC

I've got some help from ChatGPT, check the SHA256, installed the release build for ffmpeg, feel free to do the same on your risk.
Checking the version and the formats:

ffmpeg -version

ffmpeg -formats

Still no HEIC.
Let's get the latest build from master.
I have installed: ffmpeg-2024-10-24-git-153a6dc8fa-full_build.7z after having checked SHA256.
Still no HEIC.
Let's check the github of ffmpeg
and it seems that HEIC is not listed in the ffmpeg image formats
HEIC is not supported...read the manual before diving into the implementation.

Failed attempt to use ImageMagic on Win11 to convert JPEG to HEIC

Let's give a try with ImageMagic
checking if the format is supported :

magick -list format | find "HEIC"

All good, the command looks pretty simple:

magick mogrify -path /path/to/output_directory -resize 960x1280 -format heic /path/to/input_directory/*.jpg

No luck, trying CMD version:

for %i in ("C:\path\to\input_directory\*.jpg") do magick convert "%i" -resize 960x1280 "C:\path\to\output_directory\%~ni.heic"

And the result is the same: no encode delegate for this image format HEIC
It seems I need to the have the libheif.
Looks prominent, I will try to run it on WSL later on.

Failed attempt to use IrfanView on Win11 to convert JPEG to HEIC

Got it from IrfanView with all the plugins, conversion from HEIC to JPEG works, vice-versa - NO.

Failed attempt to use Win11 Photos to convert JPEG to HEIC

Conversion from HEIC to JPEG works, vice-versa - NO.

WSL

Let's get WSL
Then :

sudo apt update
sudo apt install libheif-dev
sudo apt install build-essential
sudo apt install imagemagick

same error when using convert temp.jpg temp.heic

convert-im6.q16: no encode delegate for this image format `HEIC' @ warning/constitute.c/WriteImage/1305.

Let's start over and install ImageMagick from source, at the time of writing the last version is ImageMagick-7.1.1-39, you may need to change the link to tar.gz
Cleanup:

sudo apt remove --purge imagemagick

Install from sources:

sudo apt update
sudo apt install build-essential libheif-dev libmagickwand-dev
sudo apt install wget
sudo apt install libjpeg-dev libpng-dev libtiff-dev
wget https://download.imagemagick.org/ImageMagick/download/ImageMagick.tar.gz
tar -xvf ImageMagick.tar.gz
cd ImageMagick-7.1.1-39/

and now the moment of the glory (maybe)

./configure --with-heic=yes
make
sudo make install

I have seen the line I've been waiting for :

  DELEGATES         = bzlib djvu fontconfig freetype heic jbig jng jp2 jpeg lcms lqr lzma openexr png ps tiff webp x xml zlib zstd

checking with magick -version
New error:

magick: error while loading shared libraries: libMagickCore-7.Q16HDRI.so.10: cannot open shared object file: No such file or directory

let's do our best:

sudo ldconfig
ldconfig -p | grep MagickCore

Checking again magick -version:

Version: ImageMagick 7.1.1-39 Q16-HDRI x86_64 22428 https://imagemagick.org
Copyright: (C) 1999 ImageMagick Studio LLC
License: https://imagemagick.org/script/license.php
Features: Cipher DPC HDRI OpenMP(4.5)
Delegates (built-in): bzlib djvu fontconfig freetype heic jbig jng jp2 jpeg lcms lqr lzma openexr png tiff webp x xml zlib zstd
Compiler: gcc (13.2)

The tricky part is ./configure --with-heic=yes and sudo ldconfig.
Those who know…….know..
Testing:

magick input.jpg output.heic

I've got a log message, however conversion worked:

magick: no encode delegate for this image format `HEIC' @ warning/constitute.c/WriteImage/1403.

All the rest is trivial.

Summary

It might have worked with a simple ImageMagick installation on WSL, the original goal of the post was to show how easy it is to move on with ChatGPT and in fact still you need to know what you are doing as sometimes you get suggestions which are not supported. Still it's helpful to move on.
Why has it been so difficult to make it work on Win? Most probably due to the fact that patents for HEVC will have expired somewhere near 2030.
Hopefully we have Linux, OpenSource, ImageMagick, ChatGPT and some brains.
Apply the guides to your needs, if you are in doubt - use JPEG. Check about HEVC and VVC before taking your own decision.

Bonus

This should be a good start for you:

Hi ChatGPT, I wish to compress some old photos of mine into the heic with normal quality, possibly with some loss, and my best wish would be to reduce the file size as much as we can, however preserving the quality, could you please suggest me the resolution and the file size in one line, and the ImageMagick command in the second line ? Short answer, thank you !

magick input.jpg -resize 1920x1080 -quality 80 output.heic

Industrial version:

#!/bin/bash

# Input and Output directories
input_dir="./input"
output_dir="./output"

# Ensure the output directory exists
mkdir -p "$output_dir"

# Loop through all jpg files in the input directory
for img in "$input_dir"/*.jpg; do
    # Get the image dimensions (width x height)
    dimensions=$(identify -format "%wx%h" "$img")
    width=$(echo $dimensions | cut -d'x' -f1)
    height=$(echo $dimensions | cut -d'x' -f2)

    # Check for vertical images (height >= width)
    if [ "$height" -ge "$width" ]; then
        # Vertical and height > 1280, resize to height 1280
        if [ "$height" -gt 1280 ]; then
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" -resize x1280 -quality 80 "$output_file"
            echo "Resized and converted $img to $output_file"
        else
            # Vertical image with height <= 1280, just convert to HEIC
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" "$output_file"
            echo "Converted $img to $output_file"
        fi
    elif [ "$width" -gt "$height" ]; then
        # Landscape and width > 1280, resize to width 1280
        if [ "$width" -gt 1280 ]; then
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" -resize 1280x -quality 80 "$output_file"
            echo "Resized and converted $img to $output_file"
        else
            # Landscape image with width <= 1280, just convert to HEIC
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" "$output_file"
            echo "Converted $img to $output_file"
        fi
    else
        echo "Skipping $img: does not fit any criteria"
    fi
done

More storage for media : organize files using ChatGPT : part 1, data preparation

hexfloor — Tue, 29 Oct 2024 20:40:46 +0000

Main article

Age of AI

Given the rise of ChatGPT and other Gen AI tools I have to reinvent my posts to make them relevant, some introduction here.
Hence since now my posts will be less technical and more functional. From my conversation with ChatGPT it (from now on I will use pronouns it) granted me the permission to use our conversation in this blog and to reference ChatGPT as it.

Introduction

Let's consider a common problem : we wish to optimize storage of media files.
Now, let's imagine we have made a dump of data with photos and videos by year in different formats and a magic tools that may convert them to the format of our choice.

However, we need to do some preparation work : group the files of each type instead of having the grouping by year. How to achieve this using ChatGPT ?

First steps

Hers is our dump of media :

Let's get some input from ChatGPT :

windows using git bash and linux commands, I wish to rename the folders in the current dir having name 'Photos from ' simply to year

for dir in Photos\ from\ *; do
    if [[ -d "$dir" ]]; then
        year="${dir##* }"  # Extract the year from the folder name
        mv "$dir" "$year"  # Rename the folder
    fi
done

Result :

Great, let's move on!

Metadata : data preparation

The very first thing you should consider is to fix the metadata, which in the narrow sense mean that each file should contain the Date taken for the image files and Media created for the video files. In the best case the image name should contain:

formatted timestamp in the form date_time, like 20240101_000000 or even longer
origin discriminator, anything that would help you to classify the file, when compressing could the the original extension
some counter with as many digits as necessary to avoid collisions during any compression job Some of the modern phones do create a speaking file id, some do not, in any case for the old files it may happen that the data is present only in the adjacent json file, the structure name of it usually corresponds to the following : <filename_with_extension>{<.suffix_of_type_metadata>}.json. Hence before any processing it is wise to go through all the images and all the videos and update the time attributes, for example with exiftool for images and ffmpeg for videos and as well it's a good idea to copy the processed file to the new directory with an extension that has as a prefix the formatted_date, and those who do not - move to another folder to fix them up manually.

Overall the idea could be the following:

list file extensions in all the folders to evaluate the necessary work

ls -R ./ | awk -F. '/\./ {print $NF}' | sort -u

prepare the next which will be to convert all the files to the lower case to simplify further operation, prior to this we should check for the case insensitive duplicates

#!/bin/bash

# Find all subdirectories and process files in each subdirectory separately
find . -type d | while read -r dir; do
    # Find files in the current directory
    find "$dir" -maxdepth 1 -type f | \
        # Remove the path, leaving only the filename
        sed 's/.*\///' | \
        # Convert filenames to lowercase for case-insensitive comparison
        tr '[:upper:]' '[:lower:]' | \
        # Sort the filenames
        sort | \
        # Find duplicates in the sorted list
        uniq -d | \
        # Print the duplicates with their directory path
        while read -r filename; do
            echo "Duplicates in '$dir':"
            find "$dir" -maxdepth 1 -type f -iname "$filename"
        done
done

convert all the files to the lower case:

#!/bin/bash

# Find all files recursively
find ./ -type f | while read -r file; do
    dir=$(dirname "$file")
    base=$(basename "$file")

    # Convert the filename to lowercase
    lower_base=$(echo "$base" | tr '[:upper:]' '[:lower:]')

    # If the filename is different (case insensitive), do the two-step rename
    if [[ "$base" != "$lower_base" ]]; then
        # Step 1: Rename to an intermediate name (tmp_<lowercase filename>)
        mv "$file" "$dir/tmp_$lower_base"

        # Step 2: Rename to the final lowercase name (removing tmp_ prefix)
        mv "$dir/tmp_$lower_base" "$dir/$lower_base"
    fi
done

At this point you are ready to start fixing the metadata

Metadata : updating images using data from jsons

At this point we are ready to start fixing the metadata, the bare minimum is to ensure that the Date taken is up to date, and it's quite often that this date is not set in the image itself but stored in the json separately, here is the script you may use to sort jpg images into two folder: one containing images with accurate metadata and another containing the images that need to be viewed further and therefore prefixed with the origin folder.
I will use the exiftool for this purpose and add a formatted date prefix for each file that has a relevant metadata stored in json.

#!/bin/bash

# Hardcoded input directory and output directories
INPUT_DIR="./input"               # Input directory set to ./input
OUTPUT_DIR_JPG="./jpg"           # Directory for modified JPGs
OUTPUT_DIR_JPG_TO_FIX="./jpg_to_fix"  # Directory for JPGs needing fixing

# Create output directories if they don't exist
mkdir -p "$OUTPUT_DIR_JPG"
mkdir -p "$OUTPUT_DIR_JPG_TO_FIX"

# Iterate through all JPG files in the input directory and subdirectories
find "$INPUT_DIR" -type f -iname "*.jpg" | while read -r jpg_file; do
    # Get the base name of the JPG file without the extension
    base_name=$(basename "$jpg_file" .jpg)

    # Look for the corresponding JSON file that starts with base_name and ends with .json
    json_file=$(find "$(dirname "$jpg_file")" -type f -iname "${base_name}.jpg*.json" | head -n 1)

    # Check if the corresponding JSON file exists
    if [[ -f "$json_file" ]]; then
        # Extract the creation time and description from the JSON file
        creation_time=$(jq -r '.photoTakenTime.timestamp' "$json_file")
        description=$(jq -r '.description' "$json_file")

        # Format the creation time for ExifTool (assuming it's in Unix timestamp)
        if [[ "$creation_time" =~ ^-?[0-9]+$ ]]; then
            formatted_date=$(date -d @"$creation_time" +"%Y%m%d_%H%M%S" 2>/dev/null)
            if [[ $? -ne 0 ]]; then
                formatted_date=""
            fi
        else
            formatted_date=""
        fi

        # **Always update** EXIF data based on JSON content (even if they already exist in JPG)
        if [[ -n "$formatted_date" ]]; then
            exiftool -overwrite_original -DateTimeOriginal="$formatted_date" "$jpg_file" >/dev/null 2>&1
        fi

        if [[ -n "$description" ]]; then
            exiftool -overwrite_original -Description="$description" "$jpg_file" >/dev/null 2>&1
        fi

    else
        # If JSON file is not found, notify but continue to check Date taken
        echo "JSON file not found for: $jpg_file"
    fi

    # Now check if Date taken is set in the JPG file
    date_taken=$(exiftool -DateTimeOriginal -s -s -s "$jpg_file")

    # Construct the new filename based on the Date taken
    if [[ -n "$date_taken" ]]; then
        # Format the date_taken string to be filename-safe
        formatted_date=$(echo "$date_taken" | sed -e 's/://g' -e 's/ /_/g') # Remove colons and spaces
        safe_date_taken="${formatted_date:0:8}_${formatted_date:9:6}"  # Separate date and time
        new_filename="${OUTPUT_DIR_JPG}/${safe_date_taken}_$(basename "$jpg_file")" # New filename based on formatted date
        cp "$jpg_file" "$new_filename"
    else
        # Get the last directory name if Date taken is not set
        last_dir=$(basename "$(dirname "$jpg_file")")
        new_filename="${OUTPUT_DIR_JPG_TO_FIX}/${last_dir}_$(basename "$jpg_file")"
        cp "$jpg_file" "$new_filename"
    fi
done

echo "Processing complete."

Feel free to adjust the logic for different file types using ChatGPT or another Generative AI of your choice.

Metadata : updating videos using data from jsons

The same trick might be used for video files using ffmpeg, however this time it's a bit more tricky as with ffmpeg you must create a new file copy:

#!/bin/bash

# Hardcoded input directory and output directories
INPUT_DIR="./input"               # Input directory set to ./input
OUTPUT_DIR_MP4="./mp4"           # Directory for modified MP4s
OUTPUT_DIR_MP4_TO_FIX="./mp4_to_fix"  # Directory for MP4s needing fixing

# Create output directories if they don't exist
mkdir -p "$OUTPUT_DIR_MP4"
mkdir -p "$OUTPUT_DIR_MP4_TO_FIX"

# Iterate through all MP4 files in the input directory and subdirectories
find "$INPUT_DIR" -type f -iname "*.mp4" | while read -r mp4_file; do
    # Get the base name of the MP4 file without the extension
    base_name=$(basename "$mp4_file" .mp4)

    # Look for the corresponding JSON file that starts with base_name and ends with .json
    json_file=$(find "$(dirname "$mp4_file")" -type f -iname "${base_name}.mp4*.json" | head -n 1)

    # Initialize the new filename variable
    new_filename=""

    # Check if the corresponding JSON file exists
    if [[ -f "$json_file" ]]; then
        echo "JSON file IS found for: $mp4_file"

        # Extract the creation time from the JSON file
        creation_time=$(jq -r '.photoTakenTime.timestamp' "$json_file")

        # Format the creation time for ffmpeg (assuming it's in Unix timestamp)
        if [[ "$creation_time" =~ ^-?[0-9]+$ ]]; then
            # Convert Unix timestamp to "YYYY-MM-DD HH:MM:SS" format
            formatted_date=$(date -d @"$creation_time" +"%Y-%m-%d %H:%M:%S" 2>/dev/null)
            if [[ $? -ne 0 ]]; then
                formatted_date=""
            fi
        else
            formatted_date=""
        fi

        # **Always update** MP4 metadata based on JSON content (creation_time)
        if [[ -n "$formatted_date" ]]; then
            # The filename date format: YYYY-MM-DD_HH:MM:SS
            filename_date=$(echo "$formatted_date" | tr -d ' ' | tr -d '-')  # Strip spaces and dashes
            filename_date="${filename_date//:/}"  # Replace colons with empty string for filename safety

            # Construct the new filename based on the formatted date
            new_filename="${OUTPUT_DIR_MP4}/${filename_date}_$(basename "$mp4_file")"  # New filename based on formatted date

            # Debugging: Log the new filename
            echo "New filename (with date): $new_filename"

            # Update the creation time in the MP4 metadata and copy it to the new location in one step
            ffmpeg -i "$mp4_file" -c copy -metadata creation_time="$formatted_date" "$new_filename"
        fi
    fi

    # If we did not successfully update the file, copy it to the 'mp4_to_fix' directory
    if [[ -z "$new_filename" ]]; then
        echo "JSON file not found or timestamp invalid. Moving to mp4_to_fix: $mp4_file"

        # Get the last directory name (in case the file is being moved to mp4_to_fix)
        last_dir=$(basename "$(dirname "$mp4_file")")
        new_filename="${OUTPUT_DIR_MP4_TO_FIX}/${last_dir}_$(basename "$mp4_file")"

        # Debugging: Log the action taken (moving to mp4_to_fix)
        echo "Moving to $OUTPUT_DIR_MP4_TO_FIX: $new_filename"

        # Copy the file to the mp4_to_fix directory
        cp "$mp4_file" "$new_filename"
    fi
done

echo "Processing complete."

At this point we are done with the metadata and we can go further.

ID : using proper id

It may happen that your files are coming from different sources and have completely different id's, now as your files are prefixed with a date in format 20000101_000000 you may erase the previous filename and use an identifier of your choice and an ad-hoc counter, here is an example of transformation of all id's to the format 20000101_000000_jpg_0001.jpg :

#!/bin/bash

# Set the input and output directories
input_dir="./input"
output_dir="./output"

# Make sure the output directory exists, create it if it doesn't
mkdir -p "$output_dir"

# Counter variable, starting at 1
counter=1

# Loop through sorted .jpg files, handling files with spaces correctly
find "$input_dir" -type f -name "*.jpg" | sort | while IFS= read -r file; do
    # Extract the first part of the filename (before the first three underscores)
    base_name=$(basename "$file")
    prefix=$(echo "$base_name" | cut -d'_' -f1-2)  # Extract "20000101_000000" part

    # Build the new filename with a 4-digit counter and "_jpg_" prefix
    new_filename=$(printf "%s_jpg_%04d.jpg" "$prefix" "$counter")

    # Copy the file to the output directory with the new filename
    cp "$file" "$output_dir/$new_filename"

    # Increment the counter
    ((counter++))
done

echo "Files renamed and copied to $output_dir"

Compress images

Using ImageMagick from part 2 you may convert all the images to the format of your choice:

#!/bin/bash

# Input and Output directories
input_dir="./input"
output_dir="./output"

# Ensure the output directory exists
mkdir -p "$output_dir"

# Loop through all jpg files in the input directory
for img in "$input_dir"/*.jpg; do
    # Get the image dimensions (width x height)
    dimensions=$(identify -format "%wx%h" "$img")
    width=$(echo $dimensions | cut -d'x' -f1)
    height=$(echo $dimensions | cut -d'x' -f2)

    # Check for vertical images (height >= width)
    if [ "$height" -ge "$width" ]; then
        # Vertical and height > 1280, resize to height 1280
        if [ "$height" -gt 1280 ]; then
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" -resize x1280 -quality 80 "$output_file"
            echo "Resized and converted $img to $output_file"
        else
            # Vertical image with height <= 1280, just convert to HEIC
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" "$output_file"
            echo "Converted $img to $output_file"
        fi
    elif [ "$width" -gt "$height" ]; then
        # Landscape and width > 1280, resize to width 1280
        if [ "$width" -gt 1280 ]; then
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" -resize 1280x -quality 80 "$output_file"
            echo "Resized and converted $img to $output_file"
        else
            # Landscape image with width <= 1280, just convert to HEIC
            output_file="$output_dir/$(basename "$img" .jpg).heic"
            magick "$img" "$output_file"
            echo "Converted $img to $output_file"
        fi
    else
        echo "Skipping $img: does not fit any criteria"
    fi
done

Compress videos

Similar logic can be applied to the videos using ffmpeg from part 3:

#!/bin/bash

# Hardcoded input directory and output directory
INPUT_DIR="./input"               # Input directory set to ./input
OUTPUT_DIR="./output"             # Output directory for converted MP4s

# Create the output directory if it doesn't exist
mkdir -p "$OUTPUT_DIR"

# Iterate through all MP4 files in the input directory and subdirectories
find "$INPUT_DIR" -type f -iname "*.mp4" | while read -r mp4_file; do
    # Get the base name of the MP4 file without the extension
    base_name=$(basename "$mp4_file" .mp4)

    # Get video dimensions (width and height) using ffprobe
    dimensions=$(ffprobe -v error -select_streams v:0 -show_entries stream=width,height -of csv=s=x:p=0 "$mp4_file")
    width=$(echo "$dimensions" | cut -d 'x' -f 1)
    height=$(echo "$dimensions" | cut -d 'x' -f 2)

    # Debugging: Log the dimensions
    echo "Dimensions of $mp4_file: Width=$width, Height=$height"

    # Initialize the new filename variable
    new_filename="${OUTPUT_DIR}/${base_name}_converted.mp4"

    # Check if rescaling is needed and apply the appropriate scale
    if [[ "$height" -ge "$width" && "$height" -gt 1280 ]]; then
        # If height >= width and height > 1280, rescale to -2:1280
        scale="-2:1280"
        echo "Rescaling $mp4_file to $scale"
    elif [[ "$width" -gt "$height" && "$width" -gt 1280 ]]; then
        # If width > height and width > 1280, rescale to 1280:-2
        scale="1280:-2"
        echo "Rescaling $mp4_file to $scale"
    else
        # No scaling needed
        scale=""
        echo "No rescaling needed for $mp4_file"
    fi

    # Run ffmpeg with or without scaling, based on the conditions
    ffmpeg -i "$mp4_file" \
           -r 30 -c:v libx265 -crf 28 -preset medium \
           -c:a aac -b:a 192k \
           -metadata creation_time="$formatted_date" \
           ${scale:+-vf "scale=$scale"} \
           "$new_filename"

done

echo "Processing complete."

Summary

Overall, if some formats have a limited support for metadata, for example png, it could be a good idea to encode it into the filename in order to make it available for an eventual conversion tool.

Security : CVE-2024-3094 unauthorized remote SSH access

hexfloor — Mon, 08 Apr 2024 17:44:10 +0000

Introduction

This article will be less technical as usual, as all you need to know about CVE-2024-3094 is described on jFrog and Y Combinator.

This backdoor is massive. Just to give you an idea, there are maybe 2-3 entities in the world capable of performing this activity, if it was a single person then maybe less than a thousand people in the world would have had a skill to perform the activity, even less than a hundred would dare to try and maybe less than ten would reach this point. Purely technically speaking the person behind is as professional as Usain Bolt or Taylor Swift in the respective domain. How to deal with a situation when you have an adversary that strong and there is a non zero probability that a villain has a different point of view on your personal well-being ?

Security

The principles are somewhat evident, however sometimes you may be tempted to trade a short-term benefit over an somewhat illusionary guidelines :

you'll never walk alone : use stable releases of mainstream standard solutions, then you will have thousand eyes watching your back and maybe one of them will be Andres Freund, from the point of view of probability theory, if a chance to discover a backdoor by an engineer is 0.001 and there are 1000 engineers looking at the code then the probability to discover a backdoor is 1 - (1-0.001)^1000 = 1 - 0.368 = 0.632 = 63.2%
"If you fail to plan, you are planning to fail!" (c) : have a plan of which software you intend to use and why, a recurrent problem is the questionable application of fake it till you make it, you should have a plan and this plan should take into account the boundary conditions since day 0
limit the number of software you are using to remediate the paradox of choice and to minimize the maintenance overhead
apply the best cybersecurity practices : the keyword is apply
have a professional available that knows how the things should be done : still valid in 2024, you have plenty of software providing all the possible insights with hundreds or thousand of alerts / warnings / updates, if you wish to have the business running there must be an expert onsite. "Those who know.......know."
have a plan B when the plan A has failed : someday, and that day may never come, you did everything right and still failed, the best thing you can do is to be ready for this day and to plan the recovery

Summary

Every happy ending is vanished rather fast from memory.
We have just witnessed an interception of a brutal attack.

SQL Pro Tips : industrial AWS Athena SQL using WITH

hexfloor — Thu, 28 Mar 2024 19:33:03 +0000

Introduction

A common industrial flow of data analysis consists of :

data preparation
data aggregation
data join
data output

In this guide I will show how to implement efficiently data preparation, aggregation and output.
Feel free to check on industrial Oracle SQL using WITH and AWS Athena SQL UNPIVOT : CROSS JOIN UNNEST, SQL WITH clause.

Problem definition

Let's define the following problem : as the input we have sale revenue per day, and we wish to have a json as the output with the sale revenue percentage per day of week and the sale revenue percentage per day of week within the group : weekdays and weekends. The problem definition is the same as for the Oracle SQL guide, therefore I will add an additional constraint : the output and the way of aggregation must be the same as for the Oracle SQL guide to make an easy cross check after each stage.

Parameters

In order to write professional SQL we should better define all the parameters within a separate block:

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
)
select * from parameters;

This is a simple example how to use with. Let's extend the success and go further.

Test data

Now let's generate some test data : date and sale revenue for the date :

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add('day', (t.v - 1), from_iso8601_date('2024-01-01')) as sale_date,
    sqrt(t.v * 31) as sale_revenue
  from unnest(sequence(1, 7)) t(v)
)
select * from data_in;

Great! Now let's prepare our data for aggregation.

Data preparation

At this stage we need to be sure that we have all the necessary information for the data aggregation and that all the data types are the ones that we need. Feel free to check the guide on decimal data types which is applicable to our use case as well.

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add('day', (t.v - 1), from_iso8601_date('2024-01-01')) as sale_date,
    sqrt(t.v * 31) as sale_revenue
  from unnest(sequence(1, 7)) t(v)
),
data_prep as (
  select 
    data_in_prep.sale_date,
    data_in_prep.sale_revenue,
    data_in_prep.sale_dow,
    (case when (data_in_prep.sale_dow = parameters.weekend_v0 or data_in_prep.sale_dow = parameters.weekend_v1) 
            then weekend_label else workweek_label end) as sale_type
  from (
        select
            sale_date,
            cast(sale_revenue as decimal(31,2)) as sale_revenue,
            trim( upper(date_format(sale_date, '%W'))) as sale_dow
        from data_in
  ) data_in_prep cross join parameters
)
select * from data_prep;

Good! Now we are set to start data aggregation.

Data aggregation

With properly prepared data the data aggregation is straight forward :

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add('day', (t.v - 1), from_iso8601_date('2024-01-01')) as sale_date,
    sqrt(t.v * 31) as sale_revenue
  from unnest(sequence(1, 7)) t(v)
),
data_prep as (
  select 
    data_in_prep.sale_date,
    data_in_prep.sale_revenue,
    data_in_prep.sale_dow,
    (case when (data_in_prep.sale_dow = parameters.weekend_v0 or data_in_prep.sale_dow = parameters.weekend_v1) 
            then weekend_label else workweek_label end) as sale_type
  from (
        select
            sale_date,
            cast(sale_revenue as decimal(31,2)) as sale_revenue,
            trim( upper(date_format(sale_date, '%W'))) as sale_dow
        from data_in
  ) data_in_prep cross join parameters
),
data_agg as (
    select
        sale_dow,
        sale_revenue,
        100 * sale_revenue / (sum(sale_revenue) over ())  as sale_revenue_perc,
        sale_type,
        100 * sale_revenue / (sum(sale_revenue) over (partition by sale_type)) as sale_type_revenue_perc
    from data_prep
    order by sale_revenue asc

)
select * from data_agg;

Now we are done with the aggregation and as we have a single data source we will skip join and we may start with data output. Depending on the consumer we may have multiple data outputs by caching the results of data aggregation with a view creation.

Data output

AWS Athena has a native support for json output :

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add('day', (t.v - 1), from_iso8601_date('2024-01-01')) as sale_date,
    sqrt(t.v * 31) as sale_revenue
  from unnest(sequence(1, 7)) t(v)
),
data_prep as (
  select 
    data_in_prep.sale_date,
    data_in_prep.sale_revenue,
    data_in_prep.sale_dow,
    (case when (data_in_prep.sale_dow = parameters.weekend_v0 or data_in_prep.sale_dow = parameters.weekend_v1) 
            then weekend_label else workweek_label end) as sale_type
  from (
        select
            sale_date,
            cast(sale_revenue as decimal(31,2)) as sale_revenue,
            trim( upper(date_format(sale_date, '%W'))) as sale_dow
        from data_in
  ) data_in_prep cross join parameters
),
data_agg as (
    select
        sale_dow,
        sale_revenue,
        100 * sale_revenue / (sum(sale_revenue) over ())  as sale_revenue_perc,
        sale_type,
        100 * sale_revenue / (sum(sale_revenue) over (partition by sale_type)) as sale_type_revenue_perc
    from data_prep
    order by sale_revenue asc

),
data_output as (
    select 
        upper(json_object('sale_data' value '['||array_join(array_agg(sale_data_dow), ',') ||']' format json)) as json_output
    from (
        select
            json_object(
                'sale_dow' value sale_dow,
                'sale_revenue' value sale_revenue,
                'sale_revenue_perc' value sale_revenue_perc,
                'sale_type' value sale_type,
                'sale_type_revenue_perc' value sale_type_revenue_perc
            ) as sale_data_dow
        from data_agg
    )

)
select * from data_output;

and here is the result:

{"SALE_DATA":[{"SALE_TYPE_REVENUE_PERC":11.93,"SALE_REVENUE_PERC":7.42,"SALE_TYPE":"WORKWEEK","SALE_REVENUE":5.57,"SALE_DOW":"MONDAY"},{"SALE_TYPE_REVENUE_PERC":16.86,"SALE_REVENUE_PERC":10.49,"SALE_TYPE":"WORKWEEK","SALE_REVENUE":7.87,"SALE_DOW":"TUESDAY"},{"SALE_TYPE_REVENUE_PERC":20.66,"SALE_REVENUE_PERC":12.85,"SALE_TYPE":"WORKWEEK","SALE_REVENUE":9.64,"SALE_DOW":"WEDNESDAY"},{"SALE_TYPE_REVENUE_PERC":23.87,"SALE_REVENUE_PERC":14.85,"SALE_TYPE":"WORKWEEK","SALE_REVENUE":11.14,"SALE_DOW":"THURSDAY"},{"SALE_TYPE_REVENUE_PERC":26.68,"SALE_REVENUE_PERC":16.59,"SALE_TYPE":"WORKWEEK","SALE_REVENUE":12.45,"SALE_DOW":"FRIDAY"},{"SALE_TYPE_REVENUE_PERC":48.08,"SALE_REVENUE_PERC":18.18,"SALE_TYPE":"WEEKEND","SALE_REVENUE":13.64,"SALE_DOW":"SATURDAY"},{"SALE_TYPE_REVENUE_PERC":51.92,"SALE_REVENUE_PERC":19.63,"SALE_TYPE":"WEEKEND","SALE_REVENUE":14.73,"SALE_DOW":"SUNDAY"}]}

With Athena there are more efficient ways to use parameters and to aggregate the data. By the way, if you know how to use json_array in this context, feel free to write in the comments. There are more efficient ways to output the data using unload. Stay tuned!

SQL Pro Tips : industrial GCP BigQuery SQL using WITH

hexfloor — Thu, 28 Mar 2024 19:32:47 +0000

Introduction

A common industrial flow of data analysis consists of :

data preparation
data aggregation
data join
data output

In this guide I will show how to implement efficiently data preparation, aggregation and output.
Feel free to check on industrial Oracle SQL using WITH and GCP BigQuery SQL CROSS JOIN with UNPIVOT UNNEST, SQL WITH clause.

Problem definition

Parameters

In order to write professional SQL we should better define all the parameters within a separate block:

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
)
select * from parameters;

This is a simple example how to use with. Let's extend the success and go further.

Test data

Now let's generate some test data : date and sale revenue for the date :

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add(date('2024-01-01'), interval (v - 1) DAY) as sale_date,
    sqrt(v * 31) as sale_revenue
  from unnest(generate_array(1, 7)) v
)
select * from data_in;

Great! Now let's prepare our data for aggregation.

Data preparation

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add(date('2024-01-01'), interval (v - 1) DAY) as sale_date,
    sqrt(v * 31) as sale_revenue
  from unnest(generate_array(1, 7)) v
),
data_prep as (
  select 
    data_in_prep.sale_date,
    data_in_prep.sale_revenue,
    data_in_prep.sale_dow,
    (case when (data_in_prep.sale_dow = parameters.weekend_v0 or data_in_prep.sale_dow = parameters.weekend_v1) 
            then weekend_label else workweek_label end) as sale_type
  from (
        select
            sale_date,
            round(cast(sale_revenue as numeric),2) as sale_revenue,
            trim(upper(format_date('%A',sale_date))) as sale_dow
        from data_in
  ) data_in_prep cross join parameters
)
select * from data_prep;

Good! Now we are set to start data aggregation.

Data aggregation

With properly prepared data the data aggregation is straight forward :

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add(date('2024-01-01'), interval (v - 1) DAY) as sale_date,
    sqrt(v * 31) as sale_revenue
  from unnest(generate_array(1, 7)) v
),
data_prep as (
  select 
    data_in_prep.sale_date,
    data_in_prep.sale_revenue,
    data_in_prep.sale_dow,
    (case when (data_in_prep.sale_dow = parameters.weekend_v0 or data_in_prep.sale_dow = parameters.weekend_v1) 
            then weekend_label else workweek_label end) as sale_type
  from (
        select
            sale_date,
            round(cast(sale_revenue as numeric),2) as sale_revenue,
            trim(upper(format_date('%A',sale_date))) as sale_dow
        from data_in
  ) data_in_prep cross join parameters
),
data_agg as (
    select
        sale_dow,
        sale_revenue,
        round(cast(100 * sale_revenue / (sum(sale_revenue) over ()) 
            as numeric), 2) as sale_revenue_perc,
        sale_type,
        round(cast(100 * sale_revenue / (sum(sale_revenue) over (partition by sale_type)) 
            as numeric), 2) as sale_type_revenue_perc
    from data_prep
    order by sale_revenue asc

)
select * from data_agg;

We may also check the chart:

and the json :

Data output

BigQuery has a native support for json output :

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add(date('2024-01-01'), interval (v - 1) DAY) as sale_date,
    sqrt(v * 31) as sale_revenue
  from unnest(generate_array(1, 7)) v
),
data_prep as (
  select 
    data_in_prep.sale_date,
    data_in_prep.sale_revenue,
    data_in_prep.sale_dow,
    (case when (data_in_prep.sale_dow = parameters.weekend_v0 or data_in_prep.sale_dow = parameters.weekend_v1) 
            then weekend_label else workweek_label end) as sale_type
  from (
        select
            sale_date,
            round(cast(sale_revenue as numeric),2) as sale_revenue,
            trim(upper(format_date('%A',sale_date))) as sale_dow
        from data_in
  ) data_in_prep cross join parameters
),
data_agg as (
    select
        sale_dow,
        sale_revenue,
        round(cast(100 * sale_revenue / (sum(sale_revenue) over ()) 
            as numeric), 2) as sale_revenue_perc,
        sale_type,
        round(cast(100 * sale_revenue / (sum(sale_revenue) over (partition by sale_type)) 
            as numeric), 2) as sale_type_revenue_perc
    from data_prep
    order by sale_revenue asc

),
data_output as (
  select 
    upper(to_json_string(json_object('sale_data', sale_data_dow_array))) as json_output
  from (
    select array_agg(sale_data_dow) as sale_data_dow_array
      from(
        select 
          to_json(data_agg_output) as sale_data_dow
        from (
          select 
            data_agg.sale_dow,
            trim(cast(data_agg.sale_revenue as string format '999999.99')) as sale_revenue,
            trim(cast(data_agg.sale_revenue_perc as string format '999999.99')) as sale_revenue_perc,
            data_agg.sale_type,
            trim(cast(data_agg.sale_type_revenue_perc as string format '999999.99')) as sale_type_revenue_perc
          from data_agg
        ) data_agg_output
    )
  )
)
select * from data_output;

and here is the result:

{"SALE_DATA":[{"SALE_DOW":"MONDAY","SALE_REVENUE":"5.57","SALE_REVENUE_PERC":"7.42","SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":"11.93"},{"SALE_DOW":"TUESDAY","SALE_REVENUE":"7.87","SALE_REVENUE_PERC":"10.49","SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":"16.86"},{"SALE_DOW":"WEDNESDAY","SALE_REVENUE":"9.64","SALE_REVENUE_PERC":"12.85","SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":"20.66"},{"SALE_DOW":"THURSDAY","SALE_REVENUE":"11.14","SALE_REVENUE_PERC":"14.85","SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":"23.87"},{"SALE_DOW":"FRIDAY","SALE_REVENUE":"12.45","SALE_REVENUE_PERC":"16.59","SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":"26.68"},{"SALE_DOW":"SATURDAY","SALE_REVENUE":"13.64","SALE_REVENUE_PERC":"18.18","SALE_TYPE":"WEEKEND","SALE_TYPE_REVENUE_PERC":"48.08"},{"SALE_DOW":"SUNDAY","SALE_REVENUE":"14.73","SALE_REVENUE_PERC":"19.63","SALE_TYPE":"WEEKEND","SALE_TYPE_REVENUE_PERC":"51.92"}]}

With BigQuery there are more efficient ways to use parameters and to aggregate the data, if you know them feel free to post in the comments. Stay tuned!

SQL Pro Tips : industrial Oracle SQL using WITH

hexfloor — Thu, 28 Mar 2024 19:32:14 +0000

Introduction

A common industrial flow of data analysis consists of :

data preparation
data aggregation
data join
data output

In this guide I will show how to implement efficiently data preparation, aggregation and output.
Feel free to check on Oracle XE Quick Start and Oracle SQL CROSS JOIN with UNPIVOT, SQL WITH clause.

Problem definition

Parameters

In order to write professional SQL we should better define all the parameters within a separate block :

WITH
PARAMETERS AS (
    SELECT 
        'SATURDAY' AS WEEKEND_V0,
        'SUNDAY' AS WEEKEND_V1,
        'WEEKEND' AS WEEKEND_LABEL,
        'WORKWEEK' AS WORKWEEK_LABEL
    FROM DUAL
)
SELECT * FROM PARAMETERS;

This is a simple example how to use with. Let's extend the success and go further.

Test data

Now let's generate some test data : date and sale revenue for the date :

WITH
PARAMETERS AS (
    SELECT 
        'SATURDAY' AS WEEKEND_V0,
        'SUNDAY' AS WEEKEND_V1,
        'WEEKEND' AS WEEKEND_LABEL,
        'WORKWEEK' AS WORKWEEK_LABEL
    FROM DUAL
),
DATA_IN AS (
    SELECT
        TO_TIMESTAMP('2024-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) AS SALE_DATE,
        SQRT(ROWNUM * 31 ) AS SALE_REVENUE
    FROM DUAL
    CONNECT BY LEVEL <= 7
)
SELECT * FROM DATA_IN;

Great! Now let's prepare our data for aggregation.

Data preparation

WITH
PARAMETERS AS (
    SELECT 
        'SATURDAY' AS WEEKEND_V0,
        'SUNDAY' AS WEEKEND_V1,
        'WEEKEND' AS WEEKEND_LABEL,
        'WORKWEEK' AS WORKWEEK_LABEL
    FROM DUAL
),
DATA_IN AS (
    SELECT
        TO_TIMESTAMP('2024-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) AS SALE_DATE,
        SQRT(ROWNUM * 31 ) AS SALE_REVENUE
    FROM DUAL
    CONNECT BY LEVEL <= 7
),
DATA_PREP AS (
    SELECT 
        DATA_IN_PREP.SALE_DATE,
        DATA_IN_PREP.SALE_REVENUE,
        DATA_IN_PREP.SALE_DOW,
        (CASE WHEN (DATA_IN_PREP.SALE_DOW = PARAMETERS.WEEKEND_V0 OR DATA_IN_PREP.SALE_DOW = PARAMETERS.WEEKEND_V1) 
            THEN WEEKEND_LABEL ELSE WORKWEEK_LABEL END) AS SALE_TYPE        
    FROM (
        SELECT
            SALE_DATE,
            CAST(SALE_REVENUE AS DECIMAL (31, 2)) AS SALE_REVENUE,
            TRIM(TO_CHAR(SALE_DATE, 'DAY')) AS SALE_DOW
        FROM DATA_IN) DATA_IN_PREP
    CROSS JOIN PARAMETERS
)
SELECT * FROM DATA_PREP;

Good! Now we are set to start data aggregation.

Data aggregation

With properly prepared data the data aggregation is straight forward :

with 
parameters as (
    select 
        'SATURDAY' as weekend_v0,
        'SUNDAY' as weekend_v1,
        'WEEKEND' as weekend_label,
        'WORKWEEK' as workweek_label
),
data_in as (
  select 
    date_add(date('2024-01-01'), interval (value - 1) DAY) as sale_date,
    sqrt(value) as sale_revenue
  from unnest(generate_array(1, 7)) value
),
data_prep as (
  select 
    data_in_prep.sale_date,
    data_in_prep.sale_revenue,
    data_in_prep.sale_dow,
    (case when (data_in_prep.sale_dow = parameters.weekend_v0 or data_in_prep.sale_dow = parameters.weekend_v1) 
            then weekend_label else workweek_label end) as sale_type
  from (
        select
            sale_date,
            round(cast(sale_revenue as numeric),2) as sale_revenue,
            trim(upper(format_date('%A',sale_date))) as sale_dow
        from data_in
  ) data_in_prep cross join parameters
),
data_agg as (
    select
        sale_dow,
        sale_revenue,
        sale_type,
        round(cast(100 * sale_revenue / (sum(sale_revenue) over (partition by sale_type)) 
            as numeric), 2) as sale_type_revenue_perc
    from data_prep
    order by sale_revenue asc

)
select * from data_agg;

Data output

Oracle has a native support for json output :

WITH
PARAMETERS AS (
    SELECT 
        'SATURDAY' AS WEEKEND_V0,
        'SUNDAY' AS WEEKEND_V1,
        'WEEKEND' AS WEEKEND_LABEL,
        'WORKWEEK' AS WORKWEEK_LABEL
    FROM DUAL
),
DATA_IN AS (
    SELECT
        TO_TIMESTAMP('2024-01-01', 'YYYY-MM-DD') + (ROWNUM - 1) AS SALE_DATE,
        SQRT(ROWNUM * 31 ) AS SALE_REVENUE
    FROM DUAL
    CONNECT BY LEVEL <= 7
),
DATA_PREP AS (
    SELECT 
        DATA_IN_PREP.SALE_DATE,
        DATA_IN_PREP.SALE_REVENUE,
        DATA_IN_PREP.SALE_DOW,
        (CASE WHEN (DATA_IN_PREP.SALE_DOW = PARAMETERS.WEEKEND_V0 OR DATA_IN_PREP.SALE_DOW = PARAMETERS.WEEKEND_V1) 
            THEN WEEKEND_LABEL ELSE WORKWEEK_LABEL END) AS SALE_TYPE        
    FROM (
        SELECT
            SALE_DATE,
            CAST(SALE_REVENUE AS DECIMAL (31, 2)) AS SALE_REVENUE,
            TRIM(TO_CHAR(SALE_DATE, 'DAY')) AS SALE_DOW
        FROM DATA_IN) DATA_IN_PREP
    CROSS JOIN PARAMETERS
),
DATA_AGG AS (
    SELECT
        SALE_DOW,
        SALE_REVENUE,
        SALE_TYPE,
        CAST(100 * SALE_REVENUE / (SUM(SALE_REVENUE) OVER (PARTITION BY SALE_TYPE)) 
            AS DECIMAL(31,2)) AS SALE_TYPE_REVENUE_PERC
    FROM DATA_PREP
    ORDER BY SALE_REVENUE ASC

),
DATA_OUTPUT AS (
    SELECT JSON_OBJECT(*) AS JSON_OUTPUT FROM(
        SELECT  JSON_ARRAYAGG (  
            JSON_OBJECT (*) RETURNING CLOB   
        ) AS SALE_DATA
        FROM DATA_AGG
    )
)
SELECT * FROM DATA_OUTPUT;

and here is the result:

{"SALE_DATA":[{"SALE_DOW":"TUESDAY","SALE_REVENUE":7.87,"SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":16.86},{"SALE_DOW":"WEDNESDAY","SALE_REVENUE":9.64,"SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":20.66},{"SALE_DOW":"THURSDAY","SALE_REVENUE":11.14,"SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":23.87},{"SALE_DOW":"FRIDAY","SALE_REVENUE":12.45,"SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":26.68},{"SALE_DOW":"SATURDAY","SALE_REVENUE":13.64,"SALE_TYPE":"WEEKEND","SALE_TYPE_REVENUE_PERC":48.08},{"SALE_DOW":"SUNDAY","SALE_REVENUE":14.73,"SALE_TYPE":"WEEKEND","SALE_TYPE_REVENUE_PERC":51.92},{"SALE_DOW":"MONDAY","SALE_REVENUE":5.57,"SALE_TYPE":"WORKWEEK","SALE_TYPE_REVENUE_PERC":11.93}]}

SQL is still good for the data analysis, it's immortal as Microsoft Excel. Feel free to add comments if some steps require additional details. Stay tuned !

DEV Community: hexfloor

Cybersecurity 101 : https certificate chain

Introduction

Setup

“We need to go deeper” (from Inception, 2010).

“We need to go deeper”

Summary

Cybersecurity 101 : data sanitization

Problem

Q-Day

Hashing

Setup

HMAC

Summary

Bonus

Snyk and Sonar : committed credentials security test

Introduction

Setup

Snyk

Sonar

Issue

Recovery

Summary

More storage for media : organize files using ChatGPT

Contents

Summary

Bonus

Credits

More storage for media : organize files using ChatGPT : part4, processing on Gentoo

Introduction

Setup Gentoo

ImageMagick on Gentoo

ffmpeg on Gentoo

Final step

Summary

More storage for media : organize files using ChatGPT : part 3, converting MOV to HEVC MPEG-4

Introduction

Setup

convert MOV to MPEG-4 HEVC using ffmpeg

Bonus

More storage for media : organize files using ChatGPT : part 2, converting JPEG to HEIC

Introduction

Failed attempt to use jrottenberg/ffmpeg:4.1-alpine image to convert JPEG to HEIC

Failed attempt to use jrottenberg/ffmpeg:latest image to convert JPEG to HEIC

Failed attempt to use ffmpeg builds from Gyan Dev to convert JPEG to HEIC

Failed attempt to use ImageMagic on Win11 to convert JPEG to HEIC

Failed attempt to use IrfanView on Win11 to convert JPEG to HEIC

Failed attempt to use Win11 Photos to convert JPEG to HEIC

WSL

Summary

Bonus

More storage for media : organize files using ChatGPT : part 1, data preparation

Age of AI

Introduction

First steps

Metadata : data preparation

Metadata : updating images using data from jsons

Metadata : updating videos using data from jsons

ID : using proper id

Compress images

Compress videos

Summary

Security : CVE-2024-3094 unauthorized remote SSH access

Introduction

Security

Summary

SQL Pro Tips : industrial AWS Athena SQL using WITH

Introduction

Problem definition

Parameters

Test data

Data preparation

Data aggregation

Data output

SQL Pro Tips : industrial GCP BigQuery SQL using WITH

Introduction

Problem definition

Parameters

Test data

Data preparation