We all have search functionality in our applications. It is one of the most core features you would find ranging from searching for users, products, companies, etc. 

But are you sure your search functionality is doing exactly what it is supposed to do? You might have written test cases for it as well. But still, there are things that can go haywire. Here’s what happened with mine:

Background

Let’s start with a problem which I faced recently in one of my projects. I was working on an application that manages documents so that you can understand the need for security. Here’s the stack that I used.

• Web Stack:  Grails
  One good thing about this FW is that all database access via GORM is automatically SQL escaped preventing SQL injection attacks.
• Database: MySQL
  MySQL is an open-source relational database management system (RDBMS).
  

My application had this simple autosuggestion, which suggests addresses to the user, and the user can select one of address. We used some JavaScript (a jQuery plugin) for autosuggestion and to restrict the results we used the plugin’s validation to have at least 3 characters. It worked fine for years. However, a new user came into the picture and added 3 spaces in the field making the plugin validation passed which went to the backend. Moving further, backend code removed the trailing spaces (so nothing left) and provided the result which was full data. The response was in JSON but was quite big, possibly of 20-50 MB or more. Multiple such queries or you can say attacks at the same time crashed the system. It was not a security breach unlike SQL injections, because the user had access to all that data but not at the same time and that too not that frequently. It was even hard to guess it as the logs looked fine, few autosuggest requests and the system got crashed. We fixed this issue by further UI and backend validations.

Till this point, we only faced the problem which was related to our particular application, and this may or may not be in your application.

But digging further into this I found out that it was not enough. There was still a possibility of attacks using SQL Wildcards, and the result would be a similar application crash.
After this, I did test many other applications as well and found out that the problem was same with many applications. However, it was evident that facebook, google, and others would pass the test.

MySQL WildCards

Let’s understand how SQL Wildcards (%, _) are a threat.

Generally, in the search functionality, we have LIKE statement at backend and user input goes directly into the SQL. Hibernate and other frameworks don’t see any problem with these wildcards because they are supposed to be handled at the developer level. Generally, SQL wildcards are meant to be a feature for the developer, not for the user. But what is stopping the user to use this feature?
This might not crash or affect your application like it did to mine, but it is still a bug, and something it does is more than expected.
Using and allowing wildcards in critical places like SQL query is never a good idea. It allows the attacker to execute a very long and time taking resource sensitive action on the server. For, e.g., search everything which is like %. Now if such resource sensitive actions are performing multiple times, they can cause an application level DOS with or without any framework, it doesn’t matter much. What is important is how capable your application server is and how fast does the action gets executed.

In ideal cases the query looks like this:

SELECT * FROM Address WHERE street_name LIKE '%foo%';

And we expect the user to be nice and pass foo. But then the user starts to do something like this:

1. Results in whole Data:
   SELECT * FROM Address WHERE street_name LIKE '%%%%';
2. Resource intensive string comparison at SQL level, Hangs the SQL
   SELECT * FROM Address WHERE street_name LIKE '%_[^!_%/%a?F%_D)_(F%)_%([)({}%){()}£$&N%_)$*£()$*R"_)][%](%[x])%a][$*"£$-9]_%';

You never know, the people you might be providing service to, are the ones to bring your service down. So the security of your app is in your hands.

Now that we know the problem, the impact varies from application to application. In my case, the whole application crashed.

So how do we prevent this?

Solution

1. Sanitize The Input: Input always needs to be verified before actually using them.
   • For Wildcards, you can escape the particular characters.
     So Input strings like %%% should be encoded to \%\%\% before sending it to the database.
   • You should also limit the number of characters in input as per the requirements.
2. Limit The Output: In the case of SQL Wildcards, the search output could be all data, so if you limit the output to let’s say 10 results only, it won’t fetch all results from the SQL and won’t send a huge response to the user. In turn saving your application from the crash.
   Use TOP, Limit, ROWNUM Clause for SQL level handling or setMaxResults if you are using hibernate.

Stop Word Problem 

It is a similar problem, which is not actually a SQL wildcards but could result in whole data. What if the user searches something which is very common and is found in all data? For example, searching “.” or “,” or “a”, “the”, “is” or something like this. Considering this as a bug or not is entirely based on the requirements. But in ideal cases a user will not be searching for such things, if the user searches for these things what would you like to show as a result? Whole matching data? Is this useful for the user in any way to get 50,000 search results which comprise of all categories and everything? If we take the example of an e-commerce website: displaying shoes, shirts, watches, mobiles, XBox, etc. All in the same search, will it help the user?
As far as usability is concerned, this is not useful at all, which means someone is trying to play with your application.

Solutions

Try to make search more accurate to the real world scenarios:

1. Minimum character Validation: You can avoid most of the common phrases by adding minimum character validations to let’s say 3 characters minimum?
2. Identifying common phrases: Identify common phrases and try to remove them, For example: “The”, “it”, “is”, “a”, etc.

Bug in Action

Let’s see some of the popular websites with Stop Word Problem, which I found during research. The following problems are not really a bug and might not even harm the application but usability remains in question.

• Naukri.com: It gives results on anything you search, whether it is “a”, “the”, “is”, “his”, etc. Moreover, you can combine all these keywords using comma (,)
  Search Url: https://www.naukri.com/a-jobs
  Or if I play a little with the URL here and make it https://www.naukri.com/-jobs, then this gives even more results.
• Myntra: Again you can search anything here, and it gives some result, though it doesn’t give any result on searching a dot ‘.’
  Search Url: https://www.myntra.com/,
• To The New Blog: Even in this website, you can search anything.
  Search Url: http://www.tothenew.com/blog/?s=a

Don’t just read this blog, test your applications now, search for “%%%%” and wait for the output, if you don’t see any output check the network tab of browser console to see what happened actually, is it working the way it is supposed to work or not? Also, check your application for common phrase problem. And in the end, share this blog to make more people aware.