How to make an SQL query using variable column names?

Question

I am making a query like this:

$b1 = $_REQUEST['code'].'A'; //letter 'A' is concatenated to $_REQUEST['code']
$a = $_REQUEST['num'];
echo $b1.$a;
$sql = "SELECT '".$b1."' FROM student_record1 WHERE id=".$a;
$result = mysql_query($sql);
if(!$result)
{
    echo '<p id="signup">Something went wrong.</p>';
}
else
{
    $str = $row[0]
    echo $str;
}

Here $b1 and $a are getting values from another page. The 'echo' in the third line is giving a correct result. And I am not getting any error in SQL. Instead, I am not getting any result from the SQL query. I mean echo at the last line.

Answer 1

Don't do this, it breaks your relational model and is unsafe.

Instead of having a table with columns ID, columnA, columnB, columnC, columnD, columnE and having the user select A/B/C/D/E which then picks the column, have a table with three columns ID, TYPE, column and have TYPE be A/B/C/D/E. This also makes it easier to add F/G/H/I afterwards without modifying the table.

Secondly, with the extra column approach you don't have to build your SQL from input values like that. You can use prepared statements, and be safe from SQL Injection. Building SQL from unfiltered strings is wrong, and very dangerous. It will get your site hacked.

Answer 2

If you must use dynamic table/column/database names, you'll have to run them through a whitelist.

The following code will do:

$allowed_column = array('col1', 'col2'); 
$col = $_POST['col']; 
if (in_array($col, $allowed_column)) { 
    $query = "SELECT `$col` FROM table1 "; 
} 

See: How to prevent SQL injection with dynamic tablenames?

For more details.