Fix password hash getting truncated when imported to MySQL table

Question

I currently have a Flask app that registers a Username and password to a MySQL database. My problem is that using Bcrypt to hash passwords and then save to them to the database, the outputted hash gets truncated by MySQL. My function looks like this:

class PasswordResource(Resource):
    @app.route('/password', methods=['GET'])
    @jwt_required

    def sendPassword():
        data = get_jwt_identity()
        hashed_password = bcrypt.hashpw(data['user_password'].encode('utf8'), bcrypt.gensalt())
        print(hashed_password)
        return mysqldb.addPassword("{}".format(str(data['user_name'])),"{}".format(hashed_password))

and connects to the database:

def addPassword(Username,Userpassword):
    try:
        cursorObject = connection.cursor()
        cursorObject.execute("call sp_createPassword('{}'".format(Username)+",'{}')".format(Userpassword))
        connection.commit()
        connection.close()

    finally:
        return 'User added succesfully!'

However, the hash gets truncated by MySql even though I have tried various VARCHAR() for the password table. This is an error i get in console Warning: (1265, u"Data truncated for column 'p_password' at row 1") self._do_get_result(). My password column currently is configured to VARCHAR(255), Not Null. How can I fix this error and avoid having the hashes getting truncated?

UPDATE

This is would be the schema for my password table:

+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                            |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tbl_passwords | CREATE TABLE `tbl_passwords` (
  `user_name` varchar(255) DEFAULT NULL,
  `user_password` varchar(255) NOT NULL,
  `user_number` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`user_number`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Maybe this post is helpful to check the needed size https://stackoverflow.com/a/247627/6655211 — PRMoureu, Jul 09 '18 at 18:30
**WARNING**: **DO NOT** use a high-speed cryptographic hash like SHA256 for passwords, this makes common passwords trivial to crack. At the absolute least use a password-specific hashing function like [Bcrypt](https://en.wikipedia.org/wiki/Bcrypt). — tadman, Jul 09 '18 at 18:51
@tadman Taking your suggestion into account..will not be using SHA256 then. — feners, Jul 09 '18 at 18:56
It'd help if you showed a representative schema for the table with the problem. `SHOW CREATE TABLE` can give you that output. As always edit your question to include any new code, don't use comments where formatting impractical. — tadman, Jul 09 '18 at 18:57
Use `VARCHAR(255)` as a safe default for any "string" type fields. You're using a bizarre mix of `VARCHAR` and `CHAR`. Remember `CHAR` is fixed-length and not necessary under most circumstances. — tadman, Jul 09 '18 at 19:09
@tadman So you would recommend using Bcrypt for user in my case? — feners, Jul 09 '18 at 19:14
Absolutely. It's literally a million times safer than SHA2-256. — tadman, Jul 09 '18 at 19:15
@tadman tried a couple things with Bcrypt's hash, but it still gets truncated by MySQL.. — feners, Jul 10 '18 at 13:54
Check how long the resulting string is from Bcrypt. It should be < 255 characters, and if not, something's wrong and it's not being encoded correctly or the schema isn't what you think it is. — tadman, Jul 10 '18 at 17:13

feners · Answer 1 · 2018-07-10T18:04:59.550

1

My issue was in my Procedure sp_createpassword() that added the password. In this procedure I had set for the password parameter to be VARCHAR(20) and obviously not enough for the hash. Silly error. Still, the suggestions of tadman were really helpful in setting up a proper DB.

edited Jul 10 '18 at 18:04

answered Jul 10 '18 at 16:30

feners

543
4
15
39

Fix password hash getting truncated when imported to MySQL table

1 Answers1