search

codayblue / libmyemail

mysql udf that allows you to send emails from a sql function
MIT License
2 stars 2 forks source link

TLS crashes server, no mail without TLS #5

Open novazur972 opened 3 years ago

novazur972 commented 3 years ago

Hi, I'm trying to use libmyemail on Gentoo with mysql 8.0.23 I never used plugin with mysql... What I did:

codayblue commented 3 years ago

I think it crashes because mysql does not know how to handle bool. It is looking for my_bool. Do you have the mysql devel headers installed?

codayblue commented 3 years ago

nvm looks like mysql changed that in 8. I have not tested or compiled this project for mysql 8 yet. I will see if I can get something working this weekend. If you gain some time I do accept PRs and will happily review one.

novazur972 commented 3 years ago

I think it crashes because mysql does not know how to handle bool. It is looking for my_bool. Do you have the mysql devel headers installed?

I think on Gentoo we always have headers installed. Packages are compiled with.

nvm looks like mysql changed that in 8.

confirmed here https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html#mysqld-8-0-1-compiling

I have not tested or compiled this project for mysql 8 yet. I will see if I can get something working this weekend. If you gain some time I do accept PRs and will happily review one.

Sorry, but I don't know what is "PRs".

But, whats about the use with TLS=0 ? Why no mail is sent ?

codayblue commented 3 years ago

PR = Pull Request. This is where you fork the project make code changes and then make the request for the changes to merge back into the upstream project.

Not sure what you mean with TLS=0. if no mail is sent it could be either a misconfiguration of the smtp settings or the code is no longer valid in the way it start TLS.

novazur972 commented 3 years ago

PR = Pull Request. This is where you fork the project make code changes and then make the request for the changes to merge back into the upstream project.

Oh, ok, but I'm not able to do anything in that way, no devel. Sorry.

Not sure what you mean with TLS=0. if no mail is sent it could be either a misconfiguration of the smtp settings or the code is no longer valid in the way it start TLS.

I mean mysql > SELECT sendmail('from@mydomain', 'to@mydomain', 'subject', 'body', 'localhost', 25, '', '', 0); doesn't send mail and I get logs:

Apr 27 00:54:57 mysmtpserver postfix/smtpd[15375]: connect from mysmtpserver[myip]
Apr 27 00:54:57 mysmtpserver postfix/smtpd[15375]: disconnect from mysmtpserver[myip] ehlo=1 quit=1 commands=2

And mysmtpserver works fine for years.

codayblue commented 3 years ago

@novazur972 can you please test against googles smtp server? Thank you for helping me triage this issue. I should have some free time this weekend to try and get the code working.

novazur972 commented 3 years ago

I don't really understand. smtp.gmail.com only accepts SSL (465) or TLS (587). Isn't possible to sendmail "simply" to smtp with port 25 without auth ?

when I try with smtp.gmail.com port 587, secure flag=1, no mysql crash, but gmail block the app and no mail sent. With 465 and secure flag = 0, no crash, but nothing happens. Tell me exactly what you want me to try.

But my first goal was to send a email by mysql to MY smtp server without auth.

Edit : I tried too to a gmail address, with port 25 and secure flag to 0, without auth, seems to work, but no mail sent. Exactly like with my own smtp.

novazur972 commented 3 years ago

mysql > SELECT sendmail('from@mydomain', 'to@mydomain', 'subject', 'body', 'localhost', 25, '', '', 0); tcpdump gives:

13:30:43.904520 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [S], seq 2707194583, win 65495, options [mss 65495,sackOK,TS val 2491911461 ecr 0,nop,wscale 7], length 0
13:30:43.904547 IP localhost.localdomain.smtp > localhost.localdomain.41054: Flags [S.], seq 4075221877, ack 2707194584, win 65483, options [mss 65495,sackOK,TS val 2491911461 ecr 2491911461,nop,wscale 7], length 0
13:30:43.904565 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [.], ack 1, win 512, options [nop,nop,TS val 2491911461 ecr 2491911461], length 0
13:30:43.917290 IP localhost.localdomain.smtp > localhost.localdomain.41054: Flags [P.], seq 1:36, ack 1, win 512, options [nop,nop,TS val 2491911473 ecr 2491911461], length 35: SMTP: 220 mysmtpserver ESMTP Postfix
13:30:43.917320 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [.], ack 36, win 512, options [nop,nop,TS val 2491911474 ecr 2491911473], length 0
13:30:43.917350 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [P.], seq 1:17, ack 36, win 512, options [nop,nop,TS val 2491911474 ecr 2491911473], length 16: SMTP: EHLO vmserveur
13:30:43.917366 IP localhost.localdomain.smtp > localhost.localdomain.41054: Flags [.], ack 17, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 0
13:30:43.917416 IP localhost.localdomain.smtp > localhost.localdomain.41054: Flags [P.], seq 36:219, ack 17, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 183: SMTP: 250-mysmtpserver
13:30:43.917442 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [.], ack 219, win 511, options [nop,nop,TS val 2491911474 ecr 2491911474], length 0
13:30:43.917532 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [P.], seq 17:23, ack 219, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 6: SMTP: QUIT
13:30:43.917563 IP localhost.localdomain.smtp > localhost.localdomain.41054: Flags [.], ack 23, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 0
13:30:43.917643 IP localhost.localdomain.smtp > localhost.localdomain.41054: Flags [P.], seq 219:234, ack 23, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 15: SMTP: 221 2.0.0 Bye
13:30:43.917658 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [.], ack 234, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 0
13:30:43.917686 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [F.], seq 23, ack 234, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 0
13:30:43.917704 IP localhost.localdomain.smtp > localhost.localdomain.41054: Flags [F.], seq 234, ack 24, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 0
13:30:43.917718 IP localhost.localdomain.41054 > localhost.localdomain.smtp: Flags [.], ack 235, win 512, options [nop,nop,TS val 2491911474 ecr 2491911474], length 0

It seems to send EHLO then QUIT, without sending mail. Not a smtp server issue about me.

Edit: smtp logs:

connect from localhost.localdomain[127.0.0.1]
smtp_stream_setup: maxtime=300 enable_deadline=0
match_hostname: smtpd_client_event_limit_exceptions: localhost.localdomain ~? 192.168.0.0/21
match_hostaddr: smtpd_client_event_limit_exceptions: 127.0.0.1 ~? 192.168.0.0/21
match_hostname: smtpd_client_event_limit_exceptions: localhost.localdomain ~? 127.0.0.0/8
match_hostaddr: smtpd_client_event_limit_exceptions: 127.0.0.1 ~? 127.0.0.0/8
> localhost.localdomain[127.0.0.1]: 220 mail.mydomain ESMTP Postfix
xsasl_cyrus_server_create: SASL service=smtp, realm=(null)
name_mask: noanonymous
watchdog_pat: 0x55bd4ef794d0
< localhost.localdomain[127.0.0.1]: EHLO myhost
match_list_match: localhost.localdomain: no match
match_list_match: 127.0.0.1: no match
> localhost.localdomain[127.0.0.1]: 250-mail.mydomain
> localhost.localdomain[127.0.0.1]: 250-PIPELINING
> localhost.localdomain[127.0.0.1]: 250-SIZE 102400000
> localhost.localdomain[127.0.0.1]: 250-VRFY
> localhost.localdomain[127.0.0.1]: 250-ETRN
> localhost.localdomain[127.0.0.1]: 250-STARTTLS
> localhost.localdomain[127.0.0.1]: 250-AUTH PLAIN
> localhost.localdomain[127.0.0.1]: 250-ENHANCEDSTATUSCODES
> localhost.localdomain[127.0.0.1]: 250-8BITMIME
> localhost.localdomain[127.0.0.1]: 250-DSN
> localhost.localdomain[127.0.0.1]: 250-SMTPUTF8
> localhost.localdomain[127.0.0.1]: 250 CHUNKING
watchdog_pat: 0x55bd4ef794d0
< localhost.localdomain[127.0.0.1]: QUIT
> localhost.localdomain[127.0.0.1]: 221 2.0.0 Bye
match_hostname: smtpd_client_event_limit_exceptions: localhost.localdomain ~? 192.168.0.0/21
match_hostaddr: smtpd_client_event_limit_exceptions: 127.0.0.1 ~? 192.168.0.0/21
match_hostname: smtpd_client_event_limit_exceptions: localhost.localdomain ~? 127.0.0.0/8
match_hostaddr: smtpd_client_event_limit_exceptions: 127.0.0.1 ~? 127.0.0.0/8
disconnect from localhost.localdomain[127.0.0.1] ehlo=1 quit=1 commands=2

local smtp server tested with telnet:

$ telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 mail.mydomain ESMTP Postfix
HELO localhost.localdomain
250 mail.mydomain
MAIL FROM:<from@mydomain>
250 2.1.0 Ok
RCPT TO:<to@mydomain>
250 2.1.5 Ok
DATA
354 End data with <CR><LF>.<CR><LF>
Subject: test message
This is the body of the message!
.
250 2.0.0 Ok: queued as 4B1C11C4D23
quit
221 2.0.0 Bye
Connection closed by foreign host.

email sent and correctly received.

novazur972 commented 3 years ago

I continued my research, and found part of the solution. I had an AUTH LOGIN authentication problem on the smtp and libmyemail seems to require mandatory authentication. Now, I can use sendmail('from@mydomain', 'to@mydomain', 'subject', 'body', 'localhost', 25, 'myuser', 'mypwd', 0); and the email is sent. Using "secure flag" to 1 always crashes the mysql server. I spent a lot of time on it but I was never able to determine if the problem is with my smtp server or libmyemail.

codayblue commented 3 years ago

Is your smtp server setup for TLS? I might not be handling a failed TLS handshake correctly and it causes the server the crash. So I should probably put more error handling in.

novazur972 commented 3 years ago

Is your smtp server setup for TLS?

I'm really not very comfortable with all of this.

But it seems to work: openssl s_client -starttls smtp -connect localhost:587

CONNECTED(00000003)
Can't use SSL_get_servername
depth=0 CN = mysmtpserver
verify error:num=18:self signed certificate
verify return:1
depth=0 CN = mysmtpserver
verify return:1
---
Certificate chain
 0 s:CN = mysmtpserver
   i:CN = mysmtpserver
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIDIDCCAgigAwIBAgIJALjS1ro02L9eMA0GCSqGSIb3DQEBCwUAMBoxGDAWBgNV
BAMMD21haWwubm92YXp1ci5mcjAgFw0xNjA2MjIwMzI3NTBaGA8yMTE2MDYyMzAz
Mjc1MFowGjEYMBYGA1UEAwwPbWFpbC5ub3ZhenVyLmZyMIIBIjANBgkqhkiG9w0B
AQEFAAOCAQ8AMIIBCgKCAQEAx5XZJT1t89OBH1pzv7+zKdlTf6mv+hEEvD1ptP/U
lxy2wMW09mnzES9T0nPznwHLStP5BdvA7VZrfkzqPF12KXGdzjalMT/ImcJ8B/Kd
kzXgk3dv3Hr3U7C3Mn12SENoeYq6eUSTJU8udKxaL9U9/dg8A77ss9oOxfK79Rd7
W/EvDaiU3+o1vA3vQD5gt9Xy5ioHfj6qvucAhK+OrEfBuEyg63UrbWye3a13s+K7
7rWkDOmcb8DVKmTDFVliX3DFYosfcUFqofoVMDTBhlnKvanCyJll0duIsSX65eVF
yDykmQF+GyPyJWD3lVEfeGS6GkuQavvE1D6o46NG+OeW+QIDAQABo2cwZTAJBgNV
HRMEAjAAMB0GA1UdDgQWBBTwww6NpchdYJBKVhVrhw8LVbYiGzAdBgNVHSUEFjAU
BggrBgEFBQcDAQYIKwYBBQUHAwIwGgYDVR0RBBMwEYIPbWFpbC5ub3ZhenVyLmZy
MA0GCSqGSIb3DQEBCwUAA4IBAQCA6IAhztMo/owkgx7CGnOcfkMYwvcmiEij+L24
6zOjgzj/1MJwjBPjC30WJ7ycFkELG3NxCGAEl3m4L99lR8tMjQ4RBkfR3Z5ZoWsj
ABURrpD30AaWxUzkH57K5QkxxFfSu1v0HwW3HKYCKxb+8SZkFjlAOvY/MTfntaKx
aMrahid+ssCS1uWUfuysxSQMCWthEUcjoi4mpjRbSQWRz9ERDtiYNkFpYoVC31o+
ErO+anAO4kNf7uB7O26Xh+LBUeHenb4nlkVQ3Tau1FB//HwwYHu4pIcDmFVbKmi9
FK8oZQibAE/Z06oDaH4JDdGVV2IoonWr6Vf7WwNYlQ1U4VId
-----END CERTIFICATE-----
subject=CN = mysmtpserver

issuer=CN = mysmtpserver

---
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 1592 bytes and written 406 bytes
Verification error: self signed certificate
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 18 (self signed certificate)
---
250 CHUNKING
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: EEAFEC57DA0457C38663028C11815CF4070E3544F6E80841C528B10F45AAA046
    Session-ID-ctx: 
    Resumption PSK: B63A480EAECC6351FFED75DD90545B9581B1656C93C55314A20B48A4F259AAB495D50AB651E5AC6BB1CDBDCF814EA126
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
    0000 - 29 56 00 2a db 67 01 d6-d7 5c 3d 53 5a 3c 7f c9   )V.*.g...\=SZ<..
    0010 - 62 02 7d 8a cb 31 5a 20-66 49 62 54 e5 38 3d 3c   b.}..1Z fIbT.8=<
    0020 - 95 75 3d 55 0c 80 53 46-c8 a9 d0 d4 cf ae 6c 02   .u=U..SF......l.
    0030 - ff fe e0 ec 8d d8 af d1-43 5d ef 72 d1 47 ae 38   ........C].r.G.8
    0040 - 71 1a 27 f7 36 99 58 43-18 88 64 d5 68 80 db 30   q.'.6.XC..d.h..0
    0050 - 47 7e bb c0 a6 bb 11 28-3c 16 7e b3 5e f8 52 d2   G~.....(<.~.^.R.
    0060 - 03 7c d9 f8 24 30 c1 c9-69 8b 4f 11 80 83 48 6f   .|..$0..i.O...Ho
    0070 - 3e a8 3c 9e 6a d4 7c 6e-90 db 41 fd f3 70 0b 42   >.<.j.|n..A..p.B
    0080 - e5 5c b0 f0 65 ad 4c 3a-48 f4 a6 a4 05 05 ba 4c   .\..e.L:H......L
    0090 - 7f d9 c3 aa 9b 12 fe 00-1f 93 f0 75 6d 7b aa 2a   ...........um{.*
    00a0 - 93 4f ab 5d 2c 53 f8 98-ca 74 f2 ef 30 b4 7e 10   .O.],S...t..0.~.
    00b0 - 23 5a e8 8d 2d e0 15 f1-ea 83 40 09 dc d0 c0 e6   #Z..-.....@.....
    00c0 - 4c a0 b2 48 4c 7a 35 ba-b5 9d 4a 44 8c 0f fd 7e   L..HLz5...JD...~

    Start Time: 1619840407
    Timeout   : 7200 (sec)
    Verify return code: 18 (self signed certificate)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK
novazur972 commented 3 years ago

I might not be handling a failed TLS handshake correctly and it causes the server the crash. So I should probably put more error handling in.

Even without TLS, If I use bad password, it crashes the server too.

mysql> sendmail('from@mydomain', 'to@mydomain', 'subject', 'body', 'mysmtpserver', 25, 'myuser', 'BADpwd', 0);
ERROR 2013 (HY000): Lost connection to MySQL server during query
codayblue commented 3 years ago

@novazur972 I see it is a self signed cert. Has that cert been trusted by the OS? Also thank you again for the information. I am setting up an SMTP server on my homelab to be able to replicate this. I normally just use googles smtp and had no issues. Though seeing now there is more cases I am going to try and clean up the code to handle the errors.

novazur972 commented 3 years ago

@novazur972 I see it is a self signed cert. Has that cert been trusted by the OS?

Yes.

Also thank you again for the information.

You're welcome. Thank you for your work ;)

I am setting up an SMTP server on my homelab to be able to replicate this. I normally just use googles smtp and had no issues.

I tried only one time with google smtp but the email was blocked because of untrusted app.

Though seeing now there is more cases I am going to try and clean up the code to handle the errors.

I'm really afraid of it

codayblue commented 3 years ago

@novazur972 I just pushed up a new branch called mysql8.x-patch. Let me know if that stops crashing the server. I was not able to replicate the issues. Once the server is done crashing maybe we can see from the smtp logs on what if failed on and I could try and put in better handling.

novazur972 commented 3 years ago

I tried this branch. no crash without TLS and bad password : sendmail('from@mydomain', 'to@mydomain', 'subject', 'body', 'mysmtpserver', 25, 'myuser', 'BADpwd', 0); but crashes with TLS=1. I have no time for the moment to investigate more on smtp logs. Sorry

codayblue commented 3 years ago

Hummm I will try some other things to get the crashing to stop at least.