3

My question is pretty specific, but I think it will help in my overall understanding of security and SQL injection. I am running a local webpage with a simple form for the purpose seeing how SQL injection works first hand, by doing it to my own database and webpage. I keep changing the way my php file validates a user so I can see the differences. I am a beginner and the php file is very simple on purpose. My current php code is:

<?php
$username = $_POST['username'];
$password = $_POST['password'];
$conn = mysql_connect('localhost', 'root', 'password');
mysql_select_db(test1);
$query = "SELECT username, password FROM users WHERE username = '$username'";
$result = mysql_query($query) or die("Query failed: " . mysql_error());
$arr = mysql_fetch_array($result);
if($arr['username'] == $username && $arr['password'] == $password && strlen($username) > 0){
 header('Location:index.php');
}else{
 header('Location:login.html');
}
?>

I have no idea if this is a good or bad way of validating. I just want to know an example of how to inject it because I can't figure this one out. MySQL_query() only allows 1 statement so I can't chain together statements, and I don't know what else to do to it. I have changed the file so I can do

' or 1=1; --

types of injection, but obviously that one will not work here. So just curious. Thanks.

oliholz
  • 7,447
  • 2
  • 43
  • 82
Ryan
  • 1,135
  • 1
  • 13
  • 28
  • 4
    What about `'; drop table users; --`? **[:)](http://xkcd.com/327/)** – Joel B Fant Jun 20 '11 at 18:30
  • 1
    It would work if `mysql_query` allowed multiple queries separated by semicolon. – a1ex07 Jun 20 '11 at 18:32
  • 1
    This link will def help you http://bobby-tables.com/ – afarazit Jun 20 '11 at 18:33
  • 2
    @Joel: [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) does not support multiple queries. His design has other flaws, though. Saving passwords plaintext is _terrible_! – kba Jun 20 '11 at 18:34
  • 1
    FYI: If your goal is to learn how to prevent injection attacks, there is only one decent approach: http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php – mikerobi Jun 20 '11 at 18:34
  • Sometimes the goal of the SQL injection is not always to return something or to make a valid SQL statement but to make the program return an error and reveal details about the database. – Jared Jun 20 '11 at 18:35
  • The mysql_query() method fails if another statement is added, so it can only do 1 statement at a time that I am aware of. I tried '; insert into users....;-- and now just tried yours, but same outcome. It says I have an sql syntax error. – Ryan Jun 20 '11 at 18:35
  • Thanks everyone and thank you Jrod. I agree with your answer, seeing there is no way to make a "bad" statement, gaining db info is the next thing to do. – Ryan Jun 20 '11 at 18:40

4 Answers4

2

The following passed to username would return all the rows:

' or '1'='1

In general its simply not a good idea to pass unvalidated input to a SQL query.

Joel B Fant
  • 24,406
  • 4
  • 66
  • 67
Justin Dearing
  • 14,270
  • 22
  • 88
  • 161
  • This does not do anything. I simply return to my login page. – Ryan Jun 20 '11 at 19:01
  • In this case, you may be correct. However, by using [http://php.net/manual/en/function.mysql-real-escape-string.php](mysql_real_escape_string) or better yet [PDO Prepared statements](http://stackoverflow.com/questions/60174/best-way-to-stop-sql-injection-in-php) you plug one more attack vector. Security is about mitigating risks, both the known and unknown. Finally, such an attack as the one I describe could be used as a DDOS attack. If you have a lot of users, reading a whole table will chew a lot of resources. – Justin Dearing Jun 20 '11 at 19:08
  • Thank you for the information. I was just curious on how to get access to the index page with sql injection, though. I know this would not be safe in the real world. – Ryan Jun 20 '11 at 19:21
1

Send this as username:

a' and(select 1 from(select count(*),concat((select concat_ws(0x3a,version(),database())),floor(rand(0)*2))x from information_schema.tables group by x)a) union select 1,'

I'm not sure but I think it's not possible to get redirected to index.php, but the above example will show you something interesting.

Such security holes should never be left non sanitized because a malicious user can get even the mysql's root user password if SELECT command is permitted to mysql table and so on.

By the way, you should never display mysql_error()s to end-users.

  • I know it is for learning purposes only. I can't get yours to work, here is what I tried: a' UNION SELECT 1,concat_ws(0x3a,version(5.5.12),database('test1')); -- – Ryan Jun 20 '11 at 19:10
  • no need to specify version or database, just post what i wrote. –  Jun 20 '11 at 19:15
  • Hmm... I just return to the login.html page – Ryan Jun 20 '11 at 19:18
  • Haha, it did show some valuable information. Thanks for the help. So on a lot of example login validation files, I see "select * from table where user = $user and password = $password". if(mysql_num_rows(of that query) == 1) --> success. This kind was easy to inject. So is this approach better for validating? – Ryan Jun 20 '11 at 19:47
  • it depends....This approach is called `output through error`. That means that the query will generate an error containing your selected values. However, there could be something like `echo $row['username']`. Assuming attacker can't see any `mysql_error()`s, he would see selected values instead of `$row['username']` by injecting the query with some union statements. There are many many interesting things about sql injection that you could never imagine, but, for now, be sure to isolate user-submitted variables between single quotes and to use mysql_real_escape_string. Good luck! –  Jun 20 '11 at 20:00
1

Consider the following query :

SELECT username, password FROM users WHERE username = 'anything' AND 0 =1 
UNION ALL
SELECT '\'anything\' AND 0 =1 
UNION ALL
SELECT \'user\',\'password\'','password'

If $_POST['password'] equals word 'password', your validation will fail and let unauthorized user to access protected page.

a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • Hello, I tried: anything' AND 0 = 1 UNION ALL SELECT '\'anything\' AND 0 = 1 UNION ALL SELECT \'user\',\'password\'','password';-- and could not get access. – Ryan Jun 20 '11 at 19:14
0

You are not doing any validation on your post parameters before you execute your statement. This is bad! And SQL injection is easily possible.

For example:

SELECT username, password FROM users WHERE username = '$username' AND (SELECT 1 FROM ([Almost any SQL statement you want...]))

I.E. 

$username = "' AND (SELECT 1 FROM ([Almost any SQL statement you want...])); --"

Make sure you validate your parameters before using them in a SQL statement.

Colin
  • 2,001
  • 13
  • 28