Java Prepared Statement ENCRYPT

Question

Looks like I m missing something in this statement. Trying to Encrypt the password before that goes in DB. And, using following String.

String Sql = "INSERT INTO virtual_users"
               +"(domain_id, password, email) VALUES"
               +"(?,ENCRYPT(?, CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),?)";

Is there anything wrong with above ENCRYPT?

Complete Code for reference.

public void AddMail(MailUsers mAC) {

   String Sql = "INSERT INTO virtual_users"
           +"(domain_id, password, email) VALUES"
           +"(?,ENCRYPT(?, CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),?)";

   PreparedStatement ps = null;

   try {
       ps = DBUtils.getPreparedStatement(Sql);
       ps.setInt(1, mAC.getDomain_id());
       ps.setString(2, mAC.getPassword());
       ps.setString(3, mAC.getEmail());

       System.out.println(ps);

       ps.executeUpdate();

   } catch (ClassNotFoundException | SQLException ex) {
       Logger.getLogger(DataAccess.class.getName()).log(Level.SEVERE, null, ex);
       System.out.println(ps);
   } 
}

ERROR

com.mysql.jdbc.JDBC42PreparedStatement@1c9aab0a: INSERT INTO virtual_users(domain_id, password, email) VALUES(1,ENCRYPT('asdfasdf', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),'tink@in.in') Severe: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'password' cannot be nul

Yeah, it says.. Info: com.mysql.jdbc.JDBC42PreparedStatement@1c9aab0a: INSERT INTO virtual_users(domain_id, password, email) VALUES(1,ENCRYPT('asdfasdf', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),'tink@in.in') Severe: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'password' cannot be null — user2372925, Mar 27 '18 at 05:26
yes, in Dev environment, It is Windows. But, in production it will go on Ubuntu. — user2372925, Mar 27 '18 at 05:43

Jorge Campos · Accepted Answer · 2018-03-27T05:52:08.477

The problem here is that you are calling a function ENCRYPT which uses a native *NIX cript() method to encrypt the data. If you are in a windows machine such native function doesn't exists, it is clearly stated in the docs:

If crypt() is not available on your system (as is the case with Windows), ENCRYPT() always returns NULL.

Therefore anything you pass into the ENCRYPT function will evaluate to NULL, therefore the error you mention in the comments:

Info: com.mysql.jdbc.JDBC42PreparedStatement@1c9aab0a: INSERT INTO virtual_users(domain_id, password, email) VALUES(1,ENCRYPT('asdfasdf', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))),'tink@in.in') Severe: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Column 'password' cannot be null

Since you have a Not Null constraint in the password field, the error is thrown.

Try using one of the other available encryption functions in MySQL and take special care with the recommendations in the Docs

Or set up a simple hash function such as MD5('yourpass') to your Dev environment. Or even ditch the MySql function and use a Java method to do so. Take a look at this thread: How do I generate a SALT in Java for Salted-Hash?

Thank you ! That was the problem. On Windows that MD5 works perfect. Moreover, that document says about Nix (Encrypt).. Fair enough! — user2372925, Mar 27 '18 at 06:19

Java Prepared Statement ENCRYPT

1 Answers1