QueryFailedError: Unknown column 'NaN' in 'where clause' at Query.onResult

Question

I'm encountering an issue with my Nest.js application after deploying it to Google Cloud Run. The application is designed to retrieve a store based on the client's IP address using the IP's decimal representation for querying the database. While it works perfectly on my local development environment (which uses DBeaver to manually create the database), I'm facing an issue after deploying it to Cloud Run, the database was also created manually for testing via DBeaver. However, I'm encountering the following error:

QueryFailedError: Unknown column 'NaN' in 'where clause' at Query.onResult (/node_modules/typeorm/driver/mysql/MysqlQueryRunner.js:158:37) at Query.execute (/node_modules/mysql2/lib/commands/command.js:36:14) at PoolConnection.handlePacket (/node_modules/mysql2/lib/connection.js:478:34) at PacketParser.onPacket (/node_modules/mysql2/lib/connection.js:97:12) at PacketParser.executeStart (/node_modules/mysql2/lib/packet_parser.js:75:16) at Socket. (/node_modules/mysql2/lib/connection.js:104:25) at Socket.emit (node:events:514:28) at addChunk (node:internal/streams/readable:324:12) at readableAddChunk (node:internal/streams/readable:297:9) at Readable.push (node:internal/streams/readable:234:10)

Context:

I have a service function getStoreIdByIP that takes the client's IP address as a string and converts it to a decimal representation using the ipToDecimal function.

The ipToDecimal function converts IP addresses to decimal format correctly.

The findStoreByIpNum function constructs the query using the calculated decimal value to find the corresponding store in the database.

Error Scenario:

Upon triggering the getStoreIdByIP function with a client's IP address, I encounter the QueryFailedError with the message "Unknown column 'NaN' in 'where clause'."

What I've Checked:

I've verified that the ipToDecimal function returns the expected decimal representation.

I've examined the query construction in the findStoreByIpNum function and ensured that it's using valid decimal values.

I've confirmed that the IP address passed to the ipToDecimal function is in the correct format.

I've reviewed my table definition and entity mapping to ensure they match.

Code Samples:

Here's an overview of relevant parts of my code:

// StoreController
@Get('store-id')
async getStoreByIP(@Req() req: Request): Promise<StoreIdDto | ProblemDto> {
    const ip = req.ip;
    return await this.storeService.getStoreIdByIP(ip);
}

// StoreService
async getStoreIdByIP(ip: string): Promise<StoreIdDto | ProblemDto> {
    const ipDecimal = ipToDecimal(ip);
    const store = await this.findStoreByIpNum(ipDecimal);

    // Rest of the code...
}

// ipToDecimal function
export function ipToDecimal(ip: string): number {
    // Conversion logic...
}

// findStoreByIpNum function
async findStoreByIpNum(ipDecimal: number): Promise<Stores> {
    return this.storeRepository
        .createQueryBuilder('stores')
        .where('stores.ip_min_num <= :ipDecimal', { ipDecimal })
        .andWhere('stores.ip_max_num >= :ipDecimal', { ipDecimal })
        .getOne();
}

I'm seeking assistance in identifying the root cause of the "Unknown column 'NaN' in 'where clause'" error and how to resolve it. I've checked the code and verified that the calculated decimal value is correct. Any insights or suggestions on where the issue might be originating would be greatly appreciated.

Thank you in advance for your help!

Answer 1

Check the value of ipDecimal you are passing to the querybuilder. It most certainly is not a valid number, thus the final query spells out as

where store.ip_min_num <= NaN 

instead of

where store.ip_min_num <= 17 

And because this NaN is not enclosed in quotes, it's interpreted as a column name, thus the error message.

Probably your ipToDecimal function receives an input it can't handle correctly. I don't say it's completly wrong, it may work correctly most of the time. But there seems to be some input it can't process correctly.

I'd suggest to add some exception handling in your getStoreIdByIP and whenever this exception occurs, write the the respective IP address string and the result from ipToDecimal to the log. Or you can just add a simple check like

if (isNaN(ipDecimal)) {
  //log ip address
}

befor passing ipDecimal on to the next function. This way you can identify and fix the issues with your conversion...