How to write dynamic query？

[liergou99]

If you do not use Macros and use sql to splice it into a string, it is easy to generate "SQL Injection Attack "

[abonander]

This is shown in the third and fifth examples in the Querying section in the README: https://github.com/launchbadge/sqlx#querying

let mut cursor = sqlx::query("SELECT * FROM users WHERE email = ?")
    .bind(email)
    .fetch(&mut conn).await?;

while let Some(row) = cursor.next().await? {
    // map the row into a user-defined domain type 
}

#[derive(sqlx::FromRow)]
struct User { name: String, id: i64 }

let mut stream = sqlx::query_as::<_, User>("SELECT * FROM users WHERE email = ? OR name = ?")
    .bind(user_email)
    .bind(user_name)
    .fetch(&mut conn);

The query() and query_as() functions were deliberately designed to be easiest to use with string literals to discourage formatting parameters into strings instead of using binds.

These examples could stand to be copied to the documentation, however.

[tyjyhapple]

let mut stream = sqlx::query_as::<_, User>("SELECT * FROM users WHERE email = ? OR name = ?")
    .bind(user_email)
    .bind(user_name)
    .fetch(&mut conn);

one error it give "no method fetch"

[abonander]

In the error message it should have suggested importing one of the following (depending on what type your database connection is):

• sqlx::mysql::MySqlQueryAs
• sqlx::postgres::PgQueryAs
• sqlx::sqlite::SqliteQueryAs

These are workarounds for a compiler bug/missing feature and will be removed in 0.4.

[liergou99]

I want to dynamically add it to the SQL based on whether the field value of the object is None
for example:

let mut sql_str = "select * from user_group where 1=1".to_string();
 if !user_group.group_name.is_none(){
     sql_str = format!("{} and {} = ?",sql_str,"group_name".to_string());
 }
let mut query_tmp =sqlx::query_as::<_,UserGroup>(&sql_str);
if !user_group.group_name.is_none(){
    query_tmp = query_tmp.bind(user_group.group_name);
}

how do I code

[abonander]

We're discussing that in #291

[IdemenB]

@abonander , would you have an idea on how to insert a statement like the following into the WHERE clause?

The purpose of the following query string is to run a wildcard like keyword search in a JSONB field (POSTGRES). So, the string 'ABC' is searched within the whole 'column_x'.

This runs without an error:

select * from sample_table where jsonb_path_exists(column_x, '$.** ? (@.type() == "string" && @ like_regex "ABC")');

Numerous different attempts to build the above with Sqlx have failed. As the final resort, the following compiles with query_as!, only to end up with the run-time error given by the DB.

select * from sample_table where ($1 OR $1 IS NULL)

where I provide $1 as the following string literal:

let jsob_search_clause = format!(
                r#"jsonb_path_exists(column_x, '$.** ? (@.type() == "string" && @ like_regex "{}")')"#,
                _some_variable
            )

The error given by the DB:

PgDatabaseError {
        severity: Error,
        code: "42804",
        message: "argument of OR must be type boolean, not type text",

By the way, in the DB tool I use (DBeaver) I can do this, that is, provide the whole raw string starting with the method jsonb_path_exists(... as a single bind variable.