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

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 »

Simple prepared statement example:

MySQLi:

Code: Select all

$sql = new mysqli('host', 'user', 'pass', 'db');

$query = "SELECT name, price, color FROM products WHERE id = ? AND size = ?";
$stmt = $sql->prepare($query);
$stmt->bind_param('is', $_GET['id'], $_GET['size']);
$stmt->execute();
$stmt->bind_result($name, $price, $color);

while ($stmt->fetch()) {
    echo "Name: {$name}<br>";
    echo "Price: {$price}<br>";
    echo "Color: {$color}<br>";
}
PDO:

Code: Select all

$pdo = new PDO('mysql:host=localhost;dbname=whatever', 'user', 'pass');

$query = "SELECT name, price, color FROM products WHERE id = :id AND size = :size";
$stmt = $pdo->prepare($query);
$stmt->execute(array(':id' => $_GET['id'], ':size' => $_GET['size']));

while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "Name: {$row->name}<br>";
    echo "Price: {$row->price}<br>";
    echo "Color: {$row->color}<br>";
}
While in this relatively simple example they aren't all that different, you can see a few places where PDO makes life a little easier and keeps your code looking a little more sane:
  • Being able to use placeholders (:id) rather than ? helps keep things readable
  • Being able to pass in an array of these placeholders with their values mapped is infinitely more readable than MySQLi's bind_param()
  • Not having to bind every single column of your result set keeps code significantly shorter for more complex queries.
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 it ok to just use:
echo "Name: $row->name";
?
Because that is how ALL our code is done. Having to redo that lot as well is a serious nightmare.
The rest can be added at the tops of code, so is less of a task.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
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 »

Code: Select all

$stmt = $pdo->prepare($query);
$stmt->execute(array(':id' => $_GET['id'], ':size' => $_GET['size']));
Surely we don't have to use this? Because what if we are extracting say 40 fields?
Do we have to set each field like this?? Or is there some kind of wildcard "all" one can do?
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 »

Those are your WHERE inputs, not the columns you're returning. That was also my point in suggesting PDO over MySQLi; if you're using prepared statements, and you really ought to be, then you DO have to bind all selected columns to a variable with MySQLi but you do NOT have to with 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 »

But what about if you aren't posting anything through - just running a query.
Then isn't the '_GET' redundant? Or does that _GET from the database??

And can it be done without the { } ... as I explained....?
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 »

Sure, if you aren't binding any parameters, then you aren't binding any parameters. This works pretty much the same everywhere.

Code: Select all

$query = "SELECT foo FROM bar WHERE baz = 12";
$result = $pdo->query($query);

Code: Select all

echo "$foo";

Code: Select all

echo "{$foo}";
will work the same.
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 »

Code: Select all

$pdo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'pass');

$query = "SELECT email FROM users WHERE type = 'user'";
$result = $pdo->query($query);
while ($row = $stmt->fetch(PDO::FETCH_OBJ)) {
    echo "Name: $row->email<br>";
}
This isn't bringing up anything - I suspect because of the WHILE line, as I haven't mentioned stmt anywhere else.

I guess that is only when you 'bind'. But I'm not here - so where I am going wrong with this bit?
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, you're right. PDO::query returns a PDOStatement object, which you've stored in $result. $stmt is not defined. Iterate over $result instead.
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 »

Code: Select all

$pdo = new PDO('mysql:host=localhost;dbname=dbname', 'user', 'pass');
$query = "SELECT email FROM users WHERE type = 'user'";
$result = $pdo->query($query);
while ($row = mysql_fetch_object($query))
  {
echo "$row->email<br/>";
}
This correct? I don't think it is ..............
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
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 »

Nor is this...

Code: Select all

$pdo = new PDO('mysql:host=localhost;dbname=visualsh_justbb', 'justbbguns79', 'asdgf0uUeQc345');
$sth = $dbh->query = ("SELECT email FROM admin WHERE type = 'admin'");
while ($row = $sth->fetch())
{
    echo "Name: $row->email<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 »

No. You're not using mysql_ anymore. That's the whole point. Iterate over $result.

Code: Select all

$query = "SELECT email FROM users WHERE type = 'user'";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
    echo "$row->email<br/>";
}
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 »

Code: Select all

$pdo = new PDO('mysql:host=localhost;dbname=databasename', 'user', 'pass');
$query = "SELECT email FROM admin WHERE type = 'admin'";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
    echo "$row->email<br/>";
}
Pretty basic, but this is working a treat.
So this is for extracting data - to INSERT or UPDATE, is the topend query the same as MySQL:
mysql_query("asdfafsd");
Or is the first part totally different? I assume it is, as it's should be without the mysql_query stuff as that is "old".

I've found this:

Code: Select all

$sql = "INSERT INTO books (title,author) VALUES (:title,:author)";
$q = $conn->prepare($sql);
$q->execute(array(':author'=>$author,
 ':title'=>$title));
and this

Code: Select all

$sql = "UPDATE books 
        SET title=?, author=?
		WHERE id=?";
$q = $conn->prepare($sql);
$q->execute(array($title,$author,$id));
So you set the database UPDATE query, and further on you tell it what variables to use - so you don't put the variable in the top query?
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 »

Both will work, though I prefer using named placeholders to question marks. I find it makes your code easier to read. In either case, though, you're using the placeholders in place of the variables you'll be passing to the query. The prepared statement handles escaping for you, so you don't need to worry about mysql_real_escape_string or anything of the sort. Set the placeholders in the query, pass them in as arguments when you execute the query.
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 »

Cool.

I just tried the PDO connection style on one of our web site. We use an include fiile, so if we need to alter the login, we do it just once.
The whole site is mysql_extension (I'm talking with hosts about the need for all this). But with the PDO db conn script, the rest of the site collapsed.

Do I have to have the PDO conn script, AND the queries all in PDO to use that as the conn?

Is it all or nothing??
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 »

Sort of. Any query using mysql_query will need an established mysql_connect. Any query using PDO will need a PDO object. While you're in the process of refactoring, why not use both? Leave the mysql_connect in there so your existing queries continue working and create a PDO object at the same time, say $pdo. As you rewrite each query, you can update it to use PDO. Work through it file by file as time permits. Once you have no more instances of mysql_query remaining, simply remove the mysql_connect call.
Post Reply