The only successful SQL injection attack I've encountered in the wild was interesting, because the injection point had no visible output. But by injecting timing calls (eg "SLEEP()") and appropriate conditionals, the attacker was able to extract a few bits of information each request. Their script executed some tens of thousands of requests, and they managed to extract all the table names, and start to extract data from our "users" table.
In retrospect such an attack is obvious, and presumably tools like metasploit make them trivial to execute. But previously I'd had the idea that SQL injection was usually "literal raw data output".
Another frequent type of blind SQL injection is when the server gives an error or a slightly different output on some queries. The server code may for example travel down a different path if the query doesn't output any results and then shows an error. Even if the error doesn't show the contents of the query, it can also be used to slowly extract data through generated yes/no questions.
In one case I managed a successful blind attack for a client because their server showed an English site for valid queries returning the right results, and a German one for unexpected queries.
You should take a look at https://sqlmap.org : this tool runs SQL attacks with « raw data output » as you say, but also without outputs (Blind SQL injections).
> it occurred to me that they probably are hosting this site with github but are using their own domain name
That is exactly what they are doing, and in itself this is not at all a problem.
But presumably the link worked for the original poster, so either there is a dynamic DNS problem (we are being sent to an address that serves the .github.com certificate an not the "right" one but he was sent somewhere that does have a certificate for that name) or* someone is resigning content and his machine is set to trust their CA certificate. This latter cause could be normal/expected (his company having a MiTM policy for regulatory monitoring reasons) or his machine could be cracked by an external entity.
SQLmap is a great tool for automated scanning and exploiting of SQLi vulnerabilities. Like everything though, it can miss the occasional exploit, where someone with the expertise might be better suited, but generally speaking it’s an awesome place to start.
It also has sane defaults, which means scriptkiddies chancing their luck with it should be easily spotted in your access.log, or depending on your environment a WAF/IDS/IPS should block/detect quickly.
This is a 'blind' sql injection attack. Tools like sqlmap make finding and exploiting sql injection very easy. sqlmap includes excellent support for blind sqli exploitation.
SQL injection is very common in our space which is WordPress. (I'm the Wordfence founder) There are over 40,000 plugins for WP and around 25k developers and writing PHP with a sqli vulnerability is really easy to do accidentally so it is super common.
I recommend having a read of this CTF writeup[0], and the Albatar framework that was used to solve it (cannot be done with sqlmap, I tried). It's incredible how far people can go with SQLi.
I have this friend who runs this forum out of self-made software- not a prefabbed PHP-bb, but home made software. Nonetheless, he had thousands of members who all shared the same particular hobby.
One time I decided to test his inputs, and surprisingly- one of those inputs contained a vulnerability in that it allowed you to post ANY text script or characters without sanitization. I quickly hobbled together a cookie-stealing script and proved how serious this hack was by stealing the password of an alternate dummy account just by visiting my poisoned user/info page.
I told my friend about it, and it took him a surprising long amount of time to actually get around to fixing it.
Universities and managers must do a little bit of torture, spanking and ruler on the knuckles every time they see a student or a new dev not parameterize a query. It must become a reflex like watching for cars before crossing a street.
Once I learned one can parameterize queries, not doing so began to feel kind of dirty to me.
To me, the big point was not even security, but the fact I no longer had to deal with properly escaping/cleaning messy input. Plus, somebody once told me parameterized queries can (potentially) give better performance. What's not to love?
> parameterized queries can (potentially) give better performance.
Yep! More accurately, parameterized queries almost always give better performance.
The simplest way parameterization helps (and there are many) is query plan caching. When a DB server sees a query for the first time in awhile, it has to turn the SQL text into a query plan (https://en.wikipedia.org/wiki/Query_plan). That takes time and resources (lexing, parsing, validation against schema, etc.).
In most RDBMSs I know of, query plans are cached by default for awhile after they are made and executed. The plans also contain placeholders for parameters rather than inlining the parameter values supplied with the query. This means that running the same query with different parameter values usually allows the database re-use the cached query plan and just substitute different parameters, which saves time on plan construction.
Plan caching + parameterization saves time on a ton of other stuff, too: re-running the same plan for multiple queries allows automatic tuning and heuristic analysis to get better performance of the query over time, facilitates predictive caching of result data (from index warming to full row caching), and a lot of other cool stuff.
Aside: you also occasionally see some silly tricks used to defeat plan caching. For example, say you have a complex query, hitting multiple indexes, that gets run all the time with different parameters. Most runs return very few results, and the RDBMS optimizes the cached query plan for that case. Now, say you as a developer know that one particular parameter value for is going to return a lot of results, leading to extremely inefficient index scans, and that the optimized-over-time, cached version of the query is probably going to do the wrong scans, taking an unnecessarily long time. Instead, the naïve "rebuild plan from scratch" approach would yield better performance. Now, if you're right about that (and second-guessing the query planner is a really bad idea on problems like this; the people who write it are almost always smarter than you), you have a few options:
The "correct" choice usually cited is to add RDBMS-specific "hints" to your extra-big query text instructing the database on which indexes to use, or to ignore cached plans. That might be a skill you don't have, or you might not know which ones it should use, just that the naïve approach is better.
There is usually a way to "flush" cached query plans from the database, but that is usually a very bad idea to do programmatically, may require administrator access, and very possibly compromises performance of the "fast" versions of the query running in parallel.
Instead of those, what I've seen a lot of people do is just defeat the plan cacher. Since the query plan cache is often keyed by parameterized text (e.g. somewhere in the RDBMS there's a hash table mapping "SELECT * FROM TABLE WHERE FIELD = '?'" to query plan objects), I have seen folks add pointless "1 = 1"-type clauses to their text, or just add "unique-ing" comments to the beginning. This has the advantage of leaving other cached plans alone and forcing a re-plan from scratch. It's also undeniably a bit of a hack, but a very useful one to be sure.
> I have seen folks add pointless "1 = 1"-type clauses to their text
I have seen something like that, too, and I have always wondered if that was just job protection or if there was some arcane reason to it.
The code in question was part of a view, though, so it ran unaltered countless of times. That view also contained numerous "1 <> 1" clauses, apparently to disable branches of the - rather intimidating WHERE clause. It was the one time I took over someone else's code and felt like swearing.
Let's assume you start with "SELECT * FROM ITEMS" like query. Now you're adding the WHERE clause. Do you keep track if you have the WHERE already present? Do you then keep track of if you need the "AND" or if it's the first entry that cannot take the "AND"?
With 1=1 you're guaranteed to have the WHERE clause and you're guaranteed to be able to use AND in your statement.
I agree that as a starting point for experimenting with an ad-hoc query, a "1 = 1" clause can make sense.
There were two reasons I was mad:
1. Using "1 <> 1" to disable a branch of a WHERE clause is not something I would have done. I would have commented it out. If you are used to this idiom, it might feel like the natural thing to do, but I think commenting it out makes the intention clearer.
2. I understand that queries can get a little convoluted when you start experimenting with them until you get the results you need. It is perfectly natural, and it is how the views I have written started out, as well. But before I put my views into production, I cleaned up the code, removed the parts I did not need or added a little comment to explain why the code was there.
Actually there were three:
3. The entire view was written in such a convoluted way that I was pretty sure the person who had written it was drunk at the time (he was an alcoholic, so this is probably true in the literal sense). When I inherited it, the view had about 700 lines and was mostly unreadable. When I was finished cleaning it up, I had reduced that to about 300 lines. I checked very carefully, but it still gave the exact same results. It still was not pretty, but if I could throw out more than 50% of the original code without affecting the result, you can probably imagine what a mess it was.
I think you hit the most likely reason for the "1 <> 1" clauses: it was basically "commenting out" branches of the query (or something programmatically generated the query in a way that needed to switch branches on and off without changing the query text so much that the query builder would have to understand more SQL syntax).
> It was the one time I took over someone else's code and felt like swearing.
Well, to be honest, I have taken over about four pieces of software over the years, and one of them was a one-and-a-half time maintenance job (adding one tiny little feature, later finding a bug that turned out to be not in the application after all).
I was exceedingly lucky, I guess, because except for that hairy SQL view, they were all gems of clarity.
Some weeks ago an experience developer said to me: "Parameterize a query? Since years i don't care about it, because ORMs like Doctrine or Sequelize take care of that"... So it's not only students or new devs who should watch out, because even ORMs can open up SQL injections.
Agreed, knowing the fundamentals is always preferable...but that is unfortunately a high-bar for most "developers" so it is better to have solutions with builtin guardrails.
When I did firewall/network support for managed hosting customers, the number of customers' custom/vendor apps that were vulnerable (and exploited!) by SQL injection attacks was astounding. Of course the very first thing they said was "but we have a firewall, why can't you ACL it?" Sorry, your Cisco ASA doesn't work that way. It's like people never expected their code to run in a hostile world.
It's because they don't. Two things I've found in less than a year of working in the security industry is that people only care if things work, not if they work well and, that they either believe security can be delegated or that they will never be attacked because they're special in some way.
Hey Esnard! I'm one of the people who worked on the wiki at NetSPI. We're planning on adding more DBMSs in the near future. Was there anything specific you're looking for about PostgreSQL?
Hey! Thanks for replying. Nothing specific, I just find more and more apps using PostgreSQL in the wild, so I was just curious about its absence.
Thanks for your work on the wiki. I'm going to use it a lot. :)
In retrospect such an attack is obvious, and presumably tools like metasploit make them trivial to execute. But previously I'd had the idea that SQL injection was usually "literal raw data output".