Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by anatskiy
anatskiy
7,940 Points

How to pass table name as PHP function's parameter in SQL query?

Hi guys, I'm trying to pass table name as PHP function's parameter in SQL query, but nothing happens... It displays "Fail". I don't get it...

function get_objects($table_name) {

    // Connecting to the database
    require(ROOT_PATH . "inc/database.php");

    try {
        $results = $db->prepare("
            SELECT id, name
            FROM ?
            ORDER BY name ASC");
        $results->bindParam(1, $table_name);
        $results->execute();
    } catch (Exception $e) {
        echo "Fail :(";
        exit;
    }

    $objects = $results->fetchAll(PDO::FETCH_ASSOC);

    return $objects;
}
Thiago van Dieten
Thiago van Dieten
17,059 Points
Thiago van Dieten
Thiago van Dieten
17,059 Points

What happens when you echo out $e?

anatskiy
anatskiy
7,940 Points

Thiago, 

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''publishers'
            ORDER BY name ASC' at line 2' in ...
Thiago van Dieten
Thiago van Dieten
17,059 Points
Thiago van Dieten
Thiago van Dieten
17,059 Points

Thanks Eugene.

That would mean your the SQL query it produces is : SELECT id, name FROM ''publishers' ORDER BY name ASC'

Which I don't think it's valid SQL syntax because of the quotation mark placements. So you should see if there's something inserting a ' in your $table_name. Hope this steered you in the right direction.

Thiago van Dieten
Thiago van Dieten
17,059 Points
Thiago van Dieten
Thiago van Dieten
17,059 Points

Sorry for double posting but Treehouse won't let me edit my old post. :(

If it's a table name, can you remove the quotation marks around $table_name? I think that's causing it.

anatskiy
anatskiy
7,940 Points 
    *Thiago,* look:

function get_objects($table_name) {

    echo "<pre>table_name: " . $table_name . "\n" . "\n";

    // Connecting to the database
    require(ROOT_PATH . "inc/database.php");

    try {
        $results = $db->prepare("
            SELECT id, name
            FROM ?
            ORDER BY name ASC");
        $results->bindValue(1, $table_name);
        $results->execute();
    } catch (Exception $e) {
        // echo "Fail :(";
        echo $e;
        exit;
    }

    $objects = $results->fetchAll(PDO::FETCH_ASSOC);

    return $objects;
}

On the screen:

table_name: publishers

exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''publishers'
            ORDER BY name ASC' at line 2' in

So there is no any ' in $table_name. It's so strange...

2 Answers

Corey Montgomery
Corey Montgomery
18,468 Points
Corey Montgomery
Corey Montgomery
18,468 Points

You could try some concatenation such as 'SELECT id, name FROM ' . $table . ' ORDER BY name ASC'...

Although from what I have read that is bad practice. As far as I can remember, you cannot bind the table name like other aspect of the PDO query. Another option is to "whitelist" the table name. The details of which I am not fully informed on, however when messing around with it myself i started by creating a switch statement in a function that I could pass a table name to which would match and then return a string containing the query I wanted to run. However, I know there has to be a better option and I am curious to see what the instructors would advise on.

anatskiy
anatskiy
7,940 Points
anatskiy
anatskiy
7,940 Points

Thiago, Unfortunately, I can't: it's impossible to pass an argument without them.