PHP SQL Injection is it safe?

Question

I want to know if my code is 100% secure agasint SQL injection, it looks like this:

$table = $_GET['table'];
switch ($table) {
case 'data':
    $sql = "select * from $table";
    break;
case 'anothertable':
    $sql = "select * from $table";
    break; 
}
$con = new mysqli($hostname,$username,$password,$db_name);
$result = $con->query($sql);

Answer 1

If $table is not matched by the switch, you haven't set $sql at all...

Otherwise, you have avoided the risk of injecting bad things via $table by whitelisting the acceptable table names.

One point I would make is that one has to read quite closely to see that $table has changed from an untrusted input to a validated table name. So anyone coming to your code in future may think

1. that you have a problem here that needs fixing, or
2. that interpolating variables into SQL queries is generally acceptable.

So probably worth going out of your way to explain in comments what you're doing, and (more importantly) why.

Answer 2

In your place I would avoid such a dynamical querying based on the user input, as it smells of a bad application design,

But in regard of SQL injection your code is safe, as it's rightfully implementing the only proper strategy possible - the whitelisting.

Answer 3

Your code is not safe. Here is an example of how you would make it safe with MySQL (PDO). I'm doing it in PDO because that is what I use often and not mysqli :)

$table = $_GET['table'];

$valid_command = FALSE;//value becomes true if you find your
//value in the following if conditional

if (($table==='table')||($table==='anothertable'))
{
  $valid_command = TRUE;
}

if ($valid_command)
{
  $sql = "SELECT * FROM table";
  $sqlPrepared = $conn->prepare($sql);
  $sqlPrepared->bindParam(':table', $table);
  $sqlPrepared->execute();
}

If you wish to do this in mysqli, it is easy to adapt it. Hope that helps.