This is an automated email from the ASF dual-hosted git repository.
abeizn pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
The following commit(s) were added to refs/heads/main by this push:
new 47c681e90 fix: add SQL identifier validation to prevent SQL injection
via table/column names (#8769)
47c681e90 is described below
commit 47c681e90fe237ea69ff6f5bf1e2933a3d6b018d
Author: Warren Chen <[email protected]>
AuthorDate: Sat Mar 21 10:09:31 2026 +0800
fix: add SQL identifier validation to prevent SQL injection via
table/column names (#8769)
Add ValidateTableName and ValidateColumnName functions in core/dal to ensure
table and column names used in dynamic SQL are safe identifiers. Applied to
scope_service_helper, scope_generic_helper, and customized_fields_extractor.
---
backend/core/dal/identifier.go | 50 ++++++++++++++++++++++
.../pluginhelper/api/scope_generic_helper.go | 3 ++
backend/helpers/srvhelper/scope_service_helper.go | 3 ++
.../customize/tasks/customized_fields_extractor.go | 18 ++++++--
4 files changed, 71 insertions(+), 3 deletions(-)
diff --git a/backend/core/dal/identifier.go b/backend/core/dal/identifier.go
new file mode 100644
index 000000000..710d94e6f
--- /dev/null
+++ b/backend/core/dal/identifier.go
@@ -0,0 +1,50 @@
+/*
+Licensed to the Apache Software Foundation (ASF) under one or more
+contributor license agreements. See the NOTICE file distributed with
+this work for additional information regarding copyright ownership.
+The ASF licenses this file to You under the Apache License, Version 2.0
+(the "License"); you may not use this file except in compliance with
+the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing, software
+distributed under the License is distributed on an "AS IS" BASIS,
+WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+See the License for the specific language governing permissions and
+limitations under the License.
+*/
+
+package dal
+
+import (
+ "fmt"
+ "regexp"
+
+ "github.com/apache/incubator-devlake/core/errors"
+)
+
+// validIdentifierRegex matches valid SQL identifiers: alphanumeric,
underscores, and dots (for schema.table)
+var validIdentifierRegex = regexp.MustCompile(`^[a-zA-Z_][a-zA-Z0-9_.]*$`)
+
+// ValidateTableName checks that a table name is a safe SQL identifier to
prevent SQL injection.
+func ValidateTableName(name string) errors.Error {
+ if name == "" {
+ return errors.Default.New("table name must not be empty")
+ }
+ if !validIdentifierRegex.MatchString(name) {
+ return errors.Default.New(fmt.Sprintf("invalid table name: %q",
name))
+ }
+ return nil
+}
+
+// ValidateColumnName checks that a column name is a safe SQL identifier to
prevent SQL injection.
+func ValidateColumnName(name string) errors.Error {
+ if name == "" {
+ return errors.Default.New("column name must not be empty")
+ }
+ if !validIdentifierRegex.MatchString(name) {
+ return errors.Default.New(fmt.Sprintf("invalid column name:
%q", name))
+ }
+ return nil
+}
diff --git a/backend/helpers/pluginhelper/api/scope_generic_helper.go
b/backend/helpers/pluginhelper/api/scope_generic_helper.go
index a782b4e09..895f3427a 100644
--- a/backend/helpers/pluginhelper/api/scope_generic_helper.go
+++ b/backend/helpers/pluginhelper/api/scope_generic_helper.go
@@ -565,6 +565,9 @@ func (gs *GenericScopeApiHelper[Conn, Scope, ScopeConfig])
transactionalDelete(t
}
tx := gs.db.Begin()
for _, table := range tables {
+ if err := dal.ValidateTableName(table); err != nil {
+ return errors.Default.Wrap(err, fmt.Sprintf("unsafe
table name %q when deleting scope data", table))
+ }
where, params := generateWhereClause(table)
gs.log.Info("deleting data from table %s with WHERE \"%s\" and
params: \"%v\"", table, where, params)
sql := fmt.Sprintf("DELETE FROM %s WHERE %s", table, where)
diff --git a/backend/helpers/srvhelper/scope_service_helper.go
b/backend/helpers/srvhelper/scope_service_helper.go
index 544536f01..e5d4671c5 100644
--- a/backend/helpers/srvhelper/scope_service_helper.go
+++ b/backend/helpers/srvhelper/scope_service_helper.go
@@ -255,6 +255,9 @@ func (scopeSrv *ScopeSrvHelper[C, S, SC])
deleteScopeData(scope plugin.ToolLayer
}
tables := errors.Must1(scopeSrv.getAffectedTables())
for _, table := range tables {
+ if err := dal.ValidateTableName(table); err != nil {
+ panic(errors.Default.Wrap(err, fmt.Sprintf("unsafe
table name %q when deleting scope data", table)))
+ }
where, params := generateWhereClause(table)
scopeSrv.log.Info("deleting data from table %s with WHERE
\"%s\" and params: \"%v\"", table, where, params)
sql := fmt.Sprintf("DELETE FROM %s WHERE %s", table, where)
diff --git a/backend/plugins/customize/tasks/customized_fields_extractor.go
b/backend/plugins/customize/tasks/customized_fields_extractor.go
index b9f7c1008..7bd1d932d 100644
--- a/backend/plugins/customize/tasks/customized_fields_extractor.go
+++ b/backend/plugins/customize/tasks/customized_fields_extractor.go
@@ -149,7 +149,10 @@ func extractCustomizedFields(ctx context.Context, d
dal.Dal, table, rawTable, ra
// remove columns that are not primary key
delete(row, "_raw_data_id")
delete(row, "data")
- query, params := mkUpdate(table, updates, row)
+ query, params, err := mkUpdate(table, updates, row)
+ if err != nil {
+ return err
+ }
err = d.Exec(query, params...)
if err != nil {
return errors.Default.Wrap(err, "Exec SQL
error")
@@ -169,18 +172,27 @@ func fillInUpdates(result gjson.Result, field string,
updates map[string]interfa
}
// mkUpdate generates SQL statement and parameters for updating a record
-func mkUpdate(table string, updates map[string]interface{}, pk
map[string]interface{}) (string, []interface{}) {
+func mkUpdate(table string, updates map[string]interface{}, pk
map[string]interface{}) (string, []interface{}, error) {
+ if err := dal.ValidateTableName(table); err != nil {
+ return "", nil, err
+ }
var params []interface{}
stat := fmt.Sprintf("UPDATE %s SET ", table)
var uu []string
for field, value := range updates {
+ if err := dal.ValidateColumnName(field); err != nil {
+ return "", nil, err
+ }
uu = append(uu, fmt.Sprintf("%s = ?", field))
params = append(params, value)
}
var ww []string
for field, value := range pk {
+ if err := dal.ValidateColumnName(field); err != nil {
+ return "", nil, err
+ }
ww = append(ww, fmt.Sprintf("%s = ?", field))
params = append(params, value)
}
- return stat + strings.Join(uu, ", ") + " WHERE " + strings.Join(ww, "
AND "), params
+ return stat + strings.Join(uu, ", ") + " WHERE " + strings.Join(ww, "
AND "), params, nil
}
