Page 1 of 1

Is it possible to Connect to 3 database?

Posted: Tue Aug 12, 2008 11:14 am
by zplits
Hello everyone. I'm a bit curious. I'm working on my thesis right now and need some advice regarding database. I have 4 sites. 3 managers site and 1 admin site. How many database should i use? I'm planning to use 4 database. Each site has it's own database, the admin site send queries to all 3 managers database. Is it possible to connect to 3 database?

If yes, can you please teach me how?

Re: Is it possible to Connect to 3 database?

Posted: Tue Aug 12, 2008 11:38 am
by Apollo
Sure, mysql_connect returns a connection resource. You can have multiple connections at the same time. Functions like mysql_query use the most recently opened connection by default, but you can specify the desired connection in each function call to make sure each query goes to the right database. For each connection, select the appropriate database once with mysql_select_db

By the way, if your different databases are all on the same server, you can also use just one connection and perform your queries with mysql_db_query, which accepts a database argument to specify on which database you want to perform your query (as opposed to mysql_query which operates on the last selected database).

Re: Is it possible to Connect to 3 database?

Posted: Tue Aug 12, 2008 11:49 am
by zplits
hello sir apollo, thanks for the quick response.

Really? i didn't know it is possible. I have 4 database in my server. Namely admin, fisher, cai, and del. Could you please post a sample code on how to connect in to 3 database? Is it also posible to query them? if yes, please post a sample code here sir.

I have another question, which one is better for encrypting passwords in user logins. md5 function of php or password() function of MySQL?

thanks a lot

Re: Is it possible to Connect to 3 database?

Posted: Tue Aug 12, 2008 1:01 pm
by Apollo
Well if these databases are all on the same server (and all accessible with one SQL user account), you could do with just one connection. But just to demonstrate, here's how you could do it if you had two servers: (it obviously works the same for more servers).

Code: Select all

<?php
 
$connection1 = mysql_connect( "admin.yourdomain.com",  "Mike", "kr25m9h3" ); // this connects to the admin server (using Mike's login)
$connection2 = mysql_connect( "fisher.yourdomain.com", "John", "f7x6bjgu" ); // this connects to the fisher server (using John's login)
 
mysql_select_db( "db_admin", $connection1 );  // this selects the "db_admin" database on the admin server
mysql_select_db( "db_fisher", $connection2 ); // this selects the "db_fisher" database on the fisher server
 
$result1 = mysql_query("SELECT * FROM sometable",$connection1);    // this performs a query on the db_admin database
$result2 = mysql_query("SELECT * FROM anothertable",$connection2); // this performs a query on the db_fisher database
 
?>
If you have one server, you can still make different connections to it (using different SQL logins), and select a different database for each connection:

Code: Select all

<?php
 
$connection1 = mysql_connect( "your.domain.com", "Mike", "kr25m9h3" ); // this connects to the server using Mike's login
$connection2 = mysql_connect( "your.domain.com", "John", "f7x6bjgu" ); // this connects to the server using John's login
 
mysql_select_db( "db_admin", $connection1 );  // this selects the "db_admin" database for the first connection
mysql_select_db( "db_fisher", $connection2 ); // this selects the "db_fisher" database for the second connection
 
$result1 = mysql_query("SELECT * FROM sometable",$connection1);    // this performs a query on the db_admin database
$result2 = mysql_query("SELECT * FROM anothertable",$connection2); // this performs a query on the db_fisher database
 
?>
And here's how to do it with one connection, using mysql_db_query for each query, instead of explicitly selecting different databases first. Of course this requires that Mike has access to both databases.

Code: Select all

<?php
 
$connection = mysql_connect( "your.domain.com", "Mike", "kr25m9h3" ); // this connects to the server using Mike's login
 
$result1 = mysql_db_query("db_admin","SELECT * FROM sometable",$connection);    // this performs a query on the db_admin database
$result2 = mysql_db_query("db_fisher","SELECT * FROM anothertable",$connection); // this performs a query on the db_fisher database
 
?>
zplits wrote:I have another question, which one is better for encrypting passwords in user logins. md5 function of php or password() function of MySQL?
MySQL's password() function is only meant to encrypt passwords for SQL accounts (for authentication on the database server). For your own purposes it's better to use a common hash function like md5 or sha1 (which are implemented by both PHP as MySQL by the way). See also the note here.

Re: Is it possible to Connect to 3 database?

Posted: Tue Aug 12, 2008 8:43 pm
by zplits
:D Wow, that's how it is. Now i know. Thanks for the valuable info sir. I'm very thankful.
Can you teach me on how to create a script for encrypting username and password in login form? For instance i want to register a new user, i'm hoping that the entered username and password stored in the database will be encrypted. And when the user logs in. The script will decrypt his/her username or password.

By the way sir, is it better if i also encrypt the username?

Re: Is it possible to Connect to 3 database?

Posted: Wed Aug 13, 2008 2:51 am
by Apollo
Unless you consider usernames to be privacy-sensitive, I wouldn't encrypt them. And if people visiting your site will also be able to see eachother's usernames, then it's of course useless anyway.

Furthermore, you should not store the passwords encrypted in the database, but hashed (although this is sometimes called one-way encryption). This means, you don't store the actual password, but just some checksum, e.g. md5 or sha1 (I would go for sha1). These are one way hashing algorithms that make it impossible to retrieve the original passwords, even if you have full access to the db.

To make various forms of attacks even more difficult you can add some random string to the passwords before hashing them, this is usually called a "salt" string. So instead of storing sha1($password) you store sha1($password.$salt) where $salt can be something like "h87K3_d2!dXkfDnCL". Be sure of course to use the same $salt when storing and verifying a password.

When creating an account you only store the checksum, not the password itself. And when verifying a login attempt you compare the checksum of the entered password with the checksum in your db. If you google around there are plenty example login scripts, but it's really not difficult. The essential query will be something like:

Code: Select all

$user = mysql_real_escape_string($_POST['username']);
$pwhash = sha1($_POST['password'].$salt);
mysql_query("SELECT * FROM userstable WHERE user='$user' AND password='$pwhash' ");
Note the mysql_real_escape_string() which is to protect against smartasses entering x'; DELETE * FROM userstable; # as their username ;)

Re: Is it possible to Connect to 3 database?

Posted: Wed Aug 13, 2008 3:25 am
by zplits
Thanks for the response apollo. I'll try it now, and post here about the progress. okay? thanks again.

Re: Is it possible to Connect to 3 database?

Posted: Wed Aug 13, 2008 3:33 am
by zplits
Sir, i think i didn't got it right. here is my checklogin.php code

Code: Select all

<?php
    //start session
    session_start();
    
    //include mysql_connection.php to connect to the database
    require_once("mysql_connection.php"); 
    
// Define $myusername and $mypassword
$myusername=$_POST['username'];
$mypassword=$_POST['password'];
 
// To protect MySQL injection
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);
 
$user = mysql_real_escape_string($_POST['username']);
$pwhash = sha1($_POST['password'].$salt);
mysql_query("SELECT * FROM userstable WHERE user='$user' AND password='$pwhash' ");
 
    // Generate a random salt
    $salt = substr(sha1(uniqid(rand(), true)), 0, 5);
    
    // Hash password
    $secure_password = sha1($salt . sha1($mypassword));
 
$sql="SELECT id, firstName, lastName FROM $tbl_name WHERE loginName='$myusername' and passWord='$secure_password'";
$result=mysql_query($sql);
 
//code to check for errors
if (!$result) {
     echo "Could not successfully run query ($sql) from DB: " . mysql_error();
     exit;
}
 
$count=mysql_num_rows($result);
    if ($count==1) {
          $data = mysql_fetch_assoc($result);
          $_SESSION['user'] = array('id' => $data['id'], 'realName'=>$data['firstName'] . ' ' .                                       $data['lastName']);
          header("location:main.php");
          exit();
    } 
    else {
         $error = "<b>ERROR</b>: Invalid Username / Password";
    }
    
    mysql_close();
?>
here is the update.php code

Code: Select all

<?php
    //start session
    session_start();
    
    //include mysql_connection.php to connect to the database
    require_once("mysql_connection.php");
 
    if(!isset($_SESSION['user'])){
    header("location:index.php");
    }       
    
    // Define $myusername and $mypassword
    $newLoginName=$_POST['newLoginName'];
    $newFirstName=$_POST['newFirstName'];
    $newLastName=$_POST['newLastName'];
    $newEmailAdd=$_POST['newEmailAdd'];
    
    //form processing
    $newPassWord = $_POST['newPassWord'];
    $newPassWord = mysql_real_escape_string($newPassWord);
    
    // Generate a random salt
    $salt = substr(sha1(uniqid(rand(), true)), 0, 5);
    
    // Hash password
    $secure_password = sha1($salt . sha1($newPassWord));
 
    
    // update data in mysql database
    $sql="UPDATE $tbl_name SET loginName = '$newLoginName', passWord = '$secure_password', firstName = '$newFirstName', lastName='$newLastName', emailAdd='$newEmailAdd'";
    $result=mysql_query($sql);
    
    // if successfully updated.
    if($result){
        $updateSuccess = "<b>SUCCESS</b>: Your account has been updated.";
        
        $sql="SELECT * FROM $tbl_name";
        $result = mysql_query($sql);
    
        $row=mysql_fetch_array($result);
        $dusername=$row['loginName'];
        $dpassword=$row['passWord'];
        $dfirstName=$row['firstName'];
        $dlastName=$row['lastName'];
        $demailAdd=$row['emailAdd'];
    }   
    else {
        $updateError = "<b>ERROR</b>: Invalid Username / Password"; 
    }
?>
Please help me. I really got to get this going. My time is running out. Thanks for all your help

Re: Is it possible to Connect to 3 database?

Posted: Wed Aug 13, 2008 3:55 am
by onion2k
Please stop asking people to write code for you. We don't care if you have a deadline, this forum is not for people who want to get code written for free. If that's what you need I'll move this thread to the Volunteer Work folder, or if you're willing to pay then I'll move it to the Job Hunt folder. Otherwise stick to asking proper questions about how to correct specific code problems.

Re: Is it possible to Connect to 3 database?

Posted: Wed Aug 13, 2008 4:04 am
by zplits
I'm so sorry. My apology