How to pass entire WHERE statement from Python to SQL Server

by

The following code returns error:

pyodbc.ProgrammingError: (‘42000’, “[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ‘@P1’. (102) (SQLExecDirectW)”)

Python code is:

 c, conn = connection()
 query = "SELECT case_Case_Number \
     FROM tbl_case \
     ?"
 c.execute(query, (workflow_eval_statement, ))

The workflow_eval_statement is:

WHERE case_Case_Number="23-00000500" and case_Case_Status="In Process"   and case_Case_Source="Portal"

When I use that WHERE statement via a query directly into SQL Server to returns that record correctly, without error.

If I modify the program to hard code the field names and pass the values it works:

query = "SELECT case_Case_Number \
    FROM tbl_case \
    WHERE case_Case_Number = ? and case_Case_Status = ? and case_Case_Source = ?"
c.execute(query, (parm_case_id, parm_status, parm_source))

Results in no error

I also tried passing the pre-built WHERE clause with double quotes (see below) and get the same error.

WHERE case_Case_Number = "23-00000500" and case_Case_Status = "In Process"   and case_Case_Source = "Portal"

Any guidance is appreciated.

  • 4

    Remember that the purpose of the ? is to do quoting. Your string will be converted to one large string. You can certainly do c.execute(query + workflow_eval_statement).

    – 

  • 2

    Though if workflow_eval_statement is coming from user input, you certainly shouldn’t be concatenating the value.

    – 




  • 2

    That concatenation suggested above is horrifying. That is the very essence of sql injection. There are better ways to do “kitchen sink” type of queries.

    – 

  • 1

    what’s wrong with your second attempt? that’s how things are done (unless you want to be pwned by mallicious users)

    – 

  • It’s unlikely the OP’s second WHERE will work, @siggemannen , unless they have quoted identifiers off, and I hope they don’t.

    – 




After reading the docs, this is what I found.

According to the pyodbc docs for execute:

The optional parameters may be passed as a sequence, as specified by the DB API, or as individual values.

And then, according to the DP API docs fr execute:

Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified in a database-specific notation

And then:

The module will use the getitem method of the parameters object to map either positions (integers) or names (strings) to parameter values. This allows for both sequences and mappings to be used as input

So I guess is not possible to pass a WHERE statement as a parameter but it’s values.

Leave a Comment