SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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)
    PHP Code:
    $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

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,806
    Mentioned
    158 Post(s)
    Tagged
    3 Thread(s)
    try:

    PHP Code:
    $sql "select * from"$tablename
    That should work.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Enthusiast
    Join Date
    Feb 2012
    Location
    United Kingdom
    Posts
    78
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    try:

    PHP Code:
    $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.


    Quote Originally Posted by chasejbarnes View Post
    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)
    PHP Code:
    $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
    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:
    PHP Code:
    $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 Code:
    <?php

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

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

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2011
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!

  5. #5
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    65 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by chasejbarnes View Post
    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.

    Code PHP:
    $wList = array("myTable", "anotherTable");
     
    $reqTable = $_GET['tableName'];
     
    if(!in_array($reqTable, $wList)) {
       throw new exception('whoah buddy');
    }
     
    //do work


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •