Is there a way to use a prepared statement to select a variable column?

Question

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 that you are passing in. Your RDBMS can then parse the query and determine it's full execution path before you pass in the parameters. Once that step is complete, it takes the parameters in. If you don't know the column or table, then it can't parse and build the execution path. You'll have to build the SQL dynamically with concatenation and execute. — JNevill, Jan 10 '18 at 17:02
Thanks JNevill, can you please post your comment as an answer? — R. Bourgeon, Jan 10 '18 at 17:03

score 4 · Accepted Answer · answered Jan 10 '18 at 17:22

4

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 it's 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.

answered Jan 10 '18 at 17:22

JNevill

46,980
4
38
63

1

Instead of building the SQL dynamically, based on the example in the original post, you can select all possible columns from `[students]` and have the application layer return only the one column. It would be a more secure way to avoid SQL injections. – zambonee Jan 10 '18 at 17:33
@zambonee That's a great solution. It means more I/O, but the security risks going with a dynamic statement seem worth the cost. – JNevill Jan 10 '18 at 17:36
@zambonee you mean doing something like SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = @ mycolumn ? – R. Bourgeon Jan 10 '18 at 17:50
@R.Bourgeon I meant in the application layer, completely separate from the SQL query... I'm going to put this as an answer, and hopefully it won't detract from JNevill's answer, which is a very good explanation. – zambonee Jan 10 '18 at 18:02

score 1 · Answer 2 · answered Jan 10 '18 at 18:22

To expand on what JNevill said, instead of building the SQL query dynamically, based on the example in the original post, you can select all possible columns from [student] and have the application layer return only the one column you want. Although it would mean more data being transferred between your backend and frontend, it will be far more secure. For example, if you were building in C# .NET (it can be any language/platform- just using this as an example to show you what I mean):

DataTable dataTable = new DataTable();
string columnName = "home_address";

string connString = @"your connection string here";
string query = "SELECT home_address, work_address, name /*... any other columns the user may want*/ FROM students";

//Alternatively, you can use the following, but it leaves you exposed to SQL injections, even after sanitizing:
//string query = $"SELECT [{columnName}] FROM students";

SqlConnection conn = new SqlConnection(connString);        
SqlCommand cmd = new SqlCommand(query, conn);
conn.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dataTable);
conn.Close();
da.Dispose();

List<object> list = new List<object>();
foreach (DataRow row in dataTable.Rows)
{
    list.Add(row[columnName]);
}

This way, your application layer handles some of the data management.

Is there a way to use a prepared statement to select a variable column?

2 Answers2