Query to validate hash password from user source database

Ignition
1

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.

	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.

2

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.

3

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:

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())
4 
            byte[] bytes = input.getBytes("UTF-8");
            byte[] sha1 = sha1(bytes);
            return Base64.encodeBytes(sha1);
5

No salt?

6

This profile is ancient. Internal profiles have been updated to use SHA-256 and a salt.

1 Like
7

oh shoot i got it:

import hashlib
import base64

str = "this is a password"
sha1 = hashlib.sha1(str.encode()) 
print(base64.b64encode(sha1.digest()))

THANK YOU.

8

Which versions of Ignition are using a salt for the passwords in a DB user source?

9

For DB automatic profiles it looks like it's been unsalted sha1 since at least 7.9?

10

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.

11

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.

1 Like
12

Had to figure this out myself not too long ago