[tokio_postgres] how to pass it a WHERE condition string

[zwv]

My function takes multiple filters, I construct them to a WHERE string, but seems I cannot pass it to tokio_postgres method

WHERE user_id = 10 AND 
      username = 'test' AND 
      created_at >= '2021-01-02' AND
      created_at <= '2021-05-01'

I tried

where_condition = "user_id = 10 AND username = 'test' AND created_at >= '2021-01-02' AND created_at <= '2021-05-01'"

client.query("SELECT * from users WHERE $1", &[&where_condition])

got this error

panicked at 'called `Result::unwrap()` on an `Err` value: Error { kind: ToSql(0), cause: Some(WrongType { postgres: Bool, rust: "alloc::string::String" }) }'

seem I had to explicitly define the columns like below

client.query("SELECT * from users WHERE user_id = $1 AND username = $2", &[&user_id, &username])

That seems a rather complicated way to construct the statements if my filter list is long. I don't know if it's possible to do what I want or not.

[mbrubeck]

String literals in Rust can also span multiple lines, so you can write something like this:

client.query(
 "WHERE user_id = 10 AND 
        username = 'test' AND 
        created_at >= '2021-01-02' AND
        created_at <= '2021-05-01'", &[])

[zwv]

@mbrubeck, yes, that's the solution I went with, but not safe in terms of SQL injection

[zwv]

@cole-miller, thanks, yes, option in tokio-postgres is very limited

[alice]

You could construct a string containing AND VAR_NAME = $n several times and put the dynamic values in a vector.

[sfackler]

Parameter interpolation is handled by the Postgres server, not by tokio-postgres. As @alice noted, you should dynamically construct the query string with parameters for all of the actual values.

[blonk]

You can still get safe queries, you just format the query string and not the parameters, as @alice and @sfackler noted.

This is how we do it in one of our applications (this is rusqlite -- which is modeled after rust-postgres). It doesn't do exactly what you're doing, but the same technique can be used:

pub(crate) fn add_peer(
  conn: &mut Connection,
  npi: &NewPeerInfo
) -> Result<i64, Error> {
  let mut names = Vec::new();
  let mut params = Vec::new();
  let mut vals: Vec<&dyn ToSql> = Vec::new();

  names.push("name".to_string());
  params.push("?".to_string());
  vals.push(&npi.name);

  names.push("cert".to_string());
  params.push("?".to_string());
  vals.push(&npi.cert);

  if let Some(addr) = npi.addr.as_ref() {
    names.push("addr".to_string());
    params.push("?".to_string());
    vals.push(addr);
  }

  let q = format!(
    "INSERT INTO peers ({}) VALUES ({});",
    names.join(","),
    params.join(",")
  );
  self.conn.execute(&q, &vals[..])?;

  let last_id = self.conn.last_insert_rowid();

  Ok(last_id)
}

Note that you don't actually need to build the params at each step unless you want to do some special handling. Instead you can construct it based on names:

let params = ["?"].repeat(names.len()).join(",");

[zwv]

thanks @alice and @sfackler !

thanks @blonk , i will try to use your approach, wasn't aware i can do Vec<&dyn ToSql> and &vals[..], your code is helpful, really appreciate it.