Parameters do not work in string functions

[ahmedali5530]

Describe the bug

Parameters not working when working with string functions inside where conditions. For example
this query

SELECT * FROM menu_item WHERE  string::startsWith($column, $value) LIMIT 10 FETCH categories

$column: "name",
$value: "biryani"

is giving following error

Incorrect arguments for function string::lowercase(). Argument 1 was the wrong type. Expected a string but found NONE

But when i put real values instead of parameters it works just fine.
This is a security vulnerability as well due to risk of sql injection

Steps to reproduce

create a simple table with at least 1 field with data type string

Expected behaviour

Parameters should work in string function used in where clause

SurrealDB version

1.4.2 for linux on x86_64

Contact Details

[email protected]

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

[kearfy]

Heya! If I understand your example correctly, you want to ensure that the column with the name of the column being contained in the $column variables, which is a string, contains a value that you pass from the client in the $value parameter? You would do this like so:

LET $column = "name";
LET $value = "biryani";

SELECT * FROM menu_item WHERE  string::startsWith($this[column], $value) LIMIT 10 FETCH categories

[ahmedali5530]

$column and $value are dynamic, so i don't really know whats coming in them.
Since i am using JavaScript I would do the above like this.

LET $column = "${column}";
LET $value = "${value}";

this is same as i am sending values directly in query.

My question is why can't i use parameters inside the query. The query works when i don't use string functions. But with string functions it gives error.

I have a similar issue for RELATE query, where i am sending extra data with parameters and they are also not working. While sending them directly in query working

[fokklz]

import { Surreal } from 'surrealdb.node';

const db = new Surreal();

async function main() {
  await db.connect('ws://127.0.0.1:8000');
  await db.signin({
    username: 'root',
    password: 'root',
  });
  await db.use({ ns: 'test', db: 'test' });

  await db.create('menu_item', { name: 'Pizza' });
  let res = await db.query(
    'SELECT * FROM menu_item WHERE string::startsWith($this[$column], $value)',
    { column: 'name', value: 'P' }
  );
  console.log(res);
}

main();

i've tried around a bit, this seems to work, you where just missing the $this like @kearfy mentioned

[ahmedali5530]

This resolved the issue but can you tell me why $this is used with $column variable and why not with $value?
@fokklz

[fokklz]

Sure, thats just the current execution context for that query, like $parent inside a sub-query.

https://surrealdb.com/docs/surrealdb/surrealql/parameters#parent-this

Don't forget to close the issue ^^