how to protect from sql injection when using php?id=

Question

Hello I need help finding a way to protect from sql injection on my current project, Im making bash tutorial site but ive run into a problem. I put most my content in database and depending on what link the user clicks it will pull different data onto the page. This is how im doing it

<a href="bash_cmds.php?id=1">apt-get </a><br>

And on bash_cmds.php

<?php
 require_once("connections/connect.php");
  $dbcon = new connection();
  $bash = $_REQUEST['id'];

  $query2 = "SELECT * FROM bash_cmds WHERE id = $bash ";
  $results = $dbcon->dbconnect()->query($query2);

  if($results){

  while($row = $results->fetch(PDO::FETCH_ASSOC)){
  $bash_cmd = $row['bash_command'];
  $how = $row['how_to'];
  } 
  } else { return false; }
  ?>

  <?php echo $bash_cmd ?>
  <br />
  <table>
<tr><td><?php echo $how ?> </td></tr>

</table>

However this leaves me vulnerable to sql injection, I ran sqlmap and was able to pull all databases and tables. Can someone please help I would appreciate it a lot the infomation would be invaluable.

Answer 1

There are a couple of ways to do this. I believe the best way is to use some database abstraction layer (there's a good one built into PHP called PDO) and use its prepared statements API. You can read more about PDO here, and you can see the particular function which binds a value to a ? placeholder here.

Alternatively, you could use the mysqli_real_escape_string API function, which should escape any SQL inside your $bash variable.

Of course, in this particular case, simply ensuring the ID is an integer with (int) or intval() would be good enough, but the danger of using this approach in general is that it's easy to forget to do this one time, which is all it takes for your application to be vulnerable. If you use something like PDO, it's more "safe by default," one might say - it's more difficult to accidentally write vulnerable code.

Answer 2

You could bind the values to a prepared statement.

But for something simple as a numeric variable a cast to an integer would be good enough:

$bash = (int) $_REQUEST['id'];

Using this, only a number would get stored into $bash. Even if someone enters ?id=--%20DROP%20TABLE%20xy;, as this will get casted to 1;

Answer 3

I've found one of the easiest ways to protect against injection is to use prepared statements.

You can do this in PHP via PDO, as CmdrMoozy suggested.

Prepared statements are more secure because the placeholders ? can only represent values, and not variables (ie: will never be interpreted as a table name, server variable, column name, etc. It {currently} can't even represent a list of values). This immediately makes any modification to the logic of the query immutable, leaving only possible unwanted values as injection possibilities (looking for an id of 'notanid'), which in most cases isn't a concern (they'd just get a blank/wrong/error page, their fault for trying to hack your site).

Addendum: These restrictions are what is in place when the prepared statements are done on the server. When prepared statements are simulated by a library instead of actually being server side the same may not be true, but often many of these are emulated.