escaping arguments to raw sql?

[lygaret]

Hey y'all,

I've got a complicated multi-statement query I need to run against an SQLite database (so no stored procedures), and I'm currently doing it by expanding a raw SQL erb template with arguments:

    # string.new because string literals are frozen, 
    # stringio would be better if the strings were longer,  but these are just being using for proper escaping

    # clone the tree rooted at {id} into the tree at {dpid} under {dpath}
    # @param id [UUID]
    # @param dpid [UUID]
    # @param path [String]
    def clone_tree(id:, dpid:, dpath:)
      source_id = ds.literal_append(String.new, Sequel[id])
      dest_pid  = ds.literal_append(String.new, Sequel[dpid])
      dest_path = ds.literal_append(String.new, Sequel[dpath])

      sql = clone_tree_sql.result(binding)
      connection.run sql
    end

   private

    # @return [ERB] the generated SQL template
    def clone_tree_sql
      @clone_tree_sql ||= begin
        path = File.join(__dir__, "./queries/clone_tree.erb.sql")
        ERB.new(File.read(path))
      end
    end

-- clone_tree.erb.sql
SELECT * FROM... WHERE id = <%= source_id %>
-- ...

---

two questions, in case y'all have dealt with this before:

1. is anyone else doing something similar (or completely different?!) for running large queries? I'd really rather avoid rewriting the whole thing to the Sequel dataset methods (there's a good amount of recursive ctes, and the aliasing gets confusing).
2. is there a way to get an expression, Sequel[0] for example, literalized into SQL that doesn't require calling an undocumented (public) method on the dataset? I couldn't find anything else in the source, and doing it this way works, "escaping an expression" seems like something that would be exposed somewhere, so I'm guessing I just haven't seen it.

Thanks for any help, y,all!

• Jon

[adam12]

I'm not sure I'm completely following the issue, but perhaps it's because the example is fairly simplified, but could you not just use parameters here?

-- clone_tree.sql
SELECT * FROM foo WHERE id = :source_id

sql = File.read("clone_tree.sql")

DB[sql, source_id: id]

[lygaret]

🤦

When I had been running the same as a big string against a connection, I was expecting the run method to take bindings, and had been digging through source to find them.

Earlier, I had been running against something else and wasn't getting my bindings changed - ie they were still :source_id in the generated SQL, but I must have been doing something stupid.

Thanks!