Can I use a variable as table name in a MySQL query?

Question

I want a MySQL query with a variable as table. Here is what I'm looking for:

mysql_query("SELECT * FROM $var WHERE $anothervar ='1'"))

How can I do this?

Answer 1

First of all, stop using mysql_ functions. They have been deprecated. Check out mysqli or PDO.

When using PHP to query the database you can build the entire query as you please. You can type out the full query or add variables for more dynamic queries. What you did is correct (Except for the double parenthese on the right).

<?php
$query = "SELECT * FROM users WHERE id ='1'";

//the above will be the same as

$var = 'users';
$anothervar = 'id';
$query = "SELECT * FROM $var WHERE $anothervar ='1'";

//execute query.
?>

So yes, you can just use a variable table name when constucting a query in PHP. You do need to keep in mind that all the variables you use can lead to SQL injection. So if you are getting the values for table and column names from user input, make sure you validate them properly!

Simply using mysql_real_escape_string() or mysqli_real_escape_string() will not work on table and column names, because they are not enclosed in quotes. My vote would go for a whitelist if you receive user input for columns/tables and are concatenating queries. EG:

<?php
$allowed_tables = array("users", "articles", "messages");

if (!in_array($var, $allowed_tables)) {
  echo 'Invalid table';
  exit();
}
?>

Another good read would be how-to-prevent-sql-injection-in-php which also goes into parameterized queries, which is basically what you want to do, but then in a more secure manner then concatenating strings.

Answer 2

Write your sql query as

mysql_query("SELECT * FROM $var WHERE anothervar ='1'");

You have placed extra parenthesis without semicolon, I beleive it welll definitely work.

Answer 3

You're going to hate my answer, but I really believe it's the right one (if not a direct one).

I would avoid this approach if at all possible (or at least make FOR SURE you sanitize your 'inputs'). Without knowing where those variables came from I'd say you've left yourself open to a sql-injection attack. Granted, it's more difficult to create dynamic sql via parameterization, but if you can get around this problem in any other way please do so.

It's much better to have to write a bunch more code or have a solution that isn't so elegant than to have a nice clean codebase and a door wide open giving curious minds access to your dbms.

Also as a practice I'd consider using stored procedures in favor of in-line sql. It will make your sql easier to maintain and offer you better flexibility in the long run. The way to do what you want via a stored procedure would be to build a prepared statement dynamically inside the procedure, but that's going to open you up to the same sql-injection risks as the way you're attempting to do this now.

Answer 4

Your query is looking ok. There are just an extra parenthesis. Please try to use below code.

mysql_query("SELECT * FROM $var WHERE $anothervar ='1'");

Answer 5

mysql_query("SELECT * FROM $var WHERE $anothervar ='1'")); // TYPO ERR  ))


mysql_query("SELECT * FROM $var WHERE $anothervar ='1'");

IF $var ,$anothervar have been defined,AND GET ERROR ..TRY

mysql_query("SELECT * FROM `$var` WHERE `$anothervar` ='1'");

** mysqli is good

Answer 6

In addition to what the other answers have said if $var or $anothervar may be a reserved word in sql then they should be surrounded by backticks.

"SELECT * FROM$varWHERE$anothervar` ='1'"

I agree with @Hugo that you should have a whitelist of allowed values for $var and $anothervar.

Answer 7

If you want to convert array to string in php, use implode(sep,arr) function. If your $var is array of table name or something like that, then:

mysql_query("SELECT * FROM ". implode(',',$var) ." WHERE $anothervar ='1'")

Note: If your table name is the same as keyword in mysql, this code won't work. So, to prevent that, change code like this:

mysql_query("SELECT * FROM `". implode('`,`',$var) ."` WHERE `$anothervar` ='1'")

Answer 8

You should use something like this:

mysql_query("SELECT * FROM " . $var . " WHERE " . $anothervar . " ='1'"))