SELECT * FROM $variable as table name problem

Hey everyone,

Im trying to load a page with a table from my mysql database.
Im using a dynamic variable as the table name instead of a static table name( this is obviously giving the mysql_error)


$classcode = mysql_real_escape_string($_POST['classcode']);
$q = "SELECT * FROM `$classcode`";
$query = mysql_query($q) or die(mysql_error);

Ive tried with and without single quotes and all sorts of ways of SELECTing from a variable but no luck. I understand that theres a different way to handle it if the table name is dynamic but im not sure.

Unfortunately, because each of these tables is created dynamically, I cant select information from one with a static name, as each name will be generated seperately, etc, hoepfully you see the dilemma.

Any help at all is greatly appreciated. Thanks in advance :wink:

try:


$sql = "select * from". $tablename;

That should work.

That will cause an error because you’re joining the database name to the from statement. Either way though, you’re applying the same principles as in OPs code, just in a different manner.

Have you tried dumping the variable $_POST[‘classcode’]? Dynamically referencing a table name will not be the cause of an error; if you are receiving an error, then post it here please.

Also, the mysql_real_escape_string() (MRES) function is being applied incorrectly here, leaving your database wide open to SQL injection attacks. The sole purpose of MRES is to escape quotes (single and double), not grave accents. Take the following:


$b = <<< 'php'
t'e"s`t
php;

echo mysql_real_escape_string($b); // outputs: t\\'e\\"s`t

The easiest way to validate table/column names is to use the whitelist approach, whereby you predetermine all possible values allowed (usually in the form of an array), and then compare the input value to see if there’s a match:


<?php

$allowed = array('table1', 'table2', 'table3');

if(in_array($_POST['sketchy'], $allowed, TRUE)) {
    // valid
}

Thanks for the info regarding sql injections, tpunt.

My $_POST[‘classcode’]; brings back the table name, so no problem there.
The SELECT statement simply doesnt want to accept a variable. This is my main issue.
Whether or not the query is validated or escaped, I just need to be able to access this dynamically created database. Of course security measures will come in after I get it to work but I cant seem to find out why the variable is giving the problem. Ill use the whitelist technique tpunt gave me but need a SELECT fix more than anything. Or perhaps Im not understanding your answer.

Thanks regardless, and anyone else who can contribute, please do, thanks!

can you do a vardump of the variable right before use?

As for the security, use proper pdo statement as well as a whitelist of tables you wish to allow.


$wList = array("myTable", "anotherTable");

$reqTable = $_GET['tableName'];

if(!in_array($reqTable, $wList)) {
   throw new exception('whoah buddy');
}

//do work