主页 / dba / 问题 / 297391
Accepted
SPlatten
Asked: 2021-07-31 02:16:40 +0800 CST

如何解决存储过程中的 SQL 错误

  • 772

我正在使用 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;
stored-procedures mariadb

2 个回答

  1. 在准备好的语句中不能替换第一列名称。

    第二个准备好的语句需要用户定义变量作为参数

    最后一点是安全风险，文件名或列名可能被用于 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<>在这里摆弄

    • 0
  2. Best Answer
    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;
    • 0

相关问题