Order by SQL statement not working correctly

Question

TO BE CLEAR I AM DELIBERATELY TRYING TO INJECT INTO MY OWN SITE.

I am trying to inject into one of my sites by using the order by statement to try and work out how many columns are in my table however I do not get any errors when I go greater than the number of columns I have.

My code is a very simple form, which calls a php file and then runs an SQL query.

Heres the php code:

 <?php

        $host="localhost"; // Host name 
        $username="root"; // Mysql username 
        $password="root"; // Mysql password 
        $db_name="Hack"; // Database name 

        mysql_connect("$host", "$username", "$password");//or die("cannot connect"); 
        mysql_select_db("$db_name");//or die("cannot select DB");
        $id = $_GET['id'];

        echo $id . "<br />";
        $qstr = "SELECT * from users WHERE username = '$id'";
        echo "<br />$qstr<br />";
        $query = mysql_query($qstr);
        $num = mysql_numrows($query);

        $count = 0;

        while ($count<$num){
            $id = mysql_result($query,$count,"id");
            $username = mysql_result($query,$count,"username");
            echo 'ID: ' . $id . '<br> Username: ' . $username . "<br/>";
            $count++;
        }
        if($num==0){
            echo "<br /><br /><br />";
            echo mysql_error();
        }

?>

Heres the form:

<html>
<h1>
    Search
</h1>
<form method="get" action="search.php">
    <input type="text" name="id">
    <input type="submit" value="Search user">
</form>
</html>

Lastly heres the "order by" statements I am trying in the url:

http://localhost:8888/search.php?id=admin%20order%20by%204

Please ignore the "%20" those are just spaces but chromes puts them in

So my question is why does by "order by" not work?

EDIT: Here is the SQL statement echoed out:

SELECT * from users WHERE username = 'admin order by 4'

**SQL Injection is coming...** see this topic before continuing: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — Leonardo, Dec 12 '13 at 19:19
Echo out the SQL you're generating. It's not formatted in the way you think it's formatted. — andrewsi, Dec 12 '13 at 19:22
@u_mulder, that would be an index of a field in the SELECT list. — Mike Perrenoud, Dec 12 '13 at 19:22
@u_mulder the 4 should be the column number, and should throw an error because I don't have that many columns in my table. — user2157179, Dec 12 '13 at 19:22
If you want to create injection you should close single quote first. — u_mulder, Dec 12 '13 at 19:23
@andrewsi I am echoing it out `$qstr = "SELECT * from users WHERE username = '$id'"; echo "
$qstr
";` — user2157179, Dec 12 '13 at 19:23
@user2157179 ... and what does it look like? Does it work when you run it directly in the database? — andrewsi, Dec 12 '13 at 19:24
Currently you query looks like `SELECT * from users WHERE username = 'admin order by 4'`. — u_mulder, Dec 12 '13 at 19:25
@user2157179 - and that is badly formatted SQL, so your query is failing. — andrewsi, Dec 12 '13 at 19:29
You are doing injection on the wrong way. To your query work as you want you have to inject the id to your query turns out: `SELECT * from users WHERE username = 'admin' order by 4` — Jorge Campos, Dec 12 '13 at 19:29
and should be `SELECT * from users WHERE username = 'admin' order by 4` See the difference? — u_mulder, Dec 12 '13 at 19:29
Try to put this on the form: `admin' and '1'='1 order by 4`. And @u_mulder 4 means the fourth field on the select statement, like: `select a, b, c, d ,e ...` the 4 on the order by means order by d — Jorge Campos, Dec 12 '13 at 19:31
This is just full of bad ideas. In addition to the SQL injection, which I guess you say is the point, [ORDER BY 4 is bad](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx). — Aaron Bertrand, Dec 12 '13 at 19:32

score 2 · Answer 1 · answered Dec 12 '13 at 19:32

2

You need to change

SELECT * from users WHERE username = 'admin order by 4'

to

SELECT * from users WHERE username = 'admin' order by 4

answered Dec 12 '13 at 19:32

Daniel B

797
4
13

Have tried it but I get the following error `You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1` and also my query string comes out different to yours, it comes out like this `SELECT * from users WHERE username = 'admin' order by 4'` – user2157179 Dec 12 '13 at 19:36
Then you need to figure out how to get rid of that last quote at the end of `SELECT * from users WHERE username = 'admin' order by 4'` right after the `4` @user2157179 – Funk Forty Niner Dec 12 '13 at 19:39
@Fred-ii- Yup I agree, but getting rid of it in my query does not let the form run normally. Any way to by pass this? – user2157179 Dec 12 '13 at 19:42
Is your column actually named `4`? I heard say that numerically-named columns can cause havoc. @user2157179 – Funk Forty Niner Dec 12 '13 at 19:44
@Fred-ii- No, my column names are ID, Username and Password – user2157179 Dec 12 '13 at 19:45
Then shouldn't you be using `ORDER BY col_name` ? I.e.: `ORDER BY ID` @user2157179 why the `4`, I don't follow/quite understand. – Funk Forty Niner Dec 12 '13 at 19:46
`ORDER BY 4` is perfectly legal, it will sort the result using the fourth column in the select list. – Daniel B Dec 12 '13 at 19:48
@Fred-ii- it is as Daniel said, I am trying to order the 4th column but as there is no 4th column I will get an error. – user2157179 Dec 12 '13 at 19:50
There's obviously something I'm not grasping here then. – Funk Forty Niner Dec 12 '13 at 19:52
@Fred-ii- [ORDER BY](http://www.breakthesecurity.com/2010/12/hacking-website-using-sql-injection.html) Have a look at this and look at the order by section hopefully that clears it up – user2157179 Dec 12 '13 at 19:55
Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or **column positions**. Column positions are integers and begin with 1 – Daniel B Dec 12 '13 at 19:57
Have you tried `WHERE username = '" . $id . "' ";` ? @user2157179 – Funk Forty Niner Dec 12 '13 at 20:11
@Fred-ii- Same error, there is a closing apostrophe which is causing a syntax error – user2157179 Dec 12 '13 at 20:15
Another possible reason may be because of `magic quotes` being enabled/disabled. Check this, see if this helps http://stackoverflow.com/a/1201003/1415724 @user2157179 – Funk Forty Niner Dec 12 '13 at 20:16
Maybe even use `stripslashes()` in conjunction with your `GET` @user2157179 – Funk Forty Niner Dec 12 '13 at 20:18
@Fred-ii- Yup thats off, unless theres another built in php function or a chrome function which does this, however it is not a magic quotes error its an error with my statement but I have no idea how to manipulate by string so the final apostrophe won't come up. – user2157179 Dec 12 '13 at 20:20
I don't know if this will make a difference or not, but using double quotes instead of singles `$id = $_GET["id"];` @user2157179 am a bit baffled to tell you the truth, in trying to figure out where that extra quote is coming from. Have you tried a different browser, to see if it makes a difference also? – Funk Forty Niner Dec 12 '13 at 20:30

score 1 · Accepted Answer · answered Dec 12 '13 at 19:43

As you are trying to inject your query you have to think: How I would complete my query to change it? So as you have this:

$qstr = "SELECT * from users WHERE username = '$id'"

This is literal string, so you put on your ID input field the value admin order by 4 then your query will be evaluated as

SELECT * from users WHERE username = 'admin order by 4'

Which is not invalid but there is no username with this name admin order by 4

So others said to put on your field admin' order by 4; again this is wrong as your query will be evaluated as

SELECT * from users WHERE username = 'admin'' order by 4;'

Which is also wrong, see the two single quotes?

So you have to complete your query with some command then try to put this on your input: admin' order by 4,' then your query will be evaluated as

SELECT * from users WHERE username = 'admin' order by 4,''

Which means that it your order by the fourth field plus a blank value.

Yup I agree I am now trying to make the following line `$qstr = "SELECT * from users WHERE username = '$id'";` still work as a query but not use the apostrophes around the $id — user2157179, Dec 12 '13 at 19:49

score 0 · Answer 3 · edited Dec 12 '13 at 19:37

Based off of your example query:

SELECT * from users WHERE username = 'admin order by 4'

The code that is processing your form input is forming a query by encapsulating your input into single quotes. This causes the query to search for records where the username column is set to your specified form value.

Depending on what cleaning your code is doing, you may not be able to do this, but you could try submiting the form with:

admin' order by 4;

...as the username. If the single quotes are not being stripped out or escaped, the query will then become:

SELECT * from users WHERE username = 'admin' order by 4;'

UPDATE:

I see you've posted the code now. To allow injection, simply remove the quotes from around '$id' on this line :

$qstr = "SELECT * from users WHERE username = '$id'";

Thats wrong adding this: `admin' order by 4;` as you say the query would be: `...username = 'admin'' order by 4;` which is invalid. Double single quotes. — Jorge Campos, Dec 12 '13 at 19:36

Order by SQL statement not working correctly

3 Answers3