SoylentNews is people
SoylentNews
SQL Injection Attacks Represent Two-Third of All Web App Attacks
For its "State of the Internet" report, Akamai analyzed data gathered from users of its Web application firewall technology between November 2017 and March 2019. The exercise shows that SQL injection (SQLi) now represents nearly two-thirds (65.1%) of all Web application attacks. That's up sharply from the 44% of Web application layer attacks that SQLi represented just two years ago.
Local File Inclusion (LFI) attacks, which, like SQLi, are also enabled by a Web application's failure to properly validate user input, accounted for another 24.7% of attacks. Together, SQLi and LFI attacks represented 89.8% of all attacks at the Web application layer over the 17-month period of Akamai's study.
[...] SQL injection errors and cross-site scripting (XSS) errors have topped, or nearly topped, the Open Web Application Security Project's (OWASP) list of top 10 Web vulnerabilities for more than a decade. Just this week, in fact, HackerOne published a report showing XSS errors to be by far the most common security vulnerability in Web apps across organizations. Both XSS and SQLi are well understood, and many researchers have catalogued the dangers associated with them for years.
The fact that so many Web apps still have them reflects the relatively scant attention paid to security in the application development stage, says Andy Ellis, chief security officer at Akamai. "It is not that the developers are making errors," he says. "It is system that we put them into that is dangerous."
[...] Akamai's data[pdf] shows most Web application attacks originate from inside the US and most targets are US-based as well. Of the nearly 4 billion application-layer attacks that Akamai counted over the 17-month period, some 2.7 billion targeted US organizations. Companies in the UK, Germany, Brazil, and India were also relatively heavily targeted. though nowhere nearly as much as US companies.
(Score: 4, Interesting) by Common Joe on Saturday June 15 2019, @05:03PM (32 children)
Serious Question: I was a programmer a decade ago, although I never got into Frameworks, so I'm ignorant. (I programmed a little closer to the metal than most people do today.) Anyway, about 5 years ago, I had someone tell me that SQL Injection Attacks were "a solved problem". This story seriously surprises me. I was making sure SQL Injection didn't happen 15 years ago.
So, my question to my fellow SoylentNews readers: Aren't Frameworks supposed to fix this kind of thing? Why is it still happening in such great numbers?
(Score: 4, Informative) by fyngyrz on Saturday June 15 2019, @05:17PM (12 children)
There are two major factors in play here.
First, because most self-described programmers aren't, you know, actually programmers. They're script kiddies, posers with no depth or breadth of experience, much less any inherent wisdom.
Unfortunately, it is possible to very quickly build a highly functional system from black boxes one has no real understanding of other than "someone said use this to get that done."
The second factor, closely related, is that it is much less expensive to build systems this way, and so commercial operations that create widely used products lean heavily towards hiring anyone who can build a fast, cheap system, without even considering those who could build it safely, expensively, and slowly.
Hence the broad proliferation of incompetents building such systems, and the blitzkrieg of security, usability and privacy problems.
--
🎶When you're down by the sea
And an eel bites your knee🎶
🎶That's a moray
(Score: 2) by The Mighty Buzzard on Saturday June 15 2019, @11:41PM (9 children)
It's not really any quicker to do it wrong than it is to do it safely. At the most simple level, you can write a wrapper for each data type a select statement might return and use those functions that prepare every statement for your entire codebase instead of having to write three lines or so every time you want to run a select. If you make more than a dozen or two selects, you're saving time and being safer.
My rights don't end where your fear begins.
(Score: 2) by stormreaver on Sunday June 16 2019, @01:17AM (6 children)
Or you can use prepared statements/parameterized queries, and solve the problem at its root.
(Score: 2) by The Mighty Buzzard on Tuesday June 18 2019, @01:43PM (5 children)
Too much repetitive typing. That's why functions exist.
My rights don't end where your fear begins.
(Score: 2) by stormreaver on Tuesday June 18 2019, @07:45PM
You seem to think that the two are mutually exclusive, when they're not.
(Score: 2) by edIII on Thursday June 20 2019, @10:10PM (3 children)
I do this all day long, and you've lost me a little. What do you mean?
My lines of defense:
What are you differently, that results in less typing but the same levels of defense against SQLi?
Technically, lunchtime is at any moment. It's just a wave function.
(Score: 2) by The Mighty Buzzard on Friday June 21 2019, @01:16AM (2 children)
Well, it's not a strongly typed language but have a look here [github.com] for the very loosely typed perl we use on the site. We never use raw SQL if one of these is viable to use, which means we never have to remember to prepare a statement; manually escaping shit is right out. If we need a result that's going to be a hashref of arrays, we have to specifically ask for it. It's much more effective in say Rust where you can insist on a specific type of integer or string type but even just the loose typing of rehash is a hell of a lot better and safer than having to write out prepare, execute, fetch, finish every bloody time.
My rights don't end where your fear begins.
(Score: 2) by edIII on Friday June 21 2019, @01:50AM (1 child)
Thanks man. I'm always on the lookout for better and easier ways to do stuff like this. Not to mention more securely.
Technically, lunchtime is at any moment. It's just a wave function.
(Score: 2) by The Mighty Buzzard on Friday June 21 2019, @04:31AM
Made perfect sense to me the first time I saw it. I mean, anything more than three lines you have to type repeatedly and nearly the same every time should probably be a function anyway, if only for legibility's sake.
My rights don't end where your fear begins.
(Score: 2) by Common Joe on Sunday June 16 2019, @03:48AM (1 child)
This is what surprises me the most. For some home brew stuff, Years ago, I wrote some server facing stuff for PostgreSQL in a three tier system. A C# program I wrote analyzed that, and it wrote a C#-based interface to be included in the server code that completely shut down the SQL injection attack angle. (It wasn't good enough to release publicly. I would have if I'd had time.) I'm shocked that I've never seen something generic like what you or I have done in the wild.
What do you use to do that? Something home brew? Or is there something found in a place like GitHub that you use for that?
(Score: 2) by The Mighty Buzzard on Tuesday June 18 2019, @01:47PM
Have a look at the rehash source on github, it's supposed to be entirely like that except for a few places where user input doesn't exist.
My rights don't end where your fear begins.
(Score: 0) by Anonymous Coward on Sunday June 16 2019, @03:27AM
As the old saw goes, "Quick, cheap, good. Pick two."
(Score: 0) by Anonymous Coward on Sunday June 16 2019, @11:04AM
I think his question was: aren't the makers of the blackboxes/frameworks being mindful of security? (I get that the users of frameworks will be speedy knuckleheads)
(Score: 1, Interesting) by Anonymous Coward on Saturday June 15 2019, @05:20PM
Q: Don't frameworks solve SQLi infections?
A: Sometimes. If the framework cuts corners or doesn't properly sanitize input, then every application or website using that framework is vulnerable. If an add-on vendor doesn't adhere to the framework guidelines, or bypasses some functionality to be closer to the metal (where both you and I live), that can introduce vulnerabilities as well.
Frameworks are not a silver bullet. Security is always as weak as the weakest link.
(Score: 1, Interesting) by Anonymous Coward on Saturday June 15 2019, @05:24PM (10 children)
Because all of the examples and some frameworks out there show doing things like this.
myrunstring = "SELECT " + " yourcolumn.tostring() + " FROM " + yourtable.tostring()"
Then using some form of exec on it unesscapped. Attack any portion of that string and you in like Flynn. Then on top of that have a poor security model on the server side which lets people run whatever as locked down is "hard".
So yeah it is "solved" but not easy to get right.
SQL's security model is borked from the beginning as the ODBC layer does not assume anything other than I trust you. You can poke pretty much whatever you want in through it if you know how.
You pretty much have to get the whole stack right for SQL injection to not work correctly. As its default is a model of trust not of 'non trust'. All the way from inputs from the user all the way down to it running on the server and back. Never trust your inputs.
I used to do bounce back attacks through the SQL server to get more privilege on the client to mess with my fellow devs. Basically put SQL/HTML/JS commands in the data fields and see what mayhem I could cause on the other end. Never trust your inputs even from a trusted source. Took a bit of work but not much. As maybe you escaped going in but not on the read side and then I could run more commands. I was a right bastard in my list of things I would put into all the fields to mess things up.
Like I said its tough to get right.
(Score: 0) by Anonymous Coward on Saturday June 15 2019, @05:33PM (5 children)
Prepared statements and parameterized queries are the best defense against SQLi attacks. They take a few more minutes to code, but aren't difficult and they are effective.
(Score: 1, Informative) by Anonymous Coward on Saturday June 15 2019, @05:58PM (4 children)
Doesn't help when the readily available examples online are outdated copypasta from 2 decades ago recycled for the nth time.
(Score: 1, Interesting) by Anonymous Coward on Saturday June 15 2019, @06:48PM (3 children)
Oh they are right it is what I call a good start for that sort of thing. But yeah mine and your point was there are *many* bad examples out there. Guess I got onto rant and forgot to square in on that point. Also the anti pattern many of these shops follow of 'got it to work' then massive copy and paste endeavor. Where they had 1 security issue now they have hundreds. c+p my old frenemy. But yeah bind your params bashes out most of the 'easy to exploit' stuff. You also pretty much have to hide the DB behind some sort of service to pull that off correctly.
Even *if* you bind your bind your params you need to watch what is coming back up out of the DB. As I can create a two stage SQL injection attack. As I can put junk in from one point make it look like valid HTML/JS/whatever on the other and break out of the box. So you have to be careful on your strings from that side too. The trust surface on SQL is not good for security. Great for getting shit done though. Even worse if you have more than one way to get into a DB. Do not assume all of your data is sanitized or you could have a bad time of it.
(Score: 0) by Anonymous Coward on Saturday June 15 2019, @07:41PM (2 children)
I'm the "prepared statements" AC.
I don't disagree with either of you regarding "the bad examples greatly outnumber the good examples". The "copy from a website" programming culture is resulting in an enormous amount of poorly written and insecure legacy code in many projects. This will only get worse as outsourcing becomes even more common and more cost competitive.
Poor (or nonexistent) security, combined with the lack of accountability relating to data breaches, guarantees that things will not improve. If security isn't part of the foundation of a project then it is only a matter of time before things will go badly.
(Score: 0) by Anonymous Coward on Sunday June 16 2019, @12:36AM (1 child)
Bind them helps so much! Also make sure your framework really does an odbcbindclol on the bottom of it and not just catting things together and running exec on it and calling it good. You may laugh but I have seen it. You think you are binding but really under the covers it just does a string cat and hides it from you.
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlbindcol-function?view=sql-server-2017 [microsoft.com]
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlbindparameter-function?view=sql-server-2017 [microsoft.com]
If your code is using this you are just waiting for a SQL injection attack. It works great in a quick and dirty cheap never to use again app. But anything where you expect security avoid it as much as you can.
https://docs.microsoft.com/en-us/sql/odbc/reference/syntax/sqlexecdirect-function?view=sql-server-2017 [microsoft.com]
(Score: 0) by Anonymous Coward on Sunday June 16 2019, @05:41AM
That's basically what I said in this response [soylentnews.org] earlier in this thread.
Fortunately I've written our own wrapper to DB calls that always prepares and binds, and any queries that are used often are written as stored procedures (which are called via prepare and passed bound parameters).
(Score: 2) by Common Joe on Saturday June 15 2019, @08:16PM (3 children)
I just can't even. Only under very specific circumstances is that ok. Like less than 0.1% of the circumstances that I can think of. 99.9% of the time, this is... ugh. I'm preaching to the choir.
Grumble, grumble. And they say my skills are out of date. Grumble, grumble.
(Score: 2, Informative) by Anonymous Coward on Saturday June 15 2019, @11:31PM (2 children)
To me, those examples are not the worst, because it is quick and easy to explain why it is wrong to someone. It is the examples that do something like:
People see that, you complain and then they don't understand what is wrong because they are escaping. But then, they finally read the docs or something and then switch to mysql_real_escape_string. Except you complain because that still has vulnerabilities, which are unfixed because that function is deprecated, and it begins again.
(Score: 2) by digitalaudiorock on Sunday June 16 2019, @07:46PM (1 child)
I'm not sure I understand the point you're trying to make given that the entire mysql extension (as apposed to mysqli) in php has been completely removed. The mysqli_real_escape_string on the other hand is very much still valid. In any case I've never coded any database access via PHP without using the PHP ADOdb abstraction layer:
https://adodb.org/dokuwiki/doku.php [adodb.org]
That takes care of the proper escaping...in the case of the mysqli driver using mysqli_real_escape_string. That's extremely lightweight as well, unlike others. As I recall for example, the Pear DB stuff (at least when I tried it years ago) hammered performance my orders of magnitude just by including it.
(Score: 0) by Anonymous Coward on Monday June 17 2019, @02:47AM
Well, for one thing, mysqli_real_escape_string doesn't fix the bad SQL statement I put (which I am kind of surprised no one pointed out, especially since it sort of proves my point in an ancillary way, since I did it on accident in haste). It is also still vulnerable, even if there were quotes there, because you can still trick mysqli_real_escape_string with a proper single-field payload, through interactions between multiple fields, and through how various sanitizing functions cascade. There are even tools to do it automatically by trying the various inputs when they don't know what your character set is or combination of escaping and other sanitizing. Plain and simple, yes each refinement of the functions make your site safer, but security has to be broken once and then all the automated tools get updated to add the latest generation of attacks. Meanwhile you stay vulnerable, if you don't keep up with the changes in proper functions to use, bug patches, framework changes OR your framework, hosting platform manager, or other vendor doesn't.
(Score: 0) by Anonymous Coward on Saturday June 15 2019, @09:22PM (5 children)
Even then, there's basic prepared statement in each library.
I don't understand how people making anything web don't know about it.
(Score: 0) by Anonymous Coward on Saturday June 15 2019, @10:46PM (4 children)
"Know about it" and "bother to use it" are very different beasts when it it comes to programming.
(Score: 2) by Common Joe on Sunday June 16 2019, @03:58AM (3 children)
I've had a very tough time being accepted back into the programming world. My skills are still too outdated. Howver, I have a programmer friend who lost his job and in a week's time, he had five interviews lined up. It amazed him that I had trouble finding a job because we were having dinner one night and I was explaining to him how databases worked -- table joins, indexes, really simple stuff. He'd never seen that before. He literally did not know. (Which brings up the question in my mind "How could he possibly defend against a SQL Injection attack?" I don't know and I don't want to know.) It was simply that he had the right buzz words on his resume.
(Score: 1, Funny) by Anonymous Coward on Sunday June 16 2019, @06:47AM
Don't forget about the 25 years of JavaScript, 35 in Python, 30 in Java, and 15 of Rust.
(Score: 0) by Anonymous Coward on Sunday June 16 2019, @04:19PM (1 child)
Programmers who don't look under the hood - those that just program using a published API or the public methods/functions of a framework - *don't need to know how it works.
* They should know, and in theory things will always work better if they do know, but if the methods and functions they call are all supposed to handle that stuff they can skate by in most cases.
(Score: 2) by Common Joe on Monday June 17 2019, @02:46AM
This is part of the reason I asked my original question. It seems it wasn't just functions and method calls to APIs, methods, or functions that he was using. It seems he needed to know about tables as well. If he knows about tables, then he also needs to know about joins and indexes and SQL Injection attacks. And if he needs to know this, then a lot of other programmers should also know about that stuff but don't.
But if I misunderstood something, then please elaborate.
(Score: 2) by edIII on Saturday June 15 2019, @09:26PM
It is a well solved problem. Parameterized queries, which is really just a function that properly escapes, and sanitized/verified inputs prevent SQL injection attacks. Removing the single quotes and semi-colons from a string also prevents it entirely I would think. Most inputs don't require either. For customer names, you can perform a replace on the single quote with its HTML entity code as one trick. Other types of input data can be encoded in base64, which also makes it "string safe". There are quite a few mitigation techniques, but they require that you evaluate the security of your code.
What is not a well solved problem is laziness, incompetence, avarice, and flat out stupidity. A lot of copypasta coders out there, and a lot of bad code that practices none of the mitigation techniques. A lot of management that treats security as an afterthought, or just a legal liability problem for future people after they use their golden parachutes.
Technically, lunchtime is at any moment. It's just a wave function.
(Score: 3, Interesting) by bradley13 on Saturday June 15 2019, @05:54PM (13 children)
I teach my students about SQL injection in their second semester. Injection attacks of all types are trivial to avoid; SQL injection is probably the simplest of all.
This is simple incompetence.
Everyone is somebody else's weirdo.
(Score: 1, Interesting) by Anonymous Coward on Saturday June 15 2019, @06:52PM (7 children)
Never trust your inputs. Even from a 'trusted' source output. None of them are trusted. Escape everything. Even the output fields from a DB.
(Score: 2) by stormreaver on Sunday June 16 2019, @01:15AM (4 children)
If you find yourself escaping/unescaping, you've already lost. Use prepared statements and solve the problem. Everything else is sabotaging yourself, your coworkers, and your customers.
(Score: 0) by Anonymous Coward on Sunday June 16 2019, @03:23AM
That only fixes it at the SQL level. I can turn my outputs into HTML/JS/whatever then use that code to jump back in somewhere else into the megabytes of other functions you have conveniently linked in for me. May take me a couple of jumps but I can find the hole. Never trust your inputs even if from a secure source. Make sure they stay the type you expect at your output level. Otherwise I can jump your implicit sandbox, and rather easily. I can also abuse your stored procs to do my bidding. with_reocmpile is my buddy too :)
(Score: 2, Funny) by Ethanol-fueled on Sunday June 16 2019, @05:21AM
Real N.I.G.G.A.Z. use LabVIEW [jkitchtech.com] for SQL sheeit. Not only is it much more safe, but more easy to read.
(Score: 2) by edIII on Thursday June 20 2019, @10:22PM (1 child)
Don't be so sure :)
How does the prepared statement with parameterized queries work again? Look into the code and you will find they run a standard escape function against it. The only difference between escaping it yourself, and the function, is that the function will produce the same output all the time.
If your data abstraction layer is playing those same escape games, then you've just received a heapin' helping of false confidence. Escaping things properly is a bitch. Why try when you can regex the single quotes and semi-colons out of the string entirely. Only a few use cases require dealing with that, and if it doesn't require searchability, then BASE64 encoding annihilates SQLi. If searchability is important, you can still remove those characters from the search input as well as the DB, or convert them to HTML entity codes. Super paranoid, you could create a field that stored positional data on where to put them back, and use that in an export functions.
The single quote and semi-colon are your enemies, and you should only trust your own regex code that you can see yourself. The vast majority of all use cases survive their complete removal.
Technically, lunchtime is at any moment. It's just a wave function.
(Score: 2) by stormreaver on Friday June 21 2019, @12:41AM
Any database that implements parameterized queries using escapes and unescapes is fundamentally broken. PostgreSQL, for example, bypasses the parser entirely for prepared statement parameters (as it should). I could easily see MySQL escaping and unescaping, though. But that's because MySQL is fundamentally broken on a number of levels.
(Score: 1) by webnut77 on Sunday June 16 2019, @01:34AM (1 child)
You've got to handle data like:
"Invoice Date" with an answer of "Thursday"
"Invoice Amount" with an answer of "Blue"
Not all users are idiots. Some are just multi-tasking trying to get they're job done.
I recently fixed a problem where Stop_Datetime was earlier that Start_Datetime.
(Score: 1, Funny) by Anonymous Coward on Sunday June 16 2019, @02:27PM
A QA guy walks into a bar and orders a beer. Orders two beers. Orders infinity beers. Orders negative one beers. Orders a monkey.
(Score: 0) by Anonymous Coward on Saturday June 15 2019, @08:54PM
Yes, but those people get hired. And with extreme flattening of hierarchies, and no accountability for cock ups, that incompetence turns into state of the art reality.
(Score: 1, Insightful) by Anonymous Coward on Sunday June 16 2019, @12:39AM (3 children)
This is simple incompetence.
Only if you tell them a better way and they ignore you.
Do not confuse incompetence with lack of knowledge. They do look the same and some people argue they are the same. But I would rather have a dev who can make a mistake and go 'doh here is how to fix it' than 'doh who cares I will just leave it and do it again when you are not looking'.
(Score: 2, Disagree) by darkfeline on Sunday June 16 2019, @04:26AM (2 children)
Lack of knowledge is incompetence.
Competent == able to do the job effectively.
Incompetent == not able to do the job effectively, whether due to lack of training, knowledge, or head mass.
Or more specifically, if you need to have pointed out to you by someone else that you are ignorant and what you are doing is a security problem, you are incompetent. Someone who is competent would at least know that they are ignorant and obtain the proper knowledge, training, or counsel before attempting to hack together sensitive infrastructure.
Join the SDF Public Access UNIX System today!
(Score: 1, Touché) by Anonymous Coward on Sunday June 16 2019, @05:31AM (1 child)
Yep as soon as you learn about a tech you know 100% how that thing works. You do not need anyone telling you any better at that point.
Someone who is competent would at least know that they are ignorant and obtain the proper knowledge, training, or counsel before attempting to hack together sensitive infrastructure
Mistakes happen. It is how you deal with them that matters. People do not always have 100% knowledge on something. It takes years to acquire good training. That is from code reviews and being humble. Spouting off that someone is incompetent has implications in a business environment. But you should know that or else you are incompetent (by your own definition).
(Score: 2) by darkfeline on Monday June 17 2019, @06:44AM
>Yep as soon as you learn about a tech you know 100% how that thing works. You do not need anyone telling you any better at that point.
I don't know what the point of this statement is. It sounds like you're implying that I said you need 100% knowledge to be competent. I said no such thing. It also sounds like you're implying that I said that someone who is competent has no room to improve. I also said no such thing.
>Mistakes happen. It is how you deal with them that matters.
That's what I said. Even if you are ignorant, the way you handle a task may allow you to qualify as competent. You even quoted the sentence where I stated that.
>Spouting off that someone is incompetent has implications in a business environment
Er, yes? There are many uncomfortable facts that you should not voice in a modern professional environment. Whether or not someone is incompetent is one such fact that's best left unstated, at least during work; off-hours, you might sympathize with a close friend. I also have no idea how this addresses my claim. It's yet another non sequitur "argument".
>But you should know that or else you are incompetent (by your own definition).
Ah! An ad hominem! A clear indicator that one does not have a strong argument.
I'll say it again:
If you cannot do your job, whether due to lack of knowledge, skills, or anything else, you are incompetent by definition. Note that this does not preclude someone who is incompetent at some job later becoming competent.at said job.
Here, this is the definition of competent from a dictionary:
adjective
having the necessary ability, knowledge, or skill to do something successfully.
Join the SDF Public Access UNIX System today!
(Score: 1) by RandomFactor on Saturday June 15 2019, @08:20PM
Better than the average dept.
В «Правде» нет известий, в «Известиях» нет правды
(Score: 1, Insightful) by Anonymous Coward on Saturday June 15 2019, @08:49PM
You know just like fighting pollution. Only when it's on the balance sheet and a large enough chunk of their business will they pay any attention to it.
And this problem is like spam, it may be profitable for the peddler but it will cost a MUCH larger amount to the society. So it really, really needs to go away and preferably fairly quickly.
(Score: -1, Redundant) by Anonymous Coward on Saturday June 15 2019, @11:30PM
If you can't inject SQL, them just blow it up with a suicide vest. Allah akhbar!