Using variable data to replace hard-coded table name in a query

Question

Let's say I have a prepared statement, in this case, one that selects photo information from a specific table in a database:

$conn = dbConnect('query');
$bgImage = "SELECT photo_fname
    FROM photos_bn
    WHERE gallery_id = ?
    LIMIT $curPage,".$totalPix;
$stmt = $conn->prepare($bgImage);
$stmt->bind_param('i', $gallery);
$stmt->bind_result($pFname);
$stmt->execute();
$stmt->store_result();
$stmt->fetch();

And since I have several tables in my database that deal with specific photo themes, one would assume I would need separate queries for each table. The above code selects information from the table, photos_bn, but I have other tables, we'll call them, photos_bq and photos_ps.

This may be an insanely obvious question, but how would I go about replacing the table name with a variable that could be passed to the page via query string or session variable so that the table name in the query is not hard coded, but is part of a prepared statement?

Many thanks!

Chris · Accepted Answer · 2013-05-02 18:45:34Z

1

You would have:

$bgImage = "SELECT photo_fname
FROM ".$_GET["querystringvar"]." WHERE gallery_id = ?
LIMIT $curPage,".$totalPix;

Be warned that leaves you very vulerable to SQL injection

answered May 2, 2013 at 18:45

Chris

2,9381 gold badge33 silver badges45 bronze badges

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

2 Comments

wordman Over a year ago

Thanks! I guess I should have been more specific...I want a solution that is a prepared statement. I am well aware of the dangers of SQL injection and only use prepared statements when dealing with variables.

wordman Over a year ago

Just ran across this post and wanted to update it. There's an SO article HERE that says we can't parameterize table names in a query with placeholders. Following the answer by having a white-list of tables to check against first if the table name is coming from user input. So this was the correct answer after all!

Ivan Yonkov · Accepted Answer · 2013-05-02 18:49:10Z

0

$bgImage = "SELECT photo_fname ";

    if ($querystring == 'bn') {
       $bgImage .= " FROM photos_bn ";
       }
    else if ($querystring == 'bq') {
       $bgImage .= " FROM photos_bq ";
       }
    else {
       $bgImage .= " FROM photos ";
       }
$bgImage .= "WHERE gallery_id = ?
            LIMIT $curPage,".$totalPix;
    $stmt = $conn->prepare($bgImage);

Something like that I guess. Check the value of your query string and concat whatever you need. Don't concat the pure querystring into the SQL string. Which of course is still kind of hardcoded way. But I would never suggest to put in your statement something recieved from the user without any checks.

answered May 2, 2013 at 18:49

Ivan Yonkov

7,0291 gold badge20 silver badges25 bronze badges

1 Comment

wordman Over a year ago

I understand about checking variables first, thanks. Actually, I like this approach you suggest, though I'd like to do it with a variable though.

reikyoushin · Accepted Answer · 2013-05-02 20:01:51Z

0

something like this?

$prep = $mysqli->prepare("SELECT photo_fname FROM photos_? WHERE gallery_id = ? LIMIT ?,?");
$prep->bind_param("siii",$_GET['theme_suffix'],$gallery_id,$curPage,$totalPix);

answered May 2, 2013 at 20:01

reikyoushin

2,0212 gold badges24 silver badges42 bronze badges

Collectives™ on Stack Overflow

Using variable data to replace hard-coded table name in a query

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related