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
– mp252, Commented Mar 31, 2017 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
– Panagiotis Kanavos, Commented Mar 31, 2017 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
– Panagiotis Kanavos, Commented Mar 31, 2017 at 15:12
Check Microsoft's documentation on connecting to SQL Server from Python using pymssql or pyodbc. Check How do I use SQL parameters with python? on how to use real parameterized queries with pymssql — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Mar 31, 2017 at 15:18

holdenweb · Accepted Answer · 2017-04-04 10:32:51Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related