This is sort of interesting, but I'm not sure how it applies to non-black hats. If you are responsible for this code just switch it all to mysqli and be done with it.
P.S. It's 2011, if you are concatenating strings IN ANY LANGUAGE to build up SQL statements and you aren't even bothering to sanitize input then:
1: You cannot call yourself a professional web developer. You can't even call yourself a competent amateur programmer, you are just playing with toys you clearly do not understand.
2: You deserve absolutely no pity when (not if) you get hacked.
If this sounds harsh, it's meant to, this is an incredibly well known problem (and has been for years) and it's incredibly easy to fix.
These techniques seem to only be useful against absolutely brain dead coding practices. As I said, if you're responsible for this code it's not useful to understand every aspect of this vulnerability, you just switch to a different implementation (preferably parameter binding using mysqli). This analysis is like learning techniques on how to stealthily steal a bike that is "locked" up with nothing more than twine. That's not really useful except in an "all knowledge is useful" sense.
Interesting, I really dont agree. I have two roles that I regularly play as a developer, one is 'creating a solution from scratch', and in that situation - as you have said, I dont really need to be aware of these kinds of possibilities, just because I can avoid the problem entirely.
Unfortunately the other role I frequently play is 'performing work on something someone else has built', and in that case I rarely have the ability to throw out everything that is there and start from scratch - which means I have to have an understanding of this kind of thing and know what kinds of changes will provide the greatest 'hit' in terms of security, while balancing those changes against the possibility of breaking existing work.
Besides, I find knowledge useful in a general sense.
It is not actually prepared statements that does that, it is parametrized statements. Prepared statements is a related by mostly orthogonal concept.
Prepared statements are about caching parsing and query planning and storing queries for future use, this gains performance and convenience but not necessarily security. You can use parametrized queries without storing them as prepared queries at the server. This is what Perl DBI, libpq (the C binding for PostgreSQL) and many others do.
In PostgreSQL you can use PREPARE and EXECUTE directly instead of through the convenience functions in your driver and be vulnerable to SQL injections. E.g.
PREPARE foo (boolean) AS SELECT $1;
EXECUTE foo (pg_terminate_backend(42)));
The fact that this article got any up votes, let alone 31, is a testament to how many non-programmers read this board. Like InclinedPlane said, Sanitizing database input is database 101. You can't even call yourself a hobbyist programmer until you understand that.
For non-technical people: If someone is building you a website, put a single backslash(and only that) in any text fields on your site. If it breaks when you submit, chances are good there is potential for SQL-injection. This is not a 100% check that will catch everything, heck it's not even a 70% check. But a poor programmer that doesn't know anything about SQL-injection will likely build code that fails this test.
More concisely: Every time you let a user input something, you're giving them a chance to try to extract or destroy something in your database. But preventing it is REALLY simple. It's astonishing that this is still a problem in 2011, it goes to show what happens when everybody outsources and nobody gives a f*ck about quality. Grandpa was a foreman, he used to say if you pay workers more they hammer in an extra nail and the building won't fall down in the next earthquake. Same goes.
You find it strange that this is upvoted on 'Hacker News'? Hackers like tinkering and understanding why things work. I understand the principles behind these flaws and am not interested in the article because I'm hoping it will teach me how to avoid SQL injection. The article is actually not about that; instead it's illustrates how a skilled attacker may specific features of MySQL to exploit flawed code in a stealthy way.
As a sysadmin, this is interesting. I help out people who run WordPress sites, for instance. While I do my best, on a practical level not all of the code can be audited or rewritten.
As an SQL coder, this is interesting as he's cleverly crafting code in a language I've spent a fair amount of time playing with, and about which I would like to know more. It's interesting to see how someone might do SQL injection in the same way you might wonder how people pick padlocks, despite having no need to pick padlocks yourself.
Exactly! We all know SQL injections can be a common mistake. And the example php code is just that: An example. My focus wasn't the php code, or how to avoid this exploit, most of us already know that. But like you said the point is the interesting usage of SQL in this particular situation.
?? I dont understand that attitude.
I call myself a professional programmer, Ive understood the importance of sanitizing my inputs for some time, and I still thoroughly enjoyed reading the article.
21 comments
[ 2.6 ms ] story [ 57.9 ms ] thread1: You cannot call yourself a professional web developer. You can't even call yourself a competent amateur programmer, you are just playing with toys you clearly do not understand.
2: You deserve absolutely no pity when (not if) you get hacked.
If this sounds harsh, it's meant to, this is an incredibly well known problem (and has been for years) and it's incredibly easy to fix.
Besides, I find knowledge useful in a general sense.
Prepared statements are about caching parsing and query planning and storing queries for future use, this gains performance and convenience but not necessarily security. You can use parametrized queries without storing them as prepared queries at the server. This is what Perl DBI, libpq (the C binding for PostgreSQL) and many others do.
In PostgreSQL you can use PREPARE and EXECUTE directly instead of through the convenience functions in your driver and be vulnerable to SQL injections. E.g.
For non-technical people: If someone is building you a website, put a single backslash(and only that) in any text fields on your site. If it breaks when you submit, chances are good there is potential for SQL-injection. This is not a 100% check that will catch everything, heck it's not even a 70% check. But a poor programmer that doesn't know anything about SQL-injection will likely build code that fails this test.
Even if you could solve all the code injection problems, what about Cross site Scripting? The list goes on...
[1] https://www.owasp.org/index.php/Top_10_2010-Main
As a sysadmin, this is interesting. I help out people who run WordPress sites, for instance. While I do my best, on a practical level not all of the code can be audited or rewritten.
As an SQL coder, this is interesting as he's cleverly crafting code in a language I've spent a fair amount of time playing with, and about which I would like to know more. It's interesting to see how someone might do SQL injection in the same way you might wonder how people pick padlocks, despite having no need to pick padlocks yourself.
Glad you liked the post =)
What am I doing wrong?
Rule #1: Never trust the user.
If you are on Python, consider using SQLAlchemy or OurSQL. If you are on PHP, consider using Doctrine, Propel, or PDO.