Do I have to guard against SQL injection if I used

Question

I understand that you should NEVER trust user input from a form, mainly due to the chance of SQL injection.

However, does this also apply to a form where the only input is from a dropdown(s) (see below)?

I'm saving the $_POST['size'] to a Session which is then used throughout the site to query the various databases (with a mysqli Select query) and any SQL injection would definitely harm (possibly drop) them.

There is no area for typed user input to query the databases, only dropdown(s).

<form action="welcome.php" method="post">
<select name="size">
  <option value="All">Select Size</option> 
  <option value="Large">Large</option>
  <option value="Medium">Medium</option>
  <option value="Small">Small</option>
</select>
<input type="submit">
</form>

Answer 1

The fact that you have restricted the user to only using values from a certain drop-down list is irrelevant. A technical user can capture the http request sent to your server before it leaves their network, alter it using a tool such as a local proxy server, and then continue it on it's way. Using the altered request, they can send parameter values that are not ones that you have specified in the drop down list. Developers have to have the mindset that client restrictions are often meaningless, as anything on a client can be altered. Server validation is required at every single point that client data enters. Attackers rely on the naivety of developers in this sole aspect.

Answer 2

The other answers already cover what you need to know. But maybe it helps to clarify some more:

There are TWO THINGS you need to do:

1. Validate form data.

As Jonathan Hobbs' answer shows very clearly, the choice of html element for the form input does not do any reliable filtering for you.

Validation is usually done in a way that does not alter the data, but that shows the form again, with the fields marked as "Please correct this".

Most frameworks and CMSes have form builders that help you with this task. And not just that, they also help against CSRF (or "XSRF"), which is another form of attack.

2. Sanitize/Escape variables in SQL statements..

.. or let prepared statements do the job for you.

If you build a (My)SQL statement with any variables, user-provided or not, you need to escape and quote these variables.

Generally, any such variable you insert into a MySQL statement should be either a string, or something that PHP can be reliably turn into a string that MySQL can digest. Such as, numbers.

For strings, you then need to choose one of several methods to escape the string, that means, replace any characters that would have side effects in MySQL.

• In old-school MySQL + PHP, mysql_real_escape_string() does the job. The problem is that it is far too easy to forget, so you should absolutely use prepared statements or query builders.
• In MySQLi, you can use prepared statements.
• Most frameworks and CMSes provide query builders that help you with this task.

If you are dealing with a number, you could omit the escaping and the quotes (this is why the prepared statements allow to specify a type).

It is important to point out that you escape the variables for the SQL statement, and NOT for the database itself. The database will store the original string, but the statement needs an escaped version.

What happens if you omit one of these?

If you don't use form validation, but you do sanitize your SQL input, you might see all kinds of bad stuff happening, but you won't see SQL injection! (*)

First, it can take your application into a state you did not plan for. E.g. if you want to calculate the average age of all users, but one user gave "aljkdfaqer" for the age, your calculation will fail.

Secondly, there can be all kinds of other injection attacks you need to consider: E.g. the user input could contain javascript or other stuff.

There can still be problems with the database: E.g. if a field (database table column) is limited to 255 characters, and the string is longer than that. Or if the field only accepts numbers, and you attempt to save a non-numeric string instead. But this is not "injection", it is just "crashing the application".

But, even if you have a free text field where you allow any input with no validation at all, you could still save this to the database just like that, if you properly escape it when it goes to a database statement. The problem comes when you want to use this string somewhere.

(*) or this would be something really exotic.

If you don't escape variables for SQL statements, but you did validate form input, then you can still see bad stuff happening.

First, you risk that when you save data to the database and load it again, it won't be the same data anymore, "lost in translation".

Secondly, it can result in invalid SQL statements, and thus crash your application. E.g. if any variable contains a quote or double quote character, depending which type of quote you use, you will get invalid MySQL statement.

Thirdly, it can still cause SQL injection.

If your user input from forms is already filtered / validated, intentional SQl injection may become less likely, IF your input is reduced to a hardcoded list of options, or if it is restricted to numbers. But any free text input can be used for SQL injection, if you don't properly escape the variables in SQL statements.

And even if you have no form input at all, you could still have strings from all kinds of sources: Read from the filesystem, scraped from the internet, etc. Noone can guarantee that these strings are safe.

Answer 3

A hacker can bypass the browser completely, including Javascript form checking, by sending a request using Telnet. Of course, he will look at the code of your html page to get the field names he has to use, but from then on it's 'everything goes' for him. So, you must check all values submitted on the server as if they did not originate from your html page.

Answer 4

Your web browser does not "know" that it is receiving a page from php, all it sees is html. And the http layer knows even less than that. You need to be able to handle nearly any kind of input that can cross the http layer (luckily for most input php will already give an error). If you are trying to prevent malicious requests from messing up your db, then you need to assume that the guy on the other end knows what he is doing, and that he is not limited to what you can see in your browser under normal circumstances (not to mention what you can fiddle with a browser's developer tools). So yes, you need to cater for any input from your dropdown, but for most input you can give an error.

Answer 5

One way of protecting against users changing your drop downs using the console is to only use integer values in them. Then you can validate that the POST value contains an integer, and use an array to convert that to text when needed. E.g:

<?php
// No, you don't need to specify the numbers in the array but as we're using them I always find having them visually there helpful.
$sizes = array(0 => 'All', 1 => 'Large', 2 => 'Medium', 3 => 'Small');
$size = filter_input(INPUT_POST, "size", FILTER_VALIDATE_INT);

echo '<select name="size">';
foreach($sizes as $i => $s) {
    echo '<option value="' . $i . '"' . ($i == $size ? ' selected' : '') . '>' . $s . '</option>';
}
echo '</select>';

Then you can use $size in your query with knowledge that it will only ever contain FALSE or an integer.