I work (now part-time) for a company that has designed and built an equity research publishing and distribution platform used by numerous equity research firms (usually smaller “boutique” shops of between 40 and 200 analysts). This platform is web-based, and we provide customized environments for each client. Our clients sell their research, commonly on a subscription model, to hedge funds, equity funds, and institutional investors. Security is of paramount importance. Bad actors might seek to access research for which they have not paid, and then trade on this information.

On Tuesday, 17 September, one of our clients was subject to a few dozen attempts at SQL injection attack. The attacks failed because the application is hardened against such attacks, but our monitoring system detected the attempts and created alerts. Here’s a screenshot from our monitoring system:

injection fail

You can see in the screenshot that the perpetrator tried something very similar to what’s explained in this course:

'"2 AND "x"="x'

The idea behind the attack is that the string ‘“2 AND “x”=”x’ is appended to the SQL query, where a search term might be. The attacker is trying to take a query like

SELECT * FROM table_foo WHERE record_id = 2

and get it to execute something like this:

SELECT * FROM table_foo WHERE record_id = 2 AND "x"="x"

Since the last clause always evaluates to True, such a query might return all records.

We harden our web applications in many ways so this kind of attack won’t ever work.

In lab you will harden a demo application by “sanitizing” input from the user — making sure that everything you use to build a SQL query is safe.

There were other attempts as well, all using the same basic idea. See if you can understand what’s going on in each. Imagine these strings appended to a query template ending in “WHERE some_column =”.

'2 AND 1=1'

'299999" union select unhex(hex(version())) -- "x"="x'

"2' or (1,2)=(select*from(select name_const(CHAR(111,108,111,108,111,115,104,101,114),1),
name_const(CHAR(111,108,111,108,111,115,104,101,114),1))a) -- 'x'='x"

Anyhow, it was a simple matter to track the attacks to their origin. This turned out to be from a managed hosting service in Singapore. The offending IP address was reported and blocked.

Please feel free to come by during office hours if you find this interesting, or visit the OWASP website’s page on SQL injection.

And here’s a little something on-topic from Randall Munroe’s XKCD:

little bobby tables

XKCD content licensed under a Creative Commons Attribution-NonCommercial 2.5 License