Bug: Query() returning wrong data

[mrmagic223325]

Describe the bug

I'm using the .NET SDK to retrieve some data based on user input

FormattableString x = $"RETURN array::at((SELECT * FROM characters WHERE {where} ORDER BY rand() LIMIT 1), 0);";

where can be a combination of different WHERE clauses generated based on the user input.
My problem is that when I use x as input to Query() it returns data that does not match the WHERE clauses.

If x is evaluated to:

RETURN array::at((SELECT * FROM characters WHERE gender = 'male' ORDER BY rand() LIMIT 1), 0);

and used as input to Query() it will return data for which gender is not male.

Using RawQuery() solves this issue as this seems to be related to how Query() handles the FormattableString input.

Steps to reproduce

// set is just a string which can be any combination of d, m, f, and g.
foreach (var c in set)
        {
            if (c is 'd')
                result |= (1 << 0);
            if (c is 'm')
                result |= (1 << 1);
            if (c is 'f')
                result |= (1 << 2);
            if (c is 'g')
                result |= (1 << 3);
        }

        string where = "gender";
        string[] genders = ["", "", ""];
        
        if (((result >> 0) & 0x1) is 1)
        {
            if (where.EndsWith("gender"))
                where += " = 'diverse'";
            else
                where += " OR gender = 'diverse'";
        }

        if (((result >> 1) & 0x1) is 1)
        {
            if (where.EndsWith("gender"))
                where += " = 'male'";
            else
                where += " OR gender = 'male'";
        }

        if (((result >> 2) & 0x1) is 1)
        {
            if (where.EndsWith("gender"))
                where += " = 'female'";
            else
                where += " OR gender = 'female'";
        }
        
        FormattableString query = $"RETURN array::at((SELECT * FROM characters WHERE {where} ORDER BY rand() LIMIT 1), 0);";
        
        try
        {
            var res = await db.Query(query);
            rolled = res.GetValue<Character>(0);
        }
        catch (Exception e)
        {
            Console.WriteLine(e);
            return;
        }

Expected behaviour

Query() should not return any data which does not match the WHERE clauses in the SurrealQL query.

SurrealDB version

1.5.4 for linux on x86_64

Package version(s)

[net8.0]:
   Top-level Package                   Requested   Resolved
   > Discord.Net                       3.15.3      3.15.3
   > Serilog                           4.0.0       4.0.0
   > Serilog.Sinks.File                6.0.0       6.0.0
   > Serilog.Sinks.File.GzArchive      1.1.10      1.1.10
   > Serilog.Sinks.FileEx              5.1.8       5.1.8
   > SurrealDb.Net                     0.5.1       0.5.1

Contact Details

No response

Is there an existing issue for this?

• I have searched the existing issues

Code of Conduct

• I agree to follow this project's Code of Conduct

[WlanKabL]

I got the Same issues.

Thought i did something wrong and switched every Query() to RawQuery().

Can confirm this bug

[Odonno]

Hello,

Query is made to interpolate queries while RawQuery exist for scenario where you just pass the plain string query. The first one will interpret variables and replace them with SurrealDB variable when the other one just give the expected string output. Thes particular Query method exist for different reasons, the notable one is to escape variables to avoid SurrealQL injections. This can be mandatory in a frontend client app like a Blazor app. If your code is only used in a server, you can use any of them with RawQuery being the more flexible to use.

So, from the example, this method call:

await db.Query($"RETURN array::at((SELECT * FROM characters WHERE {where} ORDER BY rand() LIMIT 1), 0);");

will output the following query:

RETURN array::at((SELECT * FROM characters WHERE $p0 ORDER BY rand() LIMIT 1), 0);

which will then, given the parameter, be interpreted by SurrealDB as:

RETURN array::at((SELECT * FROM characters WHERE "gender = 'male'" ORDER BY rand() LIMIT 1), 0);

The WHERE condition becomes a unary operator (checking on a string). It will so check if it is a truthy value. Since it is a non-empty string, it will be evaluated as true. Hence, being completely ignored and returning the whole table.

This happens also because SurrealDB can let you write these types of queries without throwing an error.

---

So, that explains the behavior of the Query method. To close on this, I can see that you are trying to write expressions by hand. Note that this is indeed the only way to do that currently until #61 is implemented.