• (2 Pages)
  • +
  • 1
  • 2

Creating a simple PM system MySQL and PHP Required Rate Topic: ***-- 2 Votes

#1 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 110
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Post icon  Posted 23 May 2008 - 09:58 AM

Several people ion the PHP section have asked about how to make a Private Messaging system, whether for a game, forum, Etc. Private Messaging is extremely simple to set up, and I will show it here.


ok, since we need to connect to the Database on each page, i've created one called "config.php".
<?php
$localhost = "$localhost";
$mysqlusername  = "$mysqlusername";
$mysqlpassword  = "$mysqlpassword";
$db = "$db";
$con = mysql_connect($localhost, $mysqlusername, $mysqlpassword);
mysql_select_db("$db", $con);
?> 


First off, we need to create a table called "messages" in the DB we have selected. it will have the columns "to_user", "message", and "from_user", "sent_deleted", and "deleted". "sent_deleted" is for a user to delete the PM after they have sent it, whereas "deleted" tells us that the pm has been deleted by the receiver. Neither of the last two are necessary, but I like to keep them in the table for reference.
<?php
//Connect to the Database
require("config.php");
//query to create the Table
mysql_query("CREATE TABLE messages(
 to_user VARCHAR(30),
 from_user VARCHAR(30),
 deleted VARCHAR(3) DEFAULT no,
 sent_deleted VARCHAR(3) DEFAULT no, 
 message VARCHAR(1000))")
 or die(mysql_error());  

echo "Table Created!";

?>


Secondly, we need to create a form to add the message into the database. I call this "sendpm.php".
<?php    
session_start();
require("config.php");

$message = $_POST['forward2'];
 if (isset($_POST['submit']))
{
// if the form has been submitted, this inserts it into the Database 
  $to_user = $_POST['to_user'];
  $from_user = $_POST['from_user'];
  $message = $_POST['message'];
  mysql_query("INSERT INTO messages (to_user, message, from_user) VALUES ('$to_user', '$message', '$from_user')")or die(mysql_error());
  echo "PM succesfully sent!"; 
}
else
{
    // if the form has not been submitted, this will show the form
?>
<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<table border="0">
<tr><td colspan=2><h3>Send PM:</h3></td></tr>
<tr><td></td><td>
<input type="hidden" name="from_user" maxlength="32" value = <?php echo $_SESSION['username']; ?>>
</td></tr>
<tr><td>To User: </td><td>
<input type="text" name="to_user" maxlength="32" value = "">
</td></tr>
<tr><td>Message: </td><td>
<TEXTAREA NAME="message" COLS=50 ROWS=10 WRAP=SOFT></TEXTAREA>
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="submit" value="Send Message">
</td></tr>
</table>
</form>
<?php
}
?>


I also decided to make an outbox, so users can view PMs they have sent.
<?php  
session_start();
require("config.php");

$user = $_SESSION['username'];
if (isset($_POST['delete'])) {
    $id = $_POST['id'];  
    mysql_query("UPDATE messages SET sent_deleted = 'yes' WHERE from_user = '$user' AND id = '$id'")or die(mysql_error());
    echo "Message succesfully deleted from your outbox.";
}
$user = $_SESSION['user'];
$sql = mysql_query("SELECT * FROM messages WHERE from_user = '$user' AND sent_deleted = 'no'")or die(mysql_error());

while($row = mysql_fetch_array( $sql ))
{
/* I have set each element into it's OWN echo statement for easy readind.
 however it is possible to create it in one echo statement like the following:
 echo "Message ID#: ".$row['id'];
*/
  echo "<table border=1>";
  echo "<tr><td>";
  echo "Message ID#: ";
  echo $row['id'];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "To: ";
  echo $row['to_user'];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "From: ";
  echo $row['from_user'];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "Message: ";
  echo $row[message];
  echo "</td></tr>";
  echo "</br>";
?>

<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<table border="0">
<tr><td colspan=2></td></tr>
<tr><td></td><td>
<input type="hidden" name="id" maxlength="5" value = "<?php echo $row['id']; ?>">
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="delete" value="Delete PM # <?php echo $row['id']; ?> from outbox">
</td></tr>
</table>
</form>

<?php
}
  echo "</table>";
  echo "</br>";
?>


Lastly, we need the actual inbox, where users can view Private Messages that have been sent to them.
<?php
session_start();
require("config.php");

$user = $_SESSION['user'];

if (isset($_POST['view_old'])) {
$user = $_SESSION['user'];
$query = mysql_query("SELECT * FROM messages WHERE to_user = '$user' AND deleted = 'no'")or die(mysql_error());
while($row2 = mysql_fetch_array($query))
{ 
  echo "<table border=1>";
  echo "<tr><td>";
  echo "Message ID#: ";
  echo $row2['id'];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "To: ";
  echo $row2['to_user'];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "From: ";
  echo $row2['from_user'];
  echo " ";
  echo "</td></tr>";
  echo "<tr><td>";
  echo "Message: ";
  echo bb ($row2['message']);
  echo "</td></tr>";
  echo "</br>";
?>
<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<table border="0">
<tr><td colspan=2></td></tr>
<tr><td></td><td>
<input type="hidden" name="id" maxlength="32" value = "<?php echo $row2['id']; ?>">
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="delete" value="Delete PM # <?php echo $row2['id']; ?>">
</td></tr>
</table>
</form>
<?php
}
}

if (isset($_POST['delete'])) {
$id = $_POST['id'];
$user = $_SESSION['username'];
$sql = mysql_query("UPDATE messages SET deleted = 'yes' WHERE id = '$id' AND to_user = '$user'")or die(mysql_error());
echo "Your message has been succesfully deleted.";
}


while($row = mysql_fetch_array($sql))
{ 
$user = $_SESSION['user'];
  echo "<table border=1>";
  echo "<tr><td>";
  echo "Message ID#: ";
  echo $row[id];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "To: ";
  echo $row[to_user];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "From: ";
  echo $row[from_user];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "Message: ";
  echo $row[message];
  echo "</td></tr>";
  echo "</br>";
  mysql_query("UPDATE messages SET read_yet = 'yes' WHERE to_user = '$user' AND id ='$row_id'")or die(mysql_error());
?>

<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<table border="0">
<tr><td colspan=2></td></tr>
<tr><td></td><td>
<input type="hidden" name="id" maxlength="32" value = "<?php echo $row['id']; ?>">
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="delete" value="Delete PM # <?php echo $row['id']; ?>">
</td></tr>
</table>
</form>

<?

}
echo "</table>";
?>






(NOTE: this is an extremely simple PM system with NO security against SQL Injection. If you wish to prevent SQL Injection, I suggest you read some tutorials.)



Please comment on this and let me know how good/bad of a tutorial this is =) thank you.

This post has been edited by JBrace1990: 24 May 2008 - 04:39 PM


Is This A Good Question/Topic? 2
  • +

Replies To: Creating a simple PM system

#2 Spikey1989  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 30-May 08

Posted 02 June 2008 - 10:58 AM

I have a problem using this code im geting an error msg when i'm using it.

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /var/www/xtreme.minken.org/sarah/pages/in.php on line 58

Line 58 from inbox = while($row = mysql_fetch_array($sql))

what wrong? the code is the same as yours haven't changed a thing.

/Spikey
Was This Post Helpful? 0
  • +
  • -

#3 JBrace1990  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 110
  • View blog
  • Posts: 760
  • Joined: 09-March 08

Posted 03 June 2008 - 08:19 PM

whoops =/

anyway, it's fixed now.... I forgot to set $sql equal to anything.... it should work now =)

View PostJBrace1990, on 23 May, 2008 - 09:58 AM, said:

Lastly, we need the actual inbox, where users can view Private Messages that have been sent to them.
<?php
session_start();
require("config.php");

$user = $_SESSION['user'];

if (isset($_POST['view_old'])) {
$user = $_SESSION['user'];
$query = mysql_query("SELECT * FROM messages WHERE to_user = '$user' AND deleted = 'no'")or die(mysql_error());
while($row2 = mysql_fetch_array($query))
{ 
  echo "<table border=1>";
  echo "<tr><td>";
  echo "Message ID#: ";
  echo $row2['id'];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "To: ";
  echo $row2['to_user'];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "From: ";
  echo $row2['from_user'];
  echo " ";
  echo "</td></tr>";
  echo "<tr><td>";
  echo "Message: ";
  echo bb ($row2['message']);
  echo "</td></tr>";
  echo "</br>";
?>
<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<table border="0">
<tr><td colspan=2></td></tr>
<tr><td></td><td>
<input type="hidden" name="id" maxlength="32" value = "<?php echo $row2['id']; ?>">
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="delete" value="Delete PM # <?php echo $row2['id']; ?>">
</td></tr>
</table>
</form>
<?php
}
}

if (isset($_POST['delete'])) {
$id = $_POST['id'];
$user = $_SESSION['username'];
$sql = mysql_query("UPDATE messages SET deleted = 'yes' WHERE id = '$id' AND to_user = '$user'")or die(mysql_error());
echo "Your message has been succesfully deleted.";
}

$sql = mysql_query("SELECT * FROM messages WHERE to_user = '$_SESSION[username]'")or die(mysql_error());
while($row = mysql_fetch_array($sql))
{ 
$user = $_SESSION['user'];
  echo "<table border=1>";
  echo "<tr><td>";
  echo "Message ID#: ";
  echo $row[id];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "To: ";
  echo $row[to_user];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "From: ";
  echo $row[from_user];
  echo "</td></tr>";
  echo "<tr><td>";
  echo "Message: ";
  echo $row[message];
  echo "</td></tr>";
  echo "</br>";
  mysql_query("UPDATE messages SET read_yet = 'yes' WHERE to_user = '$user' AND id ='$row_id'")or die(mysql_error());
?>

<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<table border="0">
<tr><td colspan=2></td></tr>
<tr><td></td><td>
<input type="hidden" name="id" maxlength="32" value = "<?php echo $row['id']; ?>">
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="delete" value="Delete PM # <?php echo $row['id']; ?>">
</td></tr>
</table>
</form>

<?

}
echo "</table>";
?>

Was This Post Helpful? 0
  • +
  • -

#4 dangmnx  Icon User is offline

  • D.I.C Regular

Reputation: -1
  • View blog
  • Posts: 428
  • Joined: 10-April 09

Posted 21 July 2009 - 01:11 AM

I love the tutorial@!

but is there a way to make some sort of ALERT, when user have a new PM?

This post has been edited by dangmnx: 21 July 2009 - 01:27 AM

Was This Post Helpful? 0
  • +
  • -

#5 mulson  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 21-September 09

Posted 11 October 2009 - 05:20 PM

This tutorial is wonderful.I just encouter this error an error with the inbox. mysql_query("UPDATE users SET read_yet = 'yes' WHERE to_user = '$user' AND id ='$row_id'")or die(mysql_error());
The error is:
Unknown column 'read_yet' in 'field list'.
Was This Post Helpful? 0
  • +
  • -

#6 mulson  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 11
  • Joined: 21-September 09

Posted 11 October 2009 - 06:26 PM

Nice Tutorial, please may i know where you make use of the from_field....it is not included in the sendpm.php,where did you make use of it?
Was This Post Helpful? 0
  • +
  • -

#7 Guest_cmoney12051*


Reputation:

Posted 14 March 2010 - 08:12 PM

whenever i try and run the mysql script i get this error

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'no, sent_deleted VARCHAR(3) DEFAULT no, message VARCHAR(1000))' at line 4"


please help. thanks
Was This Post Helpful? 0

#8 jamied_uk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-April 10

Posted 11 April 2010 - 06:27 PM

like the tut m8 ty :D


am i wriyte in thinking when messages are deleted from the web page is it deleted from mysql or just marked as deleted?
Was This Post Helpful? 0
  • +
  • -

#9 jamied_uk  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 11-April 10

Posted 13 April 2010 - 02:03 AM

im getting blank pages (my password for mysql is empty as i have not got password) would this cause blank messages? also i am not logged in is this the reason or the password?


PS i am now using pms 2.3.0 (works great) to see it in action come to my site http://jnet2010.0fees.net/

This post has been edited by jamied_uk: 13 April 2010 - 03:40 AM

Was This Post Helpful? 0
  • +
  • -

#10 redrin  Icon User is offline

  • New D.I.C Head

Reputation: 4
  • View blog
  • Posts: 46
  • Joined: 01-May 10

Posted 01 May 2010 - 08:39 AM

View Postcmoney12051, on 14 March 2010 - 07:12 PM, said:

whenever i try and run the mysql script i get this error

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'no, sent_deleted VARCHAR(3) DEFAULT no, message VARCHAR(1000))' at line 4"


please help. thanks

i'm getting the same thing, no idea why.
Was This Post Helpful? 0
  • +
  • -

#11 Guest_Icarion*


Reputation:

Posted 11 May 2010 - 04:09 PM

View Postredrin, on 01 May 2010 - 07:39 AM, said:

View Postcmoney12051, on 14 March 2010 - 07:12 PM, said:

whenever i try and run the mysql script i get this error

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'no, sent_deleted VARCHAR(3) DEFAULT no, message VARCHAR(1000))' at line 4"


please help. thanks

i'm getting the same thing, no idea why.


Heyá Redrin,

I've got same problem, but i've found the solution to solve this problem, because most websites got the updated MySQL, so try to edit your install script for the database and try this code. This works for me let me know it helped you also.

<?php
//Connect to the Database
require("config.php");
//query to create the Table
mysql_query("CREATE TABLE messages(
 to_user VARCHAR(30),
 from_user VARCHAR(30),
 deleted VARCHAR(3) DEFAULT 'no',
 sent_deleted VARCHAR(3) DEFAULT 'no', 
 message VARCHAR(1000))")
 or die(mysql_error());  

echo "Table Created!";

?>


The tags @ no isn't allowed, always set the codes after default in side 'no' otherwise it wont work.
Was This Post Helpful? 0

#12 d.fronath  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 1
  • Joined: 19-August 11

Posted 19 August 2011 - 01:04 AM

The script is vulnerable to CWE-89: Improper Neutralization of Special Elements used in an SQL Command ('SQL Injection'), #1 in the "CWE/SANS TOP 25 Most Dangerous Software Errors" list.
When using plain old sql statements with mixin parameters (i.e. using string concatenation to build the statement) you have to ensure that all parameters are encoded properly. There's no excuse for not doing so, even in an entry-level tutorial. see http://bobby-tables.com/

  $to_user = mysql_real_escape_string($_POST['to_user'], $con);
  $from_user = mysql_real_escape_string($_POST['from_user'], $con);
  $message = mysql_real_escape_string($_POST['message'], $con);
  mysql_query("INSERT INTO messages (to_user, message, from_user) VALUES ('$to_user', '$message', '$from_user')")or die(mysql_error());

same with
$user = mysql_real_escape_string($_SESSION['user'], $con);
$query = mysql_query("SELECT * FROM messages WHERE to_user = '$user' AND deleted = 'no'")or die(mysql_error());
and any other statement where you put a (string) parameter into the statement.

Which characters have to escaped (i.e. marked as having no special meaning) depends on the encoding, especially the connection charset. The escaping via mysql_real_escape_string() is performed by the mysql client lib used by php. Therefore this client lib needs to know about the encoding in use. When using the mysql_* functions use mysql_set_charset() to let both the server and the client lib know about the charset you want to use, e.g.
mysql_set_charset('utf8', $con)
.

If you have e.g. a number, something like
SELECT ... WHERE id=$id
the escape_string/quote functions are not feasible but you have to make sure $id can only contains what you expect (a number in this case).

If possible use parametrized queries. MySQL offers an API for server side prepared statements + parametrized queries. The old mysql_* php module does not support that API, but there are other php modules like mysqli and especially PDO, http://docs.php.net/pdo

------

There's already some basic error handling in the scripts
mysql_query("...")or die(mysql_error());

a) at least also check whether mysql_connect() and mysql_select_db() succeeded
b) you shouldn't show the return value of mysql_error() to just any arbitrary user. Granted, this is a tutorial. But keep in mind that you give an attacker further information that will help him/her to compromise your system. see CWE-209: Information Exposure Through an Error Message
Was This Post Helpful? 1
  • +
  • -

#13 NathanCJohnson  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 22-October 11

Posted 22 October 2011 - 09:16 AM

How can I make it where it will shpw the user that it is viewed and show a count of how much is in inbox? Like in Gmail, it makes the "Inbox" text bold and adds (#) like: "Inbox (50)". And emails are also bold when they are unread. How could I do this?


Thanks in advance!
Was This Post Helpful? 0
  • +
  • -

#14 rpgmaker  Icon User is offline

  • D.I.C Head

Reputation: 2
  • View blog
  • Posts: 224
  • Joined: 02-October 11

Posted 07 November 2011 - 12:03 PM

This is no safe from sql injection.
Do not use on a live website.
You don;t check what there posting direct into the database .
They could enter anything into the database.
Was This Post Helpful? 0
  • +
  • -

#15 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9258
  • View blog
  • Posts: 34,735
  • Joined: 12-June 08

Posted 07 November 2011 - 12:12 PM

Yeah.. there's a bit at the bottom that indicates this.

Quote

(NOTE: this is an extremely simple PM system with NO security against SQL Injection. If you wish to prevent SQL Injection, I suggest you read some tutorials.)

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2