Table in FROM clause defined by variable in MySQL

Question

I want to create a SQL script for MySQL 5.7 that inserts data from a table of a database origin into a table of another target database.

I want to have this source-database defined by a variable.

USE my_target_db;
SET @origin_db='my_origin_db';
SET @origin_table = concat(@origin_db,'.','tablename');

INSERT INTO target_table SELECT * FROM @origin_table;

Variables are used in various example to define column names but I never seen a way to define a table with it.

Is anyone has a trick for this ?

You would probably have to use a prepared statement: stackoverflow.com/questions/8809943/… — Tony
– Tony, Commented Dec 13, 2018 at 13:51
Possible duplicate of How to select from MySQL where Table name is Variable — unutbu
– unutbu, Commented Dec 13, 2018 at 14:15

Maksym Fedorov · Accepted Answer · 2018-12-13 13:55:18Z

1

Variables won't use in table name in MySQL. You only can use a prepared statement for dynamic build query. For example:

USE my_target_db;
SET @origin_db='my_origin_db';
SET @origin_table = CONCAT(@origin_db,'.','tablename');
SET @query = CONCAT('INSERT INTO target_table SELECT * FROM ', @origin_table);

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

You can read more detail about it in official documentation

answered Dec 13, 2018 at 13:55

Maksym Fedorov

6,4462 gold badges13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

@Orden i hope SET @origin_db='my_origin_db'; will not be set by the client from untrusted user input like a webapplication... Otherwise this answer is unsafe to use because off possible blind SQL injection attack

Alexey · Accepted Answer · 2018-12-13 13:56:05Z

1

You can use Prepared Statement like this:

USE my_target_db;
SET @origin_db='my_origin_db';
SET @origin_table = concat(@origin_db,'.','tablename');

SET @qry1 = concat('INSERT INTO target_table SELECT * FROM ', @origin_table);
PREPARE stmt1 from @qry1;
EXECUTE stmt1;

DEALLOCATE PREPARE stmt1;

answered Dec 13, 2018 at 13:56

Alexey

1,5491 gold badge13 silver badges24 bronze badges

3 Comments

Raymond Nijland Over a year ago

@Orden i hope SET @origin_db='my_origin_db'; will not be set by the client from untrusted user input like a webapplication... Otherwise this answer is unsafe to use because off possible blind SQL injection attack

Maksym Fedorov Over a year ago

@RaymondNijland good remark, but filtering of user input is a task for an application that used a database

Raymond Nijland Over a year ago

@MaximFedorov mine point of comment should be into using PREPARE <> FROM '<query>'; EXECUTE <> USING @<variable> with is safe against SQL injections.. You can atleast query the information_schema.DATABASES that way to test of it's safe and use that query result in the insert query.. Besides indeed the application would use prepared statements to protect against MySQL injections MySQL native seams to support this so iam pretty sure prepared staments in anny programming language can also prepare SET queries.

Collectives™ on Stack Overflow

Table in FROM clause defined by variable in MySQL

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related