2
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ What if they're fetching a document which ID is, for example, 0; DROP TABLE Students? Solution is never try to imagine how to clean your inputs (even if in this case it may work) but always to use query parameters, without exceptions. It has to be a rule otherwise your code will fail (someday) in a way you didn't expect or in a place you didn't consider or just simply because you forgot it. \$\endgroup\$ – Adriano Repetti Jan 19 '16 at 14:04
  • \$\begingroup\$ Some of the data from the table that will be displayed will be data put there by users (not the id, of course). That part isn't developed yet, but assume no user has managed to insert something harmful into the data that is getting displayed in the above example. In other words, assume the data in the table is all safe. \$\endgroup\$ – twharmon Jan 19 '16 at 14:08
  • \$\begingroup\$ Problem is not data in the table but SQL concatenation. If instead of an ID a malicious user will append (for example) a SQL snippet (see meme)? It will be executed. Moreover...what if they have not permissions to read that table (because it belongs to other users?) Simply never ever trust your inputs, in any way. \$\endgroup\$ – Adriano Repetti Jan 19 '16 at 14:10
  • \$\begingroup\$ "always use query parameters" What does that mean, and how can I implement it in this example? \$\endgroup\$ – twharmon Jan 19 '16 at 14:10
  • \$\begingroup\$ Canonical example: stackoverflow.com/a/60496/1207195 (BTW I think question is more on topic on SO than on CR - even if probably it may be closed as dupe). \$\endgroup\$ – Adriano Repetti Jan 19 '16 at 14:11
2
\$\begingroup\$

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.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Let me know if this is better, I only changed one line of code: $id = mysqli_real_escape_string($con, $_GET["id"]); (I tested the mysqli_real_escape_string() function, but it didn't seem to alter the string any, even if I ran this string through the function: DROP%20TABLE%20%'Students';--.) And for the user privileges, I selected the following privileges for the public: CREATE, SELECT, DELETE, UPDATE, INSERT. I suppose these are the only privileges to be needed. Is that what you were suggesting? It seems to me that this will only help prevent some attacks, but not all. \$\endgroup\$ – twharmon Jan 20 '16 at 5:36
  • \$\begingroup\$ Sorry, ignore the middle part of my previous comment above. The function mysqli_real_escape_string() did alter the string, I just didn't know what to look for. \$\endgroup\$ – twharmon Jan 20 '16 at 5:48
0
\$\begingroup\$

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();
| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.