This is an automated email from the ASF dual-hosted git repository. warren pushed a commit to branch fix/sql-table-name-validation in repository https://gitbox.apache.org/repos/asf/incubator-devlake.git
commit 14b02d299097e3f79d2bf7151159a2daf5e01547 Author: warren <[email protected]> AuthorDate: Mon Mar 16 00:12:13 2026 +0800 fix: add SQL identifier validation to prevent SQL injection via table/column names 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 }
