Question - does sql in query_as has to have the lifetime of the returned QueryAs ?

[jeremychone]

Any reason why the query_as function has the sql &str with the same lifetime as the returned QueryAs object?

pub fn query_as<'q, DB, O>(sql: &'q str) -> QueryAs<'q, ....

I might be missing something, but it makes it hard to do some query "building / execution" composition, as it seems to force query_as building and execution to be in the same function. For example, if I want to have a function that just builds and binds a query_as, not sure how I can return it to be used elsewhere.

pub fn sql_insert_with_return_id(....) -> QueryAs<'q, sqlx::Postgres, (i64,), PgArguments> {
   let mut sql = String::from(....);
   // .. continue to build string
   let mut query = sqlx::query_as::<sqlx::Postgres, (i64,)>(&sql);

  query // <-- Error here since query_as require sql to have same lifetime as the Query
}

Note: I thought I found a workaround, but I am stuck. It seems we have to execute the query in the same function we created it. Am I missing something?

[abonander]

You're not missing anything, this was a deliberate case of defensive API design. For example, if sqlx::query() et al. took impl AsRef<str> so you could pass an owned string, then it becomes extremely tempting to format a query string like so and introduce injection vulnerabilities into your application:

let user_id = todo!("user input");

    // injection vulnerability!
let user = sqlx::query(format!("select * from user where user_id = {}", user_id))
    .fetch_one(&mut conn)
    .await?;

By explicitly requiring a string slice, the reasoning was that it would make this kind of pattern more of a code smell and hopefully throw up a red flag to the user:

// this looks really weird, am I doing something wrong?
let user = sqlx::query(&format!("select * from user where user_id = {}", user_id))

The argument is a little shaky, admittedly. It's not immediately obvious that this is the intention, nor does it actually prevent the user from introducing an injection vulnerability, it's just a tiny little bump.

On that note, what are you doing in your code that you need to build up a string at runtime?

[jeremychone]

@abonander Thanks for the detailed answer, and it makes sense.

And actually, a few weeks back I discovered the sqlx::query_as_with:.. query.take_arguments() which is perfect and solves all of my issues in a very flexible way.

I am working on an experimental SQL Builder lib on top of SQLX, SQLB. Still very early in the process, but so far SQLX has been the perfect foundation. Thanks for the great work.

[kurtbuilds]

I struggled with this issue for a while, so I wanted to leave a comment for anyone else encountering this issue. Based on my exploration of the sqlx API and attempts at implementation, successfully executing a query where the text and arguments have different lifetimes is currently impossible in safe rust.

The reason is that typically, lifetimes are associated with structs, not lifetimes. However, in sqlx, the lifetime is associated with the trait, not the struct implementing it. The problem is that lifetimes are contravariant when used as input arguments to a function, but covariant when part of a struct. (Concretely, if I have a &'short str, I can pass that to a function fn foo(s: &'long str), but if I have a let a: &'long str = "long lived string", I can re-assign it to let b: &'short = a.

Since the lifetime is associated with the trait, Rust doesn't know what kind of items can implement the trait, and also doesn't have a way otherwise to declare co/contra-variance on lifetimes. Therefore, unless the lifetime is equivalent, compilation will fail.

I believe a refactor of sqlx that eliminates lifetimes associated with traits would solve the conflict. More realistically, I think many of the traits are planned to be removed once GATs are available or stabilized, at which point too, I believe this limitation will go away.

All of that is in safe Rust. In unsafe Rust, we know that the only types implementing IntoArguments<'q> and friends are structs, so we can use unsafe to narrow the type and get the desired result. You can see an example function implementing this workaround in the function query_as_with_recast_lifetime from the ORM & query builder library I'm developing. Copied here for completeness:

fn query_as_with_recast_lifetime<'q, 'r, DB, Model>(
    s: &'q str,
    args: QueryBuilderArgs<'r, DB>,
) -> QueryAs<'q, DB, Model, QueryBuilderArgs<'q, DB>>
where
    'r: 'q,
    DB: Database,
    Model: for<'s> sqlx::FromRow<'s, DB::Row>,
    // <DB as HasArguments<'q>>::Arguments: IntoArguments<'q, DB>,
{
    // unsafe is safe b/c 'r: 'q. Rust isn't smart enough to know that downcasting of traits is safe, because when traits get lifetimes, it doesn't
    // know if the lifetime is covariant or contravariant, so it enforces equivalence. See: https://www.reddit.com/r/rust/comments/rox4j9/lifetime_inference_fails_when_lifetime_is_part_of/
    // But we know the trait is implemented by a struct, not a function, so we can do the downcast safely. Yay!
    let recast_args = unsafe { std::mem::transmute::<_, QueryBuilderArgs<'q, DB>>(args) };
    // unimplemented!()
    query_as_with(s, recast_args)
}

[abonander]

I have plans to improve the situation with the query*() functions specifically here: #1594

[rshigapov-bhft]

I don't understand why does Arguments::add method has bound T: 'q.

Postgres and MySQL encode argument values into buffer and use the value just for duration of add method. This duration is shorter then 'q lifetime.