How do I block user viewing out DB tables?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do I block user viewing out DB tables?

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I block user viewing out DB tables?

Post 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?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I block user viewing out DB tables?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I block user viewing out DB tables?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I block user viewing out DB tables?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I block user viewing out DB tables?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I block user viewing out DB tables?

Post 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??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I block user viewing out DB tables?

Post 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.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do I block user viewing out DB tables?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I block user viewing out DB tables?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

Re: How do I block user viewing out DB tables?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I block user viewing out DB tables?

Post 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/>";
}
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I block user viewing out DB tables?

Post 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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: How do I block user viewing out DB tables?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do I block user viewing out DB tables?

Post by simonmlewis »

Can you please give me an example of that type of query, using PDO?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply