#!/bin/bash

DB_USER="uosolutions"
DB_HOST="10.124.0.3"

echo -n "MySQL password: "
read -s DB_PASS
echo

MYSQL="mysql -u${DB_USER} -p${DB_PASS} -h ${DB_HOST} -N -B"

DATABASES=$($MYSQL -e "SHOW DATABASES;" 2>/dev/null)

MODIFIED_DBS=()

USHORT_REGEX="(urshort|ushort|u-short|urshort\.live)\\.[a-z0-9.-]+"
USHORT_URL_REGEX="https?:\/\/[^\"\\'[:space:]]*(urshort|ushort|u-short|urshort\.live)[^\"\\'[:space:]]*"


for DB in $DATABASES; do

  DB_MODIFIED=0

  POSTS_TABLES=$($MYSQL -e "
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema='${DB}'
    AND table_name LIKE '%posts';
  " 2>/dev/null)

  OPTIONS_TABLES=$($MYSQL -e "
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema='${DB}'
    AND table_name LIKE '%options';
  " 2>/dev/null)

  for POSTS_TABLE in $POSTS_TABLES; do

    AFFECTED=$($MYSQL -e "
      SELECT COUNT(*)
      FROM \`${DB}\`.\`${POSTS_TABLE}\`
      WHERE
        post_content REGEXP '<meta[^>]+http-equiv=[\"\\']refresh[\"\\']'
        OR post_content REGEXP '<script[^>]*>[^<]*window\\.location'
        OR post_content REGEXP '${USHORT_REGEX}'
        OR post_content LIKE '%vEk0r4%';
    " 2>/dev/null)

    if [ "$AFFECTED" != "" ] && [ "$AFFECTED" -gt 0 ]; then
      echo "[+] Limpiando $DB.$POSTS_TABLE ($AFFECTED filas)"

      $MYSQL -e "
        UPDATE \`${DB}\`.\`${POSTS_TABLE}\`
        SET post_content =
          REGEXP_REPLACE(
            REGEXP_REPLACE(
              REGEXP_REPLACE(
                REGEXP_REPLACE(
                  post_content,
                  '<meta[^>]+http-equiv=[\"\\']refresh[\"\\'][^>]*>',
                  ''
                ),
                '<script[^>]*>[^<]*window\\.location[^<]*</script>',
                ''
              ),
              '<script[^>]*>[^<]*${USHORT_REGEX}[^<]*</script>',
              ''
            ),
            '${USHORT_URL_REGEX}',
            ''
          )
        WHERE
          post_content REGEXP '${USHORT_REGEX}'
          OR post_content LIKE '%vEk0r4%';
      "

      DB_MODIFIED=1
    fi
  done

  for OPTIONS_TABLE in $OPTIONS_TABLES; do

    AFFECTED=$($MYSQL -e "
      SELECT COUNT(*)
      FROM \`${DB}\`.\`${OPTIONS_TABLE}\`
      WHERE
        option_value REGEXP '<script'
        OR option_value REGEXP '<meta'
        OR option_value REGEXP '${USHORT_REGEX}'
        OR option_value LIKE '%vEk0r4%';
    " 2>/dev/null)

    if [ "$AFFECTED" != "" ] && [ "$AFFECTED" -gt 0 ]; then
      echo "[+] Limpiando $DB.$OPTIONS_TABLE ($AFFECTED filas)"

      $MYSQL -e "
        UPDATE \`${DB}\`.\`${OPTIONS_TABLE}\`
        SET option_value =
          REGEXP_REPLACE(
            REGEXP_REPLACE(
              REGEXP_REPLACE(
                REGEXP_REPLACE(
                  option_value,
                  '<script[^>]*>.*?</script>',
                  ''
                ),
                '<meta[^>]*>',
                ''
              ),
              'window\\.location[^;]*;',
              ''
            ),
            '${USHORT_URL_REGEX}',
            ''
          )
        WHERE
          option_value REGEXP '${USHORT_REGEX}'
          OR option_value REGEXP '<script'
          OR option_value REGEXP '<meta'
          OR option_value LIKE '%vEk0r4%';
      "

      DB_MODIFIED=1
    fi

    $MYSQL -e "
      UPDATE \`${DB}\`.\`${OPTIONS_TABLE}\` h
      JOIN \`${DB}\`.\`${OPTIONS_TABLE}\` s
        ON s.option_name='siteurl'
      SET h.option_value=s.option_value
      WHERE h.option_name='home';
    "

    DB_MODIFIED=1

  done

  if [ "$DB_MODIFIED" -eq 1 ]; then
    MODIFIED_DBS+=("$DB")
  fi

done

echo
echo "Bases de datos modificadas:"
if [ ${#MODIFIED_DBS[@]} -eq 0 ]; then
  echo " - Ninguna"
else
  for DB in "${MODIFIED_DBS[@]}"; do
    echo " - $DB"
  done
fi