[SCRIPT] Fix malformed Xenforo [URL] strings with Python.

[MentaL]

I had an issue with incorrect URL structures on tags with my Xenforo build, to fix this we can use the following script below. PS: Backup your DB first!

pip3 install pymysql
pip3 install --upgrade pip setuptools wheel
pip3 install setuptools_rust
pip3 install cryptography

Script "update_images.py"

import pymysql
import re
import urllib.parse

# Database connection
conn = pymysql.connect(host='192.168.1.3', user='x', password='y', db='z')
cursor = conn.cursor()

# Select messages with [IMG] tags
cursor.execute("SELECT post_id, message FROM xf_post WHERE message LIKE '%[IMG]%'")
rows = cursor.fetchall()

# Regex for finding URLs in [IMG] tags
url_regex = r'\[IMG\](.*?)\[/IMG\]'

# Open a file for logging
with open('corrections_log.txt', 'a') as log_file:
    for row in rows:
        post_id = row[0]
        original_message = row[1]
        updated_message = original_message

        # Find all URLs within [IMG] tags
        urls = re.findall(url_regex, original_message)
        for url in urls:
            # Decode each URL
            decoded_url = urllib.parse.unquote(url)
            # Replace the entire [IMG]...[/IMG] tag with the corrected URL
            updated_message = updated_message.replace(f'[IMG]{url}[/IMG]', f'[IMG]{decoded_url}[/IMG]')

        # Log the changes if the message was updated
        if updated_message != original_message:
            log_file.write(f"Post ID: {post_id}, Original: {original_message}, Updated: {updated_message}\n")

            # Update the database record
            cursor.execute("UPDATE xf_post SET message = %s WHERE post_id = %s", (updated_message, post_id))
            conn.commit()

# Close the connection
cursor.close()
conn.close()

print("Corrections completed and logged.")

Execute the script via...

python3 update_images.py

Done. It'll log the changes to corrections_log.txt in the directory you executed the script.

Log Example (Before / After)

Post ID: 5112757, Original: [b]Re: The Strict Shack - Feel The Burn[/b]

[img]http://www.airsoftireland.com/gallery/albums/userpics/10514/normal_2009-Inter_%28151%29.JPG[/img]

random pics from yesterday time, Updated: [b]Re: The Strict Shack - Feel The Burn[/b]

[img]http://www.airsoftireland.com/gallery/albums/userpics/10514/normal_2009-Inter_(151).JPG[/img]

random pics from yesterday time

As for my issues? Fixed!

Enjoy,
MentaL

# Update

22/11/23 ; fixed some issues where some urls would get missed.