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.

Have you tried outputting what $sql contains? Also, see above. — sberry
– sberry, Commented Nov 3, 2011 at 5:35

Konerak · Accepted Answer · 2011-11-03 07:42:09Z

4

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.

edited Nov 3, 2011 at 7:42

answered Nov 3, 2011 at 5:37

Konerak

39.8k13 gold badges102 silver badges121 bronze badges

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

8 Comments

Your Common Sense Over a year ago

A rare guest on this site - a sensible answer.

Phil Over a year ago

A much more robust solution. +1 to you sir or madam

yaswanth chilukuri Over a year ago

okk... thank u... actually i am not greately aware of this sql injections... can u suggest me some good source to steady them...

Konerak Over a year ago

@yaswanthchilukuri: You should start by reading what SQL Injection is, Wikipedia is good enough for that. Only after you understand how the vulnerability manifests, you can start protecting yourself from it. Use the libraries that exist, don't invent your own (it will fail). Since you seem to be using PHP, you will be using mysql_real_escape_string or prepared statements.

Konerak Over a year ago

I agree it does not help with field names, but since I don't think the field names solution is a good one, I proposed an alternative. If you must use field names, a whitelist is the best option.

|

Community · Accepted Answer · 2017-05-23 11:55:50Z

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.

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Nov 3, 2011 at 10:56

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Collectives™ on Stack Overflow

How to make an SQL query using variable column names?

2 Answers 2

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related