Trying to use MySQL fields as data constraint

Question

Trying to use MySQL fields as data constraint

I'll try to keep this as concise as possible, but any help is greatly appreciated. My skill level is slightly above the minimum PHP / MySQL, so I am using Dreamweaver CS6 to try and get my site up and running. I need to restrict the data returned from the database to the user who created the record, so I wanted to reuse the login information to keep track of who enters the data.

<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_DLP_RPG, $DLP_RPG);
$query_UserLoginForm = "SELECT * FROM users";
$UserLoginForm = mysql_query($query_UserLoginForm, $DLP_RPG) or die(mysql_error());
$row_UserLoginForm = mysql_fetch_assoc($UserLoginForm);
$totalRows_UserLoginForm = mysql_num_rows($UserLoginForm);
?>
<?php
// *** Validate request to login to this site.
if (!isset($_SESSION)) {
  session_start();
}

$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($_GET['accesscheck'])) {
  $_SESSION['PrevUrl'] = $_GET['accesscheck'];
}

if (isset($_POST['UserLogin'])) {
  $loginUsername=$_POST['UserLogin'];
  $password=$_POST['UserPass'];
  $MM_fldUserAuthorization = "";
  $MM_redirectLoginSuccess = "main.php";
  $MM_redirectLoginFailed = "UserRegistration.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_DLP_RPG, $DLP_RPG);
  
  $LoginRS__query=sprintf("SELECT user_login, user_pass FROM users WHERE user_login=%s AND user_pass=%s",
    GetSQLValueString($loginUsername, "text"), GetSQLValueString($password, "text")); 
   
  $LoginRS = mysql_query($LoginRS__query, $DLP_RPG) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
     $loginStrGroup = "";
    
	if (PHP_VERSION >= 5.1) {session_regenerate_id(true);} else {session_regenerate_id();}
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;	      

    if (isset($_SESSION['PrevUrl']) && false) {
      $MM_redirectLoginSuccess = $_SESSION['PrevUrl'];	
    }
    header("Location: " . $MM_redirectLoginSuccess );
  }
  else {
    header("Location: ". $MM_redirectLoginFailed );
  }
}
?>
<!doctype html>
<html>
<head>
  </head>

<body>
<div class="container">
  <div class="header"><a href="#"><img src="" alt="Insert Logo Here" name="Insert_logo" width="180" height="90" id="Insert_logo" style="background-color: #C6D580; display:block;" /></a> 
    <!-- end .header --></div>
  <div class="sidebar1">
    <ul class="nav">
      <li><a href="character_list.php">My Characters</a></li>
      <li><a href="#">Link two</a></li>
      <li><a href="#">Link three</a></li>
      <li><a href="#">Link four</a></li>
    </ul>
    <form action="<?php echo $loginFormAction; ?>" method="POST" name="UserLoginForm" id="UserLoginForm">
      <table width="200" border="1">
        <tr>
          <td>Username:</td>
        </tr>
        <tr>
          <td><label for="UserLogin"></label>
          <input name="UserLogin" type="text" id="UserLogin" size="28"></td>
        </tr>
        <tr>
          <td>Password:</td>
        </tr>
        <tr>
          <td><span id="sprypassword1">
            <label for="UserPass"></label>
            <input name="UserPass" type="password" id="UserPass" size="28">
          <span class="passwordRequiredMsg">A value is required.</span></span></td>
        </tr>
        <tr>
          <td><input type="submit" name="UserLoginSubmit" id="UserLoginSubmit" value="Submit"></td>
        </tr>
      </table><input name="user_status" type="hidden" value="">
    </form>
    <p>&nbsp;</p>
    <p><a href="UserRegistration.php">Register</a></p>
    <!-- end .sidebar1 --></div>
  <div class="content">
    <h1>Please login to proceed</h1>
    <p>This is a testing site only, no guarantees of security so watch yourself</p>
    <!-- end .content --></div>
  <div class="footer">
    <p>This .footer contains the declaration position:relative; to give Internet Explorer 6 hasLayout for the .footer and cause it to clear correctly. If you're not required to support IE6, you may remove it.</p>
    <!-- end .footer --></div>
  <!-- end .container --></div>
</body>
</html>
<?php
mysql_free_result($UserLoginForm);
?>

Run code Hide result

Thus, the above login information. The database is rpg_test and the table is users, the corresponding fields I'm looking for to track are user_id and user_login. As you might expect, user_id is the integer primary key and user_login is the alphanumeric username. The page uses this to log into other pages and appears to hold a variable that includes the actual username.

This is an example of one of the pages of a user who is already logged in:

<?php require_once('Connections/DLP_RPG.php'); ?>
<?php
//initialize the session
if (!isset($_SESSION)) {
  session_start();
}

// ** Logout the current user. **
$logoutAction = $_SERVER['PHP_SELF']."?doLogout=true";
if ((isset($_SERVER['QUERY_STRING'])) && ($_SERVER['QUERY_STRING'] != "")){
  $logoutAction .="&". htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_GET['doLogout'])) &&($_GET['doLogout']=="true")){
  //to fully log out a visitor we need to clear the session varialbles
  $_SESSION['MM_Username'] = NULL;
  $_SESSION['MM_UserGroup'] = NULL;
  $_SESSION['PrevUrl'] = NULL;
  unset($_SESSION['MM_Username']);
  unset($_SESSION['MM_UserGroup']);
  unset($_SESSION['PrevUrl']);
	
  $logoutGoTo = "index.php";
  if ($logoutGoTo) {
    header("Location: $logoutGoTo");
    exit;
  }
}
?>
<?php
if (!isset($_SESSION)) {
  session_start();
}
$MM_authorizedUsers = "0";
$MM_donotCheckaccess = "true";

// *** Restrict Access To Page: Grant or deny access to this page
function isAuthorized($strUsers, $strGroups, $UserName, $UserGroup) { 
  // For security, start by assuming the visitor is NOT authorized. 
  $isValid = False; 

  // When a visitor has logged into this site, the Session variable MM_Username set equal to their username. 
  // Therefore, we know that a user is NOT logged in if that Session variable is blank. 
  if (!empty($UserName)) { 
    // Besides being logged in, you may restrict access to only certain users based on an ID established when they login. 
    // Parse the strings into arrays. 
    $arrUsers = Explode(",", $strUsers); 
    $arrGroups = Explode(",", $strGroups); 
    if (in_array($UserName, $arrUsers)) { 
      $isValid = true; 
    } 
    // Or, you may restrict access to only certain users based on their username. 
    if (in_array($UserGroup, $arrGroups)) { 
      $isValid = true; 
    } 
    if (($strUsers == "") && true) { 
      $isValid = true; 
    } 
  } 
  return $isValid; 
}

$MM_restrictGoTo = "index.php";
if (!((isset($_SESSION['MM_Username'])) && (isAuthorized("",$MM_authorizedUsers, $_SESSION['MM_Username'], $_SESSION['MM_UserGroup'])))) {   
  $MM_qsChar = "?";
  $MM_referrer = $_SERVER['PHP_SELF'];
  if (strpos($MM_restrictGoTo, "?")) $MM_qsChar = "&";
  if (isset($_SERVER['QUERY_STRING']) && strlen($_SERVER['QUERY_STRING']) > 0) 
  $MM_referrer .= "?" . $_SERVER['QUERY_STRING'];
  $MM_restrictGoTo = $MM_restrictGoTo. $MM_qsChar . "accesscheck=" . urlencode($MM_referrer);
  header("Location: ". $MM_restrictGoTo); 
  exit;
}
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_DLP_RPG, $DLP_RPG);
$query_UserLoginForm = "SELECT * FROM users";
$UserLoginForm = mysql_query($query_UserLoginForm, $DLP_RPG) or die(mysql_error());
$row_UserLoginForm = mysql_fetch_assoc($UserLoginForm);
$totalRows_UserLoginForm = mysql_num_rows($UserLoginForm);

mysql_select_db($database_DLP_RPG, $DLP_RPG);
$query_PlaySystem = "SELECT play_systems.play_system FROM play_systems";
$PlaySystem = mysql_query($query_PlaySystem, $DLP_RPG) or die(mysql_error());
$row_PlaySystem = mysql_fetch_assoc($PlaySystem);
$totalRows_PlaySystem = mysql_num_rows($PlaySystem);

mysql_select_db($database_DLP_RPG, $DLP_RPG);
$query_characters = "SELECT * FROM characters WHERE characters.character_owner";
$characters = mysql_query($query_characters, $DLP_RPG) or die(mysql_error());
$row_characters = mysql_fetch_assoc($characters);
$totalRows_characters = mysql_num_rows($characters);
?>
<!doctype html>
<html>
<head>
  </head>

<body>

<div class="container">
  <div class="header"><a href="#"><img src="" alt="Insert Logo Here" name="Insert_logo" width="180" height="90" id="Insert_logo" style="background-color: #C6D580; display:block;" /></a> 
    <!-- end .header --></div>
  <div class="sidebar1">
    <ul class="nav">
    
      <li><a href="#">My Characters</a></li>
      <li><a href="new_character1.php">New Character</a></li>
      <li><a href="#">Link three</a></li>
      <li><a href="#">Link four</a></li>
    </ul>
    <p><a href="<?php echo $logoutAction ?>">Logout</a></p><br> I should come up with a way to show this only if you're logged in<br>

    <!-- end .sidebar1 --></div>
  <div class="content">
    <h1>List of characters</h1>
    <p>This page should list all of your characters, and just your characters.</p>
    <p>Edit and delete buttons should be included.</p>
    <p>&nbsp;</p>
    <table border="1">
      <tr>
        <td>Name:</td>
        <td>Type:</td>
        <td>System:</td>
        <td>Owner:</td>
      </tr>
      <?php do { ?>
        <tr>
          <td><?php echo $row_characters['character_name1']; ?></td>
          <td><?php echo $row_characters['character_occupation']; ?></td>
          <td><?php echo $row_characters['play_system']; ?></td>
          <td><?php echo $row_characters['character_owner']; ?></td>
        </tr>
        <?php } while ($row_characters = mysql_fetch_assoc($characters)); ?>
    </table>
<!-- end .content --></div>
  </body>
</html>
<?php
mysql_free_result($UserLoginForm);

mysql_free_result($PlaySystem);

mysql_free_result($characters);
?>

Run code Hide result

What I wanted to do was have an Owner field in the html table where the characters will only be displayed by characters belonging to the person who created them. I would ideally limit its user_id field to whatever the login tracking is using to access the page. I am assuming this is some kind of constant variable that I can hopefully call and insert as data when the table is updated.

Is there such a variable? I keep seeing $ UserName and other stuff, but maybe I'm going in circles. Any help would be appreciated.

EDIT: From what I can find on the site, I need to use a session variable.

I did print_r ($ SESSION) on one of the pages and it gives:

Array ([PrevUrl] => /rpg/character_list.php [MM_Username] => joecook [MM_UserGroup] =>)

The login for MM-Username is what would fit in the user_login field, but the table below shows that the field used by this table is user_id. I am registered as user_id = 2 and I only want to see entries that are specific to me.

    <table border="1">
      <tr>
        <td>Name:</td>
        <td>Type:</td>
        <td>System:</td>
        <td>Owner:</td>
      </tr>
              <tr>
          <td>Fuzz Duck</td>
          <td>1</td>
          <td>Palladium Megaverse</td>
          <td>1</td>
        </tr>
                <tr>
          <td>another heresy test for owner</td>
          <td>17</td>
          <td>Heresy Game Engine</td>
          <td>2</td>
        </tr>
                <tr>
          <td>Another Heresy test</td>
          <td>17</td>
          <td>Heresy Game Engine</td>
          <td>2</td>
        </tr>
            </table>

Run code Hide result

This is the previous form that populates the above table with data if that helps:

  <div class="content">
    <h1>Starting a new character</h1>
    <p>The first thing to do when starting a new character is to select the play system from a drop down list</p>
    <form action="<?php echo $editFormAction; ?>" method="POST" name="PlaySystemForm" id="PlaySystemForm">
      <table width="500" border="1">
        <tr>
          <th width="129" scope="row">System:</th>
          <td width="355"><label for="play_system2"></label>
            <select name="play_system" id="play_system2">
              <?php
do {  
?>
              <option value="<?php echo $row_PlaySystem['play_system']?>"><?php echo $row_PlaySystem['play_system']?></option>
              <?php
} while ($row_PlaySystem = mysql_fetch_assoc($PlaySystem));
  $rows = mysql_num_rows($PlaySystem);
  if($rows > 0) {
      mysql_data_seek($PlaySystem, 0);
	  $row_PlaySystem = mysql_fetch_assoc($PlaySystem);
  }
?>
          </select></td>
        </tr>
        <tr>
          <th scope="row">Name:</th>
          <td><span id="sprytextfield1">
          <label for="character_name"></label>
          <input name="character_name" type="text" id="character_name" size="25" maxlength="128">
          <span class="textfieldRequiredMsg">A value is required.</span><span class="textfieldMinCharsMsg">Minimum number of characters not met.</span><span class="textfieldMaxCharsMsg">Exceeded maximum number of characters.</span></span></td>
        </tr>
        <tr>
          <th scope="row">Type:</th>
          <td><label for="character_type1"></label>
            <select name="character_type1" id="character_type1">
              <?php
do {  
?>
              <option value="<?php echo $row_character_type['character_type1_id']?>"<?php if (!(strcmp($row_character_type['character_type1_id'], $row_PlaySystem['play_system']))) {echo "selected=\"selected\"";} ?>><?php echo $row_character_type['character_type1']?></option>
              <?php
} while ($row_character_type = mysql_fetch_assoc($character_type));
  $rows = mysql_num_rows($character_type);
  if($rows > 0) {
      mysql_data_seek($character_type, 0);
	  $row_character_type = mysql_fetch_assoc($character_type);
  }
?>
          </select></td>
        </tr>
      </table>
      <input name="CharacterOwner" type="hidden" id="CharacterOwner" value="<?php echo $row_UserLoginForm['user_id']; ?>">
      <p>
        <input type="submit" name="NewCharacterSubmit" id="NewCharacterSubmit" value="Create character">
      </p>
      <input type="hidden" name="MM_insert" value="PlaySystemForm">
    </form>

Run code Hide result

+3

php mysql login dreamweaver restrictions

Joe cook May 25 '15 at 3:51

source to share

1 answer

Sean · Accepted Answer · 2015-05-25T05:00:02+0000

You need to add a condition to your query i.e.

WHERE characters.character_owner = users.user_id

Since you are only storing user_login

/ ( $_SESSION['MM_Username']

) and not user_id

, you will need to use a subquery to get user_id

. Try to change -

$query_characters = "SELECT * FROM characters WHERE characters.character_owner";

to

$query_characters = "SELECT * FROM characters WHERE characters.character_owner = 
    (SELECT user_id FROM users WHERE user_login = '{$_SESSION['MM_Username']}')";

user_login

Trying to use MySQL fields as data constraint

More articles: