blocks|key|2334804|text|如果您发现自己有多个具有相同列的表，那么您的模式可能设计得很糟糕。如果有一个设计良好的模式，那么您所要求的就不是必需的。我鼓励您提出另一个问题，即如何设计您的模式以完全避免您的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2334805|2334806|将未过滤的字符串连接在一起容易受到SQL+Injection+attack的攻击。相反，只要有可能，就使用绑定参数并将值传入。这确保了它们被正确地转义和引用，并且不会被意外或恶意地曲解。|offset|length|2334807|SET+@q+=+'INSERT+INTO+`'+,+@tableName,+'`+VALUES(?,?,?,?,?,?)';
PREPARE+stmt+FROM+@q;
EXECUTE+stmt+USING+@NName,+@APName,+@AMName,+@NomName,+@DNIName,+@DirectName|code-block|syntax|javascript|2334808|这也解决了你的其他问题，并不是所有东西都是字符串。|2334809|CALL+insert_data+('table_x',+'NULL',+'A',+'B',+'C',+'D',+'E+')
++++++++++++++++++++++++++++++%5E%5E%5E%5E|2334810|绑定参数将保留非字符串的类型，如整数和空值。我还会注意到，N是一个整数，但您将其用作名称，这通常意味着字符串。所以这可能是个问题。|style|CODE|2334811|2334812|现在来看看那个讨厌的表名。我们不能将其作为绑定参数传递。您希望确保它不会跳出引号，所以使用must+be+escaped.+You+also+don't+want+anyone+to+be+able+to+jump+to+another+database,+so.must+also+be+escaped.+Or+better+yet,+[raise+an+error](http://www.mysqltutorial.org/mysql-signal-resignal/).+We+can't+use+[quote()`](https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote)，因为这是针对具有不同引号规则的列值。我们得写我们自己的。|2334813|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+;|2334814|现在，我们在使用它之前对表名调用它。|2334815|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;|2334816|如果它是淘气的，我们会得到一个错误。|2334817|mysql>+call+insertar_datos("foo`haha,+I+broke+your+quoting`bar",+23,+'A',+'B',+'C',+'D',+'E+');
ERROR+1644+(45000):+illegal+`+in+table+name|2334818|但是，，我强烈反对这种方法。任何时候，当您将字符串连接在一起以生成SQL语句时，就有可能出现bug和安全缺陷。我不相信所有的问题都是check_table_name造成的。|2334819|如果您必须采用这种方法，请考虑改用一组固定的允许名称。|2334820|if+name+in+("foo",+"bar",+"baz",+"table_x")+then
++++set+@tableName+=+name;
else
++++signal+sqlstate+'45000'
++++++++set+message_text+=+'unknown+name';
end+if;|2334821|我鼓励您重新考虑重新设计您的模式。|2334822|entityMap|0|LINK|mutability|MUTABLE|url|https://en.wikipedia.org/wiki/SQL_injection|1|https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote^0|0|0|H|K|0|0|0|0|0|T|1|0|0|19|2E|3O|3E|7C|24|1|0|0|0|0|0|0|1U|G|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1O|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1P|8|@]|9|@]|A|$]]|$1|C|3|D|5|6|7|1Q|8|@]|9|@$E|1R|F|1S|1|1T]]|A|$]]|$1|G|3|H|5|I|7|1U|8|@]|9|@]|A|$J|K]]|$1|L|3|M|5|6|7|1V|8|@]|9|@]|A|$]]|$1|N|3|O|5|I|7|1W|8|@]|9|@]|A|$J|K]]|$1|P|3|Q|5|6|7|1X|8|@$E|1Y|F|1Z|R|S]]|9|@]|A|$]]|$1|T|3|-4|5|6|7|20|8|@]|9|@]|A|$]]|$1|U|3|V|5|6|7|21|8|@$E|22|F|23|R|S]|$E|24|F|25|R|S]]|9|@$E|26|F|27|1|28]]|A|$]]|$1|W|3|X|5|I|7|29|8|@]|9|@]|A|$J|K]]|$1|Y|3|Z|5|6|7|2A|8|@]|9|@]|A|$]]|$1|10|3|11|5|I|7|2B|8|@]|9|@]|A|$J|K]]|$1|12|3|13|5|6|7|2C|8|@]|9|@]|A|$]]|$1|14|3|15|5|I|7|2D|8|@]|9|@]|A|$J|K]]|$1|16|3|17|5|6|7|2E|8|@$E|2F|F|2G|R|S]]|9|@]|A|$]]|$1|18|3|19|5|6|7|2H|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|I|7|2I|8|@]|9|@]|A|$J|K]]|$1|1C|3|1D|5|6|7|2J|8|@]|9|@]|A|$]]|$1|1E|3|-4|5|6|7|2K|8|@]|9|@]|A|$]]]|1F|$1G|$5|1H|1I|1J|A|$1K|1L]]|1M|$5|1H|1I|1J|A|$1K|1N]]]]

If you find yourself with multiple tables with the same columns you likely have a poorly designed schema. What you're asking is not necessary with a well designed schema. I encourage you to ask another question about how to design your schema to avoid your problem entirely.

<hr>

Concatenating unfiltered strings together is a vulnerable to a <a href="https://en.wikipedia.org/wiki/SQL_injection" rel="nofollow noreferrer">SQL Injection attack</a>. Instead, whenever possible, use bind parameters and pass the values in. This ensures they're properly escaped and quoted and won't be misinterpreted either accidentally or maliciously.

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

This also solves your other problems, not everything is a string.

<pre><code>CALL insert_data ('table_x', 'NULL', 'A', 'B', 'C', 'D', 'E ')
 ^^^^
</code></pre>

Bind parameters will preserve the type of non-strings like integers and nulls. I'll also note that <code>N</code> is an integer, yet you're using it as a name which usually means a string. So that might be a problem.

<hr>

Now about that pesky table name. We can't pass it in as a bind parameter. You want to ensure it can't jump out of its quotes, so ` must be escaped. You also don't want anyone to be able to jump to another database, so <code>.</code> must also be escaped. Or better yet, <a href="http://www.mysqltutorial.org/mysql-signal-resignal/" rel="nofollow noreferrer">raise an error</a>. We can't use <a href="https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_quote" rel="nofollow noreferrer"><code>quote()</code></a> because that's for column values with different quoting rules. We'll have to write our own.

<pre><code>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 ;
</code></pre>

Now we call that on the table name before using it.

<pre><code>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;
</code></pre>

And if it's naughty we'll get an error.

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

But again I strongly discourage this approach. Anytime you're concatenating strings together to make SQL statements you're at risk of bugs and security flaws. I'm not convinced <code>check_table_name</code> accounts for all the problems.

If you must take this approach, consider instead using a fixed set of allowed names.

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

I encourage you to instead consider redesigning your schema.

I tried to use the stored procedure below to insert data in a dynamic table in MySQL, but I get an error when calling it with the following command: 

<pre><code>CALL insert_data ('table_x', 'NULL', 'A', 'B', 'C', 'D', 'E ')
</code></pre>

Error

<pre><code>Column unknown' 0 'in the list of fields.
</code></pre>

Procedure

<pre><code>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; 
</code></pre>

How to insert data into a dynamic table in MySQL?

我尝试使用下面的存储过程在MySQL的动态表中插入数据，但在使用以下命令调用它时遇到错误：CALL insert_data ('table_x', 'NULL', 'A', 'B', 'C', 'D', 'E ')错误Column unknown' 0 'in the list of fields.操作步骤DELIMI...

问如何在MySQL中向动态表中插入数据？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中向动态表中插入数据？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在MySQL中向动态表中插入数据？
EN