Sanitizing/Escaping user input and output

Question

I know I've already asked a question about sanitizing and escaping, but I have a question which didn't get answered.

Okay, here it goes. If I have a PHP-script and I GET the users input and SELECT it from a mySQL database, would it matter/be any security risk, if I didn't escape < and > through the use of either htmlspecialchars, htmlentities or strip_tags and therefore allowed for HTML tags to be selected/searched from the database? Because the input is already being sanitized through the use of trim(), mysql_real_escape_string and addcslashes (\%_).

The problem using htmlspecialchars is that it escapes ampersand (&), which the user input is supposed to allow (I guess the same goes for htmlentities?). With the use of strip_tags, something like "John" results in the PHP-script selecting and displaying results for John, which it isn't supposed to do.

Here is my PHP-code for sanitizing the input, before selecting from the database:

if(isset($_GET['query'])) {
  if(strlen(trim($_GET['query'])) >= 3) {
      $search = mysql_real_escape_string(addcslashes(trim($_GET['search']), '\%_'));
      $sql = "SELECT name, age, address WHERE name LIKE '%".$search."%'";
      [...]
  }
}

And here is my output for displaying "x matched y results.":

echo htmlspecialchars(strip_tags($_GET['search']), ENT_QUOTES, 'UTF-8')." matched y results.";

sanitization/escaping should be done for your target environment. html cannot 'affect' sql, so it's safe to leave it unescaped in the DB. in fact, it's better to NOT escape until you retrieve it and try to display it in a browser context. that saves you having to unescape if you're actually searching for something, and eliminates the possibility of double-escaping, e.g. `&` — Marc B, Apr 23 '13 at 15:49
no. you're the deprecated mysql_*() functions. switch over to mysqli or PDO. and remove the addcslashes() call. It's unecessary and simply adds another layer of pointless escaping to the data in the db. mysql_real_escape_string is the only thing you need to safely use the form data in a query. — Marc B, Apr 23 '13 at 15:57
I know I'm using the deprecated `mysql_*()` function. I have been, as I said to @jball037, been suggested it multiple times before. But I just wanted to know if what I mentioned above is going to cut it. By the way, the `addcslashes` is there to protect my PHP-script from displaying everything from the database, as `mysql_real_escape_string` (as far as I have understood) isn't escaping wildcard characters such as `%` and `_`. And the reason as to why I've added `\` in there to, is because a search for "John\'s" would search the database for "John's", which isn't what is intended here. — AnonymousJ, Apr 23 '13 at 16:06
you expect your users to enter `John\'s` into the search box? That'll never fly. You let them enter whatever they want, then use appropriate escaping to get that data SAFELY into a query. remember: mysql doesn't actuall **SAVE** backslashes in the text. they're removed as part of the query parseing stage. If you have `INSERT ... VALUES ('John\'s')`, then `John's` is what is actually saved in the db, not `John\'s`. — Marc B, Apr 23 '13 at 16:15
I do not expect them -- but I can't let my doubt ruin it for me. I get what you're saying - that I should allow my users to enter `John's` or `John\'s` etc., by stripping HTML tags and allowing backslashes, and then display the results from the database, as if they had searched for `John's`. But is my way of doing it (displaying an error saying that their search didn't match any results) wrong? — AnonymousJ, Apr 23 '13 at 16:23

score 2 · Answer 1 · answered Apr 23 '13 at 15:52

2

A good way to go about this is to use MySQLi, it uses prepared statements which essentially escapes everything for you on the backend and offers strong protection against SQL injections. Not escaping GET data is just as dangerous as not escaping any other input.

answered Apr 23 '13 at 15:52

jball037

1,780
1
14
18

1

Thank you for pointing me to the MySQLi function. It has been suggested to me multiple times, and I will go read on it soon enough. But I guess going from MySQL to MySQLi (or for that sake PDO) isn't something that can be accomplished overnight, right? – AnonymousJ Apr 23 '13 at 15:57
PDO does have some advantages over mysqli, notably named placeholders. It can be accomplished "overnight" if you don't have too much database code to adapt. [Learning PDO](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) takes only about half an hour. – tadman Apr 23 '13 at 15:59
Depends on how much code (and MySQL queries) you have. You will have the switch all of your current MySQL queries to a MySQLi format, but other than that there isn't much overhead. I can't speak to PDO as I don't know anything about it :) – jball037 Apr 23 '13 at 16:06
Well, my PHP-script is pretty simple. It has a form-input, where the user searches, and the results of that search is then displayed to the user. It only has the MySQL query mentioned in my post. I guess it would be pretty easy? I'll certainly look into it. :-) – AnonymousJ Apr 23 '13 at 16:12

score 1 · Answer 2 · answered Apr 23 '13 at 16:04

There's two different concerns here that you've identified.

User Data in SQL Statements

Whenever you're constructing a query, you need to be absolutely certain that no arbitrary user data will end up in it. These mistakes are called SQL injection bugs and are the result of failing to correctly escape your data. As a general rule, you should never, ever use string concatenation to compose a query. Whenever possible, use placeholders to ensure that your data is correctly escaped.

User Data in HTML Document

When you're rendering a page that contains user-submitted content, you need to escape it so that the user cannot introduce arbitrary HTML tags or scripting elements. This is avoids XSS issues and means that characters like & and < do not get interpreted incorrectly. User data of "x < y" wouldn't end up breaking your page.

You'll always need to escape for whatever context you're rendering user data into. There are others, like inside a script tag or in a URL, but these are the two most common ones.

Sanitizing/Escaping user input and output

2 Answers2