0

I have following

case class Request(name:Option[String], age: Option[Int], address: Option[List[String]])

And I want to construct a query like this the conditions should apply if and only if the field is defined:

val req = Request(Some("abc"), Some(27), Some(List["add1", "add2"])    
select name, age, email from user where name = "abc" AND age = 27 AND address in("add1", "add2");

I went through doobies documentation and found about fragments which allow me to do the following.

val baseSql: Fragment = sql"select name, age, email from user";
val nameFilter: Option[Fragment] = name.map(x => fr" name = $x")
val ageFilter: Option[Fragment] = age.map(x => fr" age = $x")
val addressFilter: Option[Fragment] = address.map(x => fr " address IN ( " ++ x.map(y => fr "$y").intercalate(fr",") ++ fr" )"

val q = baseSql ++ whereAndOpt(nameFilter, ageFilter, addressFilter)

from my understanding the query should look like this if all the fields are defined:

select name, age, email from user where name = "abc" AND age = 27 AND address in("add1","add2");

but the query looks like this:

select name, age, email from user where name = ? AND age = ? AND address in(?);

What is wrong here I am not able to find that.

Thanks in advance !!!!

Raman Mishra
  • 2,635
  • 2
  • 15
  • 32
  • Are you constructing the query in the end using `q.query[Request]` ? – Alin Gabriel Arhip Jun 29 '22 at 21:00
  • yes but the model which is being used is different from Request because request is something I am getting from rest endpoint and I suppose to return the Response model as the result. – Raman Mishra Jun 29 '22 at 21:04
  • Then I suppose your `Response` must not contain optionals: Something like `case class Response(name: String, age: Int, address: List[String])` and `q.query[Response]` gives you the same result? – Alin Gabriel Arhip Jun 29 '22 at 21:20
  • Yes in Response doesn't have any optional fields but I am still not getting what I should get. And yes Response looks like how you defined. – Raman Mishra Jun 29 '22 at 21:33

1 Answers1

2

Everything is fine.

Doobie prevents SQL injections by SQL functionality where you use ? in your query (parametrized query), and then pass the values that database should put into the consecutive ? arguments.

Think like this: if someone posted name = "''; DROP table users; SELECT 1". Then you'd end up with

select name, age, email from user where name = ''; DROP table users; SELECT 1

which could be a problem.

Since the database is inserting arguments for you, it can do it after the parsing of a raw text, when such injection is impossible. This functionality is used not only by Doobie but by virtually every modern library or framework that let you talk to database at level higher than plain driver.

So what you see is a parametrized query in the way that database will see it, you just don't see the parameters that will be passed to it.

Mateusz Kubuszok
  • 24,995
  • 4
  • 42
  • 64