Read and display data from MySQL table

Question

Is there any security threat here?

$con = mysqli_connect("***", "***", "***", "***");
mysqli_query($con, "set names 'utf8'");
$id = $_GET["id"];
$result = mysqli_query($con,"SELECT * FROM document_$id");
while($row = mysqli_fetch_array($result)) {// echo table }

I am curious about my use of the $_GET variable, since that can be altered by a user. If there is a danger here, could something like this work?

If $_GET['id'] is not an integer -> redirect to error page

Answer 1

The comments have got it right - you're vulnerable to an SQL injection. If I navigate to your page with the id parameter set to something like this:

http://yoursite.com/database-operation?id=0;DROP%20TABLE%20%`Students`;--

then your SQL query will end up looking like this:

SELECT * FROM document_0;
DROP TABLE `Students`;

If you have a table called Students, that's about to get totally deleted.

---

Instead, you should be looking at using prepared statements. Both the mysqli and PDO extensions for PHP have this functionality; a quick Google search will turn up plenty of tutorials on it.

You can also help prevent this via user privileges: if all you're ever doing is SELECTs, then the MySQL user that takes these queries doesn't need the privilege to DROP tables. Use the principle of least privilege: give users (and any other entity) only the privileges they need to perform their task, nothing more.

Answer 2

What you really want to do here is use a prepared statement, like so:

$id = $_GET['id'];
$document_id = 'document_' . $id;

$query = $mysqli->prepare('SELECT * FROM ?');
$query->bind_param('s', $document_id);
$query->execute();