SQL pyodbc issue

Question

I try to execute the following query as a prepared statement:

self.cursor.execute("select distinct ? from isap.tn_documentation where ? = '?' and  ? <> ''", attribute2, attribute1, i.text(0), attribute2)

After execution i get the following error:

The SQL contains 3 parameter markers, but 4 parameters were supplied', 'HY000

You shouldn't have to single-quote the text literal in the predicate. That may be the issue. — w08r
– w08r, Commented Jan 20, 2020 at 22:06
I don't think that's true. The prepare call goes to server and should obtain type information, the use of which should allow the client to bind the parameters correctly. (If I remember correctly) — w08r
– w08r, Commented Jan 20, 2020 at 22:18

GMB · Accepted Answer · 2020-01-20 22:16:05Z

2

You cannot pass column names as query parameters. You would need to concatenate the column names in the query string (while keeping the column values as parameters).

This should look like:

self.cursor.execute(
    "select distinct " 
        + attribute2 
        + " from isap.tn_documentation where " 
        + attribute1 + " = ? and " + attribute2 + " <> ''", 
    i.text(0)
)

Please note that doing so exposes your code to SQL injection: if your attributes input are coming from outside your code, this is a severe security breach. You would need to ensure that they do not contain malicious data (for example by checking the value of each attribute against a fixed list of allowed values: this should be quite easy since we are dealing with column names).

edited Jan 20, 2020 at 22:16

answered Jan 20, 2020 at 22:11

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL pyodbc issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related