DoS attacks using wildcards

These days, my interest turned towards security related topics and this whitepaper caught my attention when I was browsing through some website. It’s about Dos attacks using SQL wildcards [PDF].

Wildcards is a trivial concept. Any student studying SQL knows what wildcards are. If you ask a novice programmer to write a search functionality to search records from a table, the popular answer must be “decorating” user input with wildcards on both ends. It’s just stupid. Well, I would have done the same thing if I write some code in college days.

Just look at this query:

select * from table where content like '%foo%';

This is logically perfect solution, but it doesn’t scale. Next, if the application provides an advanced search functionality that searches for all the terms, then the query would look like something like this:

select * from table where content like '%foo%' OR '%bar%';

Well, the application provides the functionality. So, you gotta live with it. This might be acceptable when the table has 200 records. What if the table has 2 million records?

Now, how can an attacker attack the application. You just get the answer by looking at the query once – using really long strings with lots of wildcards.

select * from table where content like '%foo___bar%i_am_a%{bad_boy}%do_you_want%to_be_my_friend%';

Do you feel the pinch now? Yeah, this can eat up CPU cycles and I/O. This can hang your machine up for few seconds. Oh! yeah, do you have a connection pool? How many connections? 100? 200? Just an equal number of queries at one shot can jam your server and there you go – DoS. If your code has this kind of problems, is a dial-up connection enough for a DoS attack?

Well, if you think these kind of problems can only affect database servers, you are completely wrong. Say, your web application processes all this data and shows it back to the user, and your code doesn’t check number of records that has been asked for, then your application is also affected. An application level DoS.

Now, how this can be converted to a DDoS attack?

If an application accepts search queries over GET requests then an attacker can use a “Web Spamming Tool” to auto-register to open forums, send forum posts and blog comments with the URL of this search or can send this search URL as the “src” value of an image tag. In this way attacker can use other websites visitors to attack the targeted application…

This can get really worse if the application has also SQL injection vulnerability. Attacker can do “anything” on your database.

How to protect from all this?

• Treat user input. Remove or escape wildcard characters properly from user input.
  • Just thinking of a funny problem. Don’t blindly escape wildcards. Consider this string “abc\%”. It would become “abc\\%” which is definitely not what you want.
• Whitelist allowed characters.
• Set timeouts for SQL queries.
• Limit number of records that can be fetched.
• Be afraid of CSRF, use some kind of token to validate if a request is really originating from user action.

Happy coding. Remember, there is no patch for stupidity.

P.S. I like the quote “There is no patch for stupidity”, is copied from someone; don’t remember where I saw it first. Read the whitepaper for details.