Prepared Statements or other SQL Injection Countermeasures?

[TimerErTim]

Hello Guys,

I have been testing SurrealDB for a project me and a few colleagues have to complete in school. I've been really liking it so far and we thus decided to use it as our backend/data storage solution.

However, along the development process we came to wonder: Is there any way to prevent SQL Injections using plain Surreal Query Language? This is not only a safety concern, but also a flexibility concern.

What if a user wants to store name=He said ", name = Gandalf;" like a computer and the SQL statement for storage looks as follows:

CREATE text SET content = "$name"

Filled in that would be:

CREATE text SET content = "He said ", name = Gandalf;" like a computer"

Which would not result in the desired record being created. Is there any solution for this? Thanks in advance!

[TimerErTim]

After some testing, escaping every " by putting an \ in front seems to be enough. So in the given example, the string placed into the query would look like He said \", name = Gandalf;\" like a computer.

[infogulch]

Please never use your language's formatting tools and manual replacements to pass parameters to a text-based query statement processor. Always [pass] parameters [via client library API]! Surrealdb [client libraries] support parameters [1]. If your query processor's [client library] API does not support parameters then it should instantly be deranked to "not a serious tool".

[1] https://surrealdb.com/docs/surrealql/parameters - see "Defining parameters within client libraries"

[TimerErTim]

I mean, I agree with you. But then if I define such a parameter in SurrealQL, I have the same risk for injections if not escaped as I'd have when directly inserting into the query. Or am I wrong?

The reason I ask this is because the Rust embedded library does not have the same support for parameters as f.e. the JS library.

E.g.:

-- Define the parameter
LET $name = "He said ", name = Gandalf;" like a computer";
-- Use the parameter
CREATE person SET name = $name;

Would still not have the desired result. In this example, no damaging effects occur, but it does not save the intended string and is a major risk of actually harming injections.

P.S.: I am well aware that the Rust library provides the functionality to build native Queries. But we want to use the same code base for a desktop and web application. So using unparsed statements on both ends would save a lot of work.

[infogulch]

I have the same risk for injections if not escaped as I'd have when directly inserting into the query

This is incorrect in general. Note the second half of the very short article:

Defining parameters within client libraries

SurrealDB's client libraries allow parameters to be passed in as JSON values, which are then converted to SurrealDB data types when the query is run. The following example show a variable being used within a SurrealQL query from the JavaScript library.

let people = await surreal.query("SELECT * FROM article WHERE status INSIDE $status", {
	status: ["live", "draft"],
});

All client libraries provide a mechanism to pass parameters separately from the query text, as this example shows. This pattern is universal across all serious SQL / query processor servers and client libraries, use it!

Parameters defined inside the query language (as you described) may be useful in certain cases, but you can consider this a completely separate feature for the purposes of our discussion here.

Your query might be invoked via the JS library with a query like this:

function create_person(namevar) {
  let person = await surreal.query("CREATE person SET name = $name;", {
	  name: namevar,
  });
  return person;
}

// presumably you would get this string from some user-input, not as a raw JS string.
create_person("He said \", name = Gandalf;\" like a computer");

For the rust client library, you would similarly provide this data via the vars parameter in the execute, process, or compute functions. https://docs.rs/surrealdb/1.0.0-beta.8/surrealdb/struct.Datastore.html#method.execute (Unfortunately the examples for these functions not include a case of providing vars.)

[TimerErTim]

This is exactly what I was looking and asking for. Could you post that reply as comment so I can mark it as the answer? This is obviously the infinitely better approach and I was only willing to accept the manual escaping approach for convinience and due to lack of security risk (in our application).

[infogulch]

I think the original answer is sufficient, however I just adjusted it to clarify that the relevant subject is parameters passed via client library api (as opposed to query language parameters).

[naisofly]

Hi all! Do check out the Query Safety section of the Security Best Practices Guide to prevent Injection attacks!