Dovecot Sieve : Run Bash shell Scripts to query Mysql

3 simple scripts to determine Out Of Office emails for Dovecot

First, the Sieve file

GNU nano 6.2                                                                                                          /var/lib/dovecot/sieve/default.sieve                                                                                                                   
require ["fileinto", "vacation", "envelope", "vacation-seconds", "variables", "vnd.dovecot.pipe", "vnd.dovecot.execute"];

if header :matches "subject" "*" {
  set "subject" "${1}";
}

if envelope :matches "To" "*@*" {
  set "recipient" "${0}";
  set "user" "${1}";
  set "recip_domain" "${2}";
}


if header :contains "X-Spam-Flag" "YES" {
  fileinto "Junk";
} else {
if address :is "to" "hello@mycompany.co.za"
{
      vacation :days 1 "Good Day,

      Thank you for contacting My Comapny Website.
      We are currently closed for our 2023 Holidays.

      All emails/inquiries & orders will only be processed/seen on 15 January 2024.

      Wishing you a wonderful & safe Festive Season.

      Kind regards,
      The My Company Team.";
}
else
{
if execute :output "script_return_value" "outOfOffice.sh" ["${recipient}","300"]
{
    if string :matches "${script_return_value}" "1" {
            if execute :output "my_vacation_message" "get-message.sh" "${recipient}" {
                vacation
                :days 1
                :subject "Automatic reply : ${subject}"
                :mime "MIME-Version: 1.0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
${my_vacation_message} ${recipient}";
}
}
}
}
}

Then the two Bash Scripts

# Example Bash script
# File: get-message.sh
# Usage: ./get-message.sh email@address

MYSQL_CONFIG_FILE="/etc/mysql/mysql_config.conf"

DB_NAME="mailserver"

# Get username from command line argument
USERNAME="$1"

# Query the database to check out-of-office status


#echo "Received email address: $USERNAME"

OUT_OF_OFFICE_MESSAGE=$(mysql --defaults-extra-file=${MYSQL_CONFIG_FILE} -D "$DB_NAME" -e "SELECT message FROM OutOfOfficeEmailContent LEFT JOIN virtual_domains ON OutOfOfficeEmailContent.domain_id = virtual_domains.id LEFT JOIN virtual_users ON virtual_users.domain_id >

echo "$OUT_OF_OFFICE_MESSAGE"

# File: outOfOffice.sh
# Usage: ./outOfOffice.sh email_address

MYSQL_CONFIG_FILE="/etc/mysql/mysql_config.conf"

# Replace with your database credentials
#DB_USER="gavin"
#DB_PASSWORD="Kimnatgav@2011"

DB_NAME="mailserver"

# Get username from command line argument
USERNAME="$1"

# Query the database to check out-of-office status
#OUT_OF_OFFICE=$(mysql -u"$DB_USER" -p"$DB_PASSWORD" -D "$DB_NAME" -e "SELECT OutOfOffice FROM virtual_users WHERE email='$USERNAME';" --skip-column-names)

OUT_OF_OFFICE=$(mysql --defaults-extra-file=${MYSQL_CONFIG_FILE} -D "$DB_NAME" -e "SELECT OutOfOffice FROM virtual_users WHERE email='$USERNAME';" --skip-column-names)


echo -n "$OUT_OF_OFFICE"