如何将数据插入MySQL中的动态表?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (1)
  • 关注 (0)
  • 查看 (248)

我尝试使用下面的存储过程在MySQL中的动态表中插入数据,但是在使用以下命令调用它时出现错误:

CALL insert_data ('table_x', 'NULL', 'A', 'B', 'C', 'D', 'E ')

错误

Column unknown' 0 'in the list of fields.

程序

DELIMITER $$
CREATE PROCEDURE insertar_datos (name VARCHAR(25), N INT, AP 
VARCHAR(15),
AM VARCHAR(15), Nom VARCHAR(30), DNI VARCHAR(8), Direc VARCHAR(30))
BEGIN
    SET @tableName = Name;
    SET @NName= N;
    SET @APName = AP;
    SET @AMName = AM;
    SET @NomName = Nom;
    SET @DNIName = DNI;
    SET @DirecName = Direc;
    SET @q = CONCAT('
        INSERT INTO `' , @tableName, '` VALUES(
            `',@NName,'`,
            `',@APName,'`,
            `',@AMName,'`,
            `',@NomName,'`,
            `',@DNIName,'`,
            `',@DirecName,'`
        )
    ');
    PREPARE stmt FROM @q;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END $$
DELIMITER;    
提问于
用户回答回答于

如果您发现自己有多个具有相同列的表,那么您可能会使用设计不佳的架构。精心设计的架构不需要您所要求的内容。我鼓励您提出另一个关于如何设计架构的问题,以完全避免您的问题。

将未经过滤的字符串连接在一起很容易受到SQL注入攻击。相反,只要有可能,请使用绑定参数并传入值。这可确保它们被正确转义和引用,并且不会被意外或恶意地误解。

SET @q = 'INSERT INTO `' , @tableName, '` VALUES(?,?,?,?,?,?)';
PREPARE stmt FROM @q;
EXECUTE stmt USING @NName, @APName, @AMName, @NomName, @DNIName, @DirectName

这也解决了你的其他问题,而不是一切都是字符串。

CALL insert_data ('table_x', 'NULL', 'A', 'B', 'C', 'D', 'E ')
                              ^^^^

绑定参数将保留非字符串的类型,如整数和空值。我还会注意到它N是一个整数,但你将它用作一个通常意味着字符串的名称。这可能是一个问题。

现在关于那个讨厌的表名。我们不能将其作为绑定参数传递。你想确保它不能跳出它的引号,所以`必须被转义。您也不希望任何人能够跳转到另一个数据库,因此.也必须进行转义。或者更好的是,引发错误。我们无法使用,quote()因为这是针对具有不同引用规则的列值。我们必须写自己的。

DELIMITER $$
drop procedure if exists check_table_name;
CREATE PROCEDURE check_table_name (
    table_name varchar(255)
)
begin
    if( locate("`", table_name) ) then
        signal sqlstate '45000'
            set message_text = 'illegal ` in table name';
    elseif( locate(".", table_name) ) then
        signal sqlstate '45000'
            set message_text = 'illegal . in table name';
    end if;
end $$
DELIMITER ;

现在我们在使用之前在表名上调用它。

call check_table_name(@tableName);
SET @q = concat('INSERT INTO `' , @tableName, '` VALUES(?,?,?,?,?,?)');
PREPARE stmt FROM @q;
EXECUTE stmt USING @NName, @APName, @AMName, @NomName, @DNIName, @DirecName;
DEALLOCATE PREPARE stmt;

我们会收到错误。

mysql> call insertar_datos("foo`haha, I broke your quoting`bar", 23, 'A', 'B', 'C', 'D', 'E ');
ERROR 1644 (45000): illegal ` in table name

我强烈反对这种做法。每当你将字符串连接起来制作SQL语句时,你就会面临错误和安全漏洞的风险。我不相信check_table_name所有的问题。

如果必须采用这种方法,请考虑使用一组固定的允许名称。

if name in ("foo", "bar", "baz", "table_x") then
    set @tableName = name;
else
    signal sqlstate '45000'
        set message_text = 'unknown name';
end if;

最好考虑重新设计架构吧。

扫码关注云+社区

领取腾讯云代金券

玩转腾讯云 有奖征文活动