Hackable sql query

Question

I was running a site I purchased that I thought was fairly unhackable. However, after having an attack, I found it was not. He informed me of the vulnerability, however my question is what user input could have been done to get all the users usernames like he did? Here is the code...

$un=$_GET['username'];
$q=$db->query("SELECT * FROM users WHERE login_name='$un' OR username='$un'");

I realize that this is highley hackable. Therefore, I changed the site over to prepared statements to prevent this from happening again. I just want to know what he could have entered to get all the users usernames.

Someone posted the script on github, you can find it here: https://github.com/sat312/Mafia-Game-Script/blob/master/checkun.php

Literally the first thing google brings up for 'sql injection php' is here: http://www.php.net/manual/en/security.database.sql-injection.php Completely explains it for you in full detail — Deryck, Jan 11 '14 at 06:14
@Deryck Did you read the question? He's looking for an example that matches the code above... not some generic description. — Brad, Jan 11 '14 at 06:15
@Brad Oh I read it. And understood it perfectly. That's why that was a comment and not an answer. I'm saying he should have put a slight effort into this before taking the time to type all that just to let us show one issue with one line of code. Give someone fish, eat for a day, teach em to fish...you get the idea — Deryck, Jan 11 '14 at 06:17
You are right @Brad. I know about sql injections I just cannot get it to produce a list of user names like he did. Can't get the right query. — alexander7567, Jan 11 '14 at 06:18
@alexander7567 both Brad and I gave you the input to make your query return all users. We need more information if it doesn't work for you. — sachleen, Jan 11 '14 at 06:20
Found where someone posted the whole script on github. And updated my answer. — alexander7567, Jan 11 '14 at 06:21
@alexander7567 For what it's worth: https://github.com/sat312/Mafia-Game-Script/issues/1 — Brad, Jan 11 '14 at 06:40

score 2 · Answer 1 · answered Jan 11 '14 at 06:13

You get $un from the user, so I can type anything I want and it'll get substituted into your query. It's called a SQL Injection attack.

Lets say $un = ' OR 1 = 1;-- then your query becomes:

SELECT * FROM users WHERE login_name='' OR 1 = 1;--' OR username='' OR 1 = 1;--'

What will happen? this gets executed:

SELECT * FROM users WHERE login_name='' OR 1 = 1;

This will return every row in the table.

score 2 · Accepted Answer · answered Jan 11 '14 at 06:13

2

' OR 1=1;

In the URL:

/yourScript.php?username=%27%20OR%201%3D1%3B

The idea is that since data is mixed with the command, you can just finish the command with data.

answered Jan 11 '14 at 06:13

Brad

159,648
54
349
530

Yes but this doesn't produce a list of usernames. Some how he produced a list of usernames using sql injection. – alexander7567 Jan 11 '14 at 06:15
@alexander7567 This is the general form. You don't show the rest of your code (such as how the output works) so I can't be more specific than that. Also, depending on configuration you may have to finish the second query in your injection. – Brad Jan 11 '14 at 06:16
1

@alexander7567 You can get creative with aggregate functions and string functions to get data out. I'm not going to read through all of the code you linked to... that would take awhile. I suspect though that this code is extremely vulnerable in other ways as well. In any case, once you control the query you control the output. You just might have to get creative in how you roll up the data. Does that make sense? Check this out: http://stackoverflow.com/q/149772/362536 – Brad Jan 11 '14 at 06:25
Okay, group concat makes sense after looking into it... Maybe as a learning tool to work my way to that point, how could I run an update query on that without spitting put an error. For example, I want to run "update users SET money =1". If a hacker was to come to my site before I changed it over to preared statments would this had been possible? I have really Been trying to learn about mysql security, and to learn that you have to know how a hacker works. – alexander7567 Jan 11 '14 at 06:35
@alexander7567 Depending on your configuration, it would absolutely be possible. In many cases, it is possible to run more than one query at a time. Sometimes though, this is disabled. No matter what though, you probably have some existing update or insert query in your code that could also be hijacked. If you found an SQL injection vulnerability you can be almost certain that it was used. There are automated bots hitting scripts these days that are very good at figuring out your table structure, and then filling it with stuff. – Brad Jan 11 '14 at 06:38
@alexander7567 Here's a nice update location: https://github.com/sat312/Mafia-Game-Script/blob/master/preferences.php#L262 It's trivial to change whatever other user properties you want at that point. `isAdmin=true`, `money=999999999`, etc. – Brad Jan 11 '14 at 06:41

score 1 · Answer 3 · edited May 23 '17 at 12:21

He may have used the GROUP_CONCAT statement in MySql which basically groups a column in multiple rows into a single row (see Can I concatenate multiple MySQL rows into one field? for more information). He may have terminated the original SQL statement or UNIONED it with his own and added a LIMIT and ORDER BY to ensure his result got returned and commented out the remained of the original statement.

This is one possibility, but there are probably a few others.

Hackable sql query

3 Answers3