Как я спам слал

    Введение


    В этой небольшой статье я хочу рассказать о том, как я настраивал отправку почты из Oracle и чем это закончилось. Хочу сразу уточнить две вещи: во-первых, делал я это не для рассылки спама, во-вторых, ранее я не имел дела с настройкой почтовых серверов, поскольку это лежит вне области моей профессиональной деятельности.



    Настройка Oracle


    Решая одну из своих задач, я столкнулся с необходимостью рассылать пользователям данные для аутентификации, т.е. логины и пароли, которые лежат в таблице. Скажем откровенно, в данный момент пользователей не сотни и не тысячи, так что можно было бы каждому отослать письмо руками, но это пару часов ручного труда – интересней ведь потратить в два-три раза больше времени, но все автоматизировать, не так ли?

    На прошлом месте работы мне уже приходилось слать джобом ежемесячные отчеты на почту пользователям, так что задача не выглядела для меня чем-то экзотичным, более того, я рассчитывал, что справлюсь за соизмеримое с ручным трудом время. С другой стороны там была вся необходимая инфраструктура под рукой + специалисты нужных тематик, с которыми можно было поговорить. Здесь же не было ничего — нужно разбираться самому.

    Oracle имеет множество методов отсылки почты из БД, вот некоторые из пакетов, имеющие нужную функциональность:
    • UTL_MAIL
    • UTL_SMTP
    • UTL_TCP
    • UTL_HTTP
    Любой из перечисленных пакетов полностью содержит необходимую логику. Самый свежий – это пакет UTL_MAIL, его я и решил использовать. Скажу сразу, этот пакет не входит в сборку Oracle по умолчанию, его нужно устанавливать руками. Части пакета лежат в двух файлах:
    1. спецификация пакета находится в файле @$ORACLE_HOME\rdbms\admin\utlmail.sql
    2. тело пакета зашифровано и лежит в файле @$ORACLE_HOME\rdbms\admin\prvtmail.sql

    Накатывать это нужно из-под пользователя SYS, нормально установить пакет из Command Window PL/SQL Developer’a мне не удалось, т.е. он установился, но выдавал ошибки компиляции. Из sqlplus все установилось отлично.

    После наката нужно выдать гранты на использование пакета. Некоторые авторы предлагают давать гранты на Public, но это не есть хорошая идея по причинам безопасности. После выдачи грантов нужному пользователю наша работа под пользователем SYS закончена (однако он нам еще понадобиться ситуации, когда нужно делать alter system).

    Почтовый сервер


    Как оказалось, для отправки почты все же нужна инфраструктура, т.е. кроме Oracle нужен почтовый сервер, которого у меня не было, а уж устанавливать и настраивать я его вообще не представлял как, да и нужно ли?

    После недолгих поисков я нашел следующую статью http://jiri.wordpress.com/2010/03/24/send-emails-using-utl_mail-and-google-gmail-smtp-server/, описывающую как слать почту через GMAIL. Автор статьи попал в аналогичную моей ситуацию и предлагал простое решение, которым я решил воспользоваться. Завел новый ящик на GMAIL, который будет использоваться для автоматической отсылки писем, скачал и установил E-MailRelay. Сразу у меня все не заработало, пришлось немного почитать документацию и поискать еще статей, описывающих взаимодействие с данным сервером. В итоге все нужное параметры были внесены в bat-файл, данные почтового ящика в файл emailrelay.auth и сервер успешно запущен.

    Отправка писем


    После установки почтового сервера (или SMTP-прокси, как в нашем случае) нужно указать Oracle его адрес:
    alter system set smtp_out_server = 'ip-address:port' scope=Both; 
    В моем случае, Oracle и SMTP-прокси стоят на одной машине, поэтому адрес 127.0.0.1, порт 25.

    Попытаемся теперь отправить письмо с помощью utl_mail.send, для этого пришлось еще немного поиграться с настройками, например, оказалось, что в поле sender обязательно нужен адрес в следующем формате:
    "<any_mail@any_mail_server.com>", там же можно указать имя отправителя:
    " Sender Name <my_mail@any_mail_server.com>", но письмо придет все равно с вашего реального gmail-ящика, и выглядеть это будет так:
    " Sender Name <my_mail@any_mail_server.com>" <real_mailbox@gmail.com>

    Немного экспериментов с mime_type и отсылкой на разные почтовые серверы показало, что для русского текста желательно использовать 'text/html; charset=«UTF-8»'.

    После контрольной отсылки писем себе была написана процедура, которая выбирала нужные данные и слала каждому пользователю его логин/пароль. Запустив эту процедуру для отсылки данных первым двадцати пользователям, я ушел обедать.

    Минут через 40 оказалось, что ушло только первых 4 письма, остальные где-то застряли. Готовясь к дебагу, я отправил той же процедурой 10 писем себе на ящик. Новый пакет «протолкнул» потерянные письма и все они успешно ушли. В общем SMTP-прокси работал, хотя и с некоторым непостоянством.

    Продолжение истории


    Через день, занимаясь своими делами на сервере, я увидел, что SMTP-прокси отослал около 80 писем, хотя я после первой рассылки больше через него ничего не отправлял. Решил проверить ящик на gmail’e. Во входящих красовалось три пачки писем:


    Все три цепочки содержали ответы почтовых серверов об отправке сообщения на несуществующие ящики. В отправленных сообщениях наблюдалась следующая картина:


    Получалось, что с моего ящика ушло по два письма на ящики vbibiorm@gmail.com и w852@ymail.com, причем каждое письмо в теме содержало IP-адрес машины, на которой стоял мой SMTP-прокси, тело писем было пустым, но письма имели по одному аттачу с именем noname и размером ноль байт.


    Очевидно, что SMTP-прокси имел уязвимость, и мне неизвестно, проявлялась ли она только при отправке писем с аттачем, или аттач должен был содержать какую-то дополнительную информацию относительно уязвимостей/характеристик машины. Поскольку время отправки сообщений на оба ящика совпадает, я делаю вывод, что они принадлежат одному и тому же человеку. В результате найденной уязвимости через SMTP-прокси были посланы письма такого вида:


    Гугл-транслейт определяет язык писем как китайский. Поиск в гугле информации по данным е-мейлам показал, что пользователи часто жалуются на непонятную активность почтовых серверов, связанную с отсылкой писем на эти ящики. В общем, так у меня в словаре появилось словосочетание «open relay». SMTP-прокси я выключил, но остался вопрос, что с этим делать дальше? Я видел три варианта:
    1. Поскольку почту отправлять планировалось не часто и целыми пакетами, то можно оставить все как есть, т.е. включать почтовый прокси только на время отправки писем – наиболее дешевое по времени решение.
    2. Можно досконально разобраться с темой почтовых серверов и либо корректно настроить данный, либо сменить его на более надежный – довольно дорогой по времени вариант, к тому же в дальнейшем мне эти знания будут абсолютно бесполезны.
    3. Найти воркэраунд, с использованием имеющихся знаний – затраты по времени не известны.
    4. Найти разбирающегося в почтовых серверах знакомого.
    Учитывая исходную задачу, наиболее перспективно выглядел 3-ий вариант, при условии получения «fun’a» от процесса решения. Далее, примерно равнозначны варианты 1 и 4.

    Варианты


    Самым простым и идеальным вариантом является отправка почты сразу через Gmail, без дополнительных серверов. Попробуем:
    alter system set smtp_out_server = 'smtp.gmail.com:587' scope=Both; 
    Теперь попытка отсылки письма возвращает следующую ошибку: «ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. m29sm5336584poh.20». Я сходил в документацию Gmail’a и попробовал другие порты, попробовал также установить в настройках ящика работу через http, а не https. В результате сообщение об ошибке менялось, но почта не уходила. Поиск в интернете указал на несколько моментов:
    1. Gmail и Yahoo требуют использования STARTTLS(другое название SSL).
    2. UTL_MAIL не поддерживает STARTTLS.
    3. Особо упорные товарищи пробовали использовать пакет utl_smtp и команду utl_smtp.command(conn,'STARTTLS'), но потерпели фиаско. Где-то в документации я нашел, что STARTTLS поддерживается Ораклом начиная с версии 11.2.
    4. Общее мнение сообщества сводилось к следующему:
    • Используйте не Gmail и не Yahoo и не морочьте себе голову.
    • Если вы хотите использовать Gmail, поставьте себе SMTP-прокси (вариант. описанный в начале статьи).
    • Если вы понимаете что делаете, можете попытаться авторизоваться на почтовом сервере через пакет utl_smtp.
    Я решил попробовать реализовать пункт 4.а – ищу другой почтовый сервер, нахожу там настройки для SMTP, делаю alter system. Пытаюсь послать письмо, получаю ошибку: «ORA-29278: Временная ошибка SMTP: 421 Service not available». Перечитываю настройки, пытаюсь подключить варианты с использованием авторизации через пакет utl_smtp (обсуждение этого варианта можно посмотреть, например, здесь). Ничего не работает, ни через другой почтовик ни через Gmail. Постоянно закрываю в браузере вкладки с описанием того, что я уже знаю, но количество потенциально полезных стремиться к бесконечности:


    Понимаю, что пора с этим заканчивать, во время очередного поиска нахожу простую мысль: а не закрыт ли у меня порт 25? Проверяю (оказывается, я теперь умею это делать) – закрыт. Делаю alter system с указанием моего альтернативного почтового сервера и порта 587. Пробую отправить письмо с помощью UTL_MAIL и получаю ошибку «ORA-29279: Постоянная ошибка SMTP: 501 sender address must contain a domain». Почти получилось, избавляюсь в имени отправителя от необходимых Gmail’y "<>", и письмо уходит.

    Заключение


    В этой статье я описал свой опыт работы с отправкой почты в полевых условиях и при отсутствии необходимых знаний о работе почтовых серверов. Полученный результат не является вполне удовлетворительным, поскольку я нашел такой же «дырявый» почтовый сервер, как и ранее установленный мною SMTP-прокси. Потратив еще минуту, я выяснил, что могу отправить письмо от имени любого существующего на почтовом сервере ящика, просто указав, например
    v_sender varchar2(200):='Вася Пупкин <admin@mail.******>'
    где mail.****** — имя найденного почтового сервера. Однако свои задачи я решил, от дырявого прокси и необходимости разбираться с его настройками я избавился, узнал немного больше про пакеты UTL_MAIL и UTL_SMTP. Также я планировал разобраться с вариантом 4.с, но сходу у меня не вышло, и я решил не тратить больше времени, если кто-то добьет таки отправку писем из Oracle напрямую через Gmail будет интересно про это почитать. На перенастройку отправления почты (после обнаружения спама) и параллельное написание статьи я потратил где-то около 6-7 часов.
    Поделиться публикацией
    Ой, у вас баннер убежал!

    Ну. И что?
    Реклама
    Комментарии 13
    • –2
      Комментируем! Создал «первый коммент», чтоб можно было комментить, пока Хабру не починят :) Подробности: habrahabr.ru/qa/12275/
      • +4
        «Беда, коль пироги начнет печи сапожник...» — неужели рядом не было ни одного сисадмина?
        А вообще — уважаю за дотошность и умение справляться имеющимися средствами!

        Я бы, конечно, просто написал скрипт периодически забирающий из базы данные, вставляющий их в шаблон и производящий рассылку.
        • +5
          Очень, ну очень хочется вставить известную картинку про троллейбус.
        • +1
          У меня тоже была первая мысль, что писма слать — задача не для СУБД.
        • +1
          По моему, эта статья — пример того, как делать не надо:

          1) использовать СУБД для рассылки почты
          2) ставить на сервер утилиты, не понимая принципа их работы. Как понимаю, ваш SMTP-релей открыл порт не на loopback-интерефйсе, а на всех интерфейсах, включая внешний, и его нашли спамеры. Вы бы еще гостевой доступ на Windows Server разрешили (или пароль root поменяли на 123456, чтобы набирать проще было).
          3) ну и вообще, использовать сложные технологии, не понимая даже что такое TLS, зачем открывают порт 2525 и т.д.

          В общем, имхо, крайне плохая статья. Если вам надо было что-то разослать на основе данных в базе, а желания читать мануалы и википедии нет, надо было просто экспортировать базу в нужном виде в Excel/Access и через outlook разослать — у майкрософта что-то есть для рассылки писем из таблицы, и особых знаний там не требуется, только кнопки «Далее» и «ОК» нажимать.

          Хранить пароли и логины в открытом виде, тоже, кстати, не лучшая идея.
          • +1
            А ведь и без запарок можно с помощью cron'a, sql*plus'a и любого консольного почтового клиента
            • +4
              На счет отсылки писем из СУБД — не вижу в этом ничего предосудительного (когда, конечно, все происходит в нормально сконфигурированной среде). Просто существует два диаметрально противоположных подхода: БД — только хранилище данных и БД — серверная часть приложения, как правило в живых проектах используется что-то среднее с перекосом в ту или другую сторону.
              Если в ваших проектах привыкли не развертывать логику в БД, то да, отсылка оттуда писем, http-запросов и прочий не sql-функционал выглядит неуместно. Если наличие в БД логики для вас норма, все данные находятся и обрабатываются там же, почему бы не отослать письмо с отчетом? Тем более, что это выполняется стандартными средствами, без вызова «external subprograms».

              На счет замечаний по моей настройке почты — то все замечания верные, и, в идеальном мире, я бы этим не занимался. Но в реальности бывают разные обстоятельства, разные люди, по-разному выполняющие свою работу. В общем, не займись я этим сам, слал бы я письма руками.

              На счет паролей в открытом виде, то в данном случае пароли — не очень критичная информация, в том смысле, что это автоматически сгенерированные пароли для доступа к определенной функциональности в БД (потому их и нужно было рассылать). Хранятся они вместе с той функциональностью, доступ к которой обеспечивают. Другими словами, атака, способная достигнуть таблицы с паролями, равновероятно достигнет и защищаемой ими информации. Поэтому с точки зрения возможности кражи шифровать их бессмысленно, разве что для защиты от передачи по сети в открытом виде. Но тут уже встает вопрос соотношения цены информации к цене ее защиты. С точки зрения критичности такой перехват пароля позволит злоумышленнику всего лишь получить доступ к системе работы с данными, заточенной под весьма специфические операции, а не к самим данным.

              Про автоматизированную отсылку через почтовый клиент типа Outlook'a даже не подумал — давно не пользовался, вот специфика мышления и определила однозначное направление поиска решения :)
              • +1
                >>На счет отсылки писем из СУБД — не вижу в этом ничего предосудительного
                Поддерживаю. Мои ночные жобы часто шлют мыло о сбоях мне и на вторую линию поддержки. В интрасеть, правда, не во вне.
                >>для русского текста желательно использовать 'text/html; charset=«UTF-8»'.
                Все мои базы в cp-1251, шлю русский текст преобразуя его в raw:

                  UTL_SMTP.write_raw_data(l_connection
                                          ,UTL_RAW.cast_to_raw(
                                                     'Content-Type: text/plain; charset=windows-1251'||crlf
                                                     ||'Subject:=?windows-1251?Q?'||subject||'?='||crlf
                                                     ||crlf||message||crlf
                                            )
                                         );
                
                • 0
                  Тоже поддержу, у меня при инсерте в таблицу блокировок заявок рассылаются емейлы пользователям, о том что им неплохо бы подойти к начальству )
              • +1
                К сожалению похожая ситуация не только с Oracle, но и с почтовыми серверами в целом.
                Для меня как человеку далекому от админства настраивать такое превращается в головную боль на день или два.
                • 0
                  Позволю себе вставить пару центов. (Впрочем, центов — много! Копеек.)

                  Полгаю, что в статье вы рассказали об эксплуатации такого софта в условиях ограниченных ресурсов. По-просту, дома. Экспериментируя дома, наверно, можно позволить себе объединить на одной машине все экспериментаторские сервисы и совершать собственные ошибки в настройках.

                  Но софт такого уровня обычно эксплуатируется в крупных организациях. Где уже есть инфраструктура.

                  Надо понимать, что задача отправки почты «средствами Oracle» разбивается на две (или даже больше):
                  — сформировать и отправить сообщение на стандартный smtp сервер
                  — иметь в наличии этот самый smtp сервер

                  Про то, как сформировать и отправить письмо вы хорошо написали. Своим «программистам» я рекомедую делать так:

                  DECLARE

                  vSender VARCHAR2(30) := 'oracle@name-of-db-goes-here.domain.name.here';
                  vSubj VARCHAR2(50) := 'Hello';
                  vRec VARCHAR2(50) := 'receiver@domain.name.here';
                  vMesg VARCHAR2(4000);
                  vMType VARCHAR2(40) := 'text/plain; charset=ISO-8859-5';

                  BEGIN

                  select TO_CHAR(sysdate,'HH24:MI:SS DD.MM.YYYY') into vMesg from dual;

                  vMesg := vMesg || CHR(13) || CHR(10) ||
                  'Hello world '|| CHR(13) || CHR(10) ||
                  'I am so happy!' || CHR(13) || CHR(10) ||
                  'И могу говорить по-русски!';

                  utl_mail.send(vSender, vRec, NULL, NULL, vSubj,vMesg, vMType, NULL);

                  END;

                  *Выбор кодировки (charset=ISO-8859-5) связан с особенностями работы наших Oracle под Solaris.

                  Теперь о второй задаче «иметь в наличии smtp сервер». Решается она очень просто: используйте свой корпоративный почтовый сервер! :) Или сервер заведомо работающего провайдера, что, собственно, вы и сделали.

                  (Или сделайте собственный почтовый сервер! Что, кстати, не так сложно, как кажется. Или наймите того, кто это может. Меня, например. :))

                  Или, все-таки, используйте «SMTP прокси», но закройте его файрволлом от подключения «извне».

                  • 0
                    Полностью согласен на счет корпоративного почтового сервера, насколько это большое благо я понял только когда пришлось слать почту без него (что и описано выше). «В условиях ограниченных ресурсов» — замечательная формулировка полностью передающая суть описанного в статье, хотя это не домашняя машина.

                    Раз уж такое дело, то добавлю пару слов о использовании внешних почтовых серверов. Скажем прямо, дело это мутное, в том смысле, что существуют разные ньансы.

                    Свой код для отправки писем с требующего авторизации почтового сервера привожу ниже. Скажу сразу, что этот код может слать письма в русской кодировке, например на Gmail. Успешность отсылки зависит от принимающего ящика, т.е. если ваше русскоязычное письмо пришло в неподобающем виде — попробуйте поменять строки с кодировкой.
                    Сам код:
                    create or replace procedure send_mail_with_authorization(p_mail_host varchar2, p_mail_port pls_integer:=587,
                                                                             p_sender varchar2, p_password varchar2,
                                                                             p_recipients varchar2, p_subject varchar2, 
                                                                             p_message varchar2) is
                      v_mail_conn utl_smtp.connection;
                      raw_message	raw(32000);
                      raw_subject raw(32000);
                      
                    begin
                      v_mail_conn := utl_smtp.open_connection(p_mail_host, p_mail_port);
                      utl_smtp.ehlo(v_mail_conn, p_mail_host);
                      utl_smtp.command(v_mail_conn, 'AUTH LOGIN');
                      utl_smtp.command(v_mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(
                    utl_raw.cast_to_raw(p_sender))));
                      utl_smtp.command(v_mail_conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode(
                    utl_raw.cast_to_raw(p_password))));
                      utl_smtp.mail(v_mail_conn, p_sender);
                      utl_smtp.rcpt(v_mail_conn, p_recipients);
                      utl_smtp.open_data(v_mail_conn);
                      utl_smtp.write_data(v_mail_conn, 'Date: ' || to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') || utl_tcp.CRLF);
                      utl_smtp.write_data(v_mail_conn, 'From: ' || p_sender || utl_tcp.CRLF);
                      utl_smtp.write_data(v_mail_conn, 'To: ' || p_recipients || utl_tcp.CRLF);
                      
                    --  utl_smtp.write_raw_data(v_mail_conn, utl_raw.cast_to_raw('Content-Transfer-Encoding: base64'||
                    UTL_TCP.CRLF));
                      utl_smtp.write_data(v_mail_conn,'Content-Type: text/html; charset=UTF-8'||UTL_TCP.CRLF);
                      raw_subject := UTL_RAW.cast_to_raw(convert(p_subject,'AL32UTF8'));
                      raw_message:=utl_raw.cast_to_raw(convert(p_message,'AL32UTF8'));
                    
                    
                      utl_smtp.write_data(v_mail_conn, 'Subject: ');
                      utl_smtp.write_raw_data(v_mail_conn, raw_subject);
                      utl_smtp.write_data(v_mail_conn, utl_tcp.CRLF);
                      utl_smtp.write_raw_data(v_mail_conn, raw_message);
                      utl_smtp.write_data(v_mail_conn, utl_tcp.CRLF);  
                      
                      utl_smtp.close_data(v_mail_conn);
                      utl_smtp.quit(v_mail_conn);
                    end;


                    Этот код у меня успешно слал письма с Рамблера и Yahoo. Вызов происходит так:
                    begin
                      send_mail_with_authorization(p_mail_host => 'mail.rambler.ru',
                                                   p_mail_port => 587,
                                                   p_sender => 'my_mail@rambler.ru',
                                                   p_password => 'my_pass',
                                                   p_recipients => 'my_friend@gmail.com',
                                                   p_subject => 'Тема',
                                                   p_message => 'Текст письма.');
                    
                      send_mail_with_authorization(p_mail_host => 'smtp.mail.yahoo.com',
                                                   p_mail_port => 587,
                                                   p_sender => 'my_mail@yahoo.com',
                                                   p_password => 'my_pass',
                                                   p_recipients => 'my_friend@gmail.com',
                                                   p_subject => 'Тема',
                                                   p_message => 'Текст письма.');
                    end;


                    Теперь о ньюансах: их масса, и всех я не знаю, но то с чем столкнулся расскажу:
                    1. Иногда почта с Yahoo некорректно отображается на Gmail, т.е. я слал одно письмо с Yahoo на 3 разных почтовика, везде все ок, а на Gmail письмо приходит с пустым телом. Почему так — не знаю, но если то же самое письмо отправить с Рамблера, то Gmail его видит отлично.
                    2. Русский текст. Тут также «темна вода в облацеях» — вы може подобрать параметры, которые будут распознаваться большинством серверов, но на каком-то вы все-равно получите кроказябры. Таким образом идеал — когда вам нужно слать письма только пользователям одного почтового сервера, чтоб вы кастомизировали под него кодировку и тип сообщения (plain/html).
                    3. Рамблер странный, может мне просто не повезло с временем экспериментов, но суть такова: у меня есть ящик на Рамблере, я протестил указанную выше процедуру — через него она почту шлет отлично. Специально для нужн системы завел новый ящик, и через него почта не пошла: выпадают поочередно ошибки вида «Incorrect username/password» или «Internal server error». Работа с настройками ящиков ни к чему не привела — они одинаковы, возможно, на сервере стоит какой-то таймаут для использования smtp для новых ящиков, хотя звучит это как-то диковато.

                  Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                  Самое читаемое