Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Parameterized queries #291

Open
Jasperav opened this issue Jan 24, 2021 · 11 comments
Open

Parameterized queries #291

Jasperav opened this issue Jan 24, 2021 · 11 comments

Comments

@Jasperav
Copy link

This fails to parse, but it can be successfully executed by SQLite (after providing a value for the parameter):

#[test]
fn go() {
    use sqlparser::dialect::SQLiteDialect;
    use sqlparser::parser::Parser;

    let sql = "select * from test_table where test_column = ?;";

    let dialect = SQLiteDialect {}; // or AnsiDialect, or your own dialect ...

    let ast = Parser::parse_sql(&dialect, sql).unwrap();

    println!("AST: {:#?}", ast);
}

Any idea why I get this error message?:

called Result::unwrap() on an Err value: ParserError("Expected an expression, found: ?")
thread 'dynamic_queries::go' panicked at 'called Result::unwrap() on an Err value: ParserError("Expected an expression, found: ?")',

@Dandandan
Copy link
Contributor

Thanks @Jasperav .
In think the ? is not something that's supported yet.

@H2CO3
Copy link

H2CO3 commented Jan 27, 2021

I was just going to open an issue about the same thing. Support for other flavors of parameterized queries like :named, @named, and $named would be nice as well.

@alex-dukhno
Copy link
Contributor

see discussions in #265

@mikoto2000
Copy link

I would also be happy to have this feature.
I would like to support the MyBatis's #{named} method.

@H2CO3
Copy link

H2CO3 commented Aug 16, 2024

see discussions in #265

That issue is closed. Of what use is it? Does that mean that we're decidedly not getting parameter support?

@git-hulk
Copy link
Member

@H2CO3 It should make sense to support this if it's a valid syntax for SQLite. And PR #265 was closed due to a lack of activity. I will have a look and support this feature if you want.

@git-hulk
Copy link
Member

@H2CO3 I found this issue has been resolved by #420 after checking the code. Would you mind having a try and close this issue if it works well for you?

@H2CO3
Copy link

H2CO3 commented Aug 26, 2024

@git-hulk Hi, and thanks for getting back to this!

The current implementation largely works indeed. Unfortunately, it still seems to have a bug that is blocking in my case. I slightly modified one of the cases in the test suite to reproduce it, and – at least in the case of the SQLite dialect – it appears that $ placeholders are incorrectly recognized as an identifier. The following code:

fn main() {
    let ast = sqlparser::parser::Parser::parse_sql(
        &sqlparser::dialect::SQLiteDialect {},
        "SELECT * FROM student WHERE id = $foo",
    );
    dbg!(ast);
}

prints the following AST (irrelevant parts excluded for clarity):

                        selection: Some(
                            BinaryOp {
                                left: Identifier(
                                    Ident {
                                        value: "id",
                                        quote_style: None,
                                    },
                                ),
                                op: Eq,
                                right: Identifier(
                                    Ident {
                                        value: "$foo",
                                        quote_style: None,
                                    },
                                ),
                            },
                        ),

@git-hulk
Copy link
Member

git-hulk commented Aug 26, 2024

@H2CO3 Thanks for your feedback.

This should be caused by SQLite dialect marks $ as the identifier start: dialect/sqlite.rs#L44. And the identifier in SQLite must start with ALPHABETIC from the documentation:

H41130: SQLite shall recognize as an ID token any sequence of characters that begins with an ALPHABETIC character and continue with zero or more ALPHANUMERIC characters and/or "$" (u0024) characters and which is not a keyword token.

Please refer to: https://www.sqlite.org/draft/tokenreq.html

So I guess it should be a bug for the SQLite dialect. I can fix this issue if this change sounds good to you. cc @alamb @iffyio

@H2CO3
Copy link

H2CO3 commented Aug 26, 2024

Perfect, thanks!

git-hulk added a commit to git-hulk/sqlparser-rs that referenced this issue Aug 27, 2024
… SQLite

Currently, `$var` would be parsed as an identifier instead of
a placeholder in SQLite dialect, which is unexpected from the SQLite's
documentation: https://www.sqlite.org/lang_expr.html#varparam.

This relates to the issue comment: apache#291 (comment)
@git-hulk
Copy link
Member

git-hulk commented Sep 2, 2024

@H2CO3 You now can try again.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

6 participants