Bug #71143 Calling ResultSet.updateRow should not set all field values in UPDATE
Submitted: 13 Dec 2013 15:26 Modified: 11 Sep 2014 15:43
Reporter: Christopher Schultz Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:5.1.27 OS:Any
Assigned to: Filipe Silva CPU Architecture:Any

[13 Dec 2013 15:26] Christopher Schultz
Description:
Using ResultSet.updateRow will set the PK of a table (in the generated UPDATE statement) even if the PK field was not in the list of fields to update. This causes problems when permissions do not allow the user to update certain fields in a table (e.g. the PK).

Connector/J appears to SET all fields that appeared in the field list in the "SELECT ... FOR UPDATE" query that generated the ResultSet.

How to repeat:
Test case:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
;

GRANT SELECT, INSERT ON test TO [user]@localhost;
GRANT UPDATE (value) ON test TO [user]@localhost;

INSERT INTO test (id, name, value) VALUES (1, 'foo', 'bar');

Now, run this program:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class MySQLPermissionsTest
{
    public static void main(String[] args)
        throws Exception
    {
        String db="test";
        String user="test";
        String password="test";
        String url="jdbc:mysql://localhost/" + db + "?user=" + user + "&password=" + password + "&useCursorFetch=true&characterEncoding=utf8&dumpQueriesOnException=true&zeroDateTimeBehavior=convertToNull";

        Connection conn = DriverManager.getConnection(url);
        PreparedStatement ps = conn.prepareStatement("SELECT id, name, value FROM test WHERE id=? FOR UPDATE",
                                                     ResultSet.TYPE_FORWARD_ONLY,
                                                     ResultSet.CONCUR_UPDATABLE);
        ps.setInt(1,  1);

        ResultSet rs = ps.executeQuery();

        rs.next();
        rs.updateString("value", "updated");
        rs.updateRow();
        
        System.out.println("Done");
    }
}

Expected result: the "value" field should be successfully set to "updated".
Actual results: Exception in thread "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: UPDATE command denied to user 'test'@'localhost' for column 'id' in table 'test'

Query being executed when exception was thrown:
UPDATE `test`.`test` SET `test`.`test`.`id`=1,`test`.`test`.`name`='foo',`test`.`test`.`value`='updated' WHERE `test`.`test`.`id`<=>1

	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2459)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2376)
	at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2360)
	at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2404)
	at MySQLPermissionsTest.main(MySQLPermissionsTest.java:26)

Suggested fix:
When generating the SQL UPDATE query, only fields that have been explicitly set with ResultSet.update[Type] should have an associated "SET" in the query.

I would have expected the UPDATE statement to look like this:

UPDATE test SET value="updated" WHERE id=1

Instead, it looks like this:

UPDATE test SET id=1, name="foo", value="updated" WHERE id=1 (simplified, of course)
[16 Dec 2013 12:03] Filipe Silva
Hi Christopher,

Thanks for this bug report. I'll analyze it and report back as soon as possible.
[17 Dec 2013 16:42] Filipe Silva
Verified as described.
[10 Sep 2014 15:03] Christopher Schultz
It would be great to get this fixed: we can't set proper table permissions while this bug is unresolved. I may be able to provide a patch if that would help.
[11 Sep 2014 15:43] Christopher Schultz
I'm raising the "Severity" of this bug because I believe it's important to be able to appropriately set GRANTS and this bug prevents us from doing so while still using ResultSet.updateRow.
[12 Jan 2015 18:36] Filipe Silva
Bug#72302 and Bug#75475 may be duplicates of this bug.