Slashdot Mirror
SQL Injection Attacks Increasing
An anonymous reader writes "Help Net Security has a story that covers the dramatic increase in the number of hacker attacks attempted against its banking, credit union and utility clients in the past three months using SQL Injection." Article follows up on press release with a little more information. Not a lot here shockingly surprising, but it's worth mentioning that SQL injection is a real pain for web developers. You have to be very careful about checking user input.
Sudden traffic surge from certain news sites can be a pain.
Simply forcing request variables to the correct type and escaping all strings is pretty much the only thing you need to do.
Most languages provide the functionality to do that (in php: intval() for all integer request vars, and _escape_string() for string data.).
It's just a small amouth of work, yet a lot of people are way to lazy.
Perhaps all programmers working on professional database systems should have to get a professional qualification to show that they can write secure code. I wouldn't say the same should be manditory for things like usability or stability (except for special sensitive areas), but being able to write code that actually allows serious danger without qualification is pretty weird. Builders need qualifications, electricians do, gas installers do, ...
The only people who consider it a pain in the ass are people who are (a) lazy, (b) not adequately security-conscious, (c) programming without a framework that provides good tools to do this. The reason we have so many SQL injections is because we have legions of web programmers who were never taught how to write code in a hostile environment. Web programming is never presented in that light; it's always, "here's a quick little script that fetches twenty records from a database and displays them." Security is far too often a footnote or an appendix that beginning programmers never get to. Building apps for the web is not like doing your Data Structures I homework. You need a different mindset. It's a lot more like designing locks--for prisons full of inmates eager to get out.
People are never as simple as their stereotypes. This applies equally to Christians, Muslims, and Emacs-lovers.
How can it be that hard for web developers to check data before it is submitted? I wouldn't imagine trusting the data that an anonymous user can enter into my website.. so maybe I'm just trained to check data. Of course, I'm also glad I use MySQL with PHP where a simple mysql_real_escape_string can prevent any popular SQL Injection attempt.
Checking input for escape attempts is error-prone. Passing in parameters as bind variables *isn't* error-prone (with regard to blocking SQL injection attacks); makes string quoting completely moot; and can result in a massive performance increase (particularly against Oracle) to boot.
I continue to be in disbelief that anyone doing professional database work can *not* follow this widely accepted best practice and continue to be employed.
If your webapp is Java based, use PreparedStatements. Never use Statements. PreparedStatements are immune to SQL Injection based attacks since the variable replacements are never interpreted. PreparedStatements are also much, much faster.
Just don't build your query on the fly.
Bind ALL parameters to placeholders in a prebuilt query. Binding is an instant kill for any SQL injection attack. It is also much more effecient on many databases.
There is nothing so silly as other peoples traditions, and nothing so sacred as our own.
Since when is it the job of the language to protect you from SQL injection? I think you're confusing the language of PHP with the standard libraries it ships with, mysql_*() and co. It's worth noting that PHP *does* support prepared statement's using the 'new' object oriented mysqi interface much like the Perl DBI. This handles the casting of types and escaping of strings for you.
You have to be very careful ...
This phrase is a common tipoff to one of the main problems.
The computer doesn't give a damn how careful you are. If you spend hours carefully crafting a chunk of code that, through your ignorance, has a big security hole, all your care hasn't helped a bit. You have merely produced bad code.
OTOH, someone with good knowledge of the subject might toss off a 30-second routine that, due to their understanding, is highly secure.
Carefulness has little to do with doing a good job. Carefully doing it wrong is merely doing it wrong, no matter how careful you are. And doing it right is doing it right, even if you hardly gave it a thought.
What we need here isn't useless exhortations to "be careful". What we need is education about how code gets into trouble, and training in writing code that doesn't have problems.
Yeah, I routinely write code that checks input. But if there's some hidden gotcha that I don't know about (typically in some library routine that's not visible to me), I'm quite aware that my careful checking might do little good.
Those who do study history are doomed to stand helplessly by while everyone else repeats it.
First rule of writing CGI: never trust the data! I work in Perl, and when an app is exposed to the outside world, I have to assume someone is going to try and get in through some hole if they can (or worse, will do something stupid that would have a negative affect oon my systems).
It starts with the web page -- validate input data. I know, I know, anyone can copy your page and rip out the JavaScript validation, but it doesn't hurt to put up a first line of defense. Next, before you actually use the data from the form for anythig validate it separately. In Perl, I have taint mode enabled by default for external apps and I treat all the data I receive as if it were dog crap. I massage it with regexes to make sure it is what it's supposed to be, and then pass it on to be processed. I find the best way to put up a wall is to have the form parameters sent to a validation script, then have the validation script call the script which would run the actual query, throwing back an error message to the user (and sending me a message in the process) if something's not right.
Data validation is really not that hard, especially if you know exactly what the inout is supposed to be. It gets iffier if the user can put in pretty much anything -- then you have to be a little more paranoid.
GetOuttaMySpace - The Anti-Social Network
- Stored Procedures
- Parameterized Queries
- Learn the SQL-92 Specification (so that you're familar with the language beyond just SELECT, INSERT, UPDATE, and DELETE. There are all kinds of things out there to help you get rid of that dynamic code, like COALESCE, and CASE WHEN, etc.)
Here's the SQL-92 Specification (pops in a new window)Except, the web is international. So "traditional" alphanumerics are not good enough. Or are you telling me that René should spell his name Rene? (in French, the two are pronounced completely differently - Ren (Rene) and Renay (René)) Or how about non-alphabetic languages like Chinese?
Many people use non-alphanumerics in their email. I, for example use underscore.
With the gradual movement of the web to non-latin URLs, too, the need for the acceptance of all printable Unicode in webforms has never been greater.
And as has often been pointed out, you can reduce the risk of your passwords being susceptible to dictionary attacks by using wierd (or perhaps unprintable) Unicode characters. Web & DB devs should do well to note that - I dislike sites immensely that restrict me to alphanumeric passwords - I'd like to use whatever alphabet I choose, to make my password more secure.
I'm not saying that input validation is a bad idea. It just needs to accept and validate input in any appropriate language - which for things like "Name" could be anything, even if the user is an anglophone. Some fields, like DOB, or numeric fields are easily validated - others like "Name" would be better cross-checked against a list of banned inputs, and escaped (or use parameters).
Please don't do this, it's bloody annoying when half your input gets chucked away because you used a special character. I really don't see why that function ever existed, it's a total fuckup and completely unnecessary when things like htmlspecialchars() exist. Encode your user-supplied data properly, don't simply chuck bits of it away.
Bogtha Bogtha Bogtha
The last time I did a SQL injection, I hallucinated that everything around me was displayed in an orderly array.
He who knows best knows how little he knows. - Thomas Jefferson
are there web application frameworks which don't support parameterized SQL statements?
that would be PHP.
Quit spreading FUD. PHP supports parameterized SQL just as well as any other language I've worked with. See, for example this doc page (search for "Example 2"). Even for databases whose native C APIs don't support the feature (i.e. MySQL), the database abstraction layer PEAR::DB that is distributed with PHP provides emulation.
You get what you pay for. A lot of people already suggested easy solutions to the problem that are just as easy to implement and that would immediately make the problem disappear. So why is it not done?
Simple: The people who write those insecure databases don't even know that those functions and features exist. Some ages ago, they learned a bit about SQL, maybe did a course about it (so they have a sheet of paper saying "Look, I can do it!") and that's it.
HR managers tend to go by papers, and by price. Now, who do you think is cheaper to hire? A person with a well rounded education concerning computers, programs and the fallacies, pitfalls and security issues around them, or someone who learned his SQL statements by heart and has no clue what exactly is going down inside the server?
Sure, both of them will create code that does what the specs say. As long as you only enter data according to spec (which is, interestingly enough, ALL that is checked, even under the SOA). The true quality of code is revealed as soon as you pit something unexpected and malicious against it.
We used to have a Bill of Rights. Now, with the rights gone, all we have left is the bill.
In fact, Perl's DBI is not only fast, but when used properly (variable substitutions, binding variables, etc) it works extremely well. Also the fact that everytime you change your data source (CSV, XLS, MySQL, SQLite, MSSQL Server, Oracle, PostGres, etc) all your functions don't change. You can always count on:
If you're not doing some kind of column binding or type-casting on your form-derived query arguments, you are always leaving yourself open to sql injection.