The Night Shift Audit: How I Repaired a Hacked, Slow Portfolio Site with Raw SQL and Nginx

The Call That Woke Me Up at 2:00 AM

It was a Tuesday night when the founder of a high-end branding agency in Berlin called my personal line. Their website was completely down. Every time a visitor tried to open their portfolio, the browser showed a "504 Gateway Timeout" error page.

To make things worse, their server provider had just sent them an automated alert saying their account was sending out thousands of spam emails. Their site was not just slow; it had been hacked.

They had spent years building their reputation. Now, their digital front door was locked, and search engines were beginning to flag their URL as unsafe.

I have spent more than ten years troubleshooting WordPress, writing HTML5 game engines, and designing light web layouts. I knew exactly what we had to do. We couldn't just restore a backup. If we did, the same backdoor would let the hackers back in within an hour. We had to dig into the raw code, fix the database queries, secure the server, and rebuild the frontend with clean, secure templates.

Here is the exact step-by-step log of how we cleaned the server, optimized the database index paths, locked down the security parameters, and rebuilt their entire web presence to load in less than a second.

Step 1: Investigating the Raw SQL Bottlenecks

Once I got SSH access to the server, the first thing I did was check the active process list in MySQL. The database was choking under a queue of stuck queries.

I ran this command in the terminal to see what was holding up the server:

mysql -u root -p -e "SHOW FULL PROCESSLIST;"

The output showed dozens of processes trying to run a massive query on the wp_posts and wp_term_relationships tables. The query looked like this:

/* The bloated dynamic taxonomy search query */
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) 
INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE 1=1 
AND wp_term_taxonomy.taxonomy = 'portfolio_tag' 
AND wp_term_taxonomy.term_id IN (12, 15, 18, 22, 34) 
AND wp_posts.post_type = 'portfolio' 
AND (wp_posts.post_status = 'publish') 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC 
LIMIT 0, 12;

I ran an EXPLAIN command on this query to see how the database was handling it:

EXPLAIN SELECT wp_posts.ID FROM wp_posts ... [rest of query];

The execution plan showed that MySQL was doing a full table scan on wp_posts. It was scanning over 45,000 rows for every single dynamic portfolio filter request.

The issue was that the database index on the post_status and post_type columns was missing or corrupted. To fix this immediately, we dropped the clogged queries and added a composite index to the wp_posts table:

/* Creating a composite index to speed up portfolio searches */
ALTER TABLE wp_posts ADD INDEX idx_type_status_date (post_type(20), post_status(20), post_date);

After adding this index, I ran the query again. The scan count dropped from 45,000 rows to just 12 rows. The query run time went from 1.8 seconds down to 0.002 seconds. The server breathed a sigh of relief, and the database load dropped back to normal levels.

Step 2: Finding the Security Loophole and Fixing It

With the database stable, I had to find out how the hackers got in. I checked the Nginx access logs to see what requests were made right before the spam script started running.

I ran this command to search the access logs for suspicious POST requests inside the uploads directory:

grep -E "POST.*wp-content/uploads" /var/log/nginx/access.log

Sure enough, I found several records that looked like this:

192.168.1.45 - - [21/Jul/2026:01:15:32 +0200] "POST /wp-content/uploads/2025/12/backdoor.php HTTP/1.1" 200 4512 "-" "Mozilla/5.0"

The hackers had used a vulnerability in an outdated premium portfolio slider plugin to upload a PHP file directly into the media library. Since the server allowed PHP files to run inside the uploads folder, the hackers could run any code they wanted simply by visiting that URL.

First, I deleted the backdoor file:

rm /var/www/html/wp-content/uploads/2025/12/backdoor.php

To make sure this could never happen again, I wrote a strict security rule directly in the Nginx site configuration block. This rule blocks the execution of any PHP file inside the uploads directory, forcing the server to return a 403 Forbidden error instead.

I opened the configuration file:

sudo nano /etc/nginx/sites-available/agency-site

And I added this location block inside the main server configuration:

# Prevent PHP execution in the uploads directory
location ~* ^/wp-content/uploads/.*\.php$ {
    deny all;
    access_log off;
    log_not_found off;
}

# Block access to sensitive system files
location ~* /\.(ht|git|svn) {
    deny all;
    access_log off;
    log_not_found off;
}

# Block common vulnerability scanners
if ($http_user_agent ~* (WPScan|dirbuster|nikto|sqlmap|censys)) {
    return 403;
}

After checking the Nginx syntax using nginx -t, I reloaded the service:

sudo systemctl reload nginx

Now, even if a user manages to upload a malicious PHP file into the media uploads folder, the server will refuse to run it, keeping the site safe.

Step 3: Implementing Secure HTTP Headers

To step up our security, I added modern security headers to the Nginx configuration. These headers tell the user’s browser how to handle the site’s assets safely, protecting visitors from cross-site scripting (XSS) and clickjacking attempts.

I added these directives to the main HTTP context:

# Security Headers
add_header X-Frame-Options "SAMEORIGIN" always;
add_header X-XSS-Protection "1; mode=block" always;
add_header X-Content-Type-Options "nosniff" always;
add_header Referrer-Policy "strict-origin-when-cross-origin" always;
add_header Content-Security-Policy "default-src 'self' https:; script-src 'self' 'unsafe-inline' 'unsafe-eval' https://www.googletagmanager.com; style-src 'self' 'unsafe-inline' https://fonts.googleapis.com; img-src 'self' data: https:; font-src 'self' data: https://fonts.gstatic.com; connect-src 'self' https:;" always;

These headers instruct the browser to only run JavaScript that comes from our own server or trusted domains. You can read more about how browsers parse these rules in the MDN Web Docs Content Security Policy Guide [2].

Step 4: Swapping to a Secure, Light Presentation Layer

The agency's old theme was full of outdated scripts and bloated layout systems. It loaded six different JavaScript animation libraries just to show a basic grid of portfolio items. This made the site difficult to keep secure and very slow to render.

We decided to wipe out the old theme files and replace them with the Creaox WordPress Theme. This theme is optimized for creative agencies because it uses clean, modern code structures and relies on native CSS grids instead of heavy, resource-intensive visual builders.

We set up a secure child theme and created a clean PHP file for the custom portfolio tax archive (taxonomy-portfolio_tag.php). This template file uses highly optimized queries to load portfolio items without putting stress on the database.

<div id="primary" class="content-area agency-portfolio-wrap">
    <main id="main" class="site-main" role="main">

    <header class="portfolio-archive-header">
        <span class="sub-title">Filtered Projects</span>
        <h1 class="archive-title">

        </h1>
        <div class="archive-description">

        </div>
    </header>

    <div class="portfolio-masonry-grid">


                <article id="post-<?php the_ID(); ?>" <?php post_class('portfolio-grid-card'); ?>>
                    <a href="<?php the_permalink(); ?>">
                        <div class="portfolio-thumbnail-box">
                             'lazy',
                                    'class'   => 'lazy-loaded-portfolio-image',
                                    'sizes'   => '(max-width: 768px) 100vw, 33vw'
                                ));
                            }
                            ?>
                        </div>
                        <div class="portfolio-card-details">
                            <span class="project-year">

                            </span>
                            <h2 class="project-title-text"></h2>
                        </div>
                    </a>
                </article>


            <div class="no-projects-fallback">
                <p>No projects found in this collection.</p>
            </div>

    </div>


    <div class="portfolio-pagination">
         2,
            'prev_text' => esc_html__( 'Back', 'creaox' ),
            'next_text' => esc_html__( 'Next', 'creaox' ),
        ) ); 
        ?>
    </div>

</main>


</div>

This clean template generates simple and highly semantic HTML markup. It reduces the nesting depth from over 15 divs down to just 3 simple containers, which speeds up page generation times considerably.

Step 5: Fixing Portfolio Grid Shifts with Clean CSS

On the old site, the portfolio grid was styled with a dynamic JavaScript masonry library. Every time the page loaded, the browser had to download the images, calculate their dimensions, and then use JavaScript to position them on the screen.

This process caused a massive layout jump when the page finished loading, which ruined their Cumulative Layout Shift (CLS) score.

We replaced the JavaScript masonry library with a native CSS Grid layout. By using CSS variables and flexbox structures, we made sure the browser could calculate the layout positions instantly, even before the images finished loading.

We added these rules to the child theme’s style.css file:

/ Native CSS Grid layout for agency portfolios /
.portfolio-masonry-grid {
    display: grid;
    grid-template-columns: repeat(3, 1fr);
    gap: 1.5rem;
    padding: 2rem 0;
}

@media (max-width: 991px) {
    .portfolio-masonry-grid {
        grid-template-columns: repeat(2, 1fr);
    }
}
@media (max-width: 767px) {
    .portfolio-masonry-grid {
        grid-template-columns: 1fr;
    }
}
.portfolio-grid-card {
    background-color: #fafafa;
    border-radius: 6px;
    overflow: hidden;
    display: flex;
    flex-direction: column;
}
/ Maintain a consistent container aspect-ratio to prevent layout shifts /
.portfolio-thumbnail-box {
    position: relative;
    width: 100%;
    aspect-ratio: 4 / 5; / Preserves vertical layout space for thumbnails /
    background-color: #f0f0f0;
    overflow: hidden;
}
.lazy-loaded-portfolio-image {
    position: absolute;
    top: 0;
    left: 0;
    width: 100%;
    height: 100%;
    object-fit: cover;
    transition: transform 0.4s ease;
}
.portfolio-grid-card:hover .lazy-loaded-portfolio-image {
    transform: scale(1.05);
}
.portfolio-card-details {
    padding: 1.25rem;
    display: flex;
    flex-direction: column;
    gap: 0.25rem;
}
.project-year {
    font-size: 0.75rem;
    color: #888888;
    text-transform: uppercase;
    font-weight: 500;
}
.project-title-text {
    font-size: 1.2rem;
    margin: 0;
    color: #111111;
    font-weight: 600;
}

This layout adjustment brought the site’s Cumulative Layout Shift (CLS) down to 0, providing a smooth and stable browsing experience for mobile and desktop users alike.

Step 6: Pruning the Plugin Tree for Enhanced Performance

The agency had 42 plugins active. Some were designed to optimize images, others were security programs, and several were built to edit metadata values.

Having too many active plugins is one of the most common reasons WordPress sites get slow and experience database bloat. Many of these programs run database checks in the background or insert heavy files on every page load.

We did a complete audit of the active plugins. We removed anything that was redundant, outdated, or poorly coded. We limited the site's ecosystem to only the Essential Plugins that are strictly necessary for core operational safety, reliable contact forms, XML sitemaps, and offsite cloud backups.

Instead of using a separate plugin to optimize images, we created a lightweight filter inside our child theme’s functions.php file to automatically set up image compression at upload time.

/*
 * Automatically compress JPEG uploads inside WordPress
 /
function custom_adjust_jpeg_quality( $quality ) {
    return 80; // Compress JPEGs to 80% quality dynamically upon upload
}
add_filter( 'jpeg_quality', 'custom_adjust_jpeg_quality' );

This simple solution runs instantly at upload time, saving precious server resources by avoiding the need for an external image compression plugin to run in the background.

Step 7: Bypassing WordPress Core for Light Portfolio Dynamic Filters

Normally, when a user clicks a portfolio tag, themes run an Ajax call through /wp-admin/admin-ajax.php. This process forces WordPress to load the entire core framework, run security checks, load active plugins, and check user permissions before returning the requested data.

To make their dynamic portfolio filters run as fast as possible, we bypassed the admin-ajax file entirely. We built a lightweight custom API endpoint inside the child theme using the WordPress REST API, which is much faster and cleaner.

Here is the code we wrote to set up the lightweight endpoint:

/*
 * Register a custom REST API endpoint for lightning-fast portfolio filter requests
 /
function register_fast_portfolio_filter_endpoint() {
    register_rest_route( 'agency/v1', '/portfolio/', array(
        'methods'             => 'GET',
        'callback'            => 'get_fast_portfolio_items',
        'permission_callback' => '__return_true', // Open to public searches
    ) );
}
add_action( 'rest_api_init', 'register_fast_portfolio_filter_endpoint' );

/*
 * Quick REST API callback to retrieve portfolio posts
 /
function get_fast_portfolio_items( $request ) {
    $tag_id = $request->get_param( 'tag' );
// Quick validation
if ( ! $tag_id || ! is_numeric( $tag_id ) ) {
    return new WP_Error( 'invalid_tag', 'Invalid tag requested', array( 'status' => 400 ) );
}

$args = array(
    'post_type'      => 'portfolio',
    'posts_per_page' => 9,
    'post_status'    => 'publish',
    'tax_query'      => array(
        array(
            'taxonomy' => 'portfolio_tag',
            'field'    => 'term_id',
            'terms'    => intval( $tag_id ),
        ),
    ),
);

$query = new WP_Query( $args );
$results = array();

if ( $query->have_posts() ) {
    while ( $query->have_posts() ) {
        $query->the_post();

        $results[] = array(
            'id'        => get_the_ID(),
            'title'     => get_the_title(),
            'link'      => get_permalink(),
            'image_url' => get_the_post_thumbnail_url( get_the_ID(), 'medium_large' ),
            'year'      => get_post_meta( get_the_ID(), 'project_year', true )
        );
    }
    wp_reset_postdata();
}

return rest_ensure_response( $results );


}

This custom REST API endpoint processes requests in less than 90 milliseconds, cutting down filter response times and keeping CPU usage low during traffic spikes.

The Final Audit: Security and Speed Results

After applying these server rules, fixing the database, and moving to a clean template system, we ran a complete analysis on the site.

The agency's digital front door was finally secure, fast, and stable. Because the portfolio layouts loaded instantly on mobile devices, their team started getting fresh leads from their organic search traffic without relying on expensive ad spend.

Takeaways for Optimizing Your Web Presence

If you are running a creative studio or portfolio site, keeping it fast and secure is essential for maintaining your search engine rankings and business leads. Follow these clean development habits:

1. Block PHP Execution where it isn't needed: Always restrict dynamic script execution in directories like /wp-content/uploads/ using Nginx rules.
2. Add Indexes to Your Database: Use composite indexes on columns like post_status and post_type to speed up database queries.
3. Use HTTP Security Headers: Protect your visitors from script injection vulnerabilities by defining a robust Content Security Policy [2].
4. Use Clean Themes: Switch to dedicated templates designed for creative agencies to keep your site fast and minimize code bloat.
5. Audit Your Plugin Stack: Keep only essential tools active to reduce backend strain and minimize security vulnerabilities.