Using QueryBuilder in a recursive function

[twiclo]

I have an AST generated from the DSL I'm working on to take user inputted queries. The AST is ran through a recursive eval function that generates the query in SQL.

[src/main.rs:32] &query = "(((first_name contains bob) and (last_name startswith rob)) or (start_date is 1/1/2022))"
[src/main.rs:49] &query_ast = Or(
    And(
        Contains(
            StringLit(
                "first_name",
            ),
            StringLit(
                "bob",
            ),
        ),
        StartsWith(
            StringLit(
                "last_name",
            ),
            StringLit(
                "rob",
            ),
        ),
    ),
    Is(
        StringLit(
            "start_date",
        ),
        StringLit(
            "1/1/2022",
        ),
    ),
)
[src/main.rs:51] eval(query_ast, i) = "((('bob' LIKE '%' || LOWER(first_name) || '%') AND ('rob' LIKE LOWER(last_name) || '%')) OR (start_date = '1/1/2022'))"

The problem is eval uses format!() to put recursive calls to itself in between already existing strings. As far as I can tell, this isn't possible with sqlx. There's QueryBuilder but you can only ever push to the end of a builder. So far the solution I've come up with is track how many binds need to be done, generate the string, then loop over that string X amount of times replacing every value with a $1,$2,$3 etc. Once I have the string I can make a new QueryBuilder with the formatted string and call push_bind() however many times I would like.

I'm hoping for input from the developers on the direction I should take as it's starting to feel a little hacky. I would also like to know if it's safe to take user input and do a regular old .push() with it. If not, does sqlx provide any sensitization methods? I'm pretty sure .push_bind() can only be used for values.

The code in case it's needed:

async fn main() -> Result<(), Box<dyn std::error::Error>> {
  let query = "(((first_name contains bob) and (last_name startswith rob)) or (start_date is 1/1/2022))";
  dbg!(&query);
  
  let mut builder = QueryBuilder::<Postgres>::new("select username from end_user left join employee using where ");
  append_query(&mut builder, query);

  Ok(())
}

fn append_query(builder: &mut QueryBuilder<Postgres>, query: &str) {
  let query_ast = ast::expr(query).unwrap().1;
  dbg!(&query_ast);
  let i = &mut 0;
  dbg!(eval(query_ast, i));
}

fn eval(ast: ast::Expr, i: &mut u32) -> String {
  match ast {
    ast::Expr::And(lhs, rhs) => {
      return format!("({} AND {})", eval(*lhs, i), eval(*rhs, i))
    },
    ast::Expr::Or(lhs, rhs) => {
      return format!("({} OR {})", eval(*lhs, i), eval(*rhs, i))
    },
    ast::Expr::Contains(lhs, rhs) => {
      return format!("('{}' LIKE '%' || LOWER({}) || '%')", eval(*rhs, i), eval(*lhs, i))
    },
    ast::Expr::StartsWith(lhs, rhs) => {
      return format!("('{}' LIKE LOWER({}) || '%')", eval(*rhs, i), eval(*lhs, i))
    },
    ast::Expr::Is(lhs, rhs) => {
      return format!("({} = '{}')", eval(*lhs, i), eval(*rhs, i))
    },
    ast::Expr::StringLit(string) => {
      *i = *i + 1;
      return string
    },
    _ => {
      return "FIELD/VALUE".to_string()
    }
  }
}

[twiclo]

I just realized you can't do push_bind on a $1 you typed out yourself. Well now I really am at a loss.

[frederikhors]

Did you find a way?

[twiclo]

Unfortunately no. I got pulled off to work on another project and this hasn't seen work since then. Sorry.