Prepared statements for queries when not possible to use parameters

[zediogoviana]

Hey 👋 What do you think about a prepared statement function for queries, when parameters are not possible to use, for example in this case?

Something that would make it possible to do the following in a safe manner:

label = "Something Bad"
query = "CREATE (s:#{label}) SET s.name = 'Some Name'"

Does it make sense to add a mechanism to the package, or is it something that each application using this package should be taking care of on their side?

[florinpatrascu]

Does it make sense to add a mechanism to the package, or is it something that each application using this package should be taking care of on their side?

Personally I'm inclined to let the user decide what's best for their app from a security perspective. But if you have a proposal to make it easier for him to integrate with this driver, then I'm all for it. Thank you.

[zediogoviana]

Yep, to be honest I'm also not sure if this should exist in the driver or just managed by the end user.

So, to give a bit more context, I've got a small personal project that sometimes needs labels to be passed as a variable to the function that calls the query, and I'm using something like this:

cypher =
  """
    MATCH (n {address: '$address'})
    SET n:{{label}}
  """
  |> Cypher.prepared_statement(label: new_label_string)

Neo.query(conn, cypher, %{address: "something")

Where the prepared_statement function just escapes ' characters from the values passed and replaces them in the query string:

@spec prepared_statement(String.t(), keyword({atom(), String.t()})) :: String.t()
def prepared_statement(query_string, variables \\ []) when is_list(variables) do
  prepared_vars =
    Enum.map(variables, fn {key, value} ->
      case value do
        nil -> {key, ""}
        _ -> {key, String.replace(value, "'", "\\'")}
      end
    end)

  Enum.reduce(prepared_vars, query_string, fn {key, var}, acc ->
    String.replace(acc, "{{#{key}}}", var)
  end)
end

If implemented, I think it should be separate from the current Bolt.Sips.Query.query implementation, and have them composable in some way.

What do you think? Is it worth to pursue it or not?

[florinpatrascu]

sure, I don't see why not ¯\(ツ)/¯

Maybe introduced gradually first, as a utility kind of function?! And have some examples documented around it.. then promote it to the Query itself, based on user's interest

.. something like that.