Cannot run mssql select query with where clause within python using pypyodbc

Question

I am using the library pypyodbc and am trying to run a select query in mssql using a where clause.

However the error I am getting indicates the syntax is wrong.

Error here ('42000', "[42000] [FreeTDS][SQL Server]Incorrect syntax near the keyword 'User'.")

my query within my function is as follows;

try:

    selectUserDetails = "SELECT Username,Password FROM User WHERE Username = ?"
   cursor.execute(selectUserDetails,(username,))

    for row in cursor:
        print(row);

except Exception as e:

    print('Error here ' + str(e))

My function to get the cursor is as follows;

def msDbCred():


try:
    from msDb import connection_string

    conn = pypyodbc.connect(connection_string)
    cursor = conn.cursor();
    return cursor;

except pymysql.err.OperationalError:
    sys.exit("Invalid Input: Wrong username/db or password found, please try again")

cursor = msDbCred()

and my db credentials are stored in a different file named msDb.py

db_host = '127.0.0.1'
db_name = 'TD_1.0'
db_user = 'ReadOnly'
db_password = 'mypaswword'

connection_string = 'Driver=SQL Server;Server=' + db_host + ';Database=' + db_name + ';uid=' + db_user + ';PWD=' + db_password + ';'

My question here is, is there something wrong with the query itself. As I am using this same format on another query elsewhere in my code and it works. Or is there something else wrong with my functions?

UPDATE:

@Gord Thompson said I should change my query.

I have changed, my query to;

selectUserDetails = "SELECT Username FROM [TD_1.0].[dbo].[User] WHERE Username = ?"
cursor.execute(selectUserDetails,(username,))

However now I get the error;

('HY010', '[HY010] [unixODBC][Driver Manager]Function sequence error')

@GordThompson I changed my query according to your comment but now get a different error. As show above in update — mp252, Mar 31 '17 at 15:06
`USER` is a keyword. You have to wrap it , eg `[User]`. You *don't* have to use a three-part name. Beyond that, your query uses `?` , which is *not* how parameters are used in SQL Server. FreeTDS doesn't support parameters anyway, at best it performs substitution. Further problems - instead storing and comparing hashes, you *load* a user's password? That almost begs for a hack like those in the news. — Panagiotis Kanavos, Mar 31 '17 at 15:08
Why are you using FreeTDS? Why don't you use ODBC directly? As for checking passwords, the correct way to do this is to use a strong cryptographing hashing algorithm with at least 1000 iterations to generate and store a hash from the password and a salt value. Whenever a user tries to login, generate the hash again and compare it against the stored hash. — Panagiotis Kanavos, Mar 31 '17 at 15:12
Check Microsoft's documentation on [connecting to SQL Server from Python](https://learn.microsoft.com/en-us/sql/connect/python/python-driver-for-sql-server) using pymssql or pyodbc. Check [How do I use SQL parameters with python?](http://stackoverflow.com/questions/3410455/how-do-i-use-sql-parameters-with-python) on how to use real parameterized queries with pymssql — Panagiotis Kanavos, Mar 31 '17 at 15:18
@PanagiotisKanavos re: *"your query uses ? , which is not how parameters are used in SQL Server"* - It *is* how parameters are used in pypyodbc, pyodbc, and many (if not most) other ODBC access layers in common use. ; re: *"FreeTDS doesn't support parameters anyway, at best it performs substitution."* - That may be true when FreeTDS_ODBC actually talks to the SQL Server, but it's still a good idea to use parameters at the Python level. ; re: *"Why are you using FreeTDS? Why don't you use ODBC directly?"* - Please explain what you mean by using "ODBC directly". — Gord Thompson, Mar 31 '17 at 16:49
I am unable to reproduce your error. Are you able to execute a simple query like `SELECT COUNT(*) AS n FROM sys.objects`? — Gord Thompson, Mar 31 '17 at 19:09
@GordThompson parameters in SQL Server are named. They are passed by name, eg `@name`. Some drivers though don't support parameters. That's when `?` is used. As for the rest, I already provided links to tutorials and questions that deal with passing parameters to actual parameterized queries. Finally, using "fake" parameters is a liability, not a benefit. You can't avoid SQL injection this way, or get any of the parameterization benefits, yet the code pretends otherwise — Panagiotis Kanavos, Apr 03 '17 at 07:22
@GordThompson I managed to resolve it, have put answer below — mp252, Apr 03 '17 at 14:33

score 1 · Answer 1 · edited Apr 04 '17 at 10:32

I managed to figure out where the error was and how to fix it.

In my msDbCred() function I was directly returning the cursor. I was testing in another file and figured this was the problem.

So instead I created an empty array and appended the cursor to the array and then returned the array.

Then used the first index of the array as the cursor elsewhere in my code

def msDbCred():

    cred = []
    try:
        from msDb import connection_string
        conn = pypyodbc.connect(connection_string)
        cursor = conn.cursor();
        cred.append(cursor)
        return cred
    except pymysql.err.OperationalError:
        sys.exit("Invalid Input: Wrong username/db or password found, please try again")

cursor = msDbCred()

Then use the first index of cursor array

try:
    selectUserDetails = "SELECT Username,Password,UserId FROM [User] WHERE Username = '"+str(username)+"' AND IsEditor = '"+str(True)+"'"
           cursor[0].execute(selectUserDetails)

    for row in cursor[0]:
        return  row[0] == username and row[1] == password
except Exception as e:
     print('Error is ' + str(e))

this is now working.

Cannot run mssql select query with where clause within python using pypyodbc

1 Answers1