Support interpolating escaped string literals

[brandonchinn178]

I want to create a function with Haskell values baked into it, e.g.

let admin = "my-admin" -- e.g. from config file
in
[sql|
CREATE FUNCTION check_admin() RETURNS void
AS $$
  IF current_user != ^{escapeText admin} THEN
    RAISE EXCEPTION "only admins allowed"
  END IF;
$$ LANGUAGE plpgsql;
|]

Current workaround:

escapeText :: Text -> Query
escapeText s = fromString $ tag <> Text.unpack s <> tag
  where
    -- unlikely to show up in string
    tag = "$" <> show (hash s) <> "$"

Ideally there would be a polymorphic escapeLiteral that would be similar to Lift for template haskell, injecting a Haskell value of some type directly into the query. Not as safe as #{...}, but works in places where query parameters don't work.

class EscapeLiteral a where
  escapeLiteral :: a -> Query
instance EscapeLiteral Text where ...
instance EscapeLiteral Int where ...
instance EscapeLiteral a => EscapeLiteral [a] where ...

It would also be nice to have an explicit unsafeSqlString function instead of calling fromString

[mzabani]

I wonder if escapeText :: Text -> Query can ever be sound, given string escaping in postgres takes so many forms (single quotes and multiple shapes of dollar quoting). Taking your example and modifying it slightly (roles have constraints on valid characters):

let expectedString = "$$ some string" -- dollar signs in the beginning
in
[sql|
CREATE FUNCTION check_string(arg TEXT) RETURNS void
AS $$
  IF arg != ^{escapeText expectedString} THEN
    RAISE EXCEPTION "not the right string"
  END IF;
$$ LANGUAGE plpgsql;
|]

Maybe a contextualized escapeText :: Quoting -> Text -> Query could exist (a call site would be something like escapeText DollarQuoting .. or escapeText SingleQuotes ..), but actually, the docs on dollar quoting don't write it down explicitly, but my reading is that there is no escaping possible inside dollar quoting, so even that is not possible? On the extreme of correctness, we would have to ask for all exact dollar quotes used in the surrounding context (there can be more than one, I think) and use one that hasn't been used yet.

On unsafeSqlString, I think it is possible to come up with such a function, but it might have some caveats in the documentation. I'll have to double-check, but one can't write e.g. [sql|SELECT * FROM table ^{unsafeSqlString ";"} SELECT * FROM other_table |] and expect hpgsql to understand the semi-colon separates two statements. There might be other contrived examples like this. I don't think it's too concerning and it would probably be fine. Out of curiosity, what are you thinking of using this for?

Oh, but note that unsafeSqlString would not be equivalent to fromString: the latter parses the user's literal string into an internal structure; it looks for statement separators because postgresql's protocol demands individual statements are sent separately.

[brandonchinn178]

For unsafeSqlString, I was just wanting an alias for fromString. fromString is primarily meant to implement OverloadedStrings, and calling fromString directly is a bit of a code smell. So if I want to do the same thing fromString does, it would be nice to have a separate function (explicitly marked unsafe) that does the same thing.

For escapeText, you could do something like:

1. Generate a random 4 character tag, ensure it doesn't exist as a substring in the text
2. Wrap expectedString with $<tag>$
3. Replace any dollar signs with $<tag>$ || chr(36) || $<tag>$

I'm not saying this is the best approach, but it is feasible.

[mzabani]

For unsafeSqlString, I was just wanting an alias for fromString

Oh, I see. I think mkQuery might work for that? The only difference (other than String vs ByteString for the argument's type) between that and fromString should be that fromString doesn't accept query arguments ($1, $2, $3, ...) because there's no way to supply those query arguments.

For escapeText, you could do something like:

Generating something not in the supplied text is definitely creative, but this is starting to feel a little bit outside the scope I had in mind for hpgsql - I wanted to avoid value escaping as much as possible.

Also, thinking about that extreme end of correctness, tag collisions are still possible if users encode their own tags without using this function (this is really extreme, I admit, to the point it wouldn't prevent me from going forward with the idea, but extremes help me better analyze the tradeoffs).

Let me think about this for a little longer. And let me know if at least mkQuery works for your first problem.

[brandonchinn178]

Oh interesting. Is there a reason fromString isn't defined as mkQuery (fromString s) ()?

Yeah I guess that works, although I do still think (philosophically) that every type with an IsString instance should explicitly export a function with exactly the same signature.

[mzabani]

fromString has nicer error messages, e.g. it will err with Dollar-numbered query arguments are not supported in string literals. Use the sql quasiquoter or mkQuery instead. if someone adds $1 to their queries instead of Query contains more dollar-numbered query arguments than actually supplied query arguments. Query: ....

A bit excessive, I admit, and perhaps a product of rushing things - more code could be shared; internally, at least.