The following code, using a prepared statement, doesn't seem to work to select the home_address of the table students :
SET @mycolumn = 'home_address' ;
SET @s = 'SELECT ? FROM students' ;
PREPARE statement FROM @s;
EXECUTE statement USING @mycolumn ;
Indeed, these instructions will simply return a column named '?', filled with the string 'home_address', and with as many rows as the table students.
How could I make this work? I know that this kind of syntax is possible because the following example (taken from Is it possible to execute a string in MySQL?) works:
SET @username = 'test';
SET @password = 'asdf';
SET @Expression = 'SELECT id FROM Users WHERE name = ? AND pass = ?;' ;
PREPARE myquery FROM @Expression;
EXECUTE myquery USING @username, @password;
It's not possible. The difference is that you are dynamically referencing an object of the database, versus just passing in a string.
Prepared statements work by specifying the SQL statement in full with placeholders for strings/values that you are passing in. Your RDBMS can then parse the query and determine its full execution path BEFORE you pass in the parameters. Once that step is complete, it takes the parameters in and gets the data. This is why prepared statements are so secure. The execution path is predetermined so it's impossible to pass in more SQL and change it.
So if you don't know the column or table, then it can't parse and build the execution path. Instead, you'll have to build the SQL dynamically with concatenation and execute. If you are getting the column or table name from a user input then you'll have to sanitize it as best as you can and pray that your sanitation efforts are better than your sneaky users abilities to inject sql.