MySQL Database misbehaving on updating with php

Question

My system is misbehaving whenever I change the username. In my database the username is the primary key.

See screenshot below. This is what it's doing:

If I log out and log in everything will show. It seems like it's failing to connect to the database whenever I change the username.

This is my code for updating the profile:

<?php

session_start();

require 'config.php';

$user=$_SESSION['lec']; 

$update = $conn->real_escape_string($_POST['update']);
$phone = $conn->real_escape_string($_POST['phone']);
$email = $conn->real_escape_string($_POST['email']);
$pswd = $conn->real_escape_string($_POST['pswd']);
$pswd2 = $conn->real_escape_string($_POST['pswd2']);
$name = $conn->real_escape_string($_POST['name']);
$username = $conn->real_escape_string($_POST['username']);
$dept = $conn->real_escape_string($_POST['dept']);

if ($pswd!=$pswd2)
{
    $_SESSION["message"] = "Passwords do not match. Please try again.";
    header("location:lecprofile?error");
}

else
{
    $pswd = password_hash($pswd, PASSWORD_BCRYPT);

    $sql1= "UPDATE lecturer SET email='$email' WHERE username='$user'";
    $sql2 = "UPDATE lecturer SET phonenumb='$phone' WHERE username='$user'";
    $sql4 = "UPDATE lecturer SET username='$username' WHERE username='$user'";
    $sql5 = "UPDATE lecturer SET name='$name' WHERE username='$user'";
    $sql6 = "UPDATE lecturer SET dept='$dept' WHERE username='$user'"; 
    $result = $conn->query($sql1) && $conn->query($sql2) && $conn->query($sql4) && $conn->query($sql5) && $conn->query($sql6);

if (empty($pswd) || empty($pswd2))
{
    header("location:lecprofile");
}
else
{
        $sql3= "UPDATE lecturer SET pswd='$pswd' WHERE username='$user'";

}
    $passresult=$conn->query($sql3);
        if($result==TRUE || $passresult== TRUE)
        {
        $_SESSION['message'] = "Profile Updated!";
        header("location:lecprofile?done");
        }
}

if(isset($_POST['submit'])){
 //Targeting Folder
 $target="propics/";
 $target=$target.basename($_FILES['propic']['name']);
 //Getting Selected image Type
 $type=pathinfo($target,PATHINFO_EXTENSION);
 //Allow Certain File Format To Upload
 if($type!='jpg' && $type!='jpeg' && $type!='JPG' && $type!='PNG' && $type!='GIF'  && $type!='png' && $type!='gif'){
  echo "Only JPG,JPEG,PNG and GIF file format are allowed to Upload";
 }
 else{
    //lmit file size
    if ($_FILES['propic']['size'] > 5000000) {
        echo "Sorry, your image is too large.";        
        }
 
 else{
  //checking for Exsisting image Files
  if(file_exists($target)){
   echo "File Already Exist";
   }else{
   
   //Moving The image file to Desired Directory
  $upload_success=move_uploaded_file($_FILES['propic']['tmp_name'],$target);
  if($upload_success==TRUE){
   //Getting Selected image Information

    //renaming the file to the username to avoid conflict when uploading
    $name=$user.".".$type;
    rename("propics/".$_FILES['propic']['name'],"propics/".$name);  
    //                   
    $sql= "UPDATE lecturer SET propic='$name' WHERE username='$user'";
   $result = $conn->query($sql);
   if($result==TRUE){
    clearstatcache();
    $_SESSION['message']= "Profile picture updated successfully!";
   header("location:lecprofile.php?done");
   }
  }
  }
  }
 }
}


?>

Code for displaying data in the database:

<?php
    
   require 'config.php';

    if ($_SESSION['logged_in'] != 1 ){
        $_SESSION['message'] = "Please login!"; 
         header("location: /prac?error");
       }
      else
      { 
        $user=$_SESSION['lec'];
      }
     if (isset($_GET['done']))
     {
        include 'done.php';
     }
     if (isset($_GET['error']))
     {
        include 'error.php';
     }
    ?>
  
    <table >
        <tr>
            <td width="20%" align="center">
                <?php $sql = "SELECT propic FROM lecturer WHERE username='$user'";
                      $result = $conn->query($sql);
                      $row = $result->fetch_assoc();
                ?>
                <img id="mainpropic" src="propics/<?php echo $row['propic'];?>" width="200px" height="200px" >
              </td>
                <td >&nbsp </td>

            <td>
            <form action="" method="post">
                <table cellpadding="2" >
                    <tr>
                        <td>

                            User Name:
                        </td>
                        <td>
                            <?php echo $_SESSION['lec'];?>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            EC Number: 
                        </td>
                        <td>
                            <?php 
                            $sql = "SELECT ecnumber FROM lecturer WHERE username='$user'";
                            $result = $conn->query($sql);
                            $row = $result->fetch_assoc();
                             echo $row["ecnumber"];?>
                        </td>
                    </tr> 
                    <tr>
                        <td>
                            Name:
                        </td>
                        <td>
                            <?php 
                            $sql = "SELECT name FROM lecturer WHERE username='$user'";
                            $result = $conn->query($sql);
                            $row = $result->fetch_assoc();
                             echo $row["name"];
                             ?>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Email:
                        </td>
                        <td>
                            <?php 
                            $sql = "SELECT email FROM lecturer WHERE username='$user'";
                            $result = $conn->query($sql);
                            $row = $result->fetch_assoc();
                             echo $row["email"];?>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Phone Number:
                        </td>
                        <td>
                            <?php 
                            $sql = "SELECT phonenumb FROM lecturer WHERE username='$user'";
                            $result = $conn->query($sql);
                            $row = $result->fetch_assoc();
                             echo $row["phonenumb"];?>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Department:
                        </td>
                        <td>
                            <?php 
                            $sql = "SELECT dept FROM lecturer WHERE username='$user'";
                            $result = $conn->query($sql);
                            $row = $result->fetch_assoc();
                             echo $row["dept"];?>
                        </td>
                    </tr>
                    
                    <tr>
                        
                    </tr>
                    <tr>
                        <td>
 
                            <input type="button" value="Edit Profile" class="btn btn-primary btn-sm" data-toggle="modal" data-target="#Modal">
                        </td>
                        <td>
                        </td>
                    </tr>
                    
                </table>

"If the path has more than one extension, PATHINFO_EXTENSION returns only the last one" What happens if have a file called image.php\.0.jpeg on a older php version you would had have a problem because move_uploaded_file() would make a file named image.php because of the null byte (\0) meaning i could have uploaded a php file on the server.. Watch out for vulnerabilities like these on newer PHP versions this move_uploaded_file vulnerability is fixed i believe PHP 5.6.5+ is safe for this exploit. — Raymond Nijland
– Raymond Nijland, Commented Oct 2, 2018 at 19:10
Besides you should also read about preventing SQL injections. — Raymond Nijland
– Raymond Nijland, Commented Oct 2, 2018 at 19:11
yes i am aware of that, for now i need to solve the main problem that is database not connecting after changing the primary key in the database. — Rayobeats
– Rayobeats, Commented Oct 2, 2018 at 19:14
the path is not the problem here, if i change the profile picture i have no problem , but the username only. — Rayobeats
– Rayobeats, Commented Oct 2, 2018 at 19:21

Moshi · Accepted Answer · 2018-10-02 19:54:38Z

4

When you do the update you are using the current username stored in sessions. The moment the update is done the username changes, so the old username used to read the data no longer exists in the database. When you log out and log back in the session variable lec is updated to the new username enabling you to read the data.

Try using a primary key that wont change on updates.

answered Oct 2, 2018 at 19:54

Moshi

862 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dkretz Over a year ago

Yeah. A mutable primary key is often problematic.

Rayobeats Over a year ago

Makes sense now, well explained. Thank you

Rayobeats Over a year ago

also when i change all the fields at one go some are not entered in the database what could be the case on my code?

Moshi Over a year ago

what query are you using for your updates and whats your database table structure

Rayobeats · Accepted Answer · 2018-10-02 20:26:16Z

0

I fixed this bug in the code. I assigned the new username to the the global $_SESSION variable of that account like this:

if($result==TRUE || $passresult== TRUE)
		{
      $_SESSION['lec']=$username;
      $_SESSION['message'] = "Profile Updated!";
      header("location:lecprofile?done");
		}
}

answered Oct 2, 2018 at 20:26

Rayobeats

1512 silver badges12 bronze badges

Collectives™ on Stack Overflow

MySQL Database misbehaving on updating with php

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related