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" />

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
– Quintium, Commented Oct 18, 2017 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
– Graham, Commented Oct 19, 2017 at 14:21

Quintium · Accepted Answer · 2017-10-19 20:29:14Z

1

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"

answered Oct 19, 2017 at 20:29

Quintium

4975 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related