1

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.

Improve this question
3
  • Table names are not part of your data, but part of the database structure. You shouldn't store table names in your tables hence - unless you are building a DBMS yourself. If you are storing table names in your database that is very likely an indicator for an inappropriate database model. Commented Jan 2, 2023 at 11:57
  • 1
    In few cases you may need to store table names for some reason (e.g. some generic housekeeping process). There are two ways to go about that: 1. Your queries know your table names, e.g. select max(updated) from t1 where (select table_name from x) = 'T1' union all select max(updated) from t2 where (select table_name from x) = 'T2'. 2. You use an app to query the table name first, then build another SQL query based on that table name and run this. This is called dynamic SQL. Commented Jan 2, 2023 at 11:58
  • @ThorstenKettner Definitely true. The background is an SQL injection which upper-cases all input. So what I want to do is SELECT * FROM (SELECT CHAR([...] USING UTF8MB4)). But I can imagine that the question would still be useful for people who have such a situation because of legacy code or whatnot. Commented Jan 2, 2023 at 12:11

2 Answers 2

Reset to default
2

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.

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

1 Comment

Thanks for the in-depth answer! I'd like the query to work universally, and sadly tablenames are case-sensitive by default on linux (at least on the system I checked). I was hoping that it would work without editing the settings, but it seems like that isn't the case. I'll wait a bit to see if I get any other answers, and will accept yours if that shouldn't be the case.
0

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.

Improve this answer

3 Comments

thanks for your answer! But from the question: "I can't use stacked queries".
@tim That is not technically a stacked query but multiple statements. If you want to execute only one statement from the client side, you can wrap the code inside a procedure. Still, maybe you could explain what is the actual problem you are trying to solve.
Sure, I added context to my question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.