Messenger Sql Alternatives.

[remenissions]

I created had created a website between me and a couple friends. On the website I made an Ajax\Javascript\Php Instant Messenger. It works fine for something small. Each user has a contact list and currently I am saving each individual conversation between each user into an Sql table { senderId, RecieverId, Message } and reading it back. Now I know its no problem for something small but obviously it leaves security risks and sends too many requests to the server. Any of you guys have any alternative Idea's for this messenger? I don't necessary need to save the conversations, I'm simply using that method to send and receive them. I have a buddy who does his messenger through J.S sockets but I would like to keep to php if possible. Any comments or suggestions would be much appreciated. 

 

 

Uploaded with ImageShack.us

[Psycho]

What security risks are you concerned with? If it is that someone could exploit the code to access the data, then that is just something you have to code against just as any site with a database. If you want the data deleted then delete the messages when someone retrieves a message meant for them. BUt, that limits your ability to resolve errors if they occur.

 

As for sending too many requests, you should only be returning the new information to the user. So, if there are no new messages, the request will take very little bandwidth and resources. That assumes the application is built to be efficient.

[remenissions]

Thank you for your response, yeah I realise i'm going to have to code against sql injections and stuff still I haven't gotten that far with the project yet. Atm i have it recieving all the data between the two users every 300ms. What do you think?

[Psycho]

ALL the data? You should only have it retrieve the NEW data. When the client makes a request to the server it should send a timestamp of the last time a request was made. Then the server would query for any messages with a newer timestamp. You'll need to add another field to your table to store the timestamp of when messages are saved. When a response is made back to the user (whether there was new data or not) you should return the timestamp used to query the DB. Don't use a JS timestamp or you could have a message missed due to a slight variance

[remenissions]

Ok that makes sense thanks for the tip. for javascript sending the request im using setinterval(ajax,300) that sound fine? I wasn't sure if a continuously running script like that to check for updated messages would be ok running continuously? ik this is php so if you're not sure np. I'm jw, I'm fairly new to coding I spend a lot of time reading on my own to figure out things as Ideas come to my mind. As far as this I just could not stand using a some one elses been trying to use my own stuff

[Psycho]

300 millisecond seems a little aggressive to me. I would probably go with every second or, at a minimum, every 500ms. But, be sure to set your request up so that if a request is pending it doesn't send another one. There are always instances where due to traffic on the net that a request may take a second or two.

[remenissions]

Ok I'll see what I can come up with and post back afterwords, Thank you for your advice :]

[remenissions]

Hey, I got the requests and everything set up, Javascript side works fine and only sends a request every 1 second if a request is not already sent. However I'm curious on some opinions as far as the php request goes. Every one second this Php script will be reloaded. I'm hoping it isn't too many requests to where it'll be hard on the server. If so any opinions on what I can do?

 

<?php session_start(); 
/* Checking User */
	if(!isset($_SESSION['User_Name']) && !isset($_COOKIE["VanityUser"])) {		
	header('Location: INTENTIONALLY BLANK');	
}

if(isset($_COOKIE["VanityUser"]) != '') 
	$User = $_COOKIE["VanityUser"];
else 
	$User = $_SESSION['User_Name'];	
/* Checking User */
?>

<?php require_once("DATABASE");  							
/* Gets User && Friend Id# */
$userIdresult = mysql_query("SELECT User_Id FROM User_Info where Username ='".$User."'");
	while($row = mysql_fetch_array($userIdresult)) {
		$userId = $row['User_Id']; 
	}			
$friend = $_GET["Friend"];	
/* /Gets User && Friend Id# */

/* Recieve Conversation Once Upon Open */	
if($_GET["load"] != '') { 	
	$result = mysql_query("SELECT * FROM Instant_Messenger WHERE Sender = '".$userId."' AND Reciever = '".$friend."' OR (Sender = '".$friend."' AND Reciever = '".$userId."') ORDER BY IM_Id");
		while($row = mysql_fetch_array($result)) {
			echo $row['Rank'] . $row['Sender'] . ": " . $row['Instant_Message'] . "<br>";
		}		
} else { 
/* /Recieve Conversation Once Upon Open */

/* Recieve And Expire All New Messages */
	$result = mysql_query("SELECT * FROM Instant_Messenger WHERE Sender = '".$userId."' AND Reciever = '".$friend."' OR (Sender = '".$friend."' AND Reciever = '".$userId."') AND (Sviewed = '0' OR Rviewed = '0')  ORDER BY IM_Id");
		while($row = mysql_fetch_array($result)) {							
			if($userId==$row['Sender'] && $row['Sviewed'] == 0) {					
				echo $row['Rank'] . $row['Sender'] . ": " . $row['Instant_Message'] . "<br>";					
				mysql_query("UPDATE Instant_Messenger SET Sviewed = -1 WHERE IM_Id = '".$row['IM_Id']."'"); //Updates Sender to viewed by Id
			}
			if($userId==$row['Reciever'] && $row['Rviewed'] == 0) {
				echo $row['Rank'] . $row['Sender'] . ": " . $row['Instant_Message'] . "<br>";
				mysql_query("UPDATE Instant_Messenger SET Rviewed = -1 WHERE IM_Id = '".$row['IM_Id']."'"); //Updates Reciever to viewed by Id		
			}		
		}
}
/* Recieve And Expire All New Messages */

?>

[remenissions]

That other code didn't work quite right and it won't let me edit it, Sorry if you guys already read through it... This code right here is what I have and it works. Again, I am just worried with this repeating every 1000ms it could be too many requests for the server.

 

<?php session_start(); 
/* Checking User */
	if(!isset($_SESSION['User_Name']) && !isset($_COOKIE["VanityUser"])) {		
	header('Location: BLANK');	
}

if(isset($_COOKIE["VanityUser"]) != '') 
	$User = $_COOKIE["VanityUser"];
else 
	$User = $_SESSION['User_Name'];	
/* Checking User */
?>

<?php require_once("DATABASE");  							
/* Gets User && Friend Id# */
$userIdresult = mysql_query("SELECT User_Id FROM User_Info where Username ='".$User."'");
	while($row = mysql_fetch_array($userIdresult)) {
		$userId = $row['User_Id']; 
	}			
$friendIdresult = mysql_query("SELECT User_Id FROM User_Info where Username ='".$_GET["Friend"]."'");
	while($row = mysql_fetch_array($friendIdresult)) {
		$friendId = $row['User_Id']; 
	}						
/* /Gets User && Friend Id# */

/* Recieve Conversation Once Upon Open */	
	if($_GET["load"] != '') { 				
		$result = mysql_query("SELECT * FROM Instant_Messenger WHERE Sender = '".$userId."' AND Reciever = '".$friendId."' OR (Sender = '".$friendId."' AND Reciever = '".$userId."') ORDER BY IM_Id");
		while($row = mysql_fetch_array($result)) {	
			if($row['Sender'] == $userId)			
				echo $row['Rank'] . $User . ": " . $row['Instant_Message'] . "<br>";		
			else 						
				echo $row['Rank'] . $_GET["Friend"] . ": " . $row['Instant_Message'] . "<br>";
		}
	}			 
/* /Recieve Conversation Once Upon Open */

/* Check If New Message Exists And Sends */
	$query = "SELECT * FROM Instant_Messenger WHERE Sender = '".$userId."' AND Reciever = '".$friendId."' OR (Sender = '".$friendId."' AND Reciever = '".$userId."') AND (Sviewed = '0' OR Rviewed = '0') ORDER BY IM_Id";
	$result = mysql_query($query) or die(mysql_error());		
	$New_Message = mysql_num_rows($result);	

	if($New_Message != 0)  {
		while($row = mysql_fetch_array($result)) {
			if($userId==$row['Sender'] && $row['Sviewed'] == 0) {					
				echo $row['Rank'] . $User . ": " . $row['Instant_Message'] . "<br>";					
				mysql_query("UPDATE Instant_Messenger SET Sviewed = -1 WHERE IM_Id = '".$row['IM_Id']."'"); //Updates Sender to viewed by Id
			}
			else if($userId==$row['Reciever'] && $row['Rviewed'] == 0) {
				echo $row['Rank'] . $_GET["Friend"] . ": " . $row['Instant_Message'] . "<br>";
				mysql_query("UPDATE Instant_Messenger SET Rviewed = -1 WHERE IM_Id = '".$row['IM_Id']."'"); //Updates Reciever to viewed by Id		
			}
		}
	} 
/* /Check If New Message Exists And Sends */

?>

[Psycho]

That other code didn't work quite right and it won't let me edit it, Sorry if you guys already read through it... This code right here is what I have and it works. Again, I am just worried with this repeating every 1000ms it could be too many requests for the server.

 

Without having access to your server to do benchmarking, knowing how many concurrent users there will be (for your site and the other sites on that server - if it is a shared server) there is no way to tell.

 

Having said that, you should probably make the above script as efficient as possible. Some things you could do:

 

1. The top section to get the user name seems odd. You first make sure that the session var and cookie var are both set, if not you redirect to blank (should be an exit() statement right after that), but then you set $User to the cookie if it is not blank, else the session var. Why are you mandating that the cookie must be set if it can be empty?

 

2. You are running two queries to get the user id of the user and the friend. a) Why are you passing the user names to the page?! You should be using the user ids to begin with!!! Database transactions are expensive in terms of performance and you are running those two ever time when they shouldn't be needed.

 

3. I'm not understanding the logic between the sections to receive conversations upon open vs. new messages. You should just need one query to get all the relevant massages after the last time the user got the messages. Plus, you should only have the query return the fields you need - DON'T USE '*' if you don't need all the fields. This goes for any query you run.

 

4. The section at the end to update the status is completely unnecessary and a huge waste of resources. You should NEVER EVER run queries in loops. First of all, even if you needed to update the viewed status of those records (which you don't) you don't need to loop through each record to do it. You could just run a single UPDATE query using the same WHERE conditions as the previous select query. But, you shouldn't be using a "viewed" status anyway. As I already stated you just need the user to pass a timestamp of the last time they retrieved the messages and pull all messages from that time period forward.

 

As it stands now there are six unique queries in that script and there is no telling how many would actually be run on each page load since two of them are in loops. However, you only need one query for for retrieving new messages and one to add a message (then an optional uery to get the names if not already stored in session). That's it. By the way, I don't see anything in there to add a new message.

 

Using either session vars or variable passes on the query string (i.e. $_GET vars) you should have available to the script the user id, the friend id and the timestamp when the user last access the messages between him and the friend. The timestamp can be null if they are accessing the messages the first time, but that means if they then send a new message a week later they will be getting the entire history. So, you can either store a timestamp for the user-to-firend last access time to use for future conversations (which would require another DB transaction) or come up with an archiving rule (i.e. only pull messages as far back as 24 hours). So, when the messages are retrieved, you would define a new timestamp and pass it back to the javascript. The javascript would then pass that timestamp back on the next request.

 

Anyway, here is some mock code of how it could work more efficiently. The first time the script is run for a user in a session, there is one query to get the names of the users to store in the session. Then there is a second query that is run ONLY if there is a message to add. Then there is a third query that is run to get NEW messages only. So, 99% of the time only one query will be run

 

Again, this is just something I threw together and I have not tested any of it. There are likely some syntax and possibly even some logic errors. But, the overall concept is sound

<?php

//Get user ID from session and friend ID from URL
$user_id = (isset($_SESSION['user_id'])) ? (int) $_SESSION['user_id'] : 0;
$friend_id = (isset($_GET['friend_id'])) ? (int) $_SESSION['friend_id'] : 0;

//If user or friend IDs are invalid stop
if($user_id==0 || $friend_id==0)
{
    echo "Invalid parameters";
    exit();
}

//Add new message if passed
$new_message = (isset($_GET['new_message'])) ? mysql_real_escape_string(trim($_GET['new_message'])) : false;
if($new_message)
{
    $query = "INSERT INTO Instant_Messenger
                  (Sendeer, Receiver, Rank, Instant_Message)
              VALUES
                  ({$user_id}, {$friend_id}, {$rank}, {$new_message})"
    $result = mysql_query($query);
    //NOTES:
    // - not sure what rank represents or how it is defined
    // - The table should include a field for created_date that is automatically populated on record creation
}

//Query the names ONLY if not already saved in session
//Used to display the names in messages
if(!isset($_SESSION['names'][$userId]) || !isset($_SESSION['names'][$friend_id]))
{
    $query = "SELECT User_Id, Username
              FROM User_Info
              WHERE User_Id IN ('{$userId}', {$friendId})";
    $result = mysql_query($query);
    while($row = mysql_fetch_assoc($result))
    {
        $_SESSION['names'][$row['User_Id']] = $row['Username'];
    }
}

//Create query to retrieve new messages since last access (if passed)
$last_access = (isset($_GET['last_access'])) ? (int) $_GET['last_access'] : false;
$last_access_sql = ($last_access) ? ' AND created_date > ' . date('Y-m-d', $last_access) : '';
//Create timestamp for this access
$this_access = time();
$this_access_sql = ' AND created_date > ' . date('Y-m-d', $this_access);

$query = "SELECT Sender, Receiver, Rank, Instant_Message
          FROM Instant_Messenger
          WHERE Sender   IN ('{$userId}', {$friendId})
            AND Reciever IN ('{$userId}', {$friendId})
            {$last_access_sql}
            {$this_access_sql}
          ORDER BY created_date";
$result = mysql_query($query);


//Display the new messages
while($row = mysql_fetch_array($result))
{
    $sender_name = $_SESSION['names'][$row['Sender']];
    echo "{$row['Rank']} {$sender_name}: {$row['Instant_Message']}<br>\n";
}

//Add a parameter on the return value for the timestamp of this access
//The javascript should parse it off of the result and store it to send on the next request

echo "-LAST_ACCESS-{$this_access}";

?>

[remenissions]

Hey mjdamato, again I appreciate your response. I am using my own server to run it on and there will be roughly 50-100 people using the site, not all at once so the traffic won't be too bad. I'm just trying to expand what I know and make this the best I can make it. I went through and re-changed a lot of things to make the site revolve around Id's instead of passing usernames. I worked with what you showed me and came up with something a little less clean than yours however it does do the trick and hopefully is not too bad. I did have a few questions about some symbols, not really sure how ':' or '?' is used, I've only been coding a couple months now. I'll probably google it later. Anyways here's what I came up with for the final code. It can query a maximum of 3 times. One to get the usernames and set them as sessions as you demonstrated (I really liked that Idea, never would have thought of it). Another only when they first click the user they wish to speak to, it'll load all previous chat. And then one upon each request to determine if there are any new Messages. Anyways here's the code if you have any opinions feel free to give them, I'm just trying to improve what I do know so any help is much appreciated. Also timestamps worked like a charm. I parsed it in javascript and used it to show when the last message was sent from each individual user, so I thought that was really neat

 

<?php session_start(); 
/* Checking User */
	if(!isset($_SESSION['User_Name']) && !isset($_COOKIE["VanityUser"])) {		
	exit();	
}

if(isset($_COOKIE["VanityUser"]) != '') 
	$userId = $_COOKIE["VanityUser"];
else 
	$userId = $_SESSION['User_Name'];	
	$friendId = $_GET["friendId"]; 
/* Checking User */
?>

<?php require_once("DATABASE");  	
/* Set Usernames */
if(!isset($_SESSION['names'][$userId]) || !isset($_SESSION['names'][$friendId])) {
   	$query = "SELECT User_Id, Username FROM User_Info WHERE User_Id IN ('{$userId}', {$friendId})";
   	$result = mysql_query($query);
    while($row = mysql_fetch_assoc($result)) 
        $_SESSION['names'][$row['User_Id']] = $row['Username'];    
}
/* Set Usernames */

/* Access Stamps */	
if(!isset($_GET['laststamp']))
	$last_Access="0000000000";
else 		
	$last_Access=$_GET['laststamp'];
/* /Access Stamps */	

/* Recieve Conversation Once Upon Open */ 
	if($_GET["load"] != '') { 				
		$result = mysql_query("SELECT Rank, Sender, Reciever, Instant_Message FROM Instant_Messenger WHERE Sender IN ('{$userId}', {$friendId}) AND Reciever IN ('{$userId}', {$friendId}) ORDER BY Created_Date");
		while($row = mysql_fetch_array($result)) {					
   			$sender_name = $_SESSION['names'][$row['Sender']];
			echo "{$row['Rank']} {$sender_name}: {$row['Instant_Message']} <br>\n";
		}
	} else {			
/* /Recieve Conversation Once Upon Open */

/* Check If New Message Exists And Sends */ 
	$result = mysql_query("SELECT * FROM Instant_Messenger WHERE Sender IN ('{$userId}', {$friendId}) AND Reciever IN ('{$userId}', {$friendId}) AND Created_Date > $last_Access ORDER BY Created_Date");		
		while($row = mysql_fetch_array($result)) {					
   			$sender_name = $_SESSION['names'][$row['Sender']];
			echo "{$row['Rank']} {$sender_name}: {$row['Instant_Message']} <br>\n";
			$last_Access=$row['Created_Date'];		
		} 
	}
/* /Check If New Message Exists */

/* Echo Time Stamp Hidden */ 
	echo "<span style='display:none;'>$last_Access</span>";
/* /Echo Time Stamp Hidden */
?>