CODEWITHSUNDEEP
mysqlmysql-workbenchmysql-error-1064
R. Bourgeon
R. Bourgeon

Reputation: 1013

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

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;

Upvotes: 1

Views: 1840

Answers (2)

JNevill
JNevill

Reputation: 50009

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.

Upvotes: 5

zambonee
zambonee

Reputation: 1647

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.

Upvotes: 2

Related Questions