Is SQL injection not possible if only one result is displayed on the page?

Question

I'm doing a SQL Injection project for my security module in college, and I'm trying to learn how it works.

I can see how it works when a script doesn't filter input, and then loops over a DB result set, displaying the data on screen. But as far as I can tell, the following code is NOT susceptible to SQL injection, as it is only expecting to display a single set of values on the screen:

<?php
mysql_connect("localhost", "root", "");
mysql_select_db("testdb");

$result = mysql_query("SELECT id, name, description FROM test_table WHERE id = ".$_GET['id']);

list($id, $name, $description) = mysql_fetch_row($result);

echo "ID: $id \n";
echo "Name: $name \n";
echo "Description: $description \n";

?>

If I set the value of id to:

1 OR 1 = 1 UNION SELECT id, username, password FROM users LIMIT 1, 1 --

The values from the UNION part of the query are not displayed, unless I run the mysql_fetch_row($result) statement twice, like so:

<?php
$result = mysql_query("SELECT id, name, description FROM test_table WHERE id = ".$_GET['id']);

list($id, $name, $description) = mysql_fetch_row($result);

echo "ID: $id \n";
echo "Name: $name \n";
echo "Description: $description \n";

list($id, $name, $description) = mysql_fetch_row($result);

echo "ID: $id \n";
echo "Name: $name \n";
echo "Description: $description \n";
?>

Only then are the values from the UNION part of the statement displayed, (i.e. username, password).

If anyone knows a thing or two about this, can you confirm that I am correct in saying that the above code is NOT susceptible to SQL injection, as it is only expecting to display a single set of values on the screen.

Please correct me if I'm wrong.

Thanks for your help.

Have you tried SQL injection to truncate the table rather than simply trying to return multiple records — Mark Baker, Jun 19 '14 at 15:09
Why downvote this question? It would do a huge Internet good to upvote it and give it an awesome answer. — Doug T., Jun 19 '14 at 15:09
SQL injection is not only used to display more results than intended in a select. I could inject `3; DROP TABLE test_table; --` and delete everything... — Bun, Jun 19 '14 at 15:09
Have you even checked whether the result being returned can be an id other than that you've selected — Mark Baker, Jun 19 '14 at 15:11
Hey Mark - I cant truncate or drop the table becuase aparently MySQL doesnt support stacked queries. i.e. I can't simlpy put a semi-colon followed by another query / statement. If this is wide-open to SQL injection, as John said, how is it done? I can't see it. — SimBot, Jun 19 '14 at 15:15
On a side note, when you have a SQL injection problem, you also have a string handling problem. Without properly escaping strings, you won't be able to handle strings that contain quotation marks. If you fix that (with either mysql_real_escape_string or something like PDO), you'll fix both problems. — JW., Jun 19 '14 at 15:27
Don't bug yourself with different conditions, "what if this", "what if that". The answer is always - YES. — Your Common Sense, Jun 19 '14 at 15:27
learn about `GROUP_CONCAT` it make things much better for attacker. — Minhaz, Jul 17 '14 at 10:56

score 5 · Answer 1 · edited Jun 20 '20 at 09:12

Most people don't understand what injection is.

They confuse injection exploit with injection itself.

Speaking of injections, as long as you're allowing unwanted code in your query - injection IS possible, even if all you injected is "Hello Kitty" (outside intended boundaries).

So, the answer to all the millions questions newbie programmers can devise, "what if I have this", "what if I don't have that" is all the same - YES, injection is possible.

Speaking of exploits, there are much more techniques than you can imagine, some of them quite sophisticated. So, to answer the question "is this injection exploitable?" the answer is most likely YES too.

Therefore, don't bug yourself with question "is it possible?". This question makes not a slightest sense. All you need is to write injection-proof code. This is why this question is bad. Because you should ask

How to write injection-proof code?

instead.

score 3 · Answer 2 · answered Jun 19 '14 at 15:14

3

SQL injection is the act of injecting unwanted commands into an SQL query. It does not have anything to do with what comes after, it is the act of being able to send commands that were not intended to the database. This can have consequences beyond displaying data on the front end. With a little bit of probing and guesstimating, it may be possible to discover the structure of your database. An attacker may then be able to cause the database to execute a query akin to:

INSERT INTO admins (username, password) VALUES ('foo', 'bar')

Et voila, the attacker has just created a root admin account with known password for himself and will come walking in through the front door.

Here's a nice story that walks you through it: http://www.unixwiz.net/techtips/sql-injection.html

Whether your particular database API would allow such queries to slip through or not is another point; but SQL injection is certainly not only about output.

answered Jun 19 '14 at 15:14

deceze

510,633
85
743
889

From what I've read, MySQL doesn't support stacked queries, meaning you can't add in another insert statement, you can only change the existing query by adding a union clause etc. – SimBot Jun 19 '14 at 15:17
@user And maybe that's already enough to get somewhere. Read the linked article, it illustrates various angles how an application that allows an attacker even a little bit of space can be attacked in the most unexpected ways. Rarely it's just a specific SQL injection flaw in itself, often the SQL injection is the first foothold that allows an attacker to wedge himself in elsewhere. – deceze Jun 19 '14 at 15:19
Depending on the situation you can perform stacked queries. For example using php there is the mysqli_multi_query function - http://php.net/manual/en/mysqli.multi-query.php – Kickstart Jul 17 '14 at 10:40

score 1 · Accepted Answer · answered Jun 19 '14 at 15:20

1

An attacker could set id to this:

1 AND FALSE UNION SELECT id, username, password FROM users WHERE username="carl" LIMIT 1, 1 --

...and would get carl's relevant information. He could repeat the process for all usernames (or ids) and would still get a lot of information he shouldn't get. It would just take longer.

Therefore you can not generally say that querying only one row makes SQL injection "less dangerous".

answered Jun 19 '14 at 15:20

Hauke P.

2,695
1
20
43

I think I see your logic, the "AND 1 = 0" at the beginning makes the first part of the statement never return anything, so only the values after the UNION SELECT part of the query are returned? Is that right? – SimBot Jun 19 '14 at 15:22
Exactly. To make it a bit clearer I just replaced the `1 = 0` with `FALSE`. – Hauke P. Jun 19 '14 at 15:23
Gotcha! Thanks dude! Glad I asked before making a fool of myself :) – SimBot Jun 19 '14 at 15:25

score 1 · Answer 4 · edited May 23 '17 at 12:07

SQL injection is the mere fact that the intention of an SQL command can be modified by externally-influenced input:

The software constructs all or part of an SQL command using externally-influenced input from an upstream component, […] that could modify the intended SQL command when it is sent to a downstream component.

And this is definitely the case in your example.

Note that the lack of a trivial exploitation doesn’t make it any less a flaw. You may not be able to exploit it today, but maybe someone other is able, whether it’s today or maybe some times in the future.

Frankly, a lack of knowledge about SQL injection and SQL in general is why many fail to identify the vulnerability to SQL injections or their possible impact.

And you gave a perfect example for that lack of knowledge: … OR 1 = 1 is true for each row, so unless your table is empty the result set of the injected UNION SELECT will never show up in your results.

Also note that there are exploitation techniques other than UNION SELECT.

score 0 · Answer 5 · answered Jul 17 '14 at 10:48

As a minor follow up to the earlier very good points to demonstrate why a single record returned is not in any way safe you can use the GROUP_CONCAT aggregate function.

For example an id of:-

1 AND FALSE UNION SELECT 1, 'fred', GROUP_CONCAT(CONCAT_WS('-', id, username, password) ORDER BY id) FROM users --

This uses the code from Hauke P. to ensure the first part of the query doesn't return anything, but then UNIONs on a query to get all the user ids, names and passwords. No need to guess as an existing id or name.

This will be fall down slightly due to the default max length of the field returned by GROUP_CONCAT, but by the time someone hits that they have probably already got the login details of 30 users, and they can easily add WHERE id > xxx to get the next batch of 30 or so.

Is SQL injection not possible if only one result is displayed on the page?

5 Answers5

How to write injection-proof code?