如何解決儲存過程中的 SQL 錯誤

我正在使用 MariaDB 10.5.9，我的儲存過程：

CREATE DEFINER=`trainer`@`localhost` PROCEDURE `updateTrainee`(
     IN `_txtTrainee` TEXT,
     IN `_field` TEXT,
     IN `_data` VARCHAR(16)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Adds or updates a field in the trainees table'
exitProc:BEGIN
#Procedure:       updateTrainee
#Parameters:      _txtTrainee Host name of trainee
#                 _field      Field to add or update
#                 _data       Data to add or update
#Notes:           Updates or adds trainee record
#History:         2021/07/30  Written by Simon Platten
     SET @SQL := CONCAT('INSERT INTO trainees (txtTrainee,?)',
                        ' VALUE (?,?) ON DUPLICATE KEY UPDATE',
                        ' ?=VALUES(?)')";
     PREPARE stmt FROM @SQL;
     IF NOT (_field IS NULL AND _txtTrainee IS NULL AND _data IS NULL) THEN
           EXECUTE stmt USING _field, _txtTrainee, _data, _field, _field;
     END IF;
     DEALLOCATE PREPARE stmt;
END

當我用以下方式呼叫它時：

CALL updateTrainee('Simon', 'vcPD', '100');

我收到一個 SQL 錯誤對話框，其中包含：

SQL Error (1064): You have an error in your SQL syntax:
check the manual that corresponds to your MariaDB
server version for the right syntax to use near
'?)VALUES(?,?)ON DUPLICATE KEY UPDATE ?=VALUES(?)'
at line 1

下面是練習生表的程式碼：

CREATE TABLE `trainees` (
     `txtTrainee` TEXT(65535) NOT NULL COMMENT 'Trainee host name' COLLATE 'latin1_swedish_ci',
     `vcPD` VARCHAR(16) NULL DEFAULT NULL COMMENT 'Probability of Detection' COLLATE 'latin1_swedish_ci',
     `intFA` INT(11) NULL DEFAULT NULL COMMENT 'False Alarm Rate',
     `intStartPoint` INT(11) NULL DEFAULT NULL COMMENT 'Start Point',
     `intStopPoint` INT(11) NULL DEFAULT NULL COMMENT 'Stop Point',
     `intGain` INT(11) NULL DEFAULT NULL COMMENT 'Gain',
     `intTVG` INT(11) NULL DEFAULT NULL COMMENT 'TVG',
     PRIMARY KEY (`txtTrainee`(100)) USING BTREE
)
COMMENT='Table for storing trainee data'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

IF NOT (_field IS NULL AND _txtTrainee IS NULL AND _data IS NULL) THEN
   SET @SQL := CONCAT('INSERT INTO trainees (txtTrainee,', _field, ')',
                      ' VALUE (?,?) ON DUPLICATE KEY UPDATE',
                      ' ', _field, '=VALUES(', _field, ')')";
   PREPARE stmt FROM @SQL;
   EXECUTE stmt USING _txtTrainee, _data;
   DEALLOCATE PREPARE stmt;
END IF;

在準備好的語句中不能替換第一列名稱。

第二個準備好的語句需要使用者定義變數作為參數

最後一點是安全風險，文件名或列名可能被用於 sql 注入，所以你應該有一個列名的白名單，就像在範例中一樣

CREATE TABLE trainees (txtTrainee varchar(100), field1 int)

CREATE PROCEDURE updateTrainee (_field varchar(50), _txtTrainee varchar(100), _data int)
BEGIN
#Procedure:  updateTrainee
#Parameters: _txtTrainee Host name of trainee
#            _field      Field to add or update
#            _data       Data to add or update
#Notes:      Updates or adds trainee record
#History:    2021/07/30  Written by Simon Platten
      DECLARE strSQL    TEXT;
      sET @trainne := _txtTrainee;
      sET @data := _data;
      sET @field := _field;
      IF EXISTS (sELECT 1 WHERE @field IN('field1','field2','field3')) then
      SET strSQL = CONCAT('INSERT INTO trainees (txtTrainee,',_field,')',
                          'VALUES(?,?)',
                          'ON DUPLICATE KEY UPDATE ',_field,' = VALUES(',_field,')');
      SET @SQL = strSQL;
      
      PREPARE stmt FROM @SQL;
      EXECUTE stmt USING @trainne,@data;
      DEALLOCATE PREPARE stmt;
      end if;
END

call updateTrainee('field1','test1',1)

SELECT * FROM trainees

txt實習生 | 欄位1
:--------- | -----:
測試1 | 1

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/297391