Print MySQL Output Based on Query String Value

Question

I'm trying to take a query string param such as ?table=products and have mysql return all the rows for the "products" table in mysql. I tried running the code below in my browser, but I just get a blank white page. I know the mysql server/username/pass information is correct, I've tested the query in mysql and it works fine.

I guess I have two question:

1. What am I doing wrong?
2. How come I can't see any error messages when php has an issue?

e.g. code:

<?php

    // Get query string parameter value
    $keys = array_keys($_GET);
    $key = $keys[0];
    $value = $_GET[$key];

    // Setup connection to mysql database
    $serverName = "localhost";
    $username = "root";
    $password = "password";
    $dbname = "webserver";

    $conn = new mysqli($serverName, $username, $password, $dbname);

    // SQL query
    $sql = "SELECT * FROM $value";
    $result = $conn->query($sql);

    // Print results
    echo $result;

?>

Answer 1

Follow the instuctions on below link to enable php.ini errors

How do I get PHP errors to display?

VULNERABLE IMPLEMENTATION WARNING

The above comments clearly mention the side effects of this implementation.

Since knowing the actual bug is a developer's right! Continue reading the answer keeping the safety of software and its users in mind.

---

You are trying to print $result which is not valid since its an object.

You can do the following instead:

$response = array();
$sql = "SELECT * FROM $value"; 
$result = $conn->query($sql);

// Print results
if ($result) {
    while($row = $result->fetch_array(MYSQL_ASSOC)) {
        $response[] = $row;
    }

}
echo json_encode($response);

Answer 2

What am I doing wrong?

Sadly, pretty much everything.

// Get query string parameter value
$keys = array_keys($_GET);
$key = $keys[0];
$value = $_GET[$key];

You are dereferencing a named value based on its position. And its totally unnecessary. Consider:

$value=$_GET['table'];

...

$conn = new mysqli($serverName, $username, $password, $dbname);

Where is your error checking to see if $conn was initialized?

$result = $conn->query($sql);

again, no error checking.

echo $result;

$result here is a mysqli_result object. You need to call some methods on it to get the data out.

while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
     var_export($row);
}

How come I can't see any error messages when php has an issue?

Have you tested that the default handlers produce output in your browser? You're not overriding the config in php.ini in the code you've shown us. Did you check your logs?

 ini_set('diplay_error', 1);
 error_reporting(E_ALL);

I just get a blank white page

Would it be so hard to put

 print "finished";

at the end of the code? Then you'd at least know if the code executed.

Answer 3

The main issue you have right now is you need to get the results

while ($row = $result->fetch_assoc()) {
    //do something with row
} 

See ( for mysqli->query method ) http://php.net/manual/en/mysqli.query.php

false on failure and mysqli_query() will return a mysqli_result object on success

See ( for the result objects definition ) http://php.net/manual/en/class.mysqli-result.php

Now as others mentioned I would never just concatenate user data into your query. Imagine a hacker knows the name of a valid table, not hard considering your sending it through the request. All they would have to do is send a value like this:

$value = 'real_table; DROP DATABASE';

And your query becomes.

$sql = "SELECT * FROM real_table; DROP DATABASE";

I won't say that this would actually work as there are ( maybe ) some restrictions on running multiple queries in a single request,user permissions etc... That might save your bacon, but I certainly wouldn't risk it.

So you have 2 choices.

• Use a white list of tables
• Query the DB for the schema

The first one is easy to do, make a list of tables

$whitelist = [
  'table1',
  'table2'
];

Then compare your user input

$safeTable = false;
if( false !== ($index = array_search($table, $whitelist))) {
    $safeTable = $whitelist[$index];
}else{
   //log error and
   exit();
}

 // SQL query
$sql = "SELECT * FROM $safeTable";
$result = $conn->query($sql);

For the second one,

$schema = $conn->query('SELECT `TABLE_NAME` FROM `information_schema`.`TABLES` WHERE `TABLE_SCHEMA` LIKE "database"');

$whitelist = [];
while ($row = $result->fetch_assoc()) {
    $whitelist[] = $row['TABLE_NAME'];
}

$safeTable = false;
if( false !== ($index = array_search($table, $whitelist))) {
    $safeTable = $whitelist[$index];
}else{
   //log error and
   exit();
}

 // SQL query
$sql = "SELECT * FROM $safeTable";
$result = $conn->query($sql);

This will return a list of all the tables in that database, from which you can build an array and then compare. The nice thing about the second one is that if you add a table then you don't have to change the code, which may or may not be a good thing. You have to have a user with permission to read from information_schema database. And you have to do an additional query.

-note- I am not directly using the users input, I'm using their input to find my data. It's less prone to breaking when there is a coder error. Consider this:

 ///all my codes are broken;
--if(!in_array($_GET['table'], $whitelist))) {
--  //log error and
--   exit();
--}

 // SQL query
$sql = "SELECT * FROM {$_GET['table']}";
$result = $conn->query($sql);

Against this:

$safeTable = false;
 // all my codes are broken
-- if( false !== ($index = array_search($_GET['table'], $whitelist))) {
--     $safeTable = $whitelist[$index];
-- }else{
--    //log error and
--    exit();
-- }

 // SQL query
$sql = "SELECT * FROM $safeTable"; //$safeTable is undefined or false;
$result = $conn->query($sql);

Were using our code for inclusion, instead of exclusion. So if it breaks, it's never included. The other way, if it breaks it's never excluded. Which is not a situation we want to be even remotely possible.

I hope that helps you understand some of the pitfalls. The #1 rule for SQL (or anything on the web), is Never Trust the User. Never put their data into your SQL.