Pdo Persistent Connection Behavior Questions

[Arnsenal]

Hello Masters... Freaks

 

I have a PDO Persistent Connections question.

 

1. If I connect to mySQL using PDO persistent connections turned as so....

 

 

$db = new PDO("mysql:host=localhost";"dbname=$this->dbName"; "$this->mySQLuser", "$this->mySQLpassword",array(PDO::ATTR_PERSISTENT => true,PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ));

 

 

2. Then I rerun this script (I rerun this script because it is a class that is auto loaded).

 

4. Will PDO reconnect to mySQL? Will PDO reconnect if the connection parameters change?

 

 

The whole idea is to create db class that connects to mySQL using a user name and password that changes depending on the state of the user (logged in, not activated, not logged in). (Thought this would improve security because of the changing of the mySQL privliges based on user state, but Im a noob so I really dont know if it helps)

 

Here is my full script.

 

<?php
class db {
private $dbName='vulnVult';
public $tableName='users';
protected $mySQLuser='anon';
private $mySQLpassword="$this->mySQLuser".'password';
private $db;
function __construct($PDO) {

 try {
  $db = new $PDO("mysql:host=localhost";"dbname=$this->dbName"; "$this->mySQLuser", "$this->mySQLpassword",array(PDO::ATTR_PERSISTENT => true,PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ));
  //Following Logic Sets MySQL user type
  if ($_SESSION['userID']) {
   $query= 'SELECT `activated` from `users` where'."$SESSION['userID']".'=`userID`';
   $stmt = $db->query($query);
   $row = $stmt->rowCount();
   if ($row = 1) {
 $this->mySQLuser='registered';
   }
   else {
 $this->mySQLuser='notActivated';  
   }
  }

  else {
   $this->mySQLuser='anon'
  }
  // return a new PDO object with the corrct MySQL user type. Persistent Connection is On.
  return new $PDO("mysql:host=localhost";"dbname=$this->dbName"; "$this->mySQLuser", "$this->mySQLpassword",array(PDO::ATTR_PERSISTENT => true,PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ));

 } catch (PDOException $e) {
  echo "<br />There was a problem connecting to the database : ".$e->getMessage()."<br />";
  die();
 }

}
}
$db = new db(PDO);
?>

Edited October 20, 2012 by Arnsenal

[requinix]

For one it won't work because

private $mySQLpassword="$this->mySQLuser".'password';

you can't use expressions as the default values for class variables. That aside,

 

1. If I connect to mySQL using PDO persistent connections turned as so....

Persistent connections are a good idea only when it takes a (relatively) long time to connect to your database. Nowadays that's almost never the case. The biggest drawback to persistent connections is that it limits the number of users you can have browsing your site: if MySQL is configured to only allow 10 concurrent connections at once then when an 11th person tries to browse your site it won't work for them.

 

Don't use persistent connections. They may sound useful but they have major disadvantages.

 

2. Then I rerun this script (I rerun this script because it is a class that is auto loaded).

 

4. Will PDO reconnect to mySQL? Will PDO reconnect if the connection parameters change?

PDO does not manage the persistence. The MySQL driver does. It reuses connections when a) they are available and B) the host/user/password/database match. If any change then it will not reuse a connection. The best case net effect is that these connections you have will be started and stopped so often because you have different users on the site and making them persistent doesn't do any good.

 

The whole idea is to create db class that connects to mySQL using a user name and password that changes depending on the state of the user (logged in, not activated, not logged in). (Thought this would improve security because of the changing of the mySQL privliges based on user state, but Im a noob so I really dont know if it helps)

This is a very bad idea. Do not do it. It would entail creating users in MySQL for every user in your site. The whole idea is just... no. Just no, okay?

The best thing you can do for security is create a couple users specifically for read-only and for read-write access. The first one has just SELECT privileges on everything and I gotta imagine most of your site will be using that. The other user has INSERT and UPDATE (and DELETE if you think that's not a bad thing to do in a database) and gets used if there are actual changes to make someplace.

If, for example, you update a last-browsed-timestamp for a user or record page views then I might consider allowing the read-only user UPDATE privileges on the users table and INSERT in the page views table. The main point is to have as few permissions as possible.

[Christian F.]

Requinix: I think you've misunderstood his intent a bit. He's not talking about creating a DB user for each system user, but having three different DB users with their own access levels. Then having the system choose which user to pick, based upon the status of the user's account. So that unregistered users only get "select" privs on the public stuff, plus "insert" on the users table.

Exactly what you're talking about, in other words, just a bit more finely grained.

[Arnsenal]

Christian F. That is exactly what I am trying to do!!

 

Requinix, thank you for the detailed response!!

 

If you guys still think persistent connections is not the way to go then I will drop the idea.

 

The other way to go, I figure, is to have the application just create a connection with the correct user type as the application needs it.

 

I thought persistent connections would be good because anytime my app needs scripting I will need a connection to the database, therefore if I have 1000 users on the site they will not be continually connecting to the database and therefor

improving the applications performance.

[requinix]

He's not talking about creating a DB user for each system user, but having three different DB users with their own access levels. Then having the system choose which user to pick, based upon the status of the user's account. So that unregistered users only get "select" privs on the public stuff, plus "insert" on the users table.

Hmm yes, that agrees more with what the post says. Awesome.

 

If you guys still think persistent connections is not the way to go then I will drop the idea.

I don't. I think it's one of those things were you should decide to opt-into it because you have a problem with the normal methods, and then only after you've conducted a thorough evaluation (even trial run) of it.

 

I thought persistent connections would be good because anytime my app needs scripting I will need a connection to the database, therefore if I have 1000 users on the site they will not be continually connecting to the database and therefor

improving the applications performance.

Problem is that unless you manage the connection very carefully, being sure to "open" it right when you need it and "close" it when you are done, the connection will stay in use for the whole duration of your scripts. Which equates to nearly 1000 connections at a time. Which is bad.

[Arnsenal]

Awesome, thanks requinix