Search code examples
sqlscalafunctional-programmingdoobie

Conditional Doobie query with Option field

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

Solution

  • 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.