SQL injection | Changing id values

Question

I'm stuck with an sql injection problem. My page displays the table's data as follows:

<input type="checkbox" name="id[]" value="<?php echo $row['id']; ?>" /><?php echo $row['data']; ?><br />

How can I be sure that the submitted value is the id of this specific row an not a random number "injected" in the page?

Obviously, I would start checking that the id returns true to is_numeric() / get it through mysql_real_escape_string().

Then, I thought of two options:

• Adding a hidden input with a copy of the $row['data'] so that I can check the correspondence between the id and the data before any mysql_query()

• Changing the row's id from an auto incremented number to a large random number, so that I could lower the chance of a lucky hit.

Do I have it wrong? Any better idea? Thanks for your help!

Answer 1

You can't. Both of your options are fundamentally flawed as well:

• One that can change a checkbox's value can very well change a hidden input's value.
• Your "random IDs" can still be saw on Dev Tools, Firebug or similar tool.

Instead of worrying about how the user sent the data, you should worry whether the data is valid and whether the user has permission for the given action.

---

Also, is_numeric is not my favorite as it will return true for hex and exponential notation. I'd recommend checking with ctype_digit or simply do an (int) cast, e.g.:

if (!isset($_POST['id'])) die('invalid data');
$id = (int) $_POST['id'];
if ($id == 0) die('invalid id');

Non-numeric strings are converted to 0 and auto increment fields usually have 1 as the first value. In case 0 is a valid value you'll need to tweak the code above, e.g.:

if (!isset($_POST['id']) || !ctype_digit($_POST['id'])) die('invalid data');
$id = (int) $_POST['id'];

Afterwards, check whether the given ID exists in your DB. Do the proper permission checks and that's it.

---

It doesn't matter how your server got the data, what matters is the data being valid and the user having permission to perform the given operation. Anything that you do in the front-end/interface can be easily changed and manipulated by a hacker or any mid-experienced web developer.

Focus into restringing non-authorized access and keeping your DB integrity. It doesn't matter whether the request is being made from your page, from a tampered page or through a terminal, all the headers and posted data can be easily reproduced to look like a request being made from your page.

---

After all this, I'm not sure whether you can call this "SQL Injection". Your application's function requires some input which includes an integer value. Now what is left is checking whether the necessary input has been provided and is valid. All user input must be treated as unsafe and be properly validated and escaped before being throw into a query.

Also, look into PDO which handles value escaping pretty well. The mysql_* extension and mysql_real_escape_string function are deprecated and very human-error prone.

As for preventing against SQL injection, the linked thread in the question's comments is a good read.