1

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.";
Improve this question
6
  • 1
    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. &amp;amp; Commented Apr 23, 2013 at 15:49
  • Okay, thank you. So, what I do seems correct enough, yea? Commented Apr 23, 2013 at 15:55
  • 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. Commented Apr 23, 2013 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. Commented Apr 23, 2013 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. Commented Apr 23, 2013 at 16:15

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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?
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 takes only about half an hour.
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 :)
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. :-)
1

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.