Best way to store, and constantly check a variable in PHP?

[dmillerw]

So, building a site (obviously) and one of the important features are differences in a users level. For example, the account needs activated, or admins.

Currently, the only way I've found to grab that value, and make sure its correct, is to query the database on each and every page. I know this is no where near the best way to do that, so I was hoping someone here would have a better idea.

[TimKu]

That's normal...?
You can have a few query's per page. no harm done.

Sometimes though for small amounts of data you can use XML or JSON since it can be faster.

[Fizzadar]

Pretty much any website which uses MySQL will have queries running on every page (often many queries, we're talking 5-15). It's perfectly normal, caching/similar would be huge over-optimisation unless you're planning on serving thousands of pages per second.

[Jelly_]

Unless you're talking about sessions.

[Fate]

Session's is a good one to think about.

[KmartSqrl]

If you're using sessions you'd still want to use queries to verify the user's "level" any time they're taking any action that the user level has an effect on, otherwise you're not covering things like someone logging in and beginning a session and then having their level changed by an administrator or what have you.

[dmillerw]

Any reason to use sessions, instead of grabbing the value every page?

[KmartSqrl]

It's useful for pages where you're JUST displaying the info.

[Lequinx]

	class RetrieveInfo {
		public $uid, $username, $password, $rank, $email, $postcount, $usertitle; # add more items here if you have more columns
		
		public function __construct($username){
			global $connection; # we want to use an already set var
			$get = mysql_query("SELECT * FROM accounts WHERE name='$username'", $connection) or die(mysql_error());
			
			while($row = mysql_fetch_assoc($get)){
				$this->uid = $row['userid'];
				$this->username = $row['name'];
				$this->password = $row['password'];
				$this->rank = $row['usergroupid'];
				$this->email = $row['email'];
				$this->postcount = $row['posts'];
				$this->usertitle = $row['usertitle'];
			}
		}
	}

and create the object if they are logged in

	if(isset($_SESSION['username']) AND isset($_SESSION['password'])){
		// creating instance of getting info for when we call it
		$getInfo = new RetrieveInfo($_SESSION['username']);
	}

(in a global file that connects to all pages)

then if you wanted to display their username, it's as easy as echo $getInfo->username.

I'm not an expert php programmer and am still learning, but hopefully this helped a little.

[Fizzadar]

Basically:

sessions & cookies: use for [i]display[/i] only (links to admin pages, username display, etc)
database: use when checking permissions/whatever (when actually doing something)

[yngndrw]

A way you can increase performance when making a database change which requires you to check the user's permissions is to use a stored procedure.

You would pass in the user's ID as a parameter and then check the permissions are suitable before performing the action. This would mean that there is less data sent between the database and the webserver which will reduce database driver overhead and latency. Stored procedures are also pre-parsed (And, at least in MS-SQL, when stored procedures are first parsed the optimal execution plan is decided.)

[commander204]

I always wondered how Procedures work and how to use them.

[yngndrw]

I should point out that I'm only experienced with them in Transact-SQL. (I.e. Microsoft SQL Server)

It's basically like a subroutine which has some parameters (Which can be either input or output parameters.) and can return multiple record sets. MS SQL will try and optimise them in a range of different ways, analyse each one and then save the best one.

Inside a stored procedure you can even write procedural code using variables, conditions and cursors. You can also make table variables to hold temporary results that you can work on.

They can also be used for security - By only allowing a specific user to run stored procedures, you can limit what that user can do. For example, the may only be able to delete a record by using a deletion stored procedure which archives the record in another table and / or records an entry in an audit log.

You can also return all of the record sets that you may require for a specific page from a single small query. (I.e. Calling the stored procedure with the required parameters.)

At work we use them for all of our SQL. Some of our stored procedures do some pretty involved pricing and others generate and consume XML which is sent to and received from an external service provider. (Although I'd strongly recommend not processing huge amounts of XML in SQL - The guy who wrote that part likes SQL rather too much.)

[Fizzadar]

[QUOTE=yngndrw;32199181]A way you can increase performance when making a database change which requires you to check the user's permissions is to use a stored procedure.

You would pass in the user's ID as a parameter and then check the permissions are suitable before performing the action. This would mean that there is less data sent between the database and the webserver which will reduce database driver overhead and latency. Stored procedures are also pre-parsed (And, at least in MS-SQL, when stored procedures are first parsed the optimal execution plan is decided.)[/QUOTE]

I believe it works the same way in PHP. Again though, unless your planning on serving a lot of requests per second, it's over-optimization to use stored procedure's for performance increase.

[yngndrw]

Well aside from the performance reasons, there are good architectural reasons to do so. (Wrong word ?)

For example, if you wanted a query to remove a customer record.

You could have a stored procedure that would:
1) Check for suitable permissions
2) Delete the client
3) Add an audit entry, showing who deleted the client and if it succeeded or not

You may rely on foreign keys to cascade deletes to most of the other related items, but there are some cases when they can't help you. (It would probably suggest that the database isn't organised as it should be, but over a product's life changes are bound to be made which may be done in a sub-optimal way - Clients have to pay for changes to be made and if it's a decision between rewriting a large portion of the system to do it properly, or tacking something on to the system in a much cheaper way, they will usually go for the cheaper way.)

In these cases, it is far more maintainable if you can add the extra deletion logic to the existing stored procedure. It also allows for better code re-use by putting common routines into SQL functions or stored procedures.

It's a bit of an odd thing really, on one hand you can do a lot of the business logic in the database and leave just the UI to the application. (Great if most of the business logic is based upon set-based operations.) On the other hand you can just use the database for the most basic of storage and rely on technologies such as Entity Framework or Linq to SQL to do the business logic. (Although Linq to SQL actually converts the logic to SQL statements so they are run on the database, it's a matter of where actually write the logic and in what language.) There's a huge amount of overlap.

I have found this which is quite disappointing for MySQL:
[url]http://stackoverflow.com/questions/1577316/mysql-stored-procedure-not-using-the-query-cache[/url]

[sentrix]

Yeah but then you're relying on having one database user account for each user, no?

[yngndrw]

No that's not what I mean. You'd have one database user for each application. (With only the permissions required to execute stored procedures in a specific schema.)

The User IDs that I speak of would be just the identity of your own authentication system (Open IDs, user names / passwords, etc.) and would just be stored in a table.

You would pass in the User ID that you want to perform the action from (Or even full session information.) and the stored procedure would perform the permission checking / authentication required.)

[sentrix]

[QUOTE=yngndrw;32258276]No that's not what I mean. You'd have one database user for each application. (With only the permissions required to execute stored procedures in a specific schema.)

The User IDs that I speak of would be just the identity of your own authentication system (Open IDs, user names / passwords, etc.) and would just be stored in a table.

You would pass in the User ID that you want to perform the action from (Or even full session information.) and the stored procedure would perform the permission checking / authentication required.)[/QUOTE]

Ew.

[yngndrw]

[QUOTE=sentrix;32259110]Ew.[/QUOTE]
Informative post.

[sentrix]

[QUOTE=yngndrw;32259160]Informative post.[/QUOTE]

Okay, try this on for size.

It doesn't make sense that you'd want to move logic like that out into the Database like that, it's actually backwards. Stored procedures should be maintaining data integrity, not doing checks like that. If you really think you should be able to come up with a solution that performs well and doesn't suck like that.

[yngndrw]

It's a matter of where do you put your trust. We already have a database username / password in case the server gets compromised, but those credentials are usually stored in plain text in some configuration file.

With my solution, the website becomes not much more than a UI - It is responsible for displaying data and entry forms in a suitable format and providing validation. The database handles the data and the security of that data. It's not really backwards as databases are meant for handling data, which this is.

If the server is compromised, the attacker would get the same access to the database that the website does. They would still have to have a valid application login in order to do anything to the data.

Stored procedures are not related to data integrity - Check constraints, triggers and primary and foreign keys are for that.