Erratic behaviors with LIKE and %

[rosdec]

Ciao,

the Edge functions seem picky when the LIKE operator and the wildcard % are involved. I'm fiddling with a straightforward query. I rewrote it in SQL, Typescript, and JS. The point is that strange errors arise when I use LIKE with the %.

The code below, in particular, works in the Edge function editor but, once deployed, it returns a 500:

const gamename = request.params.gamename;
const platform = request.params.platform;

const result = await connection.sql(`
    SELECT * FROM db_sito 
    WHERE ((nome_originale LIKE "%${gamename}%") OR (nome LIKE "%${gamename}%")) 
        AND (console = "${platform}")
`);


return {
    data: {
        result,
        request
    }
};

And, when deployed, I get:

{
  "error": {
    "status": "500",
    "title": "TypeError: undefined is not an object (evaluating 'connection.sql')",
    "detail": "GET /v2/functions/search an error occured"
  }
}

Any hints?

Thank you for your work, this is a nice platform!

[danielebriggi]

Thank you rosdec, could it be that you are not setting the API KEY in the configuration of the edge function and in the request as Bearer token? I get this kind of message in that case and we need to improve it. The API KEY must be set in the edge function or in the request when you want to use the connection.sql() function.

I suggest you to use this syntax to ensure parameters to be escaped:

await connection.sql(`
    SELECT * FROM db_sito 
    WHERE ((nome_originale LIKE ?) OR (nome LIKE ?)) 
        AND (console = ?)
`, `%${gamename}%`, `%${gamename}%`, platform);

[rosdec]

This is a solution for sure. And a pretty obvious one once it's on my screen 😄

Still, there are some inconsistencies when the '%' character is involved in the query.

Thank you for the prompt response.

Cheers

[danielebriggi]

what kind of inconsistency do you have with % in the query? Do you have troubles in the second syntax as well?

If you refer to the case where the variable contains the % character then the % must be escaped with \, like:
gamename.replace(/[%_]/g, char => \${char})

and the query must explicit it in this way ESCAPE '\\':
``WHERE ((nome_originale LIKE ? ESCAPE '\') OR (nome LIKE ? ESCAPE '\'))
AND (console = ?)