12 Replies - 16019 Views - Last Post: 21 March 2013 - 11:12 AM Rate Topic: -----

#1 P.Conrad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 10-March 13

connection to mysql - best practice

Posted 18 March 2013 - 12:38 PM

Right now I've got "utils.php" with function connecting to db and returning mysqli object. I use it once in my index.php and then If I want access my db elsewhere I use globals.
global $db


I heard thats not the best practice though ? Can anyone give my brief explanation why ? And If that's true what is better practice then?

thanks in advance
Is This A Good Question/Topic? 0
  • +

Replies To: connection to mysql - best practice

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3724
  • View blog
  • Posts: 12,990
  • Joined: 12-December 12

Re: connection to mysql - best practice

Posted 18 March 2013 - 01:38 PM

Globals are bad for a number of reasons. However, I am of the opinion that if anything is a candidate to be a global it is the connection object, particularly as the concerns, and design, for a web page/site are significantly different to those for a desktop application.

However, I like the following Singleton Pattern:

<?php
class DB
{
   private static $instance; // stores the MySQLi instance
 
   private function __construct() { } // block directly instantiating
   private function __clone() { } // block cloning of the object
 
   public static function get() {
      // create the instance if it does not exist
      if(!isset(self::$instance)) {
         // the MYSQL_* constants should be set to or
         //  replaced with your db connection details
         self::$instance = new MySQLi(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB);
         if(self::$instance->connect_error) {
            throw new Exception('MySQL connection failed: ' . self::$instance->connect_error);
         }
      }
      // return the instance
      return self::$instance;
   }
}
?>

Usage:
$result = DB::get()->query("SELECT * FROM ...");

from this page.
Was This Post Helpful? 4
  • +
  • -

#3 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3724
  • View blog
  • Posts: 12,990
  • Joined: 12-December 12

Re: connection to mysql - best practice

Posted 18 March 2013 - 01:45 PM

I still don't see the need for global $db though. The object could be created where necessary and passed as a parameter to other methods.
Was This Post Helpful? 0
  • +
  • -

#4 P.Conrad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 10-March 13

Re: connection to mysql - best practice

Posted 18 March 2013 - 04:12 PM

Thanks, it helps a lot.

Could you also give me brief explanation why exactly globals are bad?
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3724
  • View blog
  • Posts: 12,990
  • Joined: 12-December 12

Re: connection to mysql - best practice

Posted 18 March 2013 - 04:29 PM

View PostP.Conrad, on 18 March 2013 - 04:12 PM, said:

Thanks, it helps a lot.

Could you also give me brief explanation why exactly globals are bad?

There are a number of reasons outlined quite well here, and reproduced here:

Quote

Non-locality -- Source code is easiest to understand when the scope of its individual elements are limited. Global variables can be read or modified by any part of the program, making it difficult to remember or reason about every possible use.

No Access Control or Constraint Checking -- A global variable can be get or set by any part of the program, and any rules regarding its use can be easily broken or forgotten. (In other words, get/set accessors are generally preferable over direct data access, and this is even more so for global data.) By extension, the lack of access control greatly hinders achieving security in situations where you may wish to run untrusted code (such as working with 3rd party plugins).

Implicit coupling -- A program with many global variables often has tight couplings between some of those variables, and couplings between variables and functions. Grouping coupled items into cohesive units usually leads to better programs.

Concurrency issues -- if globals can be accessed by multiple threads of execution, synchronization is necessary (and too-often neglected). When dynamically linking modules with globals, the composed system might not be thread-safe even if the two independent modules tested in dozens of different contexts were safe.

Namespace pollution -- Global names are available everywhere. You may unknowingly end up using a global when you think you are using a local (by misspelling or forgetting to declare the local) or vice versa. Also, if you ever have to link together modules that have the same global variable names, if you are lucky, you will get linking errors. If you are unlucky, the linker will simply treat all uses of the same name as the same object.

Memory allocation issues -- Some environments have memory allocation schemes that make allocation of globals tricky. This is especially true in languages where "constructors" have side-effects other than allocation (because, in that case, you can express unsafe situations where two globals mutually depend on one another). Also, when dynamically linking modules, it can be unclear whether different libraries have their own instances of globals or whether the globals are shared.

Testing and Confinement - source that utilizes globals is somewhat more difficult to test because one cannot readily set up a 'clean' environment between runs. More generally, source that utilizes global services of any sort (e.g. reading and writing files or databases) that aren't explicitly provided to that source is difficult to test for the same reason. For communicating systems, the ability to test system invariants may require running more than one 'copy' of a system simultaneously, which is greatly hindered by any use of shared services - including global memory - that are not provided for sharing as part of the test.

Was This Post Helpful? 2
  • +
  • -

#6 P.Conrad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 10-March 13

Re: connection to mysql - best practice

Posted 19 March 2013 - 03:21 AM

hmm, actually it seems that only major con of globals is making your code less clear and more error-prone. TBH I was afraid that using globals is highly insecure but if thats not the case I'll probably stick to my global $db!

Oh, and I'm not really into passing mysqli object as method parameters since in almost every single class I need db connection and that would actually make my code less clear to me.
Was This Post Helpful? 0
  • +
  • -

#7 P.Conrad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 10-March 13

Re: connection to mysql - best practice

Posted 19 March 2013 - 06:08 AM

or actually, what the heck, lets follow trends! I created this singleton and it took me some time switching from global $db to $db = DB::get(); but fortunately its done by now.

One quick question tho. From efficiency point of view is it better to have $db as a class static variable and then in methods:
 $db = self::$db;  
or in every method which needs connection reference to our singleton ?
 private function(){ $db = DB::get(); ... 

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3724
  • View blog
  • Posts: 12,990
  • Joined: 12-December 12

Re: connection to mysql - best practice

Posted 19 March 2013 - 11:03 AM

View PostP.Conrad, on 19 March 2013 - 06:08 AM, said:

or actually, what the heck, lets follow trends! I created this singleton and it took me some time switching from global $db to $db = DB::get(); but fortunately its done by now.

One quick question tho. From efficiency point of view is it better to have $db as a class static variable and then in methods:
 $db = self::$db;  
or in every method which needs connection reference to our singleton ?
 private function(){ $db = DB::get(); ... 

I think you've missed the essential point :dontgetit:. We don't create a variable $db at all. We include the file that contains the class definition for DB, and whenever we need to use the connection we use statements like:

$result = DB::get()->query("SELECT * FROM ...");
If the connection doesn't already exist, this will create, and use it, in one go. If the connection does exist then this will continue to use the existing connection.

So, you drop $db altogether (excusing the pun..).
Was This Post Helpful? 1
  • +
  • -

#9 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3069
  • View blog
  • Posts: 10,750
  • Joined: 08-August 08

Re: connection to mysql - best practice

Posted 19 March 2013 - 06:51 PM

Of course in addition to not using globals, you shouldn't be using mysql functions at all. Read up on prepared statements using PDO or MySQLi, and remember that either can be treated as if it were MySQL. Be sure not to do that. (It looks like you might be:
$result = DB::get()->query("SELECT * FROM ...");

If that query string contained user supplied data it would defeat any prepared statement.

This post has been edited by CTphpnwb: 19 March 2013 - 06:57 PM

Was This Post Helpful? 1
  • +
  • -

#10 P.Conrad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 10-March 13

Re: connection to mysql - best practice

Posted 20 March 2013 - 05:19 AM

Okey, thank you guys! Please give a glimpse on my login module code if that's the way it should be

if(isset($_POST['nick']) && isset($_POST['pass']))
		{
			
			$stmt = DB::get() -> prepare("SELECT id_user FROM user where nick = ? and pass = ?");
			$stmt -> bind_param("ss", $_POST['nick'],  md5($_POST['pass']));
			$result = $stmt -> execute();
			
		if($result -> num_rows > 0) // good login and pass
else // bad login and/or pass 


If I'm using prepared statements should I use other security tricks against sql injections like escape_strings ? Or I'm totally safe ?
Was This Post Helpful? 0
  • +
  • -

#11 P.Conrad  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 19
  • Joined: 10-March 13

Re: connection to mysql - best practice

Posted 20 March 2013 - 06:51 AM

pardon guys, last code I was typing from my memory without checking (mea culpa :) )!

If you can verify this one:

$stmt = DB::get() -> prepare("SELECT verified FROM user where nick = ? AND pass = ?");
$stmt -> bind_param("ss", $_POST['nick'], md5($_POST['pass']));
$stmt -> execute();
$stmt-> store_result();
$stmt -> bind_result($verified);
$stmt -> fetch();

if($stmt -> num_rows == 0) // bad login and/or pass
elseif($verified == 0) // users not verified
else // users logged properly!


Was This Post Helpful? 0
  • +
  • -

#12 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3069
  • View blog
  • Posts: 10,750
  • Joined: 08-August 08

Re: connection to mysql - best practice

Posted 20 March 2013 - 12:33 PM

Prepared statements cannot be injected because all data is only treated as data: it cannot be executed. Only the prepared SQL gets executed.

SQL isn't the only security threat out there. By not salting your password before hashing you make it easier to use a rainbow table should anyone get a hold of your hashed and stored passwords.
Was This Post Helpful? 2
  • +
  • -

#13 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3730
  • View blog
  • Posts: 6,017
  • Joined: 08-June 10

Re: connection to mysql - best practice

Posted 21 March 2013 - 11:12 AM

You should also know that the MD5 hashing algorithm is nowhere near safe enough to be used for password hashing any more. It was OK 15 years ago, but these days, as far as securing passwords against hackers goes, you may as well just leave them in plain-text. You should upgrade your hashes to something like SHA2 or Whirlpool. (See the hash function.) And, like CTphpnwb says, salt them! The hash_hmac function is better in that regard than the plain hash function.

Or you could go all out and move straight to something like Bcrypt. It can be a bit more complicated, but things like PHPass make it simpler to use.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1