Page 1 of 4
How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 3:25 am
by simonmlewis
We have had a report of someone who has logged in or seen a bug in our site, and been able to view all 'admin' accounts.
All pages are viewable only if your cookie type is a set type, and he's literally emailed us all the logins for admin!!
Is there something one can put into a search box that means this can be just extracted?? And if so, how do I block that?
We do have an admin page where we can see users, but a) you have to be logged in as admin to see the page, and b) the SQL for it does not search for a particular type of user that is an administrator.
So I'm stuck, but in a bit of a pickle!!
Simon
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 4:39 am
by requinix
I can think of a number of possible exploits, but it's all just guesswork without being able to see the actual code.
This cookie you speak of, that is quite possibly it. You know cookies aren't secure, right? That you can't trust their values?
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 4:42 am
by simonmlewis
Sure, but how do you then make a cookie secure?
We am tighting up on the:
$c=mysql_real_escape_string($_GET["c"]);
throughout the site.
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 12:33 pm
by requinix
Cookies are not secure period. It's like asking some guy on the street to remember something for you, then coming back the next day having forgotten and asking what it was: he can tell you whatever he damned well pleases, and though you may be able to validate what he said (it sounds reasonable) you won't be able to verify it (whether it's truly what you told him).
So it's not just SQL injection. At the very least you need two values, preferably two cookies, to verify each other: one piece of data you want to remember, another piece completely unknown and unrecognizable to the user that helps you verify the other. For example a user ID by itself is stupid because then I could just set my user ID to be that of the admin user, but if you add some sort of token (which you've also recorded somewhere else) then you can do a lookup to see if the two belong with each other.
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 12:38 pm
by simonmlewis
Do you mean, the token randomly gets generated, and entered into the database by that user, and for each page you query the cookie, you also query their cookieid against that token both in the session and against the db?
But back to the basic question: is that how you stop SQL Injection?
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 12:45 pm
by requinix
simonmlewis wrote:Do you mean, the token randomly gets generated, and entered into the database by that user, and for each page you query the cookie, you also query their cookieid against that token both in the session and against the db?
Basically, yes. If you're using sessions then you only need to do it the first time they come (back) to your site, then store the fact that they've been validated in the session - which the user can't access.
simonmlewis wrote:But back to the basic question: is that how you stop SQL Injection?
If you're using the old, deprecated, and inefficient mysql extension (which apparently you are) then you have to use mysql_real_escape_string() at a minimum and be wary of un-quoted values in your SQL.
If you're reading between the lines and wondering what extension you should be using instead then that'd be mysqli or PDO.
If you're using either of those then you can use prepared statements (safer) or their value-escaping mechanisms.
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 12:52 pm
by simonmlewis
Well to avoid having to rewrite about 20+ web sites, I'll stick with the "old" code.
I never use unquoted values.
And yes I get you about the tokens. Might take a look at doing that.
Can someone FAKE a cookie on your site then, to make it look like they have logged on, without seeing your code??
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 12:55 pm
by Celauran
Yes, absolutely cookies can be spoofed. mysql_ extension has been officially deprecated and will be removed in upcoming versions of PHP. You're going to have some rewriting to do eventually anyhow.
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 12:59 pm
by requinix
simonmlewis wrote:And yes I get you about the tokens. Might take a look at doing that.
Can someone FAKE a cookie on your site then, to make it look like they have logged on, without seeing your code??
They can fake whatever values they want. Cookies are completely and entirely under the user's control: they can create, edit, and delete cookies as they want. Whether they can get into your site depends on how you use those cookies.
Re: How do I block user viewing out DB tables?
Posted: Tue Aug 27, 2013 1:03 pm
by simonmlewis
I'm with you.
So if they got into the site and logged in, they can edit the content of that cookie? I didn't know that. Tho I guess they have to know, to what they msut edit it to, to be able to "hack" the site.
And yes, they have to know what to login with to hack it at all.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 12:48 am
by Eric!
I would guess that you have a few problems other than just cookies, which are really insecure. If they emailed you all the admin passwords then there are several things that are wrong here. I know you said a cookie setting can allow you to view the pages, but for argument's sake perhaps he found some other hole:
1. You should be hashing the passwords with a salt at a minimum. They should not be stored in plain text.
2. Admins really shouldn't be able to view passwords. Reset them, yes, but viewing them shouldn't be a feature that is under CMS control.
3. If access was not elevated using a cookie session value OR if admins can't view all the passwords, then this guy has found an exploit to read your raw database. You need to audit this code or hire someone to whitebox it.
4. You shouldn't be using mysql_ functions at all. I've tried to extol the benefits to you in using mysqli or better yet PDO before on this forum. There are tools that can convert msqli calls to mysqli automatically so your sites won't break when the servers are upgraded and mysql is dropped, but for a more secure solution use PDO and prepared statements.
5. Perhaps he found a way to upload a file and run it on your server.
6. Perhaps he tampered your sign-up and/or profile form and elevated his profile to an admin.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 4:08 am
by simonmlewis
Is this correct, basic, mysqli ?
Code: Select all
$DBServer = 'localhost';
$DBUser = 'siteuser';
$DBPass = 'password';
$DBName = 'dbname';
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
if ($conn->connect_error) {
echo "no";
}
$result= "SELECT * FROM `products` WHERE status = 'live'";
while ($row = $result->fetch_object($result))
{
echo "$row->title<br/>";
}
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 6:21 am
by Celauran
You've missed a step in there; you still need to execute the query.
Code: Select all
$conn = new mysqli($DBServer, $DBUser, $DBPass, $DBName);
$query = "SELECT title FROM products WHERE status = 'live'";
$result = $conn->query($query);
while ($row = $result->fetch_object()) {
echo "{$row->title}<br>";
}
MySQLi book as a reference.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 6:23 am
by Celauran
If you're just beginning to transition from mysql_ functions, I'd recommend using PDO over MySQLi. You'll be making heavy use of prepared statements, and I find them to be considerably less tedious using PDO.
Re: How do I block user viewing out DB tables?
Posted: Wed Aug 28, 2013 6:24 am
by simonmlewis
Can you please give me an example of that type of query, using PDO?