Conditional Doobie query with Option field

Question

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 !!!!

Are you constructing the query in the end using q.query[Request] ? — Alin Gabriel Arhip
– Alin Gabriel Arhip, Commented Jun 29, 2022 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
– Raman Mishra, Commented Jun 29, 2022 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
– Alin Gabriel Arhip, Commented Jun 29, 2022 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
– Raman Mishra, Commented Jun 29, 2022 at 21:33

Mateusz Kubuszok · Accepted Answer · 2022-06-29 20:59:01Z

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.

answered Jun 29, 2022 at 20:59

Mateusz Kubuszok

27.9k4 gold badges47 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Raman Mishra Over a year ago

If everything is fine why I am not getting the result I should be getting in the response?

Mateusz Kubuszok Over a year ago

Run your query against the database and/or log the result you got through Doobie. There is more than one thing you could have missed but ? are surely unrelated.

Raman Mishra Over a year ago

so you were right there was nothing wrong @Mateusz Kubuszok, Thanks

Collectives™ on Stack Overflow

Conditional Doobie query with Option field

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related