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)
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)