What is Digg?Sponsored by Dragon Age: Origins
Join the Dragon Age: Origins development team on Facebook view!
facebook.com/DragonAgeOrigins - EA presents BioWare's new dark fantasy epic Dragon Age: Origins. '9/10' from Game Informer.
51 Comments
- ArchieAndrews, on 10/12/2007, -0/+14Finally, an article on injection attacks with some meat. For a problem that is so potentially destructive to so many sites, past attempts I have read on this topic have been less than comprehensive. Well done.
- lord2800, on 10/12/2007, -1/+11The problem with SQL injection is that the entirety of it lies with the developer. It is trivial to prevent SQL injection, but so few developers do it, and for no apparent reason. I make it a point to validate, revalidate, and escape anything I put into a query or any other part of a program that expects specific inputs, just out of fear that I might have missed something. Every platform that supports SQL(with the exception of .NET, for some unknown reason) supports some form of SQL escaping mechanism. PHP has it almost built into the language, in such a way where escaping every piece of data you could need is as simple as 3, maybe 5 lines of code. Hell, you can even abstract that away into some class and/or function and never have to worry about SQL injection again. I'm absolutely appalled by the lack of processing and the frequent bad habits that most introductory examples into most programming languages show. The answer should always be "do it right, or don't do it at all", instead of this mentality that the program comes first, and safety second.
Meh, enough ranting for now. - rishubhav, on 10/12/2007, -3/+13Or if you want to do one
- ShaolinTiger, on 10/12/2007, -0/+6Ah quite an old article, but this is one of the best references, there are also a couple of good white papers, if you are interested I also recommend checking out:
http://www.spidynamics.com/whitepapers/WhitepaperSQLInjection.pdf
http://www.ngssoftware.com/papers/advanced_sql_injection.pdf - inactive, on 10/12/2007, -1/+7If you want to see one in action, just install PHPNuke and wait...
- dongiaconia, on 10/12/2007, -0/+5You may also want to check out materials related to the Microsoft .NET security exam.
http://www.microsoft.com/learning/exams/70-340.asp
Not only does it talk about SQL injection, it also explains tons of other things like luring attacks, elevated privledges, man-in-the-middle attacks... etc. - Bogtha, on 10/12/2007, -0/+5LIMIT 1 is no protection at all, since injection attacks can simply declare the rest of the query to be a comment with --. The article actually mentions this if you pay attention.
There's simply no point even considering LIMIT 1 as a security measure - writing code without these gaping holes is *easy* if you actually know you need to do it. So if you are writing secure code, then LIMIT 1 as a security measure is utterly pointless, and if you are writing insecure code, LIMIT 1 won't do any good and fixing your code the right way is easy.
This problem isn't widespread because it's hard to write secure code, this problem is widespread because complete newbies are jumping in without knowing what they are doing, and going on to write software or tutorials without ever learning how to do it properly. - Xoligy, on 10/12/2007, -0/+4It happens because you choose to trust a 12 year old developer because they charge peanuts. Do research before buying services. Cheaper isn't better.
- bogomill, on 10/12/2007, -0/+3MSSQL xp_cmdshell: Who in their right mind would want to have this enabled for non-administrator accounts on a database accessible over the Internet? And also why did the MSSQL team find it necessary to include a stored procedure command that allowed for execution of arbitrary programs/commands?
I did a quick search on xp_cmdshell and what I found were ridiculous applications of this command that could have very well been replaced by something safer. - GlassCasket, on 10/12/2007, -0/+3Please don't consider this as spam.
But try going to www.hackthissite.org , that site is to practice stuff like SQL injections. - Namco, on 10/12/2007, -0/+3Outstanding read. Really sheds some light on how our website got hacked last month :)
- EternalNY1, on 10/12/2007, -1/+4This is easily mitigated in a high level language like C# by either using parameterized queries, or doing all your data access through stored procedures. If you are doing ANY string concatenation in code for SQL queries in .Net, you are doing it the wrong way.
- bouche, on 10/12/2007, -0/+3this also pointed out why I've gotten random password reset/requests in the past for some websites.
- Takshaka, on 10/12/2007, -0/+3Then you can't add Mr O'Brien of Rock 'n' Roll Collectibles.
- trutwin, on 10/12/2007, -0/+3Heh - unless your "database" doesn't do stored procs. :)
- astuteObserver, on 10/12/2007, -0/+3You hurt his feelings. Look here: http://www.dslreports.com/forum/remark,16252796
- Bogtha, on 10/12/2007, -0/+2GET and POST are both capable of sending apostrophes to the server. There is no security advantage in either case regarding SQL injection attacks.
- foohookups311, on 10/12/2007, -0/+2excellent read. i know the basics of sql because i took a course in college on it, but i never knew how sql injection worked. this is probably how my myspace account got hacked.
- Philoushka, on 10/12/2007, -0/+2Developers: Parameters, Parameters, Parameters!
I
LOVE
THIS
DB ENGINE! Whoooo!
Honestly, if your DBA allows you to execute pass-thru SQL statements, they need to find a new *hobby*. All Stored Procs, All The Time. - GlassCasket, on 10/12/2007, -1/+3I don't think it matters at all. As long as you are able to send information to the PHP script, ***** can easily hit the fan.
- adolfojp, on 10/12/2007, -0/+2Where is the fun in that?
Don't you like the challenge of having 3 developers executing 3 different SQL routines to do the same data manipulation techniques on a database when only 1 set of rules applies?
Having developer #1 not documenting his job is a challenge.
Having developer #2 forgetting to log the transactions is interesting.
Having developer #3 leaving countless orphans in your database because he couldn't understand the undocumented code of #1 and #2 is as glorious as the first morning of spring :-) - OsiVert, on 10/12/2007, -3/+5For someone more knowledgeable than me, would this only work from a form that uses GET? Would a form that uses POST not work as well since apostraphes and other syntax doesn't translate very well from the url to the page? This article is pretty informative, but doesn't show what type of form is vulnerable (unless I missed it).
- Piper7865, on 10/12/2007, -2/+3This looks like a great article I'm digging it for further perusal when I have more time to read it
- mailbox125, on 10/12/2007, -1/+2how is this news? if you do not validate user input bad things will happen. the example in this article covers the most basic form of sql injection there is. it is often the example people use just so people will understand the concept. quite frankly any dynamic sql that does not check for single quotes is amateur hour crap. there is nothing discussed here that has not been known and covered for years.
- bouche, on 10/12/2007, -0/+1I shared this at work and it's likely going to be a HUGE learning experience for everyone. I've not seen an article show me how to really test an app I'm working on like this, nor try and break someone else's.
- Xoligy, on 10/12/2007, -1/+2Why are you digging him down? He asked a valid question.
- madeingermany, on 10/12/2007, -1/+2OK, I really need to do more input validation for that website, I once created ...
and a backup ;) - JayBachatero, on 10/12/2007, -0/+1This was a great read. I will have to bookmark this page. Since I am part of the Simple Machines Forum team I get to see some of the discussions the Developers have and have learned a lot from it in regards to this.
I have been a victim of Googling "PHP login system tutorial" and once I look at it all that comes to my head is Security issues. They don't touch on addslashes() and so on. - lateralus, on 10/12/2007, -0/+1Great article. I cant wait to hack our soon to be production server in the test lab tomorrow. Employee of the Month, here I come!
- adolfojp, on 10/12/2007, -0/+1I agree. Adding parameters to a command is a much better choice than concatenating SQL strings. Using a data access layer is even better.
If you want a free and easy generator for your data access layer you can try something like this: www.mygenerationsoftware.com :-) - pHr34kY, on 10/12/2007, -0/+1I didn't know about that backslash escape trick. However I always replace single quotes with double quotes and will always convert numerical values to an integer/long then back to a string.
Damn.. I got some code to patch up! - GlassCasket, on 10/12/2007, -0/+1Great article, very detailed!
@Ghoul: You are right that it's easy to protect ourselves from *most* SQL injection attacks. But I think a big poroblem is that beginner make websites without having any knowlege whatsoever of PHP and SQL. So they Google how to make a dynamic site using PHP & MySQL. And I've noticed that most of these tutorial don't cover the real_escape_string() method. They simply show you how to make a simply HTML form, how so store your data using an INSERT statement, and how to display it. BAM, you've got your *unsecure* website. - jzimmerman, on 10/12/2007, -0/+1For those that are interested the current issue of PHP Architect also does a pretty good run down one SQL Injections and solutions specific to PHP
- Bogtha, on 10/12/2007, -2/+3It seems like every other crappy tutorial submitted to Digg is vulnerable to these kinds of attacks. This is a widespread problem.
- irgordon, on 10/12/2007, -1/+1wouldn't it just make sense to remove the semi-colon character? I mean wouldn't that limit a majority of what this article is talking about?
- dtfinch, on 10/12/2007, -1/+1Nice to see the word getting out. SQL injection is a big problem.
- Xoligy, on 10/12/2007, -1/+1If a site is not protected against SQL injections, it deserves to be hacked. At least then the developer will learn not to make the same mistake again.
- Ghoul, on 10/12/2007, -0/+0On the topic of general security, these are a few security related rules I always adhear to:
- Passwords should be stored as salted hashes (hashes to prevent blatant stealing, salted to prevent rainbow tables).
- Input from a client SHOULD be validated client side and MUST ALWAYS BE validated server side, with validation being both sanitation (escaping what needs to be escaped if the data is destined to a data server, etc.) and formatted correctly (example: e-mail addresses are name@domain.tld, first names don't have percent signs, etc.).
- Client privileges should be validated when giving the option to perform actions and when actually doing the actions (for example, displaying the "edit" link next to a comment requires the user owns the comment, but the act of actually performing the UPDATE query also requires the same permissions)
- Always assume that something is false rather than true, meaning, for example, that "all accounts are admins unless otherwise noted" is incorrect, and "all accounts are without privileges unless otherwise noted" is good.
- Always initialize everything.
- Store as little data on the client as possible, preferably only a session name.
For PHP specifically, I recommend anyone who wants to learn the language read and understand the security section of the PHP manual:
http://php.net/manual/en/security.php
Read it, then read it 5 more times. Read all the comments on all the pages! - phlogiston99, on 10/12/2007, -0/+0You can also add "error handling must be designed WITH the application, not as an afterthought", recover gracefully from expected an unexpected errors. http500 is a dead givaway that the door is wide open for more trouble.
If you can too, use a modern development tool and language that has good support for error handling (yes, I'm thinking of structured exception handling) Scripting is what is sounds like. Script. - Bogtha, on 10/12/2007, -1/+1@Xoligy,
Burying a comment isn't punishment, it's intended to hide the comments that don't contribute to the discussion. He asked a question based on an incorrect assumption, so it doesn't contribute to the discussion in any way. It doesn't mean anybody thinks he's a bad person, just that the comment is useless. - therernospoons, on 10/12/2007, -1/+1Thanks, nice find.
- littlebluedevil, on 10/12/2007, -2/+2This happens because the software developers aren't liable for their code. If you sued someone's ass off because of a security glitch then the thought of a multimillion dollar law suit would put security up in the fore front.
- adolfojp, on 10/12/2007, -2/+2-1
Where is the dumbass tag? :-P - mdwvt, on 10/12/2007, -2/+1well that immediately made me start thinking about the application I program for every day!
myParanoia ++;
I know what I'm doing tomorrow! :-D Thanks for posting an awesome article. I really like the examples... very straight forward. - Ghoul, on 10/12/2007, -2/+1It's sad that SQL injection is such a big security issue when it's a simple solution in most cases. For example, in a PHP environment that makes use of the mysqli class, I just create a simple extension of the class:
// slightly extended mysqli class, mysqle
class mysqle extends mysqli
{ // make our query args safe and standard
function make_safe($query, $args)
{
if (!$args || !is_array($args)) return $query;
for ($i = 0; $i real_escape_string($args[$i])."'";
}
// copy our args over to the string
$query = vsprintf($query, $args);
return $query;
}
}
The make_safe function takes a string and array of replacements, and just loops through and runs real_escape_string on all applicable parameters, then runs vsprintf and returns the formatted string.
This is how it would be used in code, assuming the $mysqlObj variable is of the class above:
$someString = "I'm a string!";
$querySQL = "SELECT * FROM table WHERE field = %s";
$queryParams = array($someString);
$queryFinal = $mysqlObj->make_safe($querySQL, $queryParams);
$queryResult = $mysqlObj->query($queryFinal);
This is just a simple example; any knowledgeable programmer would be able to design a much more robust system.
EDIT: Note the resulting SQL string would be: SELECT * FROM table WHERE field = 'I\'m a string!'
EDIT2: Digg doesn't like backslashes? (I had to escape my own backslash D:) - SnOwie, on 10/12/2007, -2/+1can't you just ban the character ' from any field?
just return false when entered at any field or at the url added with ?.
Or do I miss something here?
A larger problem is when u can upload a script, and then make the page execute it.
All control etc :) - bouche, on 10/12/2007, -2/+1shouldn't that be a new submission then? Sounds like a good service, if that's what it is.
- Ghoul, on 10/12/2007, -2/+1Aw crap, and there Digg goes filtering out HTML... That should be:
for ($i = 0; $i < count($args); $i++)
{ // if it's numeric we don't have to worry
// otherwise escape the string
if (!is_numeric($args[$i]))
$args[$i] = "'".$this->real_escape_string($args[$i])."'";
} - sbovisjb1, on 10/12/2007, -3/+1Terrible Article... IF you want to learn hacking goto any good site... not some stupid security wannabe.
1: www.milw0rm.com
2: www.hackthissite.com
3: www.criticalsecurity.net
4: USE GOOGLE - holmes101, on 10/12/2007, -4/+1I was going to submit this a while ago, but forgot to...
Good article.
Browsing Digg on your phone just got easier with our enhancements to the 
© Digg Inc. 2009
— Content created and posted by Digg users is