Exploiting 'INSERT INTO' SQL Injections Ninja Style | Hacker News Reader

Exploiting 'INSERT INTO' SQL Injections Ninja Style

mathyvanhoef.com

46 pointsgipsies21 comments

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.

It is actually worse: it may show that not many people are aware of Owasp[1] top 10 list of security risks, since Injection is the first one.

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

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.

Glad you liked the post =)

?? 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.

What am I doing wrong?

This is not exploiting INSERT INTO; it's exploiting horrible developers.

Rule #1: Never trust the user.

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.

Why do you believe only black hats should be able to understand various sql injections tricks like this?

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.

In 2011, one should never need to resort to building SQL with string concatenation in web applications.

If you are on Python, consider using SQLAlchemy or OurSQL. If you are on PHP, consider using Doctrine, Propel, or PDO.

I thought they were phasing these functions out (mysql)? I've been using PDO for the last couple of years.

The problem is not which driver you use. It's whether you concatenate strings to form sql queries and then execute them against your database.

I think the point he was trying to make, was that using prepared statements with PDO or mysqli would prevent this

exactly.

is it works also with prepared statements ? or only with really lame code ?

It's injection attacks. Using prepared statements will prevent that.

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)));

Escape your strings. Ninja problem solved.

Reply on news.ycombinator.com