The Mysterious Case of the Query That Never Ran

Over the past weekend I encountered a weird bug.

I was refactoring code for my personal website. I am a casual runner and I have a page with all my run records. The data for it are stored in Neon database and I am reading them through my own custom nuxt-neon module.

I was updating the code to reflect the latest changes in my module. Namely I tried to change the filtering by date (year and/or month). Originally, I use literal string WHERE condition r.date BETWEEN '${fromDate}' AND '${toDate}'. Now I planned to battle-test my new object syntax:

where.push({ 
  column: 'r.date', 
  condition: '>=',
  value: fromDate,
});
where.push({ 
  column: 'r.date', 
  condition: '<=', 
  value: toDate, 
  operator: 'AND' 
});

Now, you may argue this is much more complicated and you'd probably be right. But I wanted to test how it works beyond sterile tests defined inside my module. And...

It was a complete failure.

No data. Just an ugly HTTP error when trying to get data from the Nuxt server endpoint:

NuxtNeonClientError in fetchFromNeonBackend: 
[POST] "/api/_neon/select": 400 Bad Request (status: 500). 

To make a quick explanation - my module is providing a client-side composable useNeon that exposes couple of wrappers for SQL functions (namely select in this case). Those wrappers are essentially just forwarding input query to Nuxt server endpoints for each SQL function. Like this, I can keep the DB credentials safely stored on the server side and not leak them via runtime config into the browser. AFAIK Neon does not provide safe public API keys as services like Supabase do.

So I started to investigate what might be wrong.

At first, I thought I messed up the implementation, and I am constructing a bad SQL query. But there were two "buts" to this theory:

1) The very same query worked just fine when I reproduced it as a test case in my module codebase. And in fact, I was already testing whether "greater than / less than" operators pick the right data.
2) Despite I set enhanced debug logs for my module (you can debug both the execution flow and the actual SQL produced inside the module), I saw no data for my request. Actually, it looked like the API endpoint wasn't even called.

I found myself desperately tampering with the files inside my node_modules folder trying to track down where I am and why I am not seeing any usable output to understand the issue.

I had a faint suspicion that the literal characters > and < should be the root issue. But no viable theory of why it would matter.

I gave it a one-day break and unsurprisingly I didn't get any brilliant idea overnight. As a last resort, I just tried to ask my Copilot. I was postponing this move, because what would my question be? I don't understand what is going on, how should I instruct mindless LLM to come up with a solution? Without good prompt, I can't get good result, can I?

Well no, but actually yes. Copilot didn't magically save the day. But it gave me one important clue to follow: "Reproduce with curl (bypasses browser and frontend)"

This means, just call the API endpoint directly with a bare HTTP request. Strip it down from all frontend framework noise. Then you might understand better what is breaking up before backend can process your request. So I did as machine mind said and voilà!, everything clicked together.

Turned out my request was being rejected by Nuxt Security module and its XSS attack protection:

...\node_modules\.pnpm\nuxt-security@2.4.0_magicast@0.3.5_rollup@4.52.5\
node_modules\nuxt-security\dist\runtime\server\middleware\
xssValidator.js:38:18

Like that, it is pretty obvious. The problem was this particular line was swallowed inside Nuxt leaving me only with a cryptic error with no apparent reason.

Naturally, it was because of the > and < brackets. Nuxt Security treats them as potentially malicious attempt to smuggle a script into the payload. Between me and you - this module is pretty good in protecting your Nuxt applications with little to no effort of setting it up, but then it keeps biting you back like that.

So this was the missing piece. It worked in my tests because the naive demo app is not concerned about security that much. But I chose to shield my homepage with more advanced protection, and it backfired.

To mitigate the problem, I am adding an internal mapping that translates angle brackets to text abbreviations on client and back when constructing the actual SQL query on the server. For user convenience I decided to keep them available instead of forcing everyone to learn they should use GT and LT abstraction.

Despite the root cause and the solution itself isn't groundbreaking, I still thought the unique combination of circumstances is worth an article. I wanted to share following lessons learned from this debugging struggle:

1. Don't be shy to ask AI for help - Even if you don't know the right question yet, just ask: "Why do I have this error?" Start explaining the situation and you may vibedebug the solution eventually. It keeps surprising me how effective this becomes lately.
2. Isolate the problem - If backend seems not to respond properly, call it directly to abstract from possible interference. I should have known this after all those years in software development, yet I got lost in the frontend land and needed virtual assistant to pull me out.
3. Take 3rd party SW in account - Maybe it is not just your code that causes the trouble. Maybe some other library is acting up (because of something in your code).

I have been developing software for at least two decades now. I feel like a senior and being treated as such. But as you can see, I keep getting stuck in trivial situations and feeling rather stupid afterwards. I believe this is part of the process. You keep running into the walls, keep falling down and keep raising up, being ready for more.

Thank you for reading this and may it help someone in the future. Until the next awkward issue to solve.