Redis (raw) command parameters not working as expected

[Dragos_Vingarzan]

• Goal: I'm trying to compose&run a Redis query with LSET
• Steps: Command - use a raw command, then I'm adding something like "LSET {{ param1 }} {{ param2 }} {{ param3 }}"
• Details: param3 contains a JSON encoded object. I have already escaped quotes, new-lines, etc, to make it work according to Redis CLI | Docs. If I inspect the state, the query.rawCommand attribute seems correct (I also can paste and run it successfully in a redis.cli)

rawCommand
"LSET "hss:default:imsu:Subscriber 1234" 0 "{"Name":"Subscriber 1234 - SIM-0","IMPI":"001011234@ims.mnc001.mcc001.3gppnetwork.org","IMSI":"001011234567"}""

But this input box on this forum here might have a bug and replace my backslashes... so here's a screenshot:

• Error: I'm getting though "ERR wrong number of arguments for 'lset' command". I have checked with Wireshark and indeed, the command is split weirdly on the wire, at places like escaped quotes in the last parameter (a.i. ")

So my question is: how does this work? I tried also not escaping the JSON encoded string, but of course, that's even worse.

[Jack_T]

Hello @Dragos_Vingarzan,

This sounds like an issue with our query builder, which is altering your query's arguments.

You may need to try turning prepared statements off, which will give you greater control over the end formatting of your query at the risk of taking off any guard rails Retool has to prevent SQL injections or malicious queries.

[Dragos_Vingarzan]

Hi @Jack_T !

It does sound like a probably interference.

Unfortunately, the Redis Resource Connector doesn't seem to have an option to turn off prepare statements, or I can't see it in the connection configuration.

Cheers,
-Dragos

[Jack_T]

Hi @Dragos_Vingarzan,

Ah ok, good to know. Let's see if we can get the formatting set correctly in the query builder.

This is what I have set up so far to reproduce the query, let me know if this is similar to how you have it set up.

I need to spin up Redis for the resource so just want to make sure this looks right before I go to the next step of testing

[Dragos_Vingarzan]

Hi @Jack_T,

and thank you for pushing on this issue!

Unfortunately, I think Discourse was fighting me on raw text that I pasted here. So you can see in a screen-shot image (attached to my initial post) exactly what I was sending.

I tried to follow this Redis CLI | Docs when escaping the JSON encoded string.

Cheers,
-Dragos

[Dragos_Vingarzan]

Here is also my testing (and probably too simple, but the example LGTM) escaping function:

function toEscapedJSON(obj) {
  return escapedJSON(JSON.stringify(obj));
}

function escapedJSON(obj) {
  return obj
    .replace(/\\/g, '\\\\')
    .replace(/"/g, '\\"')
    .replace(/\n/g, '\\n')
    .replace(/\r/g, '\\r')
    .replace(/\t/g, '\\t');
}

[Jack_T]

Good thinking with the custom function to try to escape the double quotes around the JSON that is nested inside the query.

I am worried that if that didn't work then this might be a bug for me to file a report on

One more idea to test out.

We could try formatting the raw query to be

LSET "hss:default:imsu:Subscriber 1234" 0 "{\"Name\":\"Subscriber 1234 - SIM-0\",\"IMPI\":\"001011234@ims.mnc001.mcc001.3gppnetwork.org\",\"IMSI\":\"001011234567\"}"

We might be able to do this my constructing the string in a JS Query block and then seeing if we can pass this directly into the query's raw input field.

const key = "hss:default:imsu:Subscriber 1234";
const index = 0;
const value = JSON.stringify({
  Name: "Subscriber 1234 - SIM-0",
  IMPI: "001011234@ims.mnc001.mcc001.3gppnetwork.org",
  IMSI: "001011234567"
});

const query = `LSET "${key}" ${index} "${value}"`;
return query

Let me know if that works If not I can file a bug report that we are not able to escape inner quotes when passing JSON as an argument to a raw query.

[Dragos_Vingarzan]

I think you meant to have a function which returns the whole query, and then in the Resource-Query to pass that whole thing as a parameter, like this:

If I do this, then I'm getting the following similar result (added some extra logs in the console, for debugging):

Or maybe I misses something in how to call the Resource-Query? I'm pretty new to it and my JS is also rusty... What I did was that I had the Save action on a table row trigger my funcSaveSIMCard function which returns the query now and then trigger also the redisSaveSIMCard2 function. I feel like I'm doing something wrong there, as the order of the function calls is important, but the UI won't let me reorder the calls...

[Jack_T]

Hi @Dragos_Vingarzan,

Yes correct, I was thinking if we have a function return the single entire argument as a string directly into the raw query that would be the closest we can get in the front end to setting the format.

Just tested out the query on a Redis resource and reproduced the error.

This is a bug on our end in the applications backend/Redis DB connector, the query builder can run into parsing issues when grabbing stringified objects. I can pass thing along to our engineering teams and keep you updated on this.

Is your use case involving Redis for saving data for preserve stateful data from the Retool front end?

To your point on the order of function calls, it looks like you are doing it correctly. Running the save func then the redis LSET function. You could try chaining the redis query to the "on success" handler for the save function to have them run synchronously.

[Dragos_Vingarzan]

Yes, I'm trying to have a basic CRUD to a Redis back-end. I might move to Postgres for this particular UI, but would be cool to have full capabilities with Redis too, since other components will stay on Redis. Those would benefit from being able to edit and delete data.

Cool tip with the chaining on success! That sounds cleaner/better than what I was doing. Thanks!

And of course, thanks for the follow-up and tracking!

[Jack_T]

Of course no problem!

I would say that our Postgres DB connectors have been battle tested and should work really well for most use cases

Also I have one more idea to try for the Redis query, give this a shot and let me know if it works.

Where you split up the spring into query2.data and the object that you are going to convert to a string as query3.data

This should pass 3 arguments as expected (key, index, and element).

[Dragos_Vingarzan]

Nope, unfortunately I'm getting the same thing - the parameters are not split correctly and the server complains.

I used double-quotes mainly because that's what the Redis documentation indicates for string definitions, so was hopefully the logical choice for a raw query.

[Jack_T]

Ah sorry to hear that.

Just got a ping from an engineer who is looking at this so hopefully I will have some good news soon.

Defiantly an issue on our end with the query builder. Apologies again.

[Dragos_Vingarzan]

I'm switching to PostgreSQL... but there I'm hitting another issue where I issue "ORDER BY" (with server-side pagination), yet the results are always ordered by primary key... I found a thread similar to my problem Sort columns when server side pagination is enabled

Turning off prepared queries helps... but it's dangerous...

[Jack_T]

Ah yes we have a ticket for that with our engineering team.

Apologies for the workaround needing to have prepared statements turned off, I completely understand the security concerns with that option.

I just added your +1 to that ticket and linked this thread to the ticket as well, so if there is any news on changes made so that 'order by' can work with server-side pagination I will let you know in this thread!