So I'm writing a Python program in Python 3.7 and I'm interacting with my database through a module named PyMySQL. It basically lets you run queries on the database through Python/your program.
I've been searching all around SO and Google to find a solution for my problem, but I've found nothing so I'm gonna ask about it.
Let's say I have for example 3 columns, "UserID", "Username" and "Password", in the table "Users". I want to replace all sorts of characters (alphabetical, numeric and special characters) under the column "Password" with an asterisk(*) per character and IF POSSIBLE, I want the replace to happen when a new entry is being made, so I don't have to put load on the whole database on all columns but on individual NEW entries.
First; a warning. Do not continue with what you're trying to do. This is a serious security risk that will actually make things worse. Firstly, you shouldn't have plaintext passwords, but converting them all to * makes the situation worse because then all I have to do is guess the sequence length, dramatically reducing complexity (not to mention, invalidating all of your user credentials).
You should use a library to generate hashes of passwords. There are multiple, I'm picking the base one used by Flask because it needs no setup and will also salt the password. You can also use hashlib etc.
from werkzeug.security import check_password_hash, generate_password_hash
password = 'hello123'
pass_hash = generate_password_hash(password)
print(pass_hash) # STORE THIS
print(check_password_hash(pass_hash, 'hello')) # False
print(check_password_hash(pass_hash, 'hello123')) # True
Do not try to create your own hash algorithms, always rely on professional packages.
You should be able to simply fix this with:
cursor.execute("""
SELECT user_id, password
FROM users
""")
data = cursor.fetchall()
hashed = []
for user, password in data:
hashed.append([generate_password_hash(password), user])
cursor.executemany("""
UPDATE users
SET password = %s
WHERE user_id = %s
""", (hashed))