Sqlmap: Automatic SQL injection and database takeover tool | Hacker News Reader

Sqlmap: Automatic SQL injection and database takeover tool

sqlmap.org

248 pointsmadewulf79 comments

If you're a small startup looking to automate security testing against your application, I strongly recommend just shelling out for Burp Suite; it's like a couple hundred bucks tops, can be used to automate almost every common web app attack, and is the tool most likely to be used by good auditors when you eventually decide to get tested professionally.

http://portswigger.net/burp/

There's a free version, which disables the (mostly vestigial, anyways) "scanner", and slows down the fuzzer tool; if you're really automating security testing --- which, do --- pay for the real version; you want the fuzzer to work.

There are things sqlmap does that Burp does not do easily, but they're not important to app developers; if you're generating database exceptions with inputs, don't dick around trying to make time-based blind SQL work; just fix the bugs.

Nice recommendation tptacek. Also nice is the book by the author of the same software: http://www.amazon.com/The-Web-Application-Hackers-Handbook/d...

If you click over to my profile, there's an Amazon reading list I made up to answer the question "what should I read up on to get better at appsec". I think WAHH is #2 or #3 on it.

If you're a talented programming (we especially like talking to people who are good in C, but we're happy to meet anyone) and you're seriously interested in working from Chicago, NYC, or SFBA doing appsec, but don't feel like you have the background, drop me a line. My usual next step for people who I think would be ready to interview with us but for some web app knowledge is to shoot them a copy of that book. Infinite free books is a small perk here.

Here's a blog posting from Bruce Schneier on getting started in computer security:

http://www.schneier.com/blog/archives/2012/07/how_to_become_...

There are links within his blog posting to other tools.

I wrote a similar piece for Krebs the week prior; I think it's linked from this one.

I'm not a fan of Schneier's take on this. Don't start a career in security by thinking about certification.

Can you provide the link to Krebs?

Here: http://krebsonsecurity.com/2012/06/how-to-break-into-securit...

I have found that certification courses can give a proper introduction to specific technical areas. Of course, if the goal is to just get the cert, then one would probably miss most of the learning aspects of the certification process.

The cert curriculum can be a great learning tool, in addition to just plain playing around and learning from other social networks.

This has been one of my most favored tools in my belt. It is extremely handy for more than just security work. The proxy and intercept, and repeater tools are excellent. The only complaint I have is that the proxy is sluggish compared to something like Fiddler2. Never have figured out why though.

I probably misinterpreted you, but haven't you written skeptically of automated security testing in the past? Or is it just people who actually work in security shops that should stay away from them?

I am very skeptical of tools that find security flaws automatically.

I am enthusiastic about automation.

The holy grail is to strip away all the tedium from testing, leaving only the mental challenge of finding and exploiting the broken assumptions of our adversary developers.

Burp Suite does a very good job here. sqlmap crosses the line for us. But that doesn't make it a bad tool.

>I am very skeptical of tools that find security flaws automatically. I am enthusiastic about automation.

Just my $0.02 here, since I think tptacek has it covered (as usual). The way I explain use of automated tools to our clients is that, yes, the assessment you're paying for is to have a skilled security expert manually assessing your application. That said, in order to get the most efficiency out of manual testing, those parts which can be easily automated should be.

A few trivial examples would be finding open ports, or detecting expired/weak SSL certificates. Sure, an engineer could manually connect to all 65535 ports to see if they're open, and manually grab banners, or he could just run `nmap -p- -sV hostname`. Similarly, he could use openssl to check all aspects of an SSL certificate, but why bother when there are tools to do this for you?

The same is true in application security, although the examples can be a little fuzzier (pun intended?). The more seamless automation can be integrated into testing, the more time for manual analysis can be allotted. I think this is the point that tpatacek was making, and just wanted to expand upon it for those that might be a little confused.

Forgive my ignorance, but if you're using prepared statements for all your SQL queries using user input, aren't you by definition safe from any kind of injection?

Of course not. This is a pernicious myth about SQL security. Many inputs to query construction can't be parameterized.

"Many inputs to query construction can't be parameterized."

Such as? If you only use PreparedStatment (Java), or the equivalent for your language, and have a rule against, and never broken, to concatenate SQL strings together, you are safe from SQL injection.

Without dynamic sql, it is tough to do arbitrary (user-selected) columns, sorting, and server-side paging simultaneously (limiting the result set in the query instead of skipping through results in the app). SQL does not allow use of parameters in all the necessary locations in the query (result column list, order by list, etc.), and some databases are more limited than others.

There are options available as one or more of the above 3 requirements is dropped: http://stackoverflow.com/questions/149380/dynamic-sorting-wi...

User selected columns is easy, just select all required columns each time and display as needed, the performance impact of this is minimal. The fact that the query need not be reparsed is probably a bigger benefit performance wise.

Paging is equally easy, using ROWNUM < X in Oracle or equivalent in other RDBMS.

The order by is a good point. The obvious solution here would be to always have the same sorting as default, and do the user sorting client side (by client here, I mean calling application).

Yes, the requirement of paging server-side (when a result set is ridiculously massive or the client/network is so overloaded that alternatives are impractical) is the big one: page X of Y changes depending on the sort, so sorting has to be done server-side too.

Do you agree that this is a scenario requiring dynamic SQL?

It doesn't hurt to prepare a statement, execute it once and then deallocate it. Intersect the user-selected columns with a whitelist of possible columns, use a simple flag for sorting and map it to ASC/DESC. Prepend the primary key, bind parameters, execute, harvest the results, deallocate temporary resources.

This is somewhat less efficient, but if the alternative is to be open to sqli attacks it'd be my pleasure.

"Whitelist of possible columns".

Congratulations, we are no longer relying on bound parameters to prevent SQL injection. THREAD FINISHED! :)

You could do without using a CTE, it's just not as convenient most of the time. Alternatively, you could pass the column names to a function (using a prepared statement, no less) and let that function figure out which columns are valid. If your tables change frequently, this would save more time in the long run.

We are talking about manual, purpose-built functionality designed to prevent SQLI in queries that use bound parameters. Don't move the goalposts: nobody is saying it's particularly hard to avoid SQL injection, just that bound parameters aren't a panacea.

If your tables has SO many columns that you can't simply return them all and show the relevant ones only at the application layer, .... well then just maybe there's something wrong with your database design.

I agree this is the best way; it's still dynamic sql (the database is not paramaterizing the columns, the application is). I think you accurately summarized best practices, but it always comes down to implementation and people make mistakes.

(See another person discussing this here in this thread: http://news.ycombinator.com/item?id=4203929 )

> Many inputs to query construction can't be parameterized.

I do not allow the client direct access to the database. They access the database through objects, which have methods that only permit parameterized input.

The client cannot specify the database, table, or columns. That is hard-coded. They can only specify values. Values that are always entered through a prepared statement.

In that scenario, isn't injection impossible?

Rephrasing the question, pessimistically (ideally as kindly as possible, but my intent could easily be lost):

I've implemented a [complex?] white-listing solution to a problem in a domain where there is a long history of developer mistakes, passing user input to a 3rd-party technology most developers only know the minimum required for their job. Isn't injection impossible?

Edit: Sorry for any confusion; this was my way of saying 'this is only bulletproof if you got every edge case 100% right; few security experts make guarantees of impossibility of failure'.

How about a black listing solution? commit block on anything containing the full word "Statement" (or equivalent in in your language of choice).

Also code review, of which there will be need for plenty, when bare minimum developers is involved.

For the record, it's my understanding that white-listing is normally better than black-listing. I didn't mean that white-listing wasn't the best option, just that it sounded like it was being used.

Yes it is impossible. For example the following query "SELECT person_name FROM persons WHERE person_id = ?" Will be parsed and put in the query cache. Then when it is run (multiple times) it will bind the value in the correct place, and if there is anything else than a numeric value when it is trying to bind, the query will fail to execute.

Yeah, that's kinda the whole point. You can put an incorrect value in there and it'll fail, but you can't change the query itself. (i.e. Bobby tables would be an incorrect value, not a dropped students table.)

No. Of course not. In all likelihood your SQL RDBMS will only allow you to bind data. Do you paginate? What about "LIMIT" and "OFFSET"? Do you sort tables? Then you have to handle column names. Do you sort in both directions? Then you have to handle "ASC" and "DESC". And so on.

Obviously, you can write simple code to do each of these safely. But then, you can write simple code to handle query parameters too.

By all means, use parameterized queries. Just don't assume they're a magic totem against SQLI.

Could you give some actual examples of prepared statements that are injectable? I do not understand how it would work.

For example the following example from http://de.php.net/manual/en/sqlite3.prepare.php :

$stmt = $db->prepare('SELECT bar FROM foo WHERE id=:id');

$stmt->bindValue(':id', 1, SQLITE3_INTEGER);

You're missing his point somewhat. I don't know if this applies universally to SQL, but consider MySQL.

    $stmt = $db->prepare('SELECT bar FROM foo 
                            LIMIT :offset, :count');
    ...

You can't do that. You can only bind data to fields.

    $stmt = $db->prepare('SELECT bar FROM foo
                            ORDER BY :column :direction');
    $stmt->bindValue(':column', 'foo');
    $stmt->bindValue(':direction', 'DESC');

You can't do that either, because again, it's not data.

So, you end up having to do something similar to this (for the love of god, don't actually do this):

    $stmt->prepare("SELECT bar FROM foo WHERE id = :id
                    ORDER BY {$column} {$direction}
                    LIMIT {$offset}, {$count}");

Bound parameters won't save you, and the potential attack vector is there if you're not really careful. That example shows the most stupid thing you could ever do, so don't do it.

Other than probably historical reasons and interface definitions, is there any reason why this is the case? I can't immediately think of any sensible argument against allowing the binding of a value into a limit/order field. In fact, it should be type-checking/converting it as well, to prevent exactly mistakes like this.

Even if the DB interface doesn't directly support this, it seems like it's something all client wrappers should handle, preferably at the low level in addition to having to pull in a whole ORM + assorted bacon for the purpose.

Easy: bound parameters are a performance feature, not a security feature.

Thanks!

That would not have been a "real" prepared statement in my mindset so I get why I was confused.

I don't feel super smart, but wouldn't I simply make sure that $column is a valid column (ok, that one might need extra attention) and $direction would be either ASC or DESC and that $offset and $count are integers?

You can and should bind LIMIT and OFFSET with parameters on many if not most drivers (some can't handle it but most that I work with these days do, though I'm in Python, perhaps JDBC or other drivers are a different story). ASC and DESC are SQL keywords so assuming you're not a terrible programmer (which I know is what you're addressing) you wouldn't have those rendered from user input any more than the WHERE or ORDER BY keywords.

The lexical structure of the statement and the parameters themselves are two different things. You are correct that "parameterization can't save you from SQL injection", if you're such a bad programmer that you're feeding user input directly to produce lexical tokens. One example is a recent blog post talking about how to escape table names for apps that feed things like "customer_name" to serve as the table name (totally insane). Your example of feeding in "sort=xyz" to produce the column name in the ORDER BY is also a pretty awful practice - I haven't seen that one in like a decade, but sure.

So of course parameterization doesn't magically protect a system against all forms of attack - the programmer could be feeding user input directly into shell commands too. The recommendation for parameterization is addressing the bulk of the issue at least among the code that I regularly work with, maybe you deal with crappier programmers than I do on a regular basis.

> Your example of feeding in "sort=xyz" to produce the column name in the ORDER BY is also a pretty awful practice - I haven't seen that one in like a decade, but sure.

This still happens every day. Google how to do dynamic sorting and you'll find a hundred examples of it in PHP, and as unfortunate as it is, that's how a significant portion of code is authored.

Actually, no. Stored Procedures can contain dynamic SQL, which is the actual problem, so it is quite possible to have a stored procedure with SQL Injection built into it. This would yield high-performance SQL Injection and little else.

The better answer is a parameterized query, which actually controls the inputs properly to ensure that no dynamic SQL is possible.

SP:s was never mentioned in the post you are replying to, but ofcourse if you use dynamic SQL in them (execute immediate in Oracle) it is just as bad as doing string concatenation in other languages. Just dont do that.

It would be a silly thing to do because you keep exactly the same problem you'd have without stored procedures. Would the client you're writing rather execute something along the lines of

  SELECT sp_enroll("bob\"); DROP TABLE students; --");

  SELECT sp_enroll("bob"); DROP TABLE students; --");

At least if you grant the user only SELECT and EXECUTE privileges and define the procedures using the SECURITY DEFINER property, you could still prevent this type of damage. (This relies on the procedures to be as strict as possible or the whole scheme essentially fails.)

How can I donate to you guys?

This is an excellent tool. But generally on a penetration test, BurpSuite is what you want to use.

If, however, simply demonstrating that SQLi is there is not sufficient, or if the blind SQLi is too ephemeral sounding to the client, Sqlmap can help you dump the entire database. This will convince even the most skeptical.

I prefer using w3af over BurpSuite. Full source is a good thing.

Havij is a similar tool thats gaining in popularity due to its point and click interface. check out: http://blog.imperva.com/2012/04/dissecting-the-sql-injection...

Here's a video demonstration playlist: http://www.youtube.com/watch?v=fGBQm9Nfn24&list=UU8lIbC-...

Here's a better web page that contains the same information: https://github.com/sqlmapproject/sqlmap

Thanks god this kind of tools now has so much better layouts than few years ago.

Nice little tool thanks

Very cool, Thanks

Has anyone done a comparative review of sqlmap vs skipfish?

They're not really comparable. Skipfish is a fast crawler with some checks for DOM corruption, SQL metacharacters, and hygiene. sqlmap is a tool for exploiting SQL injection.

I like the code for Skipfish a lot, but neither tool is particularly important in the profession; if you're working with a contract application security firm and they give you findings that came from sqlmap or Skipfish, you should be irritated.

I'm not sure I read your final remark correctly. Do you mean to say that using Skipfish or sqlmap has no place in a professional pen test or that a professional pen tester should not subject his clients to the raw results from said tools?

The latter I agree with, the former I don't. Any automated security scanner has its shortcomings - combining the results of various scanner products generally has a positive effect upon the quality of the test. I've often been in a situation where one tool (commercial or otherwise) indicated or hinted at a vulnerability while all other tools (commercial or otherwise) used did not indicate anything.

The former. With some very minor exceptions, we don't use automated scanners at all in our practice. We use Burp to set up testing experiments, and we use ruby for automation. We've found that "scanners" make good testers much less sharp; if the tool spits out the names of specific vulnerabilities, it's suspect.

But even practices that do use automated scanners won't be relying on Skipfish or sqlmap.

This obviously doesn't hold for network pentesters. For tests in which application flaws aren't the whole story, and you're just looking for the fastest way in you can find, sure; sqlmap is valuable there, much more so than Skipfish. We don't do those kinds of tests often.

When you say "we use ruby for automation" do you mean using something like buby with Burp, or something custom?

I find myself writing a lot of custom fuzzers in python, and am looking to improve my toolkit.

A long time ago, Eric Monti, then a member of our team and now at a big startup in SF, wrote a Burp/Ruby bridge called Buby. For a long time, and maybe still, another team member --- Timur Duehr, who also wrote the first version of our Ragweed native code debugger DSL --- maintained it. A lot of people on our team use Buby to automate.

Another faction of our team uses Mike Tracy's WWMD toolkit, which does some of what Burp does but from an irb prompt.

I'm allergic to both approaches and tend to just start from EventMachine and ev-http-request.

Good example of the kind of scenario that'll get me into "writing code to test web apps" mode: testing those goofy cryptographic tokens applications send via email to reset passwords.

"Goofy" cryptographic tokens? Is there a better way?

Not having the tokens be replayable, rewritable to different user IDs, or decryptable would be a good start, right?

Yes: the better approach is to have the token be a 256 bit cryptographically strong random number that corresponds to a row in the database with metadata sufficient to expire the token and invalidate it on password reset.

Ah. So it's not the use of cryptographic tokens per se that you're objecting to -- just that they're not generated and/or handled correctly.

I object in general to the widespread practice of using tokens that contain semantically interesting information protected by encryption.

Got it. Didn't realize that was so widespread.

You'd be surprised to know how often it's just the username encoded in base64...

> I find myself writing a lot of custom fuzzers in python, and am looking to improve my toolkit.

Have you looked into Haskell's QuickCheck? It's essentially a fuzzer for unit testing. You might find some interesting ideas there.

Thanks for the tip. I've just been looking at Learn You a Haskell, it's nice to have some specific usecases when learning a new language

Thanks for your clarification. Phrased this way I tend to agree with you.

I think the point is automated tools are a nice way of directing your pen test, sort of like the way you might use binoculars to scan your surroundings while hiking to spot interesting terrain you might wish to explore.

This is the idea that scares me the most, because while it sounds great, it has an obvious flip side: wherever the scanner doesn't "focus" you is getting less attention.

Has this been something you have found in practice? Or just theory/fear you've developed? I would think tools like sqlmap are helpful in building that initial recon/inventory of items to explore. You should still be doing the due diligence of manual exploration. I suppose if one were to get lazy and stop doing the manual exploration, and just let the mapper tell them where to look, it is most definitely true. However, people can get lazy without automated tools, and fall into the same trap.

Would you consider it a perfectly good procedure to run some automated tests against the web app and glance at the results? It's only a problem if you use those results as the focus of your investigation, right?

What it seems like you're saying is that you have found that scanners are almost always a distraction at best, misleading at worst, and that they offer zero useful information about the app or about the MO of the app developer.

You (matasano) are good enough that any information those scanners could reveal about the app or the developer, you'd probably turn up independently. Not everyone has those skills, and the results of a scan might include something that a lesser practitioner wouldn't have noticed any other way.

What I'm saying is that I have found that scanners are almost always a distraction at best, misleading at worst, and that they offer very little useful information about the app that can't be acquired more easily and efficiently by a tester reasoning about the behavior of the app for themselves.

I'm not against automation. I don't think testers need to launch every fiddley little query they need to gauge behavior. What I'm against are tools that run some unspecified number of fiddley little queries, do some bogus analysis, and then spit out a "YES XSS" or "NO XSS" answer.

Not that your approach needs validation,

but I'm going through the exercise of building out an app sec practice, and for what it's worth, I've adopted the same approach (we're not using automated scanners).

Or in other words you've decided to decree the world as flat.

I work in an AppSec practice. Scanners help me find the low hanging fruit. I can now spend more time looking for logic bugs.

You tell yourself that, but what I think is that you miss a lot of medium-hanging fruit, and you find the same number of "logic bugs". Meanwhile: your firm and our firm empirically bill a similar number of hours (if you're at a competent firm; if you're at a body shop, you probably bill 1.5x to 2x more hours than we do).

Reasonable people can disagree on this point, but we're a pretty large, well-established practice and our belief isn't coming out of nowhere.

Your argument is literally the first thing anyone who wants to convince us to use scanners brings up. It is the point we've thought about and debated most. I just don't think it pans out in the real world:

(1) The bugs you find "because" your scanner took the low-hanging fruit will be bugs any good tester will find;

(2) meanwhile, the extra scrutiny you're not giving the app to find that low-hanging fruit is costing you insight that would reveal still more bugs...

(3) also, your scanner is missing bugs, probably in the neighborhood of 20-30%, and:

(4) you're not making up for that because it's very difficult to force yourself to focus on terrain that a scanner has covered and flagged bugs in.

I'll take your word for it Thomas. I'm quite familiar with your work.

No, I've decreed that after a decade breaking applications professionally, there isn't an application scanner I've used (and I've used very very many) that is worth anything.

I am not as philosophically opposed to scanners as I think Thomas is, I've just found that they provide nearly no useful value. For many years, I argued that although they provided no value, "they didn't hurt", so there was no harm in also running them at the end of a test to make sure there aren't any low-hanging fruit that a tester may have missed.

What started to turn me around in that belief was that I noticed an increasing number of tests being performed by my team where the scanner wasn't just not providing value, but actually causing issues.

Under the best case scenario, you are now having to take the time to validate your scanner findings (which are all things you would/should have found anyway but are relying on the scanner to do for you).

Under the scenarios I've witnessed play out, people assume that the scanners will actually find the low-hanging fruit, and they slack off on that part of the assessment (because, hey, the scanner will cover it, and now they can spend more time looking for logic bugs). Then the scanner doesn't find something trivial (which happens about one in every...oh, I don't know...actually it happens in nearly every test).

I'm happy you've found that scanners don't make your work product worse, but that's not what I've found at all.

Could you not do your normal practice, and then have someone else run the automated scanners as a sort of double-check just to make sure you didn't miss anything obvious? Sure, it would likely never find anything, as it's your job to not miss anything obvious, but if they're basically automated and free, seems odd to dismiss them outright.

EDIT: Furthermore, how embarrassing would it be if someone hired you and then ran one of these scanners themselves and did find something you missed? Given the magnitude of the potential downside, and the marginal cost of using them as described, it'd almost be more of a business insurance tactic than anything else.

First, we're a pretty big company, and I'm only one of 3 founders and of 4 practice managers, so you can imagine we've debated this pretty thoroughly.

Last point first:

Try real hard not to miss stuff. Seriously, that's it. Appsec is a competitive field. Forget scanners: many clients are going to hire a different firm for their next test, and some of those firms are really, really good. When you're testing an app, that is the thing that is animating your work: trying not to leave nuggets for the next team to find and shame you with. Think we're worried about missing things sqlmap finds? Try worrying about what iSec Partners is going to find.

To your first point: this is hard to articulate well, but let me take a stab at it: it's very hard to double back over terrain a scanner has already covered. Think of security testing like a treasure hunt. Have you ever had a fun treasure hunt? Can you get a bead on the feeling you had when you started out hunting for stuff? Imagine trying to put the same focus and motivation together if, before the treasure hunt, the organizers announced "we sent a bunch of people out before you to try to make sure there's no treasure".

Interesting, thanks for the response! Yeah, of course I'd figured you guys had answers for those questions, I'm just genuinely curious.

SQL Map is a SQL Injection tool. Skipfish is a web vulnerability scanner. Skipfish, in other words, is more like Burpsuite or WebInspect or Nikto than like SQL Map.

Reply on news.ycombinator.com