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.

You might want to read this: http://www.php.net/manual/en/security.database.sql-injection.php — Lorenz, Jan 27 '14 at 23:34
possible duplicate of [How can I prevent SQL injection in PHP?](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — , Mar 31 '14 at 01:33

CmdrMoozy · Answer 1 · 2014-01-27T23:44:50.227

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.

edited Jan 27 '14 at 23:44

answered Jan 27 '14 at 23:33

CmdrMoozy

3,870
3
19
31

It looks like PDO is already in use in the question (or what else do PDO functions and things like `PDO::FETCH_ASSOC` indicate to you?). – miyasudokoro Jan 27 '14 at 23:41
True, I agree PDO is already in use. The particular thing to note is that the OP should be using `PDO::prepare` instead of `PDO::query` (see: http://www.php.net/manual/en/pdo.prepare.php). However, I still like to mention other alternatives, for completeness' sake. – CmdrMoozy Jan 27 '14 at 23:44
-1 for you have quite strange idea on what escaping function does. – Your Common Sense Jan 28 '14 at 18:47
What do you mean? It "escapes special characters in a string for use in an SQL statement, taking into account the current charset of the connection" - i.e., if `$bash` from the OP contained `'); DROP TABLE ...`, it should escape the `'` , `)`, and `;`, rendering the value invalid but innocuous. What do *you* think it does? – CmdrMoozy Jan 28 '14 at 18:50

score 0 · Answer 2 · answered Jan 27 '14 at 23:34

0

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;

answered Jan 27 '14 at 23:34

feeela

29,399
7
59
71

So maygbe something like $bash = fasle; if (is_int($_REQUEST['id']) && $_REQUEST['id'] >= 0 && $_REQUEST['id'] <= 999) { $bash = $_REQUEST['id']; } – user1425394 Jan 28 '14 at 00:29
@user1425394 Yes, but I would just leave through any IDs and check whether there where any DB results. Limiting the IDs on the application level sets a static limit to what you could insert as pages in the DB. That is why I'm just casting that to int instead f checking it via `is_int` (plus you are using three conditions, which is slower than a simple type conversion) – feeela Jan 28 '14 at 00:34

score 0 · Answer 3 · answered Jan 27 '14 at 23:43

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.

in all my forms, logins, register, mail, on all my other sites i use prepared statements. this particular issue is different. if (ctype_digit($_REQUEST['id']) && $_REQUEST['id'] >= 0 && $_REQUEST['id'] <= 34){ $bash = $_REQUEST['id']; } This is what stoped the sql injection — user1425394, Jan 28 '14 at 03:24
yea that stops the injection, but in the example you provided you're not using a prepared statement, which would have also worked. Though by hard coding the <= 34 into php you'll have to update both your code and the database if you add more, instead of just inserting another row. — ThisGuy, Jan 30 '14 at 18:24

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

3 Answers3