This is an automated email from the ASF dual-hosted git repository.
jackietien pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/iotdb.git
The following commit(s) were added to refs/heads/master by this push:
new 96e8681dd51 Support the JDBC client to prevent SQL injection (#16734)
96e8681dd51 is described below
commit 96e8681dd51afdce5027a60d01f5ed555bab8ac7
Author: Le Yang <[email protected]>
AuthorDate: Wed Nov 12 20:16:33 2025 +0800
Support the JDBC client to prevent SQL injection (#16734)
---
.../apache/iotdb/jdbc/IoTDBPreparedStatement.java | 17 ++-
.../iotdb/jdbc/IoTDBPreparedStatementTest.java | 154 ++++++++++++++++++++-
2 files changed, 156 insertions(+), 15 deletions(-)
diff --git
a/iotdb-client/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBPreparedStatement.java
b/iotdb-client/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBPreparedStatement.java
index bb6a7641ab5..c92b6549bf9 100644
---
a/iotdb-client/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBPreparedStatement.java
+++
b/iotdb-client/jdbc/src/main/java/org/apache/iotdb/jdbc/IoTDBPreparedStatement.java
@@ -909,19 +909,18 @@ public class IoTDBPreparedStatement extends
IoTDBStatement implements PreparedSt
@Override
public void setString(int parameterIndex, String x) {
- // if the sql is an insert statement and the value is not a string
literal, add single quotes
- // The table model only supports single quotes, the tree model sql both
single and double quotes
- if ("table".equalsIgnoreCase(getSqlDialect())
- || ((sql.trim().toUpperCase().startsWith("INSERT")
- && !((x.startsWith("'") && x.endsWith("'"))
- || ((x.startsWith("\"") && x.endsWith("\""))
- && "tree".equals(getSqlDialect())))))) {
- this.parameters.put(parameterIndex, "'" + x + "'");
+ if (x == null) {
+ this.parameters.put(parameterIndex, null);
} else {
- this.parameters.put(parameterIndex, x);
+ this.parameters.put(parameterIndex, "'" + escapeSingleQuotes(x) + "'");
}
}
+ private String escapeSingleQuotes(String value) {
+ // Escape single quotes with double single quotes
+ return value.replace("'", "''");
+ }
+
@Override
public void setTime(int parameterIndex, Time x) throws SQLException {
try {
diff --git
a/iotdb-client/jdbc/src/test/java/org/apache/iotdb/jdbc/IoTDBPreparedStatementTest.java
b/iotdb-client/jdbc/src/test/java/org/apache/iotdb/jdbc/IoTDBPreparedStatementTest.java
index 1a523d9459f..2ae65dfed2a 100644
---
a/iotdb-client/jdbc/src/test/java/org/apache/iotdb/jdbc/IoTDBPreparedStatementTest.java
+++
b/iotdb-client/jdbc/src/test/java/org/apache/iotdb/jdbc/IoTDBPreparedStatementTest.java
@@ -200,7 +200,7 @@ public class IoTDBPreparedStatementTest {
ArgumentCaptor.forClass(TSExecuteStatementReq.class);
verify(client).executeStatementV2(argument.capture());
assertEquals(
- "SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature <
'abcde' and time > 2017-11-1 0:13:00",
+ "SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature <
'''abcde''' and time > 2017-11-1 0:13:00",
argument.getValue().getStatement());
}
@@ -217,7 +217,7 @@ public class IoTDBPreparedStatementTest {
ArgumentCaptor.forClass(TSExecuteStatementReq.class);
verify(client).executeStatementV2(argument.capture());
assertEquals(
- "SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature <
\"abcde\" and time > 2017-11-1 0:13:00",
+ "SELECT status, temperature FROM root.ln.wf01.wt01 WHERE temperature <
'\"abcde\"' and time > 2017-11-1 0:13:00",
argument.getValue().getStatement());
}
@@ -233,7 +233,7 @@ public class IoTDBPreparedStatementTest {
ArgumentCaptor.forClass(TSExecuteStatementReq.class);
verify(client).executeStatementV2(argument.capture());
assertEquals(
- "SELECT status, temperature FROM root.ln.wf01.wt01",
argument.getValue().getStatement());
+ "SELECT status, 'temperature' FROM root.ln.wf01.wt01",
argument.getValue().getStatement());
}
@SuppressWarnings("resource")
@@ -325,7 +325,7 @@ public class IoTDBPreparedStatementTest {
ArgumentCaptor.forClass(TSExecuteStatementReq.class);
verify(client).executeStatementV2(argument.capture());
assertEquals(
- "INSERT INTO root.ln.wf01.wt01(time,a,b,c,d,e,f)
VALUES(12324,false,123,123234345,123.423,-1323.0,'abc')",
+ "INSERT INTO root.ln.wf01.wt01(time,a,b,c,d,e,f)
VALUES(12324,false,123,123234345,123.423,-1323.0,'''abc''')",
argument.getValue().getStatement());
}
@@ -351,7 +351,7 @@ public class IoTDBPreparedStatementTest {
ArgumentCaptor.forClass(TSExecuteStatementReq.class);
verify(client).executeStatementV2(argument.capture());
assertEquals(
- "INSERT INTO root.ln.wf01.wt01(time,a,b,c,d,e,f,g,h)
VALUES(2017-11-01T00:13:00,false,123,123234345,123.423,-1323.0,\"abc\",'abc','abc')",
+ "INSERT INTO root.ln.wf01.wt01(time,a,b,c,d,e,f,g,h)
VALUES(2017-11-01T00:13:00,false,123,123234345,123.423,-1323.0,'\"abc\"','abc','''abc''')",
argument.getValue().getStatement());
}
@@ -374,7 +374,7 @@ public class IoTDBPreparedStatementTest {
ArgumentCaptor.forClass(TSExecuteStatementReq.class);
verify(client).executeStatementV2(argument.capture());
assertEquals(
- "INSERT INTO root.ln.wf01.wt02(time,a,b,c,d,e,f)
VALUES(2020-01-01T10:10:10,false,123,123234345,123.423,-1323.0,\"abc\")",
+ "INSERT INTO root.ln.wf01.wt02(time,a,b,c,d,e,f)
VALUES(2020-01-01T10:10:10,false,123,123234345,123.423,-1323.0,'\"abc\"')",
argument.getValue().getStatement());
}
@@ -400,4 +400,146 @@ public class IoTDBPreparedStatementTest {
"INSERT INTO root.ln.wf01.wt02(time,a,b,c,d,e,f)
VALUES(2020-01-01T10:10:10,false,123,123234345,123.423,-1323.0,'abc')",
argument.getValue().getStatement());
}
+
+ // ========== Table Model SQL Injection Prevention Tests ==========
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelLoginInjectionWithComment() throws Exception {
+ // Login interface SQL injection attack 1: Using -- comments to bypass
password checks
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, "admin' --");
+ ps.setString(2, "password");
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals(
+ "SELECT * FROM users WHERE username = 'admin'' --' AND password =
'password'",
+ argument.getValue().getStatement());
+ }
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelLoginInjectionWithORCondition() throws Exception {
+ // Login interface SQL injection attack 2: Bypassing authentication by
using 'OR '1'='1
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, "admin");
+ ps.setString(2, "' OR '1'='1");
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals(
+ "SELECT * FROM users WHERE username = 'admin' AND password = ''' OR
''1''=''1'",
+ argument.getValue().getStatement());
+ }
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelQueryWithMultipleInjectionVectors() throws
Exception {
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE email = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, "'; DROP TABLE users;");
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals(
+ "SELECT * FROM users WHERE email = '''; DROP TABLE users;'",
+ argument.getValue().getStatement());
+ }
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelString1() throws Exception {
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE password = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, "a'b");
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals("SELECT * FROM users WHERE password = 'a''b'",
argument.getValue().getStatement());
+ }
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelString2() throws Exception {
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE password = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, "a\'b");
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals("SELECT * FROM users WHERE password = 'a''b'",
argument.getValue().getStatement());
+ }
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelString3() throws Exception {
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE password = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, "a\\'b");
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals(
+ "SELECT * FROM users WHERE password = 'a\\''b'",
argument.getValue().getStatement());
+ }
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelString4() throws Exception {
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE password = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, "a\\\'b");
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals(
+ "SELECT * FROM users WHERE password = 'a\\''b'",
argument.getValue().getStatement());
+ }
+
+ @SuppressWarnings("resource")
+ @Test
+ public void testTableModelStringWithNull() throws Exception {
+ when(connection.getSqlDialect()).thenReturn("table");
+ String sql = "SELECT * FROM users WHERE email = ?";
+ IoTDBPreparedStatement ps =
+ new IoTDBPreparedStatement(connection, client, sessionId, sql, zoneId);
+ ps.setString(1, null);
+ ps.execute();
+
+ ArgumentCaptor<TSExecuteStatementReq> argument =
+ ArgumentCaptor.forClass(TSExecuteStatementReq.class);
+ verify(client).executeStatementV2(argument.capture());
+ assertEquals("SELECT * FROM users WHERE email = null",
argument.getValue().getStatement());
+ }
}
