MySQL select statement with tablename derived from database query-CodePudding

I want to write a SELECT statement, where the tablename is based on the response to a different SELECT query. I can't use stacked queries, and I can only use MySQL.

As pseudo-code, this is what I'd like to do:

tablenamevariable = (SELECT 'tablename');
SELECT * FROM tablenamevariable;

Which should be equivalent to executing SELECT * FROM tablename (where the string tablename comes from the database).

What I have so far is the following, which executes successfully:

SELECT * FROM (SELECT 'tablename') AS x;

However, the result simply prints tablename (which isn't what I want).

_{The background is an SQL injection which upper-cases all input. So what I want to do is SELECT * FROM (SELECT CHAR([...] USING UTF8MB4)) to be able to select data from a table with lower-case characters in the name.}

CodePudding user response：

You can't use a string as an identifier in the same query.

A subquery or an expression can return a string, but not an identifier.

So your subquery like select ... from (select ...) as x doesn't work the way you think. It will not query from the table named by the string. It will query from a derived table which consists of the string value returned by the subquery.

mysql> select * from (select 'abc' as tablename) as x;
 ----------- 
| tablename |
 ----------- 
| abc       |
 -----------

The reason for this is that in SQL, all identifiers must be fixed at the time the query is parsed, before it evaluates any expressions. This is so the table names can be validated that the corresponding tables exist, and you have SQL privileges to read those tables.

Another reason is that if the subquery worked the way you expect, then there would be no way to simply query strings from a subquery without querying an hypothetical table named by those strings. Also what would you expect it to do if the subquery returned multiple columns or multiple rows?

You clarified in an edit that what you're trying to do is to query a table after your query is formatted with uppercase table names, regardless of how the table was defined.

Case-sensitivity of identifiers in MySQL is a bit complex, because MySQL has versions on different operating systems, some of which have case-sensitive filesystems and some have case-insensitive filesystems.

But the result is that in most cases, it doesn't matter that your table names are uppercase in your query. Table name comparisons are case-insensitive by default on an OS that has uses case-insensitive filesystems.

mysql> select * from mytable limit 1;
 ---- ------- 
| pk | name  |
 ---- ------- 
|  3 | hello |
 ---- ------- 

mysql> select * from MYTABLE limit 1;
 ---- ------- 
| pk | name  |
 ---- ------- 
|  3 | hello |
 ---- ------- 

mysql> select * from MyTable limit 1;
 ---- ------- 
| pk | name  |
 ---- ------- 
|  3 | hello |
 ---- -------

(Test performed on MySQL 8.0.31 on MacOS)

On UNIX and Linux, the default is that table comparisons are case-sensitive. But there is an option to configure this if you want it to work in a case-insensitive manner on UNIX or Linux. You should read https://dev.mysql.com/doc/refman/8.0/en/identifier-case-sensitivity.html to understand how this works on different operating systems, and the option you can use to control it.

CodePudding user response：

To do that you would need to use prepared statements:

set @t = 'tablename';
PREPARE stmt FROM concat('select * from ', @n);
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

It is highly unusual that you would need to do that though.