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!

score 1 · Accepted Answer · answered May 02 '13 at 18:45

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 02 '13 at 18:45

Chris

2,955
1
30
43

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 May 02 '13 at 19:48
Just ran across this post and wanted to update it. There's an SO article [HERE](http://stackoverflow.com/questions/11312737/can-i-parameterize-the-table-name-in-a-prepared-statement) 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! – wordman Jan 28 '15 at 04:30

score 0 · Answer 2 · answered May 02 '13 at 18:49

$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.

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. — wordman, May 02 '13 at 19:50

score 0 · Answer 3 · answered May 02 '13 at 20:01

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 02 '13 at 20:01

reikyoushin

1,993
2
24
40

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

3 Answers3