How to insert data into a dynamic table in MySQL?

Question

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:

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

Error

Column unknown' 0 'in the list of fields.

Procedure

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;    

Answer 1

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.

---

Concatenating unfiltered strings together is a vulnerable to a SQL Injection attack. 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.

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

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

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

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

---

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 . must also be escaped. Or better yet, raise an error. We can't use quote() because that's for column values with different quoting rules. We'll have to write our own.

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 ;

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

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;

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

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

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 check_table_name accounts for all the problems.

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

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

I encourage you to instead consider redesigning your schema.