Is SQL injection a risk today?

Question

I've been reading about SQL injection attacks and how to avoid them, although I can never seem to make the "awful" examples given work, e.g. this post http://stackoverflow.com/questions/332365/xkcd-sql-injection-please-explain

I created a php file and a table in the database, had a value passed through $_GET and tried to delete the table by doing bob'); drop table students; -- and it didn't work. PHP automatically escapes the \' and the query has an error, no harm done. Same issue when trying to replicate login "attacks" like AND WHERE 1=1 etc.

example code:

<?php
$id = $_GET['id'];

$sql = "INSERT INTO Users (Username) VALUES ($id)";
echo $sql;
mysql_query($sql) or die(mysql_error());

And I'd pass sql.php?id=1); delete from Users; --

So is this some dated thing that used to apply in the days of PHP3 or something, and nowadays even novices are protected from things like magic quotes?

I'm using PHP5 on Ubuntu.

Answer 1

No this is still very relevant.

As are XSS and CSRF. Never underestimate the importance of proper input filtering.

Answer 2

This is very much an active risk, magic quotes tries to give you a solution but I prefer to always develop with magic quotes off. This way I have to make sure I actually escape the inputs myself. Who knows if magic quotes will be on or off on the server where the script is actually deployed.

Answer 3

Quite the contrary. Magic quotes are deprecated in PHP5 and will be completely removed in PHP6, as they brought more confusion to the programming world than they did good. Checking whether magic quotes are active, and escaping any SQL input scrupulously if necessary, is still very, very important... No reason to feel bad though, we've all been there, and my unknowing ass has been saved by magic quotes countless times :)

The PHP manual on magic quotes explains everything: http://www.php.net/magic%5Fquotes

Answer 4

No, and the less you worry about SQL Injection, the more likely you are to get hit by it.

Answer 5

Heh, you're saved in this case by having magic_quotes_gpc set to "on".

You'll be screwed soon.

Answer 6

This is still a big problem. You can't assume that magic_quotes is turned on in every PHP installation you might use.

To see if magic qotes is turned on and clear out the mess from magic quotes:

if ( get_magic_quotes_gpc() !== 0 ) { $foo = stripslashes( $foo ); }

Then cleaning your statements a little:

$foo = mysql_real_escape_string( $foo );
$sql = "select * from foo where bar='{$foo}'";

etc.

In fact, you're better off just strictly turning of magic_quotes if you have the ability to do so.

I hope that helps you.

Answer 7

That particular attack doesn't work, as mysql_query will only execute a single statement.

I can still abuse your code though, e.g. if I arranged for id to be SELECT password FROM Users WHERE Username='admin' I might have a fighting chance of being able to get your system to expose some internal information.

Basically, if you allow unfiltered input into your SQL, there will be some very creative ways of both creating data you didn't expect, and exposing data you didn't intend!

Answer 8

Parameters passed to sql queries from the web pages ofen tend to be numeric IDs. For example let's assume you have an url http://foo.com/page.php?section=34 from which the section ID is used in a query like this:

SELECT content FROM sections WHERE section_id=$section;

No quotes to escape like in your example and whatever you'll put after the number in the URL will be passed to the query... So thew risk is real.

Answer 9

The bobby tables example will not work with the mysql interface because it doesn't do multiple queries in one call. The mysqli interface is vulnerable to the multiple query attack. The mysql interface is more vulnerable to the privilege boost attack:

In your form I type account: admin password: ' or 1=1 -- so that your typical login sql: select * from users where user_name = '$admin' and password = '$password'. The or causes this to be true and let's you log in.

Answer 10

The simplest rule of thumb is to assume that all user input can be tainted. Check that data types are what you expect, variables are in the length/size ranges you were expecting, files are of the size and types you allow, etc. Other checks on non-external data can be warranted - before you call some important admin-level function, do a check - ($userlevel != ADMIN)?die():important_function();

There's always a bigger fish, or somebody who's a bigger jerk than you. Avoid assumptions about data and you've got a head start.

Answer 11

There are a lot of different ways to perform a SQL Injection and quite a lot of ways to bypass basic safety precautions.

Those attacks have been within the top 10 web application vulnerabilities (rank #2) according to OWASP.

For more information, please refer to: http://www.owasp.org/index.php/Top_10_2007-A2

Answer 12

Can't PHP do query parameters? If it can (as I'd be surprised if it didn't), that is the one solution which mitigates ALL SQL injection attacks.

Answer 13

Magic quotes don't take character encoding into account, and thus are vulnerable to attacks based on multi-byte characters.

As for it being a risk today, Google searches turn up countless vulnerable sites. An SQL Injection vulnerability was reported for Bugzilla around September 10. So, yes, sites are still at risk. Should they be? The tools are there to prevent injection, so no.

Answer 14

The largest identity-theft in history was achieved in 2007 by exploiting an SQL injection vulnerability: see "SQL injection attacks led to Heartland, Hannaford breaches" (ComputerWorld, 8/18/2009).

OWASP reported in 2007 that injection attacks (of which SQL injection is one example) continue to be one of the most common software security problems.

However, the example in the XKCD cartoon isn't necessarily the most common type of exploit. Dropping a table by executing a second SQL statement in one request probably wouldn't gain the attacker much in the way of valuable data, it would just be vandalism.

Also, some query interfaces disallow multi-query by default anyway. That is, the database client API executes only a single statement given the SQL string, regardless of semicolons. This defeats the example shown in the cartoon.

As other folks have pointed out, the more likely risk is that an SQL injection will alter the logic of SQL expressions, and apply your query to extra rows besides those you intended.

For example:

$sql = "UPDATE Users SET PASSWORD = MD5('" . $_POST["password"] . "'||salt) " . 
       "WHERE user_id = " . $_POST["userid"];

What happens when I send a request with parameter userid set to the string 123 OR userid=456? I would reset my own password (userid 123) as well as the password of userid 456. Even hashing the password with a per-user salt wouldn't protect against this. Now I can log into either account.

There are lots of ways SQL injection can be perpetrated.

Answer 15

As I've mentioned several times on stackoverflow before, I am a strong supporter of PDO, just stop using the old fashioned mysql, do yourself and your clients a big favor and learn PDO (it's really easy) and take advantage of prepared statements and bound parameters. Even if you do not need prepared statements performance wise, you still get the security benefits.

Also, I will recommend crashing your entire app in the clients face if magic quotes is set to on. It's just a drain on resources designed to protect the dumb and annoy the smart. (it uses more cpu than escaping manually, because it encodes everything, even when you don't need it)

Answer 16

Oh my.. SQL Injection is not a risk, it is a gaping security hole. It mainly exists in php because the API makes you want to interpolate any old data into your SQL queries.

When I see a site written in PHP or ASP, I can just smell the SQL injection vectors that they reek of. People try to secure their PHP apps with mysql_real_escape_string() and intval() and do similarly in other languages. This is a mistake. It's like coding in C instead of Java or Python, where in the former, you make one mistake and you're dead, but in the latter, only semantic flaws can exist.

I strongly urge people to use either mysqli with prepared statements, or anything else that is parameterized, substituting text into code and then interpreting it is just bad practice in the first place IMHO.

On another note, PHP's magic quotes is just silly, and thankfully, deprecated. It can only cause more harm than good. If you rely on magic quotes, it means your app will be owned when magic quotes is disabled. Similarly, it may break other apps that don't expect escaped strings in inputs.

Answer 17

Not today yet, but it's only 20:34 UTC

Guardian jobs database attack demonstrates difficulties of database security, 06 Nov 2009

Guardian Jobs website hack may have been an SQL injection and not a 'sophisticated' attack, 27 Oct 2009

Answer 18

Whenever building up SQL from strings, SQL injection is a real danger.

I have also discovered that trying to avoid building up SQL from strings is a pointless endeavor. Sooner or later the full form of your SQL (not just things that could be parameters) must be generated at runtime.

Answer 19

I've have to develop for a server which has no way for me to disable magic_quotes! I include this on every page to undo the effects of magic quotes, so I can do proper escaping myself without \'double escaping\'. Even though I can taste vomit just from reading this, I haven't found a better solution.

if (get_magic_quotes_gpc()) {

    $process = array(&$_GET, &$_POST, &$_COOKIE, &$_REQUEST);

    while (list($key, $val) = each($process)) {

        foreach ($val as $k => $v) {

            unset($process[$key][$k]);

            if (is_array($v)) {

                $process[$key][stripslashes($k)] = $v;

                $process[] = &$process[$key][stripslashes($k)];

            } else {

                $process[$key][stripslashes($k)] = stripslashes($v);

            }

        }

    }

    unset($process);

}