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:

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

Post by simonmlewis »

So keep the current older DBConn script there, and just update the include files with PDO?
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 could even do both in the same include file, if it's already being included everywhere.
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 don't mean that.
We have multiple include files to run the site. Plus of course the template.
But we have one include file that is the database connection.

My question is: can I do PDO on all the internal includes first, while keeping the older style dbconn?

Or do they both have to be "PDO" ?
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 »

simonmlewis wrote:My question is: can I do PDO on all the internal includes first, while keeping the older style dbconn?
Sorry, I'm not following what you mean here. What I was suggesting is setting up your dbconn (mysql_connect()) and instantiating a PDO object in the same file so that you don't need to update your include calls anywhere and have both methods available to you so you can make the transition piecemeal.

Code: Select all

define('DBHOST', 'localhost');
define('DBUSER', 'username');
define('DBPASS', 'password');
define('DBNAME', 'database');

$conn = mysql_connect(DBHOST, DBUSER, DBPASS);
if ($conn) {
	mysql_select_db(DBNAME);
}

$pdo = new PDO('mysql:host=' . DBHOST . ';dbname=' . DBNAME, DBUSER, DBPASS);
Including this file means your mysql_query calls will continue to work, while $pdo will also be available to you.
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 »

Sorry teach, now you've lost me too.
Are you saying this script of yours does both old and new versions in the same script?
So if PDO is available, it will work with it. If the include script is old style, it will work with that too?
And once all old versions have been replaced with PDO, this will just work anyway?
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 »

That's what I'm saying, yes. Your mysql_query() calls depend upon a mysql_connect() and mysql_select_db() call. We've got those covered. PDO simply requires that a PDO object be created. We're covering that, too.
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 »

Ok - new question then.
My template closes all queries with:
mysql_close($sqlconn);
Otherwise I have countless numbers of opening and closing connections (bad).

Or can I now add a second "close" function for PDO? (which is...??).
So the template loads.
It runs TWO connections scripts.

The include files run whatever queries they want, under whichever connections is most suitable.
At the end of the page, it runs two "dbclose" scripts.
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 »

Is it as simple as:

Code: Select all

<?php
$dbh = new PDO('mysql:host=localhost;dbname=test', $user, $pass);
// use the connection here


// and now we're done; close it
$dbh = null;
?>
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 »

It's not necessary. From the manual:
Upon successful connection to the database, an instance of the PDO class is returned to your script. The connection remains active for the lifetime of that PDO object. To close the connection, you need to destroy the object by ensuring that all remaining references to it are deleted--you do this by assigning NULL to the variable that holds the object. If you don't do this explicitly, PHP will automatically close the connection when your script ends.
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 »

Oh - so you MUST close it.... but you don't have to.
Bit contradictory but I get it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

simonmlewis wrote:Oh - so you MUST close it.... but you don't have to.
Bit contradictory but I get it.
It doesn't say you must close it; it says you can close it -- and here is how. Typically PHP scripts do not close database connections because it is more efficient to let PHP do it during its post-execution clean-up.
(#10850)
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! »

While this is all good, don't forget items 1,2,3,5 and 6 that I mentioned. And here is a good reference on hashing.
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 »

My next step, is to update my XAMPP locally. I did install the latest but all my sites died locally, so I had to install the older version.
I didn't think it would kill them tho. So that's a puzzle.
I may try again later and see if I can find out the issue.

At least from "Celauran" help, I've been able to get right into this, so much appreciation to him. And thanks to others - I also use a Close script as I was taught it best practice.
"You've opened the connection, now close it after".
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 »

What is wrong with this code?
It's my first bigger query using PDO.

I'm using your PDO multiple connection while I go thru all the pages.

Code: Select all

$query = "SELECT id, catid, catname, uk_catname FROM products WHERE pause = 'off' AND catid IS NOT NULL GROUP BY catname ORDER BY 
catname='XL' DESC,
catname='L' DESC,
catname='M' DESC,
catname='S' DESC,
catname='XS' DESC";
$result = $pdo->query($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
      $categ = "$row->catname"; 
      $findcateg ="/ /"; 
      $replacecateg ="-"; 
      $categreplace = preg_replace ($findcateg, $replacecateg, $categ); 
			echo "<div class='submenu'><a href='/category/$row->catid/$categreplace'>$row->catname</a></div>";
			}
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post by Christopher »

simonmlewis wrote:What is wrong with this code?
Are you getting an error or looking for a critique?

On a macro level, I would recommend separating the database code into a separate class that reads all the rows into a $rows array and returns it, then have you display code use the returned array. You will find that you will start to get code reuse if the do that.
(#10850)
Post Reply