# Query to validate hash password from user source database **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [March 5, 2024, 10:27pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861 "2024-03-05T22:27:07Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Eugene49](https://avatars.discourse-cdn.com/v4/letter/e/c6cbf5/32.png) [@Eugene49](https://forum.inductiveautomation.com/u/Eugene49)\ **Post date:** [March 5, 2024, 10:27pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/1 "2024-03-05T22:27:07Z")
I setup user source to use database with mode set to Automatic. The table now has a user record with hashed Password. On the perspective client, I want the user to enter password, and be able to validate via query. How does the MSSQL query where clause looks like? Below returned error. ```auto args=[] args.append("operator") args.append("operator") query = \ """ select username from scada_users where username = ? and password = MD5(?); """ result = system.db.runPrepQuery(query, args) ``` Returned Error: `SQLServerException: 'MD5' is not a recognized built-in function name.` ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [March 5, 2024, 10:38pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/2 "2024-03-05T22:38:36Z")
The DB automatic source is not relying on a SQL MD5 function, it's applying a SHA1 hash before it runs the query and then matching the password to the hash in the WHERE clause. ---
**Author:** ![Eugene49](https://avatars.discourse-cdn.com/v4/letter/e/c6cbf5/32.png) [@Eugene49](https://forum.inductiveautomation.com/u/Eugene49)\ **Post date:** [March 5, 2024, 11:37pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/3 "2024-03-05T23:37:51Z")
Any code snip how ignition convert literal password to hash password? Seems the hash password in database is not HEX. I tried the following in script console, output is not same with database: ```auto import hashlib # initializing string str = "operator" result = hashlib.sha1(str.encode()) # printing the equivalent hexadecimal value. print("The hexadecimal equivalent of SHA1 is : ") print(result.hexdigest()) ``` ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [March 5, 2024, 11:54pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/4 "2024-03-05T23:54:31Z")
```auto byte[] bytes = input.getBytes("UTF-8"); byte[] sha1 = sha1(bytes); return Base64.encodeBytes(sha1); ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [March 5, 2024, 11:55pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/5 "2024-03-05T23:55:43Z")
No salt? ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [March 5, 2024, 11:58pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/6 "2024-03-05T23:58:08Z")
This profile is ancient. Internal profiles have been updated to use SHA-256 and a salt. ---
**Author:** ![Eugene49](https://avatars.discourse-cdn.com/v4/letter/e/c6cbf5/32.png) [@Eugene49](https://forum.inductiveautomation.com/u/Eugene49)\ **Post date:** [March 6, 2024, 12:16am UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/7 "2024-03-06T00:16:59Z")
oh shoot i got it: ```auto import hashlib import base64 str = "this is a password" sha1 = hashlib.sha1(str.encode()) print(base64.b64encode(sha1.digest())) ``` THANK YOU. ---
**Author:** ![ben.hutche](https://avatars.discourse-cdn.com/v4/letter/b/9fc348/32.png) [@ben.hutche](https://forum.inductiveautomation.com/u/ben.hutche)\ **Post date:** [May 29, 2025, 3:57am UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/8 "2025-05-29T03:57:47Z")
Which versions of Ignition are using a salt for the passwords in a DB user source? ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [May 29, 2025, 4:10am UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/9 "2025-05-29T04:10:08Z")
For DB automatic profiles it looks like it's been unsalted sha1 since at least 7.9? ---
**Author:** ![ben.hutche](https://avatars.discourse-cdn.com/v4/letter/b/9fc348/32.png) [@ben.hutche](https://forum.inductiveautomation.com/u/ben.hutche)\ **Post date:** [May 29, 2025, 4:13am UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/10 "2025-05-29T04:13:49Z")
Yeah noticed that a salt wasn't being used, why aren't they though, it makes it very easy to tell which passwords are the same. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [May 29, 2025, 12:10pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/11 "2025-05-29T12:10:25Z")
Some of these profile implementations pre date salted passwords (and better hashing algorithms) being standard practice. There was an effort to modernize it at one point but it never got merged. ---
**Author:** ![dillon](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dillon/32/81537_2.png) [@dillon](https://forum.inductiveautomation.com/u/dillon)\ **Post date:** [May 29, 2025, 12:29pm UTC](https://forum.inductiveautomation.com/t/query-to-validate-hash-password-from-user-source-database/86861/12 "2025-05-29T12:29:55Z")
Had to figure this out myself not too long ago > [@Internal Authentication Password Encoding](https://forum.inductiveautomation.com/t/internal-authentication-password-encoding/104959/7): > > Working Python function for others that achieves the same thing import random import hashlib def sha256PasswordSalt(password): rand = random.Random() salt = "{:08X}".format(rand.randint(0, 0xFFFFFFFF)) hashedPass = hashlib.sha256((password + salt).encode('utf-8')).hexdigest() return "[{}]{}".format(salt, hashedPass)