{"id":48157,"date":"2017-05-18T17:13:03","date_gmt":"2017-05-18T11:43:03","guid":{"rendered":"http:\/\/www.tothenew.com\/blog\/?p=48157"},"modified":"2017-05-19T09:41:42","modified_gmt":"2017-05-19T04:11:42","slug":"sql-wildcards-is-your-application-safe","status":"publish","type":"post","link":"https:\/\/www.tothenew.com\/blog\/sql-wildcards-is-your-application-safe\/","title":{"rendered":"SQL Wildcards..is Your Application Safe?"},"content":{"rendered":"<p>We all have search functionality in our applications. It is one of the most core\u00a0features you would find\u00a0ranging from<span style=\"font-size: 1rem\">\u00a0searching for users, products, companies, etc.\u00a0<\/span><\/p>\n<p>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&#8217;s what happened with mine:<\/p>\n<p><strong>Background<\/strong><\/p>\n<p>Let&#8217;s start with a problem which I faced recently in one of my projects. I was working on an\u00a0application that manages documents so that you can understand the need for\u00a0security. Here&#8217;s the stack that I used.<\/p>\n<ul>\n<li><strong>Web Stack:\u00a0 Grails<br \/>\n<\/strong>One good thing about this FW is that\u00a0all database access via GORM is\u00a0automatically SQL escaped preventing SQL injection attacks.<\/li>\n<li><strong>Database: MySQL<br \/>\n<\/strong>MySQL is an open-source relational database management system (RDBMS).<strong><br \/>\n<\/strong><\/li>\n<\/ul>\n<p>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\u2019s 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.\u00a0The 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.<\/p>\n<p>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.<\/p>\n<p>But digging further into this I found out that it was not enough. There was still a possibility of attacks using\u00a0<strong><a title=\"SQL Wildcards\" href=\"https:\/\/www.w3schools.com\/sql\/sql_wildcards.asp\" rel=\"nofollow\">SQL Wildcards<\/a>,\u00a0<\/strong>and the result would be a similar\u00a0application crash.<br \/>\nAfter this, I did test many other applications as well and found out that the problem was same with many applications.\u00a0However, it was evident that facebook, google, and others would pass the test.<\/p>\n<h2>MySQL WildCards<\/h2>\n<p>Let&#8217;s understand how\u00a0<a title=\"SQL Wildcards\" href=\"https:\/\/www.w3schools.com\/sql\/sql_wildcards.asp\" rel=\"nofollow\">SQL Wildcards<\/a>\u00a0<strong>(%, _)<\/strong>\u00a0are a threat.<\/p>\n<p>Generally, in the search functionality, we have <a title=\"SQL LIKE Operator\" href=\"https:\/\/www.w3schools.com\/sql\/sql_like.asp\">LIKE<\/a> statement at\u00a0backend and user input goes directly into the SQL. Hibernate and other frameworks don\u2019t see any problem with these wildcards because they are supposed to be handled at the developer level. Generally, SQL wildcards are\u00a0meant to be a feature for the developer, not for the user.\u00a0But what is stopping the user to use this feature?<br \/>\nThis 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.<br \/>\nUsing 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\u2019t matter much. What is important is how capable your application server is and how fast does the action gets executed.<\/p>\n<p>In ideal cases the query looks like this:<\/p>\n<p><code>SELECT * FROM Address WHERE street_name LIKE '%foo%';<\/code><\/p>\n<p><b>And we expect the user to be nice and pass foo. But then the user starts to do something like this:<\/b><\/p>\n<ol>\n<li>Results in whole Data:<br \/>\n<code>SELECT * FROM Address WHERE street_name LIKE '%%%%';<\/code><\/li>\n<li>Resource intensive string comparison at SQL level, Hangs the SQL<br \/>\n<code>SELECT * FROM Address WHERE street_name LIKE '%_[^!_%\/%a?F%_D)_(F%)_%([)({}%){()}\u00a3$&amp;N%_)$*\u00a3()$*R\"_)][%](%[x])%a][$*\"\u00a3$-9]_%';<\/code><\/li>\n<\/ol>\n<p>You never know, the people you might be providing service to, are the ones to bring your service down. So\u00a0the security of your app is in your hands.<\/p>\n<p>Now that we know the problem, the impact varies from application to application. In my case, the whole application crashed.<\/p>\n<p>So how do we prevent this?<\/p>\n<h1 style=\"text-align: left\"><strong>Solution<\/strong><\/h1>\n<ol>\n<li><strong>Sanitize The Input:\u00a0<\/strong>Input always needs to be verified before actually using them.\n<ul>\n<li>For Wildcards, you can escape the particular characters.<br \/>\nSo Input strings like <strong>%%%<\/strong> should be encoded to <strong>\\%\\%\\%<\/strong> before sending it to the database.<\/li>\n<li>You should also limit the number of characters in input as per the requirements.<\/li>\n<\/ul>\n<\/li>\n<li><strong>Limit The Output:\u00a0<\/strong>In the case of SQL Wildcards, the search output could be all data, so if you limit the output to let&#8217;s say 10 results only,\u00a0it won&#8217;t fetch all results from the SQL and won&#8217;t send a huge response to the user. In turn\u00a0saving your application from the crash.<br \/>\nUse\u00a0<a title=\"SQL TOP, LIMIT, ROWNUM\" href=\"https:\/\/www.w3schools.com\/sql\/sql_top.asp\">TOP, Limit, ROWNUM Clause<\/a>\u00a0for SQL level handling or\u00a0setMaxResults if you are using hibernate.<\/li>\n<\/ol>\n<h1 style=\"text-align: center;padding-top: 25px\"><strong><a title=\"Stop Word Wiki\" href=\"https:\/\/en.wikipedia.org\/wiki\/Stop_words\">Stop Word Problem<\/a>\u00a0<\/strong><\/h1>\n<p>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 &#8220;.&#8221; or &#8220;,&#8221; or &#8220;a&#8221;, &#8220;the&#8221;, &#8220;is&#8221; 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\u00a0way to get 50,000 search results which comprise of all categories and everything? If we take the example of an e-commerce website: displaying\u00a0shoes, shirts, watches, mobiles, XBox,\u00a0etc. All in the same search, will it help the user?<br \/>\nAs far as usability is concerned, this is not useful at all, which means someone is trying to play with your application.<\/p>\n<h1 style=\"text-align: center\"><strong>Solutions<\/strong><\/h1>\n<p>Try to make search more accurate to the real world scenarios:<\/p>\n<ol>\n<li><b>Minimum character Validation: <\/b>You can avoid most of the common phrases by adding minimum character validations to let&#8217;s say 3 characters minimum?<\/li>\n<li><b>Identifying common phrases: <\/b>Identify common phrases and try to remove them, For example: &#8220;The&#8221;, &#8220;it&#8221;, &#8220;is&#8221;, &#8220;a&#8221;, etc.<\/li>\n<\/ol>\n<h1><strong>Bug in Action<\/strong><\/h1>\n<p>Let&#8217;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.<\/p>\n<ul>\n<li><a title=\"Naukri.com\" href=\"https:\/\/www.naukri.com\/\">Naukri.com<\/a>: It gives results on anything you search, whether it is &#8220;a&#8221;, &#8220;the&#8221;, &#8220;is&#8221;, &#8220;his&#8221;, etc. Moreover, you can combine all these keywords using comma (,)<br \/>\nSearch Url:\u00a0<a href=\"https:\/\/www.naukri.com\/a-jobs\">https:\/\/www.naukri.com\/a-jobs<br \/>\n<\/a>Or if I play a little with the URL here and make it\u00a0<a href=\"https:\/\/www.naukri.com\/-jobs\">https:\/\/www.naukri.com\/-jobs<\/a>, then this gives even more results.<\/li>\n<li><a title=\"Online Shopping site in India for Fashion\" href=\"https:\/\/www.myntra.com\/\">Myntra<\/a>: Again you can search anything here, and it gives some result, though it doesn&#8217;t give any\u00a0result on searching a dot &#8216;.&#8217;<br \/>\nSearch Url:\u00a0<a title=\"Search ,\" href=\"https:\/\/www.myntra.com\/,\">https:\/\/www.myntra.com\/,<\/a><\/li>\n<li><a href=\"http:\/\/www.tothenew.com\/blog\">To The New Blog<\/a>: Even in this website, you can search anything.<br \/>\nSearch Url:\u00a0<a href=\"http:\/\/www.tothenew.com\/blog\/?s=a\">http:\/\/www.tothenew.com\/blog\/?s=a<\/a><\/li>\n<\/ul>\n<p>Don&#8217;t just read this blog, test your applications now, search for &#8220;<b>%%%%<\/b>&#8221; and wait for the output, if you don&#8217;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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>We all have search functionality in our applications. It is one of the most core\u00a0features you would find\u00a0ranging from\u00a0searching for users, products, companies, etc.\u00a0 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 [&hellip;]<\/p>\n","protected":false},"author":598,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"iawp_total_views":43},"categories":[7,446,1],"tags":[4843,4840,265,4844,76,558,4555],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/48157"}],"collection":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/users\/598"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/comments?post=48157"}],"version-history":[{"count":0,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/posts\/48157\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/media?parent=48157"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/categories?post=48157"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tothenew.com\/blog\/wp-json\/wp\/v2\/tags?post=48157"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}