A question about SQL injection

[lorenzo]

First of all, I wanted to thank you so much for making rel8 available 🎉

While trying to make myself familiar with the API, I saw many uses of the lit function. This made me wonder how rel8 was dealing with potential SQL injections, and digging into the internals I was left with the impression that the entire SQL generation is based on string concatenation, including the params themselves. Is this correct?

Normally in Postgres params are sent separately as part of the protocol, but I could not find that being done anywhere. I could not find any use of the prepared statement either. I guess my question is, is this library safe against SQL injection when passing text parameters as conditions in a query?

[shane-circuithub]

I have some very unfinished work to add support for prepared statements to Rel8. The Hasql library which we build on top of supports Postgres parameters and prepared statements, but it needs some non-trivial plumbing to integrate that with Rel8.

Having said that, unless you're using unsafeLiteral, I don't think you need to be worried about SQL injections with lit. For example:

putStrLn $ showQuery $ pure $ lit @(Expr Text) "Robert' ); DROP TABLE Students;--"

SELECT
CAST(CAST(E'Robert'' ); DROP TABLE Students;--' AS text) AS text) as "anon"
FROM (SELECT
      0) as "T1"

As you can see, the ' is escaped to '' in the generated SQL.

[ocharles]

To expand on what @shane-circuithub wrote - what matters is what the implementation of DBType is for the type of thing being passed to lit. For Text, String, etc, the encoder will create Opaleye StringLit nodes in the intermediate AST that's eventually compiled to SQL. So for strings, what matters is that this function does all the necessary escaping - and (as far as we know) it does.

[ocharles]

I'm gonna move this over to a GitHub discussion for now, just to keep the issue tracker focused on bugs & feature requests.