As someone who uses Metasploit, Canvas, Burp Suite Pro and am currently evaluating Netsparker I might be able to offer an unbiased view.
Metasploit is an exploitation framework. There's different versions available (community, pro etc.). The community version has a web app scanner and is reasonably ok, but it tends to be caught by intrusion detection systems fairly easily and it's payloads often don't clean up properly. It's better suited to infrastructure exploitation, but can be used in a web app context.
Canvas[1] has some limited web app scanning capability but has more of a core focus on infrastructure exploitation.
Burp Suite Pro[2] is a framework for testing web applications. It's probably the best tool out there for testing web apps (if you know what you're doing). It's also ludicrously cheap and there is a free version for non-commercial use. It comes with a fairly comprehensive web app scanner.
NetSparker is a web application scanner. From what I can tell it's mainly competing with things like Accunetix. It has some features that are similar to Metasploit Pro but focuses primarily on the application layer. It sits more between Burp Suite Pro and Canvas for my purposes. You can download an eval from http://www.mavitunasecurity.com/ or the community edition.
Metasploit mostly focuses on Infrastructure and exploiting known vulnerabilities.
Netsparker is solely focused on web application security (detection & exploitation). For example Netsparker can crawl AJAX/Javascript apps, support form authentication etc. Metasploit on the other hand possibly will never do that kind of stuff.
You are comparing a $1000 commercial product to open source Ruby code. Metasploit is sponsored by Rapid7, which does in fact have a product that is competitive with your offering.
Do we need to get into a detailed discussion of why I think the plug for your scanner is inappropriate for this thread? Or can we just let it suffice to say that HN isn't a great place to promote products on random threads?
I don't know when HN became a place where hackers felt afraid of plugging their products - given that we are all hackers trying to build/sell products - where appropriate.
I don't get the big fuss.
If it was a story about Bingo Cards, and patio11 plugged bcc.com I am pretty sure you wouldn't be all worked up about this.
Let's just calm it down a notch and not try to be kingmaker's here.
If the community found the plug abhorrent, they would downvote it. He would get the point.
Although, to be quite honest, now you have given his product even more promotion and visibility so it's a net positive for him - not sure it's the result you wanted in the first place.
And given that I would likely do something similar, kudos to him for every extra dollar he has earned from your rant.
I'm not comparing, I'm telling the difference. Just like Netsparker will not do port scanning, possibly Metasploit will not do full web app stuff. You can talk with someone from Rapid7 and they'll tell you the same thing. And for the record I love Metasploit, it's a fantastic tool.
We have a good relationship with Rapid7 guys, they even has a module to import Netsparker results into Metasploit and we keep getting synced with them in new updates.
> Or can we just let it suffice to say that HN isn't a great place to promote products on random threads?
Personally I love seeing other HNers to send their relative products, projects, startups, commercial ideas, job ads in HN threads, I don't think there is anything wrong with that. You might think otherwise, that's why there is one upvote and one downvote button.
Metasploit isn't web application penetration tool. W3af, the other open source security tool Rapid7 sponsors, is. Meanwhile, Rapid7's commercial offering, Nexpose, also crawls Ajax applications and, if this flaw is as simple as people seem to think it is, would likely have found it... as would OWASP WebScarab or Burp (a tool that costs a fraction of what your tool does and belongs in the back pocket of every web developer).
I'm responding harshly because I do not agree with your logic (to wit: any thread involving security is a great place to plug your scanner) and because I found your comparison of Netsparker to Metasploit disingenuous: Metasploit simply isn't Rapid7's web app offering.
Disclaimer: I use Metasploit on a more or less daily basis, as well as Burp Suite Pro and more recently have been evaluating NetSparker Community Edition as our Canvas D2 subscription is up for renewal, and we've been considering switching to NetSparker. I've met Ferruh once at DC4420[1] and he seemed like a sound guy to have a beer with.
Ferruh was simply responding to a direct question about how it was different. Sure, he's the author but a) he was asked. b) he's probably best suited.
Ferruh isn't running a matasano scale operation, he's doing it on his own, peldi style.
It's not inappropriate for him to discuss his product, nor to answer questions on it - this is a startup community after all. At what point did you become the HN comment police?
It's one of a large variety of tools that addresses this problem, which begs for a response clarifying where his product fits in and what its strengths and weaknesses are. Now we're no longer talking about SQLI on MYSQL.COM; we're talking about scanners... because a vendor wants us to.
But, more to the point, how annoying would it be if every time I responded to a comment on HN, I added "this is one of the many things we take care of it you get us to review your applications"?
When threads are about products, I think it's fine --- helpful, even! --- to point out competing offerings and alternatives. But on news stories, let's just keep the commercial offerings off the threads.
I disagree with you sir. I think the plug was fully appropriate given that others reading this story might want to scan their own sites/app and not want to look around for something.
If you don't like it, downvote it...but given that it has been upvoted some 15 times as of this writing, I think the community agrees that it provides some value.
Ahh...now I get it. I was wondering why this plug peeved you so much. You compete with that guy.
Wow...talk about having a vested interest.
This is so unfortunate because now I have to double check grievances on HN to see who has a vested interests - which degrades the integrity of the community.
For someone with such high karma...this is so disappointing.
It may have been (to paraphase an earlier tptacek ranty comment) 'disingenious' of him not to disclose that he does web app security, but it's not necessarily his vested interest at fault.
For what it's worth, I often disagree with tptacek and while I lack the HN celebrity status, I don't really care because in the real world he's just another guy on the other side of the world arguing on the Internet. I do think that he's not motivated by commercial greed in his commentary though, and on the whole his comments (while I've never met him personally) display a good level of honesty, integrity and community spirit.
Unfortunately in this case he's gone after someone who in my experience is the epitome of what HN should be encouraging - a guy in an industry, who's set up on his own, who has written and sells a product more or less solo that helps address the specific problem being discussed. If this was bingo cards noone would give patio11 any grief for talking about BCC. What's worse is that tptacek's company is several orders of magnitude bigger than Ferruh's and his services directly compete, but I don't think tptacek was thinking of this at the time of his post, and certainly not looking for any commercial advantage.
I understand and appreciate that line of reasoning - had it been for a one off comment and had it been someone else.
We are talking about the guy with the most karma points on HN - so he knows the culture - and we are talking about (presumably) someone that owns & runs his own company. So at the very least, ignoring that this guy is his competitor, given that he is the highest ranking member on HN (karma-wise aside from PG), he should be applauding someone that has taken the road he travelled earlier and built his wonderful firm that he is running today.
Yet he tears him a part, not on one instance - i.e. in a reply to one comment, but if you look through the thread there are MANY comments when he jumps on the guys back.
It's totally and completely unacceptable, for any HN member to deal with any non-trolling HN member like that. Regardless of karma points.
What makes it worse is that he has a vested interest. So the only rational excuse is that 2 + 2 = 4.
Why else would he betray the ethos & spirit of HN so blatantly ? Let the truth be told, it could very well be just that he was having a bad day or whatever it is - but given that this is a story closely related to his main bread and butter you would think that he would be extra cautious.
What he has done here, is the equivalent of PG ripping apart a plug by someone from TechStars or any of the other incubators (e.g. the program in Chile that I see pop-up here from time to time, or the one in Ireland or the UK most recently) when they come here to post a 'shameless plug'.
That would be very irresponsible for PG to do.
As I said, if it were someone else that was new to the community I could let it slide...but not for someone that is CLEARLY the most karma'd person here.
I am afraid being the person with the most karma doesn't make him an epitome of hacker ethics or shining beacon of hope but mostly a guy who knows very well how to work it. They have those "superstars" on reddit as well (just look for andrewsmith) and well, we also have them here, even if that leaves a bad taste in some mouths... looks like it is a very human thing after all.
I understand that it might come across as mean...but that doesn't change that it is the truth.
I think it is fair to say that Matasano is well known on HN - based on your valuable contributions to the community.
That is also why it was so disappointing to me - who has watched you add value to the community over the years - to see how you handled a seemingly 'innocuous' plug.
The modus operandi for dealing with those things is downvote and maybe one comment. But you not only likely downvoted but made MANY comments. Constantly attacking the guy.
It was clear that there was something else below the surface. I just didn't know what. I thought that perhaps you guys might have had beef or perhaps the OP might have been a known troll or something, but nothing in his history would indicate that he was trolling. He seemed to be legit.
So it was only when I looked at your profile that I was reminded of your affiliation with his competitor that it jumped out at me.
It was as if Bill Gates was here criticizing someone else for plugging their own OS - because it was 'inappropriate', when it was fully appropriate.
So I apologize if my calling it out offended you, but your conduct offended me and the sensibilities of this community.
I would be doing as much disservice to the community, if I sat on the sidelines and didn't say anything.
while I understand that sql injection is mostly a fault of the host programming language/developer (php in this case) and not of the dbms/dba, couldn't the latter have avoided this in part by limiting user privileges so that it was impossible to "list the internal databases, tables and password dump" e.g. "REVOKE SHOW DATABASES, SHOW VIEW" ?
(I'm aware this may make impossible to use some web frameworks which rely on rdbms reflection, but I have the feeling this is not the case)
The database in this case has to share some of the blame. For years MySQL did not support parametrized queries or stored procedures (or did not promote them), and many of the people coding against it consistently claimed that they didn't need that kind of "enterprise crap" polluting their favorite DB. This gave way to a MASSIVE code base that is inherently vulnerable to injection attacks.
So yeah, it's the fault of the developers but it's also the fault of the people who aggressively marketed and evangelized MySQL and helped create the ditch they're just now digging themselves out of. It's a bit like the VB/Access culture Microsoft promoted back in the day, which generated some of the most hideous corporate apps I've ever seen. Yes, some developers are bad, but the company or group doing the evangelizing/marketing also needs to share some responsibility.
You don't need parameterized queries or stored procedures for protection against injection. In fact they're not injection protection mechanisms at all. What you actually mean is that the database libraries should provide parameterized-style APIs. There's no need for the database's native wire protocol to explicitly support parameterization to implement this. For example Ruby on Rails's ActiveRecord provides an API that looks a lot like it uses parameterization under the hood:
But if you look at the source code you'll see that it just constructs a normal SQL query by internally substituting the '?' with the escaped version of 'foo@bar.com'. It does not utilize the database parameterization APIs at all.
If you think stored procedures protect you against SQL injection, consider the following code snippet:
> You don't need parameterized queries or stored procedures for protection against injection ... There's no need for the database's native wire protocol to explicitly support parameterization to implement this.
This means that every protocol client must implement their own query escaping, rather than relying on the database to provide a single, normative implementation of escaping.
> For example Ruby on Rails's ActiveRecord provides an API that looks a lot like it uses parameterization under the hood:
> If you think stored procedures protect you against SQL injection, consider the following code snippet ...
I believe the original poster was referring to the use of stored procedures as a mechanism for preventing or discouraging unintended direct modification of the database by applications, rather than SQL injection, specifically.
It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.
The examples you've provided of Rails SQLI issues are bad ones:
* The first is a discussion of SQLI in an interface designed to accept raw SQL; it's the ActiveRecord "back door" interface.
* The second is a discussion of SQLI in a context where parameterized queries don't work anyways (the MySQL protocol doesn't accept LIMIT and OFFSET arguments as anything but integer constants); it is also the simplest of the class of SQLI concern areas (you can solve it by blindly calling #to_i on your inputs), which also includes table names, sort orders, and column references.
It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.
The database is the normative reference on what is and is not a special cased character and how escaping should be implemented. I don't think it's reasonable to assert that escaping is a concern that should be adopted by every framework that might ever talk to a database.
The examples you've provided of Rails SQLI issues are bad ones
I'm sure you could supply some better examples since your focus is in security, and there are a vast number of issues that have arisen in ActiveRecord's escaping (especially in early versions of Rails). These are merely what I quickly found while Googling.
The first is a discussion of SQLI in an interface designed to accept raw SQL; it's the ActiveRecord "back door" interface.
It's also an interface that was repeatedly and unintentionally used by Rails users to insert unescaped queries in a way that did not immediately appear incorrect, as evidenced by the preponderance of questions on the subject.
The second is a discussion of SQLI in a context where parameterized queries don't work anyways (the MySQL protocol doesn't accept LIMIT and OFFSET arguments as anything but integer constants); it is also the simplest of the class of SQLI concern areas (you can solve it by blindly calling #to_i on your inputs), which also includes table names, sort orders, and column references.
Given that this conversation is occurring in the context of discussing how MySQL's design has led to exactly these types of errors, I think this is an applicable example.
I don't know how to respond to any of these points.
The discussion at hand is, "who's job is it to defend against SQLI, the database, the framework, or the application?".
Of those three components, the database is least well equipped to defend against SQLI. SQLI is "avenue for attacker to submit queries to a database contrary to the intentions of the application". The database's simple job is to accept and execute queries.
... and spent a good hour reading the Rails source code. My take away was that, historically, a lack of care coupled with a lack of use of the parameterized APIs left the door open to repeated failures in the implementation to protect against SQL injection.
Things have improved in Rails, but as a historical example of the pitfalls of ignoring/eschewing parameterized query APIs, I believe it to be quite valid.
The discussion at hand is, "who's job is it to defend against SQLI, the database, the framework, or the application?". Of those three components, the database is least well equipped to defend against SQLI. SQLI is "avenue for attacker to submit queries to a database contrary to the intentions of the application". The database's simple job is to accept and execute queries.
No, you're reframing the discussion. The original poster commented on MySQL's longtime begrudging support for parameterized queries (or lack thereof) as one of the major causes of the prevalence of SQL injection issues.
It seems clear to me that the database -- as the central implementation responsible for parsing queries -- is the best equipped to provide safe, correct string interpolation of those queries.
People that don't know Rails are going to think that you're making pointed critiques of the framework, when in fact you appear to be repeatedly citing examples of people going through extra effort to use the interfaces Rails provides for directly using SQL instead of ActiveRecord's finders.
I'm not offering a "pointed critique of Rails" ([edit] although your framing it as such seems to encourage downvotes into the negatives). I'm demonstrating how the use of non-parameterized queries source of error that has repeatedly resulted in SQL injection issues in ActiveRecord. I have provided three different examples:
- In the first, the API design itself made it non-obvious/easy to directly concatenate strings while -- at a glance -- appearing to be correct.
- In the second, the API's implementation resulted in SQL injection because :limit and :offset arguments were not correctly escaped.
- In the third, the API's documentation and recommended usage (in 2004) encouraged users to use constructs that appeared to provide defense against SQL injection, but in reality, did not.
> It is not unreasonable to assert that SQLI defense is a framework concern, not a database issue.
Sure, but it is unreasonable to assert that SQLI defense is not also a database issue.
Using the provided apis in a way that prevents SQLI is a framework concern. Providing apis that make that possible without re-implementing basic things like escaping is a database concern, otherwise you're just asking people to re-solve the same problems so they each get a chance to screw it up.
The SQL Injection vulnerability is, "user coerces application into submitting an unexpected and unauthorized query".
Blaming the database for that is like blaming the filesystem for pathname injection vulnerabilities.
It could, after all, send a Unix signal to a calling process when a filename contained "..", and demand that the process re-assert it's desire to really reference a different directory.
We may be spiraling here. Parameterized queries are a good thing. I'm glad MySQL has them. I'm not, however, going to wag a finger at MySQL every time someone finds an SQLI vulnerability in an app that uses MySQL, just because 6-7 years ago they didn't have parameterized queries. For one thing, it's not a useful comment (do you want them to implement parameterized queries... again?); for another, it's not particularly valid architectural point; and finally, it's really boring.
I think we definitely are spiraling. My argument is about how the stance MySQL took on parameterized queries has affected the product's community. I don't disagree with you on the technical issues. Well, except that providing useful tools to handle SQL defense correctly is a database issue. And pathnames are a read herring, as they are not a transport for data manipulation commands.
I guess part of it is that I'm just done watching various problems with MySQL (transaction support, parameterized queries, subqueries, bizarro query optimization) be given a pass as "not quite MySQL's problem". Maybe I'm just taking that out on this thread.
Not comparable. h() was a one-sized-fits-all problem to quoting things that could occur anywhere in an HTML DOM. #to_i assures that a bit of syntax that can only ever accept integers is in fact given a real integer.
In case we're misunderstanding each other, I'm also saying that the framework should be doing that, not the caller (as was the case with h()).
You're arguing against the periphery of his point. By waiting so long to provide parameterized queries, MySQL helped foster the attitude that they were "useless enterprise bloat". The lack of parameterized queries was likely an influencing factor in the non-support or non-advertisement of parameterized-style APIs.
MySQL is a crucial part of its own community. You cannot hold the community responsible for this situation while giving MySQL a pass.
I have never heard someone say parameterized queries were "useless enterprise bloat". Their use in modern web apps is an industry best practice widely adopted across all the Internet apps Matasano gets to test. Your reaction here sounds hyperbolic, and the parent commenter is right: parameterized queries, while helpful, are neither required nor sufficient for defense against SQLI.
> I have never heard someone say parameterized queries were "useless enterprise bloat".
I have. Usually from people with no idea what they're talking about, maybe one of us has been (un)lucky in our experience. Hopefully it's me.
> Their use in modern web apps is an industry best practice widely adopted across all the Internet apps Matasano gets to test.
That's great! How long has it been true, though? When I first learned PHP (2003), this was not mentioned. Maybe it existed, either in MySQL directly or as an api method, but it certainly wasn't widely advertised. Maybe looking all the way back to 2003 is stepping outside the scope of "modern" here, but I still occasionally get to deal with problems created back then.
> Your reaction here sounds hyperbolic, and the parent commenter is right: parameterized queries, while helpful, are neither required nor sufficient for defense against SQLI.
I'm sure it does, considering the differences in our experience of the situation.
Also, my point was not that parameterized queries are the end of SQLI defense or required for it. Just that you can't give MySQL as a company a pass on a situation they helped create. Parameterized queries are a very simple and common first step in learning to deal with SQL injection. Not implementing them for so long did not help the situation in this respect.
Could you expand a bit on how parameterized queries are not sufficient for defense against SQL injection (assuming, of course, that developers use the escaping and do not concatenate unescaped data into queries)?
As for them being required -- you can obviously escape queries yourself, but the normative reference for escaping is the target database itself, and reproducing escaping locally in the client brings with it the likelihood of introducing an error in the custom implementation.
Not every "input" to a "query" (using these terms loosely) can be bound as a variable. Simple example: ASC and DESC. There are trickier examples that are still common.
It is a bad idea for applications to implement quoting regimes, and it is a bad idea for frameworks to try to create one-size-fits-all quoting regimes like PHP used to. That doesn't mean it's a bad idea for a framework's e.g. MySQL support to provide the capability of sanitizing MySQL inputs under a common database API.
The problem with that is that almost nobody uses the official database APIs. The official APIs are usually C libraries (e.g. libmysqlclient) but pretty much everybody uses third party wrappers (e.g. Perl DBI, the mysql/mysql2 gem for Ruby, the PHP default MySQL bindings, etc). Few people program against the database in C or C++. It was and is up to the third party API providers to provide easy sanitization APIs, I don't see how MySQL could have changed that situation by providing such APIs themselves.
> Their use in modern web apps is an industry best practice widely adopted across all the Internet apps Matasano gets to test
I'll resist the pot/kettle/colour connection here about plugging your own stuff on HN in a web app security thread. Agh, too late.
On a more serious note, if all the Internet apps Matasano test are using parameterized queries then:
a) Matasano never tests a MySQL-based app
AND
i) Matasano does very little web app testing these days
OR
ii) You're not quite telling us the truth
Seriously, parameterized queries while being a best practice so to speak are not a one size fits all solution and is not something implemented by everyone. The amount of shonky development practices we come across at my unnamed and unplugged company far outweighs the instances of the security aware, certainly on the first, second and sometimes third time round.
Today I'm testing a wordpress-based web app. A beer says that it's not using parameterized queries.
"In fact they're not injection protection mechanisms at all"
What?! Parameterized queries (in any competent implementation) ARE, in fact, injection protection mechanisms. Escaping != Parameterization.
Ruby on Rails is a poor example to use here - it's a trendy web language that (judging by your comment) does not use proper database practices.
Properly implemented parameterized queries offer protection against SQL injection because they seperate data from instruction - none of the data points in a query are executed, ever.
Gluing SQL instruction strings together with data from users is incredibly stupid and you should never, ever do that.
Use a framework that supports proper parameterized queries.
If parameterized queries guaranteed a total separation between user input and query structure, you'd be right. But they don't. They guarantee a separation between some user inputs and query structure.
I'm failing to see how ASC and DESC would apply here, unless someone is simply passing them on directly from a URL or input form into the SQL, which is almost the equivalent of just letting the user type in the SQL directly. Also, the worst thing that can happen to an extension like LIMIT is that more rows show up than expected, but that wouldn't change the context of the original query to return data that wasn't originally intended to be shown. Plus, it would be very difficult to tag on more SQL to a direct LIMIT number being passed in unless the back-end application was totally brain-dead about how it converted strings to integers.
One other thing - extensions like LIMIT can be parameterized also (shameless plug: our product ElevateDB can do it with its RANGE clause). It's a nice way of getting rid of this type of issue, and allows for easier pagination without having to force the database engine to constantly re-prepare the same query over and over again.
IMHO choosing between ASC and DESC isn't exposing an input into the query in the same way that accepting arbitrary text (escaped or not) into the query is, but thanks for clarifying.
And with regard to LIMIT, it's a bad idea to allow the user to pass in any values without validation, even if you restrict it to integers.
Parametrized queries help resolve the most common kinds of SQL injection, all the "yes but..." argue that it's not a blanket instrument that instantly should make you feel safe and no longer think about security/robustness. That's also true. Such a thing doesn't exist anyway.
SQL Injection isn't even really an issue with the host programming language. It's an issue with processing input that's submitted "as is".
Aside from implementing bound parameters (which is useful for more than just preventing sql injection), we usually recommend using stored procedures if at all possible.
It's really suboptimal to rely on the user to specify what table they're accessing.
Absolutely. The web application should be logging to the database as an unpriviledged user. Seems that by now most people have learned that running web services as root is a bad idea, but they still forget that they should also have an unpriviledged database user for them.
PHP has been known to provide convenient footguns in the past (e.g. register_globals, mercifully depreciated), so it's not surprising that security-minded people give it a hard time.
Think of it as the difference between the language keeping loaded footguns under its pillow with the safety off and keeping unloaded footguns in a locked gun safe. One is a lot less likely to get used than the other, even if either one will shoot your foot just as well.
It doesn't seem fair to call a defect "old" if it wasn't seriously addressed between then and now. I had to pick up PHP (presumably because of heinous sins in a past life) and every tutorial I saw was still pasting user input into non-parameterized queries. There are apparently several different MySQL clients, and our production boxes still had the original (inexplicably still in existence) which didn't even support parameterized queries. And that was in 2007!
Just hit the same issue here in 2011. Plesk, a popular package for managing shared hosting used by hosting companies, doesn't include the MySQL drivers for PDO (what's meant to be PHP's "standard" database library).
Last week I had to rewrite an import script to use mysql_query(), with mysql_real_escape_string() and quotes for every query variable.
Oracle's security team bear full responsibility for this breach. MySQL's founder Monty Widenius left Sun in 2005. Sun declined, Oracle bought them as a strategic buy and the portal has been neglected to the point of being compromised.
One wonders what internal neglect MySQL is suffering behind the corporate veil.
I was thinking more about why it is allowed at all to send text-like SQL queries to a server. A binary protocol would both be simpler to handle and would have saved us from a lot of trouble.
Of course that does not help if you construct the entire query string as text before sending it to the server, which is how SQL injection most commonly happens.
Reply to your further edit (let't hope this is the last one ;)
Prepared statements should afford you a clean conscience because the values never make up part of the SQL query .. unless you are using a library that emulates it, and there are libraries out there that do, so don't assume anything.
Prepared statements do not afford you a "clean conscience". You still need to be careful with your inputs in parameterized queries, because not every input to every query can be bound as a variable. Some operations do require dynamic query construction.
"because not every input to every query can be bound as a variable" Can you give me an example?
var sql = "select * from User where UserType=@ut order by ModificationDate " + sort_order;
In this case, if sort_order directly comes from a 'asc'/'desc' radio button then you have an injection attack.
The correct way to do it would be:
var sql = "select * from User where UserType=@ut order by ModificationDate " + (sort_order=="asc"?"asc":"desc");
The point was that there are some parts of sql that can't be parameterized like sort order or limits on the resultant recordset. Although, ideally support for those things should be coming from your database vendor (if it isn't already there).
For example, SqlServer supports using variables in top expressions: select top(@max) * from ....
Because when you try to use a prepared statement to populate a paginated table, you still force the database to construct a dynamic query based in part on user inputs when you pass the OFFSET and LIMIT to it.
Recommended exercise: implement the MySQL wire protocol. It took me a couple hours a few years ago. It's not hard. Do it, come back, and then see if you still think the same things about SQLI defenses.
The language itself doesn't matter at all. Everybody would be fine to use some construct like:
Select(args=["name", "id"], from="students")
And maybe "name", "id" and "students" also could be replaced my some reference pointers.
But in case someone really wants to keep the language, you could still do the parsing inside of the lib and thus for example disallow multiple commands in one statement and then only send some binary representation to the server (mostly like Prepared Statements in MySQL). Whereby I think that this step is obsolete and would in any way produce further problems.
Btw., this is not exactly like NoSQL what I am talking about here.
Yes, this looks much like what I want from the outside/usage.
But the implementation of it is actually again the error-prone and slow way to generate a SQL-statement from that, then send that to MySQL and MySQL parses it back to get the original structure.
I actually want to complete avoid that step to generate an SQL statement.
1. It would be much cheaper and faster. Right now, you are: constructing a string first, doing some escaping, then sending this more bloated pure-text query to the server, then parse the SQL language, unescape, convert back into some machine representation. I.e.
a) Most of this extra handling (escaping, string-conversions, parsing) would go away.
b) Less data need to be send around.
2. It would actually make SQL injections impossible and thus solve all these problems.
You've made "SQL injection" impossible by inventing "albertzeyerql", which will inevitably be vulnerable to "AZQL injection". Many of the NoSQL's have similar problems; expect to hear more about them in Blackhat presentations yet to come.
It's not pointless to discuss how query languages can be made simpler to parse and thus less susceptible to injection, but the technique isn't foolproof and is (obviously) expensive; meanwhile, the proper defense against SQLI (being mindful about query inputs, and using the framework to abstract and normalize dynamic queries) is available today and works well.
I am not really talking about a language at all (at least not on a byte-alphabet).
Maybe we misunderstood a bit. For example, take a look at the [new Redis 2.0 protocol](http://redis.io/topics/protocol). In this protocol, injections are simply impossible. This may not be the best example because you don't really have more complicated queries but you could just do the same for tree-like structures.
I think you're wrong, both in your general point and about the possibility about Redis query injection; unfortunately, you're wrong in ways I'm not at liberty to correct. I can only ask that you take my word for it that you need to be as mindful with the code that constructs Redis keys as you do with code that concatenates SQL queries. And, as with SQL, much (perhaps most) production-quality code isn't susceptible to attack; but the lines that are tend to blindside you.
I don't see why you couldn't inject a CR LF? Sure it might not be that dangerous (although it could be depending on what validation the server does on # of args), but you're still modifying the query.
I don't understand this (or, to be honest, the parent). MySQL has a binary protocol. The MySQL binary protocol does, in fact, allow you to parse a query template and use it in subsequent calls with bound variables; that's how "parameterized queries" work.
It's not in a very obvious place. Downloading the mysql source is one of the most annoying experiences I've had on an OSS site. Iframes, select boxes, login forms! Just give me a list!
I hate sites that require logins. Though apparently this one had a way around it, lots don't, and many of them are already in bugmenot. If not, add a mailinator-based account for others :)
I do, every time I do an "aptitude install" or a "yum install", but not from their site. And the original source was probably downloaded from version control, not the public site. Also, when I do that with Drizzle, I am downloading a lot of MySQL with that.
But I agree. Most of the time, I prefer PostgreSQL.
http://seclists.org/fulldisclosure/2011/Mar/309?utm_source=t...
http://pastebin.com/BayvYdcP