JAVA MySQL insert with AES_ENCRYPT fails java.sql.SQLException: Incorrect string value

Question

I'm trying to take the values from a HTML form on a JSP and save them to a user table in MySQL with one of the fields encrypted on insert but I get the following error in the Tomcat logs

java.sql.SQLException: Incorrect string value: '\xEF\xA3H\x013p...' I've read "Incorrect string value" when trying to insert UTF-8 into MySQL via JDBC? and set the character encoding

ALTER DATABASE mydb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
ALTER TABLE users CHANGE phone phone VARCHAR(265) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_c

I haven't gone near the MySQL server ini file and changed the default character set there as in reality I will most likely not have access to this file and permission to change the values.

The query is as follows. The key is a simple string value similar to AbcD10Qs could it be my query in my model that is causing the issue?

String key=confUtil.encDeckey();

    String query
            = "INSERT INTO users (fName, lName, email,pwd,phone)"
            + "VALUES (?, ?, ?, ?, AES_ENCRYPT(?,?))";
    try {
        ps = connection.prepareStatement(query);
        ps.setString(1, user.getFName());
        ps.setString(2, user.getLName());
        ps.setString(3, user.getEmail());
        ps.setString(4, user.getPwd());
        ps.setString(5, user.getPhone());
        ps.setString(6,key);
        ps.executeUpdate();

I'm using a connection pool to manage the connection to the DB

private ConnectionPool() {
    try {
        InitialContext ic = new InitialContext();
        dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/testdb");
    } catch (NamingException e) {
        System.err.println(e);
    }
}

No character encoding set in the context.xml

  <Resource name="jdbc/medsdb" type="javax.sql.DataSource" auth="Container" 
        driverClassName="com.mysql.jdbc.Driver" 
        url="jdbc:mysql://localhost:3306/medsdb?autoReconnect=true" 
        username="testdbbuser" password="xxxxxxx" 
        logAbandoned="true" 
        removeAbandoned="true" removeAbandonedTimeout="60" 
        maxActive="100" maxIdle="30" maxWait="10000" />

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html mentioning following: "Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT)" Try not using VARCHAR as field type for "phone" — Quintium, Oct 18 '17 at 19:19
Changed column type to VARBINARY and this worked Thanks. Add it in as the answer and I'll mark it so — Graham, Oct 19 '17 at 14:21

score 1 · Accepted Answer · answered Oct 19 '17 at 20:29

https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html mentioning following: "Many encryption and compression functions return strings for which the result might contain arbitrary byte values. If you want to store these results, use a column with a VARBINARY or BLOB binary string data type. This will avoid potential problems with trailing space removal or character set conversion that would change data values, such as may occur if you use a nonbinary string data type (CHAR, VARCHAR, TEXT)" Try not using VARCHAR as field type for "phone"

JAVA MySQL insert with AES_ENCRYPT fails java.sql.SQLException: Incorrect string value

1 Answers1