How I sent spam

Introduction

In this small article I want to talk about how I set up sending mail from Oracle and how it ended. I want to clarify two things right away: firstly, I did not do it to send spam, secondly, I hadn’t dealt with mail server settings before, as this is outside the scope of my professional activity.

Oracle setup

Solving one of my tasks, I was faced with the need to send users authentication data, i.e. logins and passwords that are in the table. Frankly, at the moment there are not hundreds or thousands of users, so everyone could send a letter with their hands, but this is a couple of hours of manual labor - it’s more interesting to spend two or three times more time, but to automate everything, isn't it?

At the previous place of work, I had to send monthly reports to users by mail, so the task did not look something exotic to me, moreover, I expected to cope with time commensurate with manual labor. On the other hand, there was all the necessary infrastructure at hand + specialists of the necessary topics with whom it was possible to talk. There was nothing here - you need to understand yourself.
')
Oracle has many methods for sending mail from the database, here are some of the packages that have the necessary functionality:

• UTL_MAIL
• UTL_SMTP
• UTL_TCP
• UTL_HTTP

Any of the listed packages completely contains the necessary logic. The freshest one is the UTL_MAIL package, which I decided to use. I will say right away that this package is not included in the Oracle assembly by default, it must be installed by hand. Parts of the package are in two files:
1. The package specification is located in the @ $ ORACLE_HOME \ rdbms \ admin \ utlmail.sql file.
2. The package body is encrypted and lies in the file @ $ ORACLE_HOME \ rdbms \ admin \ prvtmail.sql

It is necessary to roll it out from under the user SYS , I could not install the package from the Command Window PL / SQL Developer normally, ie it was installed, but produced compilation errors. From sqlplus everything was fine.

After the run, you need to issue grants to use the package. Some authors suggest giving grants to Public , but this is not a good idea for security reasons. After issuing grants to the right user, our work under the SYS user is completed (however, we still need situations where we need to do the alter system ).

Mail server

As it turned out, the infrastructure is still needed to send mail, i.e. In addition to Oracle, I need a mail server, which I did not have, and I didn’t even imagine how to install and configure it, and is it necessary?

After a brief search, I found the following article http://jiri.wordpress.com/2010/03/24/send-emails-using-utl_mail-and-google-gmail-smtp-server/ , describing how to send mail via GMAIL. The author of the article got into my similar situation and offered a simple solution, which I decided to use. He got a new mailbox on GMAIL, which will be used for automatic sending of letters, downloaded and installed E-MailRelay . Immediately everything did not work for me, I had to read some documentation and search for more articles describing the interaction with this server. As a result, all the necessary parameters were entered into the bat-file, the mailbox data in the emailrelay.auth file and the server was successfully started.

Sending letters

After installing the mail server (or SMTP proxy, as in our case), you need to specify the Oracle address:

alter system set smtp_out_server = 'ip-address:port' scope=Both; 

In my case, Oracle and SMTP proxies are on the same machine, so the address is 127.0.0.1, port 25 .

Let us now try to send a letter using utl_mail.send , for this we had to play a little more with the settings, for example, it turned out that in the sender field you definitely need an address in the following format:
"<any_mail@any_mail_server.com>" , there you can also specify the name of the sender:
"Sender Name <my_mail@any_mail_server.com>" , but the letter will still come from your real gmail-box, and it will look like this:
"Sender Name <my_mail@any_mail_server.com>" <real_mailbox@gmail.com>

Few experiments with mime_type and sending to different mail servers showed that it is desirable to use 'text / html for Russian text ; charset = "UTF-8" ' .

After checking the sending of letters to myself, a procedure was written that selected the necessary data and sent each user his login / password. By running this procedure to send data to the first twenty users, I left for lunch.

After 40 minutes, it turned out that only the first 4 letters were gone, the rest were stuck somewhere. Preparing for debug, I sent 10 letters to my box using the same procedure. The new package "pushed through" lost letters and they all successfully left. In general, the SMTP proxy worked, albeit with some inconsistency.

Continuation of a story

A day later, doing business on the server, I saw that the SMTP proxy sent about 80 emails, although I didn’t send anything through it after the first mailing. I decided to check the mailbox on gmail. In the incoming there were three packs of letters:


All three chains contained email server replies about sending messages to non-existing mailboxes. In the sent messages the following picture was observed:


It turned out that two letters were sent from my mailbox to vbibiorm@gmail.com and w852@ymail.com , each letter in the subject contained the IP address of the machine on which my SMTP proxy stood, the body of the letters was empty, but the letters each had one attachment with the name noname and size zero bytes.


Obviously, the SMTP proxy had a vulnerability, and I do not know whether it manifested itself only when sending emails with an attachment, or if the attachment had to contain any additional information regarding the vulnerabilities / characteristics of the machine. Since the time of sending messages to both mailboxes is the same, I conclude that they belong to the same person. As a result of the vulnerability found, an email of the following type was sent via SMTP proxy:


Google translation defines the language of letters as Chinese. A search in Google for information on these e-mails showed that users often complain about the incomprehensible activity of mail servers associated with sending letters to these mailboxes. In general, this is how the phrase “open relay” appeared in my dictionary. I turned off the SMTP proxy, but the question remains, what to do next? I saw three options:

1. Since it was not often planned to send mail in whole packages, you can leave everything as it is, i.e. include mail proxy only at the time of sending letters - the cheapest solution for time.
2. You can thoroughly deal with the theme of mail servers and either correctly configure this, or change it to a more reliable one - quite an expensive option, besides, in the future, this knowledge will be absolutely useless to me.
3. Find a workground, using existing knowledge - time costs are not known.
4. Find someone familiar with mail servers.

Taking into account the initial task, the third option looked the most promising, provided that “fun” was obtained from the solution process. Further, variants 1 and 4 are approximately equivalent.

Options

The simplest and ideal option is to send mail immediately via Gmail, without additional servers. Let's try:

 alter system set smtp_out_server = 'smtp.gmail.com:587' scope=Both; 

Now an attempt to send a letter returns the following error: “ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. m29sm5336584poh.20. I went to the Gmail documentation and tried other ports, I also tried setting http work in the mailbox settings, not https. As a result, the error message changed, but the mail did not leave. A search on the Internet indicated several points:

1. Gmail and Yahoo require the use of STARTTLS (another name for SSL).
2. UTL_MAIL does not support STARTTLS.
3. Particularly stubborn comrades tried to use the utl_smtp package and the utl_smtp.command command (conn, 'STARTTLS'), but failed. Somewhere in the documentation I found that STARTTLS has been supported by Oracle since version 11.2.
4. The general opinion of the community was as follows:

• Use no Gmail and no Yahoo and do not fool yourself.
• If you want to use Gmail, set yourself an SMTP proxy (option. Described at the beginning of the article).
• If you understand what you are doing, you can try to log in to the mail server through the utl_smtp package.

I decided to try to implement clause 4. - I am looking for another mail server, I find there settings for SMTP, I am doing alter system . I'm trying to send a letter, I get the error: "ORA-29278: Temporary error SMTP: 421 Service not available". I re-read the settings, I try to connect the options using authorization through the utl_smtp package (a discussion of this option can be found, for example, here ). Nothing works, either through another mailer or through Gmail. Constantly close the browser tabs with a description of what I already know, but the number of potentially useful to strive to infinity:


I understand that it is time to end this, during the next search I find a simple thought: isn’t my port 25 closed? Checking (it turns out, I now know how to do it) - closed. I do alter system with indication of my alternative mail server and port 587. I try to send an email using UTL_MAIL and I get the error "ORA-29279: Permanent error SMTP: 501 sender address must contain a domain". Almost got it, get rid of the necessary Gmail'y "<>" in the name of the sender, and the letter leaves.

Conclusion

In this article I described my experience with sending mail in the field and in the absence of the necessary knowledge about the operation of mail servers. The result obtained is not quite satisfactory, since I found the same “leaky” mail server as the SMTP proxy I had previously installed. After spending another minute, I found out that I can send an email on behalf of any mailbox that exists on the mail server, simply by specifying, for example

 v_sender varchar2(200):='  <admin@mail.******>' 

where mail. ****** is the name of the found mail server. However, I decided my tasks, I got rid of the leaky proxy and the need to deal with its settings, I learned a little more about the UTL_MAIL and UTL_SMTP packets. I also planned to deal with option 4.c, but it didn't work out right away, and I decided not to spend more time if someone finished sending letters from Oracle directly through Gmail, it would be interesting to read about it. I spent about 6-7 hours on reconfiguring mail sending (after detecting spam) and writing this article in parallel.