CODEWITHSUNDEEP
javamysqlhibernateencryption
paquita
paquita

Reputation: 11

Data truncation: Data to long for column

I'm trying to insert encrypted Password into a UserProfile Table.

My sql file to create tables it's like:

-- ---------- Table for validation queries from the connection pool. ----------
DROP TABLE PingTable;
CREATE TABLE PingTable (foo CHAR(1));
-- ------------------------------ UserProfile ----------------------------------
DROP TABLE UserProfile;
CREATE TABLE UserProfile (
    usrId BIGINT NOT NULL AUTO_INCREMENT,
    loginName VARCHAR(30) COLLATE latin1_bin NOT NULL,
    enPassword VARCHAR(150) NOT NULL, 
    firstName VARCHAR(30) NOT NULL,
    lastName VARCHAR(40) NOT NULL,
    email VARCHAR(60) NOT NULL,
    CONSTRAINT UserProfile_PK PRIMARY KEY (usrId),
    CONSTRAINT LoginNameUniqueKey UNIQUE (loginName))
    ENGINE = InnoDB;

CREATE INDEX UserProfileIndexByLoginName ON UserProfile (loginName);

My implementation to register user:

private String doEncryptedPassword(String clearPassword) {
    HashFunction hf = Hashing.sha512();
    HashCode hc = hf.newHasher(clearPassword.length()).putString(clearPassword, StandardCharsets.UTF_8).hash();
    return hc.toString();
}

public UserProfile registerUser(String loginName, String clearPassword,
        UserProfileDetails userProfileDetails)
        throws DuplicateInstanceException {

    try {
        userProfileDao.findByLoginName(loginName);
        throw new DuplicateInstanceException(loginName,
                UserProfile.class.getName());
    } catch (InstanceNotFoundException e) {
        String encryptedPassword = doEncryptedPassword(clearPassword);

        UserProfile userProfile = new UserProfile(loginName,
                encryptedPassword, userProfileDetails.getFirstName(),
                userProfileDetails.getLastName(), userProfileDetails
                    .getEmail());


        userProfileDao.save(userProfile);
        return userProfile;
    }

}

UserProfile.java:

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;

@Entity
public class UserProfile {

    private Long userProfileId;
    private String loginName;
    private String encryptedPassword;
    private String firstName;
    private String lastName;
    private String email;

    public UserProfile() {
    }

    public UserProfile(String loginName, String encryptedPassword,
            String firstName, String lastName, String email) {

        /**
         * NOTE: "userProfileId" *must* be left as "null" since its value is
         * automatically generated.
         */

        this.loginName = loginName;
        this.encryptedPassword = encryptedPassword;
        this.firstName = firstName;
        this.lastName = lastName;
        this.email = email;
    }

    @Column(name = "usrId")
    @SequenceGenerator( // It only takes effect for
    name = "UserProfileIdGenerator", // databases providing identifier
    sequenceName = "UserProfileSeq")
    // generators.
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "UserProfileIdGenerator")
    public Long getUserProfileId() {
        return userProfileId;
    }
    public void setUserProfileId(Long userProfileId) {
        this.userProfileId = userProfileId;
    }

    public String getLoginName() {
        return loginName;
    }
    public void setLoginName(String loginName) {
        this.loginName = loginName;
    }

    @Column(name = "enPassword")
    public String getEncryptedPassword() {
        return encryptedPassword;
    }
    public void setEncryptedPassword(String encryptedPassword) {
        this.encryptedPassword = encryptedPassword;
    }

    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }
    public void setEmail(String email) {
        this.email = email;
    }

    @Override
    public String toString() {
        return "UserProfile [userProfileId=" + userProfileId + ", loginName="
                + loginName + ", encryptedPassword=" + encryptedPassword
                + ", firstName=" + firstName + ", lastName=" + lastName
                    + ", email=" + email + "]";
    }

}

The message error:

Data truncation: Data too long for column 'enPassword' at row 1; SQL [n/a]; nested exception is org.hibernate.exception.DataException: Data truncation: Data too long for column 'enPassword' at row 1

How do I solve this problem?

(The length of string returns in method doEncryptedPassword is 129 characters)

Upvotes: 1

Views: 4200

Answers (2)

paquita
paquita

Reputation: 11

I found the problem.

I've two databases Data and DataTest. In 'Data' all works right.

In 'DataTest' (database for execute test) had this 'UserProfile' before with multiple foreign keys (reason why don't delete).

The old table has enPassword column define like

enPassword VARCHAR(30) NOT NULL

That's the reason for error (the string with 129 character cannot stored into a column with 30 of size).

To solve the problem to delete the tables with foreign keys, do this

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS UserProfile;
SET FOREIGN_KEY_CHECKS=1;

Upvotes: 0

MANOJ GOPI
MANOJ GOPI

Reputation: 1279

This exception will occur when the value passed for column enPassword is greater than 150 character. Try increasing the enPassword column size

Also check what is the length of string returns in method doEncryptedPassword

Upvotes: 1

Related Questions