Skip to content
Advertisement

SQLite query SELECT login, password FROM user WHERE login = ‘login’ returns wrong result

I have a “user” table with two entries.

id | login | password
0  | unauthorized user | pas
1  | login | hash

For some reason, the query "SELECT login, password FROM user WHERE login = 'login'" returns two records. Any help, please.

Advertisement

Answer

I assume that your query is actually using double-quotes for your string 'login' instead of single-quotes, i.e. that your query actually looks like this:

SELECT login, password FROM user WHERE login = "login"

This will not work as-intended because SQLite follows the ISO SQL specification which actually states that double-quotes are used to delimit literal object identifiers. Other RDBMS frequently deviate from this rule (e.g. MySQL uses `name` backticks, T-SQL for Sybase and MS SQL Server uses square-brackets [name], etc).

Change your query to ensure you’re using single-quotes for the value, or better yet, use parameterised queries (you’ll need to check with your SQLite library’s API documentation on how to do this, as you haven’t told us what library and language you’re using):

SELECT login, password FROM user WHERE login = 'login'

Or, to be clear:

SELECT "login", "password" FROM "user" WHERE "login" = 'login'


I note that because SQLite is in-proc and does not have built-in authentication or authorization (i.e. it has no access-controls) it does not have USER, LOGIN and PASSWORD as reserved keywords – but I feel it’s important to write portable SQL because it’s almost inevitable that a growing project will move from SQLite to a client/server RDBMS which will have those keywords and then your queries will break with a syntax error.

Also, I trust you aren’t actually storing unencrypted (or even reversibly-encrypted) passwords in that table. If you are storing hashed passwords then you should also store a salt value (which may be contained within the stored hash value if you’re using a scheme like bcrypt).

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement